I’m enjoying the fire here in Telluride, Colorado and thought I’d put up a fun blog post while nursing my sore arms. I had the idea for this a few weeks back while working with a great new client in Columbus, Ohio and have finally found some time to work on it in between climbing Horsetail Falls, playing in the Ouray Ice Park, and doing some ski touring along the Uncompahgre River. The project is a quick, cheap, and effective DIY Google Drive Connector for use in Tableau Desktop and Tableau Server. The technique I’m showing below is a nice proof-of–concept using off the shelf software. If I had more time, it would be nice to hook into the Google Drive API and build a more customized version. But I digress – here goes:
One of the questions I get all the time is about a native data connection to spreadsheets kept in Google Documents (or Google Drive). Well, the short answer is that there isn’t one. In lieu of a native connection (and no, Tableau 8, won’t include one either), I wanted to share this nice little trick that will let us use Google Spreadsheets to drive workbooks in Live time. I know I and everyone else will be talking about Tableau 8 quite a lot in the coming weeks, and the nice thing about this technique is that it will work in Tableau 6, 7, or 8.
The key to this technique is essentially a work-around. Since we don’t have the ability to connect to the Google Doc up in the Cloud, we need to first bring it down here to earth. I did some snooping around the Internets and found a nice little program called SyncDocs. The program’s primary function is to automatically sync local documents up to the Cloud, but I’m going to take advantage of the secondary functionality that syncs from the Cloud down to a localized Excel document. I’m not in the business of hawking others’ wares (I’ll hawk mine all day), but the price is right ($20/yr per Google Account) and the program has worked very well in my tests.
I logged onto Google Drive and created a new virtual folder with a pair of spreadsheets in it for this test, and then set up SyncDocs with my Google login/password. Configuring SyncDocs to automatically pull down that virtual folder to my local drive only took a few minutes and I was in business. Changes in the Cloud seem to take 5-30 seconds to sync down to my local machine with the default settings. Good enough for me – as I mentioned above, if I had more time for this project, I could probably utilize the Google Drive Change List to cut that down a bit.
The next step was to add Tableau into the mix; so I pointed Tableau Desktop at the local Excel version of the document that SyncDocs is creating with a Live Connection (so my Tableau queries are passed directly to the Excel doc). Now, I’ve got a complete loop – I can make changes in the Cloud through my browser or Google Drive on my PC (or Phone), and the changes will automatically be synced to the local sheet, which in turn will populate my visualization with new data.
I built out a few Viz to prove out the system and found a few fun uses. Starting out simply with a quick map based on the spreadsheet worked out well, so I extended the concept to build a dashboard that integrated some Viz with the Google Doc itself embedded in a Web Page object. Even more fun, I was able to take that dashboard and push it to my local Tableau Server where it worked like a charm.
Take a look at the video below to see the results – I’m quite pleased with the results and look forward to any comments. Meanwhile, I’m going to head out for a bit of skate skiing and maybe even a glass of whisky by the fire. Happy New Year everyone!