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.

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)

 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