Tamsen Haught

Posted & filed under Data Analytics for Associations, Data Visualization, Tableau.

Tableau Public is a free tool to share your visualizations with the world.  One question we often here is how do you refresh data for a Tableau Public visualization.  Tableau Public will do an automatic, nightly refresh data of Google Sheets data, no extra work on your side.   However, there is a bit of additional work required in order to automatically refresh your Google Sheets data.  In our example we are pulling data from a AWS Redshift database using Python version 3.6

Google Sheets Set-Up

This set-up will create the credentials necessary to refresh your Google Sheets data.

  • Create a project in Google Developers Console https://console.developers.google.com/flows/enableapi?apiid=drive. Create an account if you have not already done so.
  • Click on Library and search for the Google Drive API, and verify the API is enabled.
  • Next, click on Credentials. Then click on create credentials -> OAuth Client ID, choose “Other” as the application type and enter a name for your credentials.  You will need these credentials to connect to Google Sheets.
  • Now, download the client ID you just created. Move this file to your working directory and rename it client_secret.json.  For example, C:\xxxx\xxxx\.credentials
  • Go into your Google Drive account and create a blank google sheet and copy the File ID from the url. https://docs.google.com/spreadsheets/d/1kadJ8y1_VZnKGqPx6ohA3F-OdHtablmyzr2ZF6nuH18/edit#gid=0.  You will need this ID for your Python script.

Python Scripts

  • For this script you will need to install both psycopg2 and google-api-python-client libraries.
  • Now create your Python script which begins with getting your credentials. You can find this at the Google Sheets developers guide on Step 3.
  • Once you have your get_credentials function in place, you will need to connect to your Redshift environment and insert your SQL query. You can connect to Redshift using the psycopg2 package.
  • We then use csv_writer to write the database query to a CSV file.
  • Now overwrite your Google Sheets file with the new data. This requires the Google Drive API.
  • When you run your script, a browser window will open and ask you to enable authentication. This will ensure your code has rights to write to Google Sheets.  This will only happen the first time this is run.
  • Once you have this set-up, you can schedule the Google Sheets refresh to happen prior to the Tableau Public refresh, which occurs around 6 am ET.

I hope this helps you get started with utilizing Tableau Public’s built in data refresh so you can share your visualizations with the world.