Tableau Prep – Connecting to Google Sheets

As of version 2019.1.3, Tableau Prep does not have a builtin Google sheets connector, but it is not a problem if you are running your flows from command line.  We  can can download the sheets data using below one liner.

powershell.exe "Invoke-WebRequest 'Google Sheet Link' -OutFile 'path/to/store/file'"

Here are the steps to get it working:

1. Create a direct link to your google sheet

We need to make sure that the access setting is ‘Anyone with the Link can view‘. Sharing with specific people is certainly possible, but then we need more code than the above one liner. Your link typically looks like https://docs.google.com/…/edit?usp=sharing , you need to convert it to https://docs.google.com/…/export?format=xlsx&usp=sharing format. Please pay special attention to the link, we are replacing edit?  with export?format=xlsx&

2. Download the File using command line

You can try below lines in your command line as an example. Permissions for this file is already configured to allow download for anyone with the  link.

powershell.exe "Invoke-WebRequest 'https://docs.google.com/spreadsheets/d/1zLo1wMo0UrcPaKTu0z28maABkbG8wasCzNXk_u9uMrE/export?format=xlsx&usp=sharing' -OutFile 'Titanic.xlsx'"

3. Create a flow that uses the downloaded file

4. Run the flow using a batch file like below

@echo off
powershell.exe "Invoke-WebRequest 'https://docs.google.com/spreadsheets/d/1zLo1wMo0UrcPaKTu0z28maABkbG8wasCzNXk_u9uMrE/export?format=xlsx&usp=sharing' -OutFile 'C:/Automation/Titanic.xlsx'"
call  "C:\Program Files\Tableau\Tableau Prep 2019.1\scripts\tableau-prep-cli.bat" -t "C:\Automation\sheets.tfl"