Google Sheets' IMPORTXML function let you import data from feeds containing structured data types such as XML, HTML, RSS and CSV onto Google Sheets.
Used in combination with our Spreadsheets integration you can create a simple weather widget.
Where's the weather data coming from?
In order to create this widget we will be using OpenWeatherMap's currentweather API. It's free (registration is required to access the API KEY) and we can choose the format to be exactly what we need it to be to use this function.
By default the API will serve the data as JSON, however by going through the documentation we can see that by adding the mode parameter we will get the data as XML.
Example XML API call:
This will serve us the weather in London in XML.
To make sure I'm getting the weather for London, UK, I'll add the two letter country code to the query:
The last step in formatting the URL for requesting the data would be to select the Units we'd like to use.
The temperature information is available in Fahrenheit, Celsius and Kelvin units.
- For temperature in Fahrenheit use units=imperial
- For temperature in Celsius use units=metric
- Temperature in Kelvin is used by default, no need to use units parameter in API call
We'd like to display the data in Celsius so we're using the metric parameter:
The last step would be to add the API key found on your account with OpenWeatherMap, the final API call would look like this:
Importing the data
The result from querying the API from the browser is shown below.
We can see that we're getting back the information we wanted and in the right format. It's time to import the data to our spreadsheet.
In Google Sheets:
- We’ve written Weather in Cell A1 simply as a heading to explain what data will be found in the cells next to it.
- In Cell B1, we’ll get the result our ImportXML function. This is made up of the URL of the page to examine, http://api.openweathermap.org/data/2.5/weather?q=London,uk&mode=xml&units=metric&appid=2de143494c0b295cca9337e1e96b00e0, and the XPath query that we want to run on that page "/*/temperature/@value"
The full function will look like this:
- Once we press enter, the temperature value will be imported.
Now we have London's weather being displayed on our spreadsheet.
Updating your Data Automatically
Unfortunately, the Google Sheets ImportXML function won't automatically update 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 next steps do not follow on from the example above so please open a new Google Sheet to work through these next steps.
Step 1: Setup your Google Sheet
For this step all we need is to open a sheet, give it a name and like our example above, update cells A1 with a descriptive title:
That's all we need to do with this sheet as the script will be doing all the work. Cell B1 will be used to display the result of it.
Step 2: Setup your script
- 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:
- Save your script and give it a name.
Note: What this function will do is Open the document that is linked to the script and in cell B1 display the result of the query below:
Step 3: 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.
Step 4: Add a Google Sheets Number Widget in Geckoboard
- From your Geckoboard dashboard, click + Add widget
- Choose the Spreadsheets integration
- Select the Google Sheets file that you've just created
- Select the Number widget
- Select the appropriate Sheet
- Click on cell B1
- Configure the other widget options. Disable the Comparison Visualization and adjust the title and size
- Click Add to dashboard to add your Number widget to your dashboard
Step 5: The Result!
The Geckoboard dashboard is now displaying the information found in Cell B1 of the Google Sheet - the current weather in London.
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 Google Sheet Number widget will refresh every 10 minutes with the new data from your Google Sheet.
Note: We've found that, server errors can 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, when these errors happen the information on your widget will still be relatively correct.