Use Google Sheets’ ImportHTML function to display data in Geckoboard

Google Sheets offers a function called ImportHTML that imports data from a table or list within an HTML page. You can also use this function to automatically pull data into a Google Sheet.

This guide details how ImportHTML can be used with our Spreadsheets integration to display data on your dashboard.

Important notes

To import data from a table or list within a website into a Google Sheet using the ImportHTML function, the data needs to be readily available on the website’s first load.

The data must also be publicly available (by this we mean not requiring authorization and/or login credentials).

Also, if you’re comfortable using your browser’s developer tools, you can check to see if the ImportHTML function should work for the data you want to use.

Function and Syntax

The function is written as =IMPORTHTML(URL, query, index). And the syntax requires the following elements.

  • URL - The URL of the page to examine, including protocol (e.g. http://).
    • The value for URL must either be enclosed in quotation marks or be a reference to a cell containing the appropriate text.
  • query - Either "list" or "table" depending on what type of structure contains the desired data.
  • index - The index, starting at 1, which identifies which table or list as defined in the HTML source should be returned.

How to Import a Table

In this example, we pull data from a table that shows how much $1 USD is worth in a number of other currencies into a Google Sheet.

To import a table, follow these steps:

  1. Create a new Google Sheet, or create a new tab on an existing sheet and give it a name.
  2. Next, add the following formula into cell A1 and adjust the index field as needed.

    =IMPORTHTML("https://www.bloomberg.com/markets/currencies/cross-rates","table",1)

Tip

If the website you are importing from includes multiple tables, open your Developer Tools and run the following code in the console: var i = 1; [].forEach.call(document.getElementsByTagName("table"), function(x) { console.log(i++, x); });. To find the index associated with the table you want to display, mouse over the results until the table you want to display is highlighted. The number shown in the results is the table number you'll need to use.developer_tools_find_table_index

How to Import a List

In this example, we pull data from a list that shows Geckoboard's values and beliefs into a Google Sheet.

To import a list, follow these steps:

  1. Create a new Google Sheet, or create a new tab on an existing sheet and give it a name.
  2. Next, add the following formula into cell A1 and adjust the index field as needed.

    =IMPORTHTML("https://www.geckoboard.com/about-us/","list",5)

How to Display a Table

In this example, we go through the steps necessary to display data from a Google Sheet, using the Spreadsheets integration with a Leaderboard widget.

To display a table using the leaderboard visualization, follow these steps:

  1. On your dashboard, click Add widget.
  2. Select the Spreadsheets integration.
  3. Select the spreadsheet that contains your table.
  4. In the top right, click the Leaderboard widget visualization.
  5. Disable the First selected cell contains heading setting.
  6. Click in the Labels field, and Shift click all currency type cells or type the range, e.g. C1:I1.
  7. Click in the Values field, and Shift click all the currency value cells, or type the range.
  8. Add a title to your widget.
  9. Click Add widget.Add_leaderboard_widget

How to Display a List

In this example, we go through the steps necessary to display data from a Google Sheet, using the Spreadsheets integration with a Text widget.

To display a list using the Text visualization, follow these steps:

  1. On your dashboard, click Add widget.
  2. Select the Spreadsheets integration.
  3. Select the spreadsheet that contains your list.
  4. In the top right, click the Text widget visualization.
  5. Disable the Ignore the first selected cell setting.
  6. Click in the Text selection field, and Shift click all text fields you want to display or type the range, e.g. A:A.
  7. Add a title to your widget.
  8. Click Add widget.Add_leaderboard_widget

How to Configure Automatic Updates

The ImportHTML function does not automatically update the table or list from the source, even if the data on the source web page changes. To address this, we've written a script you can adapt to update your imported data automatically, even when the Google Sheet is closed.

Warning

We recommend opening a new Google Sheet initially as the following steps will overwrite any information you have in an existing Google Sheet.

Step 1: Write a Script

  1. In a new Google Sheet, select the Tools menu and click Script Editor. A new Google Apps Script tab will open.
  2. Replace the default code with the following script:
    function getData() { 
      var sheetName = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("<sheet-name>"); 
      var queryString = Math.random(); 
      var cellFunction = '=IMPORTHTML("<url>?","table",<index>)';
    
        sheetName.getRange('<import-cell>').setValue(cellFunction); 
    }
  3. Replace <sheet-name> with the name of your sheet (the tab name, not the name of the file).
  4. Replace <url> with the URL of your web page, e.g. https://www.bloomberg.com/markets/currencies/cross-rates.
  5. Replace <index> with the table position on the page, e.g. 1.
  6. Replace <import-cell> with the cell where you want to put your import statement, e.g. A1.google_apps_automatic_updates
  7. In the title field, add a name to your project.
  8. Next, select File, then choose Save.

Note

If you're curious about why the script is adding a random number to the end of our URL - without it, the website will be cached and the function stops importing the latest data.

Step 2: Set up a trigger

To create a trigger for your function, follow these steps:

  1. Click on the project's trigger's menu.trigger_for_daily_update.png
  2. Click the + Add Trigger button in the bottom right corner of the page. add_trigger.png
  3. Set a trigger for the myFunction function, that runs from Head on a Time-driven source that's triggered on an Hourly timer.Google Analytics authentication box
  4. Once you set the trigger (and so that you don't have to wait 1 hour to get data), run the script for the first time.run_script.png

You're done!

Now that you've written the script and set the trigger running, you'll be able to see the data on your Google Sheet refresh every hour (if you prefer -and your GSuit accounts allows it- you can lower the refresh to every minute instead). This will continue to refresh even when your Google Sheet is closed.

Your Geckoboard widgets don't require any changes to reflect the addition of this script and trigger; they will refresh every 15 minutes with the new data from your Google Sheet, just as they always have. The difference now is that your Google Sheet will have new data in it!

Note

We've found that a few times a week, a server error will occur on Google's end and the script will fail to run. By default, you'll receive an email to notify you that this has happened. Considering the script runs every minute of the day which totals 1440 times, a few times a week is nothing to be concerned about.Google_server_error.png

 Further reading

Was this article helpful?

Awesome! 👍 Thanks so much for your feedback!

Sorry about that! Start a conversation now.

We're ready to help, 24 hours a day, 5 days a week

Get support
  • Fernanda Customer Success Avatar

    Megan

    USA
  • Hariharan Customer Success Avatar

    Hariharan

    India
  • Fernanda Customer Success Avatar

    Yasmin

    Spain
  • Luis Customer Success Avatar

    Luis

    UK
  • Richard Customer Success Avatar

    Richard

    UK
  • Fernanda Customer Success Avatar

    Fernanda

    Brazil
  • Heather Customer Success Avatar

    Heather

    USA