By: Eric Parker
Eric Parker lives in Seattle and has been teaching Tableau and Alteryx since 2014. He's helped thousands of students solve their most pressing problems. If you have a question, feel free to reach out to him directly via email. You can also sign up for a Tableau Office Hour to work with him directly!
I’ve known for several years that Google Sheets is the only data source which Tableau Public can automatically refresh for users. Until a few weeks ago, I had never tried to set up a living connection between Tableau Public and a routinely updated Google Sheet. It turns out that Tableau Public is good at holding up it’s end of the bargain, but keeping Google Sheets updated isn’t as simple.
After I noticed my Tableau Public dashboards stop updating, I did some digging. Resources on the web mentioned that functions like IMPORTHTML should update every 15 minutes or sooner. My own research demonstrated that IMPORT functions (e.g. IMPORTHTML, IMPORTXML, IMPORTDATA) in Google Sheets only update automatically if the Google Sheet is left open. Otherwise, the sheet caches earlier data and updates when opened by the user.
That doesn’t work well unless you want to keep your Google Sheet constantly open to keep your Tableau Public dashboard up to date. I pieced together some concepts from a few resources and was able to write a custom script which web scrapes data on an hourly basis to keep a Google Sheet updated.
On Tuesday (5/11/2021), I hosted a YouTube Live session called “No Python? No Problem. The Secret to Living Connections on Tableau Public) to walk through the process. You can view it in its entirety below!
Here are some resources mentioned in the video:
IMPORTHTML Function
=IMPORTHTML("https://www.baseball-reference.com/teams/SEA/2021-schedule-scores.shtml","table",1)
Google Apps Script
function gethtmlData() { var sheetName = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("SEA"); var queryString = Math.random(); var cellFunction = '=IMPORTHTML("https://www.baseball-reference.com/teams/SEA/2021-schedule-scores.shtml","table",1)';
sheetName.getRange('A1').setValue(cellFunction);
}
Tableau Public Dashboard Link
If this has been helpful, you’ll probably like our classes. Check out our upcoming sessions here!