Google Sheets has a built-in function called ImportHTML which imports a table or list from a webpage to a Google Sheet. This article details how this function can be used with our Spreadsheets integration to display data on your Geckoboard dashboard.
This function doesn't update on its own by default so we've written a script that you can adapt to ensure your data is kept up to date. Our Importing a Table example shows the basics of using this function and doesn't use this script. If you'd like your data to update automatically, you should skip straight to the Updating Your Data Automatically section as it supersedes the first example.
- Syntax of the Function
- Geckoboard Example - Importing a Table
- Updating Your Data Automatically
- Other Resources
Syntax of the Function
Here's the syntax of the ImportHTML function:
=IMPORTHTML(URL, query, index)
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.
Geckoboard Example: Importing a Table
We'll use the ImportHTML function and a Leaderboard widget to create a currency exchange table that shows how much 1 USD is worth in a number of other currencies.
Here is a screenshot of the Live Exchange Rates table that we want to import from the xe.com website:
Step 1: Google Sheets
- To import the table from the xe.com webpage into Google Sheets, open a new Google Sheet and type the following formula into cell A1:
This formula is looking at the xe.com website, finding the first table on the web page and importing the data from the table into the Google Sheet.
- Save your Google Sheet and give it a name.
Step 2: Add a Leaderboard Widget in Geckoboard
The Google Sheet is now displaying the data table from the xe.com website so we'll create a Leaderboard widget in Geckoboard to display this information on a dashboard.
- From your Geckoboard dashboard, click + Add Widget.
- Choose the Spreadsheets integration.
- Select the Google Sheets file you just created.
- Select the Leaderboard widget.
- Select the appropriate Sheet.
- Disable First selected cell contains heading.
- Click in the Labels field. Either click and drag from cell D2 to K2, or type D2:K2.
- Click in the Values field. Either click and drag from cell D3 to K3, or type D3:K3.
- Adjust the title and size of your widget.
- Click Add Widget to add the Leaderboard widget to your dashboard.
Step 3: The result!
The Geckoboard dashboard is now displaying the information found in Rows 1 and 2 of the Google Sheet - the different currency titles and their value against 1 USD.
Updating Your Data Automatically
Unfortunately, the Google Sheets ImportHTML function doesn't automatically update the list or table from the source, even if the data on the source webpage is changed. This isn't particularly helpful so we've written a script that you can adapt to update your imported data every minute, even when the Google Sheet is closed.
Please note that these instructions do not follow on from the example. It would pay to open a new Google Sheet as the steps below will overwrite any information you have in an existing Google Sheet.
Step 1: Write a Script
- In a new Google Sheet, click on the Tools menu and select the option Script Editor...
- A Google Apps Script window will appear. Click the Blank Project option.
- Replace the default function with the script shown below:
- In the script, replace
<url>with the URL of your web page e.g.
<index>with the table position on the page e.g.
<importcell>with the cell where you want to put your import statement.
- Save your function and give it a name.
Note: In case you're wondering why we're adding a random number to the end of our URL, it's because otherwise the website will be cached and the function stops importing the latest data.
Step 2: Set up a Trigger
- From inside the Script Editor, click on the Resources menu and select the option Current project's triggers.
- In the window that appears, click the link No triggers set up. Click here to add one now.
- This will create a trigger for your function. Change the default Hour timer to Minutes timer.
- Click Save.
- You'll be prompted to authorize your app by clicking Continue and then Accept. More information on Google Apps authorization can be found here.
- You can now close the Script Editor.
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 minute. 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 10 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.
If you're interested in learning more about the ImportHTML function, others have also written blog posts on the subject. Here are some we found useful:
- How to Import Web Data into Google Docs
- Reshaping importHTML data in Google Spreadsheet using QUERY and TRANSPOSE formula