Use Google Sheets’ ImportXML function to display data in Geckoboard

Google Sheets has a built-in function called ImportXML which will import pieces of structured data from a webpage to a Google Sheet. You can use this function to import publicly available information (unfortunately you can't use it with websites that you have to authenticate to or prevent scrapping).

We'll show you how this function can be used to aggregate Twitter followers from different Twitter accounts and then display that information in Geckoboard through our Spreadsheets integration.

Turn spreadsheet data into shareable dashboards

Note

Widgets powered by spreadsheets using the ImportXML function refresh approx. every 15 minutes.

Syntax of the ImportXML function

The syntax of the ImportXML function is:

=IMPORTXML(URL, query)

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
The XPath query to run on the data given at the URL. Each result from the XPath query is placed in its own row of the spreadsheet.

How does the ImportXML function work?

We recommend working through the first 4 chapters of this blog post as it will give you a really good basic knowledge of reading source code and interpreting HTML, XPath and the ImportXML function.

Once you've got your head around these things, come back to this article and we'll explain how you can use your newfound knowledge to the benefit of your Geckoboard dashboard.

Step1: Importing Twitter Follower Data

In a web browser:

  1. Navigate to our Twitter feed http://twitter.com/geckoboard (we’re using Safari in our example).Geckoboard_twitter_feed
  2. Right-click on the page and click Show Page Source.
  3. We’re looking for the Follower count in amongst all the code so click inside the code that appears and press Command + F (or Control + F) and type in "followers".
  4. The screenshot below shows the section of the page source that forms the image above  e.g. Tweets, Following, Followers, Favorites.Twitter_page_source
  5. Our goal is to find something in the source code that uniquely identifies the number of followers we have. On closer inspection we can see that, inside the <a> tag, is an element called data-nav=”followers”. This element is our key to importing just the piece of data that states how many followers we have.

    To pull out this data we’ll use the XPath query //a[@data-nav='followers']. This finds all the <a> tags in the source code and imports any with a data-nav=”followers” attribute. As we’ve discovered, this is only used once in the source so will only import one result into our Google Sheet when we write our ImportXML function. Data-nav_followers

In Google Sheets:

  1. We’ve written Twitter in Cell A1 simply as a heading to explain what data will be found in the cells next to it.
  2. In Cell B1, we’ll type our ImportXML function. This is made up of the URL of the page to examine, http://twitter.com/geckoboard, and the XPath query that we want to run on that page //a[@data-nav='followers']

    =IMPORTXML("http://twitter.com/geckoboard","//a[@data-nav='followers']")

  3. The word Followers is imported into cell B1 and the number of followers into cell C1. We haven’t done anything special to make this happen, this is just how the data has imported. However, it’s really fortunate as it leaves the number in its own cell which will make it easy to do calculations on.Import_XML_Twitter
Repeat the above step for all the Twitter accounts you want to aggregate.

Step 2: Use Spreadsheets to visualize the data in Geckoboard

With the number of Twitter followers data in your Google Sheet, create a new Spreadsheets widget in Geckoboard to display this information on the dashboard.

Optional: Speed up refresh periods for ImportXML

Widgets powered by spreadsheets using the ImportXML 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 that can further reduce the refresh times.

These next steps don't follow on from the examples above, so please open a new Google Sheet to work through these next steps.

  1. In a new Google Sheet, the first thing we'll do is split the ImportXML function into two logical pieces; the URL and the XPath query. Write the URL in cell A1 and the XPath query in cell A2.
  2. If you want to add another ImportXML function into this sheet so that you can do calculations with the data, add the second URL in cell A4 and the XPath query in cell A5.
  3. Give your Google Sheet a name.Prepare_google_sheet
  4. To write a script, click on the Tools menu.
  5. Select the option Script Editor.
  6. A Google Apps Script window will appear. Click the Blank Project option.

    Setup_script_blank_project

  7. Replace the default function with the script shown below:
    function getData() {
      var queryString = Math.random();
    
      var cellFunction1 = '=IMPORTXML("' + SpreadsheetApp.getActiveSheet().getRange('A1').getValue() + '?' + queryString + '","'+ SpreadsheetApp.getActiveSheet().getRange('A2').getValue() + '")';
      SpreadsheetApp.getActiveSheet().getRange('C1').setValue(cellFunction1);
    
      var cellFunction2 = '=IMPORTXML("' + SpreadsheetApp.getActiveSheet().getRange('A4').getValue() + '?' + queryString + '","'+ SpreadsheetApp.getActiveSheet().getRange('A5').getValue() + '")';
      SpreadsheetApp.getActiveSheet().getRange('C2').setValue(cellFunction2);
    }
  8. Double-check the following aspects of the first section of the script (var cellFunction1...):

    Cell reference A1 should reflect where your first URL is written in your Google Sheet

    Cell reference A2 should reflect where your first XPath query is written in your Google Sheet

    Cell reference C1 should reflect where you want to display the result of your ImportXML function

  9. If you want to include a second ImportXML function, double-check the following in the second section of the script (var cellFunction2...):

    Cell reference A4 should reflect where your first URL is written in your Google Sheet

    Cell reference A5 should reflect where your first XPath query is written in your Google Sheet

    Cell reference C2 should reflect where you want to display the result of your second ImportXML function

  10. If you don't want to include a second ImportXML function, delete everything from var cellFunction2... to ...setValue(cellFunction2); leaving just the following script:
    function getData() {
      var queryString = Math.random();
    
      var cellFunction1 = '=IMPORTXML("' + SpreadsheetApp.getActiveSheet().getRange('A1').getValue() + '?' + queryString + '","'+ SpreadsheetApp.getActiveSheet().getRange('A2').getValue() + '")';
      SpreadsheetApp.getActiveSheet().getRange('C1').setValue(cellFunction1);
    
      var cellFunction2 = '=IMPORTXML("' + SpreadsheetApp.getActiveSheet().getRange('A4').getValue() + '?' + queryString + '","'+ SpreadsheetApp.getActiveSheet().getRange('A5').getValue() + '")';
      SpreadsheetApp.getActiveSheet().getRange('C2').setValue(cellFunction2);
    }
    
  11. Save your script and give it a name.Save_script

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 3: Set up a Trigger

  1. From inside the Script Editor, click on the Edit menu and select the option Current project's triggers.
  2. Click the + Add Trigger button in the bottom right corner of the page. add_trigger.png
  3. Set a trigger for the myFunction function, that runs from Head on a Time-driven source that's triggered on a Minute timer.trigger settings in Google Sheets
  4. 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.run_script.png
Was this article helpful?

Awesome! 👍 Thanks so much for your feedback!

Sorry about that! Start a conversation now.

Ready to create your own dashboard?

Get started for free

Still have questions? Get in touch.