Use Google Sheets’ ImportHTML function to display data in Geckoboard

 Tip: Use this function to automatically pull data into a Google Sheet.

Google Sheets offers a function called IMPORTHTML that imports data from a table or list within an HTML page. This guide details how this function can be used with our Spreadsheets integration to display data on your dashboard. 

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. 

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

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

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.

  7. In the title field, add a name to your project.
  8. Next, select File, then choose Save

Important 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

  1. In the Script Editor, click the Edit menu and select Current project's triggers.
  2. In the window that appears, click the link No triggers set up. Click here to add one now.
  3. In the Events field, make sure Time-driven is selected.
  4. In the fields to the right, select the time interval you want your script to run on.  
  5. Click Save.

    Trigger_set_up.png

  6. In order for the script to run, Google requires your authorization and you will be prompted to authorize your app by clicking Review Permissions and Allow. More information on Google Apps authorization can be found in this guide.

  7. Your script is now setup and will update at the time interval you have selected!

Important 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. Depending on the time interval you have selected, this is nothing to concerned about and your script should continue to run on the next scheduled time interval. 

Additional Resources

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:

Was this article helpful?
👍

Thank you for your feedback!