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!
Google Sheets has some cool functions which allow users to import data to Google Sheets from a webpage. The main IMPORT functions are:
IMPORTDATA: fetches .csv or .tsv formatted data from a webpage.
IMPORTHTML: fetches data from a published table on a webpage.
IMPORTXML: fetches data from data structured in a variety of formats including XML, HTML, CSV, TSV and RSS XML feeds.
IMPORTFEED: fetches data from a RSS or ATOM feed.
IMPORTRANGE: fetches a range of cells from a specified spreadsheet.
More on all these functions here in Google’s support documents.
As long as you are opening the Google Sheet which contains the IMPORT function on a regular basis, Google will continue to run the import function and keep your spreadsheet updated. However, if you aren’t regularly opening the spreadsheet, Google Sheets will stop running the import leaving you with old, stale data. Check out the video below to learn how you can utilize simple code in Google Apps Script to ensure the IMPORT function runs routinely, even if you are not opening the spreadsheet.
Want to learn how to integrate your Google Sheet with Tableau and keep data on Tableau Public updated daily? Check out our recording. No Python. No Problem. The Secret to Living Connections on Tableau Public.
Here’s an example of the Javascript code from the video:
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);
var sheetName = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("OAK"); var queryString = Math.random(); var cellFunction = '=IMPORTHTML("https://www.baseball-reference.com/teams/OAK/2021-schedule-scores.shtml","table",1)';
sheetName.getRange('A1').setValue(cellFunction); }
Thanks for following along! To learn more, check out our upcoming classes, webinars and office hours here.