Make beautiful charts using Google Docs and Tableau (pt.1 Automation)

Excel runs the world. Let me rephrase…Excel RUNS the WORLD. Okay, now that we’re in agreement, Google Spreadsheets are the Stephen Baldwin of the spreadsheet world yet are gaining popularity. The only real benefit I can see from them is the easy online collaboration and price (eg. free ninety-nine).

That said, Google Spreadsheets are awful places to try and make charts, we all know that Tableau is the mac daddy in this arena, so I thought perhaps it is time to explore a couple options of making the most out of your data living in Google Spreadsheets. I’d like to cover 2 methods today (I’m sure there’s more); Manual, and Automatic.

When I first learned to drive my mother took me to a parking lot in our old beat up Nissan pickup which was a stick shift. This first method for manually creating the charts is going to feel a lot like how you imagine that felt for me. The second method however will feel like the last time you rode in a private G5 jet (eg. like never before). Here goes, scroll down below the video for detailed instructions.

Manual (aka Stick)

This method is great for getting started, but if you plan to continue using a Google spreadsheet as your data source you’ll want to make the move the automatic method otherwise you’ll be stuck downloading stuff every time you want to update and you’re better than that!

  1. Find your Google Spreadsheet with the data you want to make beautiful with
  2. Download the spreadsheet as a Comma Separated Values (.csv) like so: http://www.screencast.com/t/Dv6kAnXj3Cf
  3. Open Tableau Desktop and drag the downloaded .csv on to the Tableau window
  4. Make beautiful with your data

Automatic

This method requires some coding skills, so if you’re uncomfortable with that do not fear, just let off the clutch slowly until you get it. Once you get it, this method will provide you a way to continually update your beautiful Tableau views using your Google spreadsheets as a source.

Setup Python on your Tableau Server:

  1. Download and install python: http://python.org/download/
  2. Download my code from github here: https://github.com/bsullins/gspread-csv
  3. Install ez_setup.py: https://pypi.python.org/pypi/setuptools
  4. Setup the gspread python api here: https://github.com/burnash/gspread

Setup Sync

  1. Open config.py in the folder where you downloaded gspread-csv and add your username and password
  2. Open download-spreadsheet.py in that same folder, and update the list of spreadsheets (docs’) to download
  3. Run the file and make sure it downloaded everything correctly
  4. Create a batch file (.bat) on your server to automatically run the sync (ex. gspread-sync.bat)
  5. Schedule this .bat file to run nightly (or more often) using windows scheduled tasks (ex. gspread-sync-scheduled-task.xml)

You now possess a freedom shared by few, be responsible with it, enjoy it and realize it is a privilege not a right 🙂

Cheers!

  • Jeff Buscher

    Ben,

    This is great. Thanks for doing it. Everything works for me with the old Google Sheets. However Google changed their URLs in the new version of Sheets and gspread needs to be updated to use the new URL format before it’ll work with the new version.

  • Juan Pablo

    ahh, it says invalid syntax when i run the module, how do i fix this?

Free 5 Day Video Course | Tableau Quick Start

Everything you need to become dangerous :)

x