8/18/2016
Connecting Google Sheets with Tableau 10
Tableau 10 was released yesterday and there are loads of great new features. I love the new Tableau fonts, the cross-database joins and the new clustering capabilities, but one of the most exciting new features is the way it connects to Google Sheets. This is very exciting for a number of reasons. Back in 2014, Tara Walker wrote a blog post How to Turn a Viz into a Poll in Tableau where she used Google Docs to record the response to a poll from Tableau Public. In other words writing a response back to Google Sheets from Tableau Public.
Last year I wrote to her in an email, "Think about what could be done if we could get Tableau Public to read directly from Google Docs and refresh on command. You'd basically have a near real-time database connection on the web with the ability to read and write back and forth."
Well guess what? That day has come. Tableau 10 not only connects to Google Sheets, but it also manages a daily refresh of the data and a forced refresh upon request by the author. So let's see what we can do by reading some data from Google Sheets. I am going to demonstrate by creating a viz that will pull real-time statistics directly from Twitter into Google Sheets and connect that to Tableau.
Step 1 - Build the Google Sheet:
To start, I created a Google Sheet and compiled a list of the Twitter handles
1.) Create new Google Sheet
2.) Compile a list of Names in column A and Twitter handles in column B. I am going to use the list of Tableau Zen Masters.
3.) Concatenate a link to the profile to use in the viz. (Optional)
The formula in C2 is =CONCAT("http://twitter.com/",B2)
3.) Create a IMPORTHTML formula in column D. Use the mobile version of Twitter and return the 3rd table, which has the statistics for the user. It will return 3 columns, one for Tweets, Following and Followers; columns D, E and F respectively.
The formula in D2 is =if(Minute(Now())=Minute(Now()),IMPORTHTML("http://mobile.twitter.com/"&B2,"table",3),"")
Update: Some people noticed issues with the auto-refreshing of the IMPORTHTML function. Adding an IF statement using minute(now()) = minute(now()) should fire off the formula in accordance with the Google Sheets setting (#7 below).
4.) Parse the number from these text columns in columns G, H and I.
Example formula is =left(F2,find(char(10),F2)) - this formulas parses the number of followers.
5.) Copy and paste these formulas down the sheet for all users.
6.) Click Share in the top right-hand corner, set the setting so this document can be viewed by others and copy the URL.
7.) Click "File" -> "Spreadsheet setting" and set the "recalculate" to "On change and every hour". This will keep the data refreshed, reading directly from Twitter.
You now have a database of Twitter statistics for a list of Twitter handles and it will update hourly. My finished Google Sheet is here.
Step 2 - Connect to Tableau:
1.) This is super simple. Just open Tableau and select a new database source as Google Sheets.
2.) Enter your credentials and select the Google Sheet or enter the URL that you copied previously.
3.) The data will now load into Tableau.
4.) Build your viz with the data from the Google Sheet.
Step 3 - Publish to Tableau Public and Keep Data in Sync:
1.) When you publish to Tableau Public, make sure to check the box "Keep my data in sync with Google Sheets and embed my Google credentials". This will keep the data updated, refreshing daily.
2.) Once on Tableau Public you can request an update of the data by clicking "Request Update".
Below is a visualization putting the pieces together. I built a sheet of the twenty-one Tableau Zen Masters with each of their Twitter handles. The Google Sheet then captures the number of Tweets, Following and Followers for each of them. This data is refreshed hourly in the Google Sheet and is refreshed daily on Tableau Public or on demand by the author.
I think you'll see this is a very powerful tool. The possibilities are endless.
Here are a few additional examples. Enter these formulas in cell A1:
Currency Exchange Rates: =transpose(IMPORTHTML("http://www.xe.com","table",1))
You now have a table of the current currency exchange prices that can be read by Tableau in a visualization. The same method as described above can be used to refresh the data.
Current weather in a certain city: =IMPORTHTML("https://www.wunderground.com/us/oh/cincinnati/zmw:45207.1.99999","table",2)
Historical weather for a city (ex. Cincinnati, OH): =ImportHtml("http://www.accuweather.com/en/us/cincinnati-oh/99501/august-weather/346835?view=table","table",1)
These are just a few examples of some of the things that can be brought into Google Sheets. There is a also a IMPORTXML function in GoogleSheets that will read XML files and the XPath as well as various add-ins that will let you connect to all sorts of data.
I hope you find this information helpful. If you have any questions feel free to email me at Jeff@DataPlusScience.com
