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.

🙏 Grappling with bringing your spreadsheet data to life? Don't hesitate to contact our support team. We can build a dashboard with your Google Sheets for you.

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'll pull data from a table that shows the latest US Dollar (USD) exchange rates into a Google Sheet.

To import a table, create a new Google Sheet, then add the following formula into cell A1 and adjust the index field as needed.

=IMPORTHTML("https://www.exchangerates.org.uk/US-Dollar-USD-currency-table.html","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.

How to Import a List

In this example, we pull data from a list that shows the list of web-based spreadsheets from the Wikipedia page on spreadsheets.

To import a list, create a new Google Sheet, then add the following formula into cell A1 and adjust the index field as needed.

=IMPORTHTML(“https://en.wikipedia.org/wiki/Spreadsheet”,”list”,8)

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 to reduce the refresh times further.

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

In a new Google Sheet, select the Tools menu and click Script Editor. A new Google Apps Script tab will open.

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); 
}

Replace <sheet-name> with the name of your sheet (the tab name, not the file's name).
Replace <url> with the URL of your web page, e.g. https://www.bloomberg.com/markets/currencies/cross-rates.
Replace <index> with the table position on the page, e.g. 1.
Replace <import-cell> with the cell where you want to put your import statement, e.g. A1.
In the title field, add a name to your project.
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:

Click on the project's trigger menu.
Click the + Add Trigger button in the bottom right corner of the page.
Set a trigger for the myFunction function that runs from Head on a Time-driven source that's triggered on a Minute timer.
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.
Now that you've written the script and set the trigger running, you can 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, totalling 1440 times, a few times a week is nothing to be concerned about.