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 display it with the Spreadsheets data source in Geckoboard.
To import data from a table or list within a website into a Google Sheet using the ImportHTML function, the data needs to be:
Available on the website’s first load
Publicly available, i.e., does not require credentials to access
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.
If you'd like to set a specific refresh rate for your data, see this guide for more details.
ImportHTML 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 dataindex- 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 into a Google Sheet
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)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 and list into a Google Sheet
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)