Don’t have access to a database server but want to keep your data dynamic? Google Sheets and Tableau team up to make this so.
Recently, I discovered it is possible to use Google Sheets to scrape data from a webpage. The solution is fairly simple and intuitive. Not only that, but this can be used to auto-refresh a data source that Tableau is connected to.
How to do it?
The first task is to find a data source that updates daily (at least). A few ideas come to mind: Stocks, Petroleum Prices, Weather Data etc.
For this example, I am using Coronavirus Data that is aggregated by Johns Hopkins University Center for Systems Science and Engineering (JHU CCSE). The data refreshes daily in a GitHub repository that is sourced from multiple data sources.
Copying the URL from above, I can use the IMPORTHTML function on Google Sheets to scrape the data table from the website.
The function syntax used looks like this:
=IMPORTHTML(“URL”, “query”, “index”)
The help article is a good place to start if you get stuck or are interested in any of the other functions you can perform in Google Sheets.
The query will be either “list or “table”.
Index refers to which query will be used.
In our example, I want to query “table 1” from the URL used above for the Coronavirus Data.
To force our Google Sheets to automatically refresh I am going to add the following to my expression:
=if(Minute(Now())=Minute(Now()),
My expression will now look like this:
=if(Minute(Now())=Minute(Now()),IMPORTHTML(“https://github.com/RamiKrispin/coronavirus-csv/blob/master/coronavirus_dataset.csv”, “table”, “1”).
Google Sheets will now populate the data in tabular format. One last setting must be set to make sure the data refreshes. Navigate to File > Spreadsheet Settings and change the ‘Recalculation’ field to “On change and every hour”.
Now, we are all set to pull the data into Tableau!
Google Sheets Server Connector
The Google Sheets Connector can be selected from the connect data screen. After clicking on the button a new webpage will open to allow Tableau to connect to your Google account.
After this has been authenticated I can select any sheet that is available on my account. Tableau will now load my sheet as the data source which will refresh every time I open my Tableau Workbook.
From this point on, it functions like any other data source connection. I have uploaded my dynamic dashboard for Coronavirus on Tableau Public
My design was inspired by the “Live Dashboard” from Johns Hopkins CSSE.
In the future, given the situation with the nCOV virus, I would like to incorporate a livestock dashboard to investigate any fluctuations from the virus.
