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
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"
Freakin’ awesome. Thanks!
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.