Automating a Tableau Public Dashboard

Manually updating Tableau dashboards isn't exactly hard. First you open up Tableau, next you open the workbook you wish to update, lastly you click on data in the toolbar, hover the dataset you wish to update and click refresh. Not hard, but doing it daily, can begin to be a pain and feel like a chore. Luckily! Tableau Public has google sheets as a datasource and allows you to keep the dashboard synced with the sheet daily updates. This will show you how to add a few lines to your pandas workflow to upload your dataframe to google sheets automatically.

For this you will need:
- A Google account
- A Tableau public account
- Knowledge of Python & Pandas

Step 1: Google Service Account

pygsheets docs have a great walkthrough on how to set up authentication. The method I use is through using a google service account. While following those steps, the project name doesn't really matter and the service account name will appear as the name that updated the google sheet. Once you download the service account key save it somewhere safe, NOT anywhere public. Lastly copy the email of the service account as you will need to add it to the google sheet allowing it to edit the workbook.

Step 2: Set up the Google Sheet

To start, set up the basics, create a new sheet, add the tabs & name them. Next format the columns however you want, number format, alignment, date format, font size anything that doesn't require changing a cell. There's no need to add column headers, you can do that from the pandas DataFrame. Next, add the service account email allowing it to edit the sheet. To do this from the google sheet, go to the top right -> click "share" -> "invite people" -> add email.

Step 3: Pyton Code

I assume you have your own data, so I'm going to make a basic sample DataFrame.

import pandas as pd
import pygsheets

# sample dataframe
numbers = [1, 5, 7, 5, 2, 6, 9, 8, 4]
letters = ['A', 'B', 'D', 'G', 'Z', 'Z', 'J', 'G', 'N']
df = pd.DataFrame({'letters': letters, 'numbers': numbers})

# google sheets authentication
creds = 'C:/path/to/your/service_file.json'
api = pygsheets.authorize(service_file=creds)
wb = api.open('[YOUR GOOGLE SHEET FILE NAME]')

# open the sheet by name
sheet = wb.worksheet_by_title(f'[YOUR SHEET NAME]')
sheet.set_dataframe(df, (1,1))

Simply replace the creds with your service file, the [YOUR GOOGLE SHEET] with the workbook name and [YOUR SHEET NAME] with the sheet tab name, the (1,1) is where the dataframe will begin, so A1. Obviously re-running all this code would accomplish nothing... As the sample data is hard-coded and will never change. However assuming your reading from a csv, excel or loading data from a database it would write over the existing data showing the new info. If for some reason your new data is smaller then your existing data make sure to add sheet.clear() prior to the set_dataframe or some of the old data would still exist.

Step 4: Make the Tableau Dashboard

To add data from google sheets, under connect -> google sheets a chrome tab will open requiring you to login. Then it will ask for permission click "Allow" and close the tab. You'll be provided a list of every sheet you have access to, find your sheet, connect to it and now you have your data, make your dashboard like normal. Now when you go to publish to Tableau Public you'll have a checkbox to keep the data synced and embed your google credentials which from looking at the xml appears to just be your login email name.

Common Issues with This

Tableau Public allows connecting to google sheets, and automatic refreshes. However this will only happen automatically once per day and it can't be scheduled for a specific time. This still eases updating though as instead of needing to open Tableau again you can simply go to the dashboard and there will be a button to "request update" which will update the dashboard within a few minutes. If you require more frequent updates, and don't wish to have to click request update. This would require a paid version of Tableau like server or online which allow scheduled refreshes of your data.

I've run into an issue when joining data, if you use a geojson/shapefile layer and join a google sheet to it. You don't get the option to automatically update the google sheet. I'm not sure if this happens with other file formats but a possible solution could be a blend rather than a join.

This still requires you to run the code on your own, however if you're on a Mac you can look into using CRON to schedule a python script. On Windows you can use the windows task scheduler to use a timed based trigger to run your python script. Personally the data I used this for updates once a day at 12pm so I just added some code to sleep my program till 12:05pm.

# wait till 12:05 then continue
target = pd.datetime.now().replace(hour=12).replace(minute=5)
while pd.datetime.now() < target:
    print(f"[status] waiting for 12pm", end='\r')
    time.sleep(60)