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'"

 

Getting Data From Google Sheets

 

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"

 

2 thoughts on “Tableau Prep – Connecting to Google Sheets”
  1. Thanks so much for the helpful post – tons of great stuff here. For whatever reason, the CSV was not downloading in useable format for me, but I was able to use the URL for the google sheet when Published to the Web – in case that helps anybody else.

Leave a Reply

Your email address will not be published. Required fields are marked *