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 use this function to automatically pull data into a Google Sheet and then into Geckoboard.

The first step is getting the data you need in Google Sheets using ImportHTML. Once there you can simply use our Spreadsheets data source to display it on your Geckoboard dashboard. This article focuses on the first step.

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.
  • 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.

Note

Widgets powered by spreadsheets using the ImportData function refresh approx. every 15 minutes. If this isn't fast enough for your needs, you can run a script and set your own refresh interval.

Turn spreadsheet data into shareable dashboards

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)

Optional: Speed up refresh periods for ImportHTML

Widgets powered by spreadsheets using the ImportHTML function refresh approx. every 15 minutes. If this isn't fast enough for your needs – and your G Suite account allows it – you can write a script that can further reduce the refresh times.

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.

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 a Minute timer.trigger settings in Google Sheets
  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 faster than the default 15 minutes. This will continue to refresh even when your Google Sheet is closed.

Potential Google server errors

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.

Ready to create your own dashboard?

Get started for free

Still have questions? Get in touch.