Data Viz Done Right

August 24, 2016

Tableau Tip: Connect to Google Sheets with Daily Auto-Refresh

1 comment

After publishing my viz earlier today that used Google Sheets, Zen Master Chris Love posted this question on our collaboration tool:


So I told him I’d create a video that shows three things:

  1. How to scrape data from a web page into Google Sheets
  2. How to import that data into Tableau with the Google Sheets connector
  3. Ensuring it refreshes when you publish the viz to Tableau Public


Enjoy!

UPDATE: Zen Master Jeffrey Shaffer sent me a link to this post on his blog. It’s important to note that the IMPORTHTML function does not seem to auto-refresh. You can force an auto-refresh in your Google Sheet by making the first cell an IF statement like this following -

=if(Minute(Now())=Minute(Now()),IMPORTHTML("http://www.realclearpolitics.com/epolls/2016/president/us/general_election_trump_vs_clinton-5491.html","table",4))

Then you need to update your spreadsheet settings. Click "File" -> "Spreadsheet setting" and set the "recalculate" to "On change and every hour". This will keep the data refreshed.

1 comment :

  1. Another way to add a timed refresh is by writing a Google Apps Script via Tools > Script Editor. The syntax is pretty simple and it's well documented. Steps are to (1) write the script and (2) set the update frequency as a timed trigger. The syntax is really easy to follow and well documented. Got the following working in minutes with zero prior experience:

    function getData() {

    // Set the active sheet
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var active = ss.getSheetByName("Polls");
    active.activate();

    // Refresh the table
    var queryString = Math.random();
    var cellFunction = '=IMPORTHTML("http://projects.fivethirtyeight.com/2016-election-forecast/updates/?' + queryString + '","table")';

    SpreadsheetApp.getActiveSheet().getRange('A1').setValue(cellFunction);
    }

    ReplyDelete