Data Viz Done Right

August 24, 2016

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

3 comments

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.

3 comments :

  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
  2. Thank you Andy for your wonderful tips.
    I developed a project using Google Form and Google Sheets. This is working great on Tableau desktop, I just need to refresh data source and data gets updated.

    I now want to put this workbook on our Tableau server. But, data is not refreshing live. We don’t see any updates.. Data is updating on Google Sheets but when we hit “refresh” on Tableau Server’s workbook, data is not updating... it will eventually get updated 30 minutes later..



    I then created API JavaScript, but still data is not refreshing "live".



    Any tips on how to make it work on Tableau server?
    Is it a problem with Google Sheets’ connection (Tableau server not properly connecting to Google Sheets)? Problem with Tableau Server’s caching system? Any comment or idea?


    ReplyDelete
    Replies
    1. I believe you're seeing this lag due to the Google Sheets API. It's not anything you can control.

      Delete