Create a weather widget using Google Sheets


If you have a helpful developer (or some time and coding skills), you could use this solution instead.

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'll 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'll get the data as XML.

Let's run through a scenario:

  1. We'll write an example API call that will serve us the weather in London in XML.
  2. To make sure we're getting the weather for London, UK, we'll add the two letter country code to the query:,uk&mode=xml
  3. 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 (use units=imperial parameter), Celsius (units=metric), and Kelvin (used as default, so no need to use units parameter).
  4. We'd like to display the data in Celsius so we're using the metric parameter:,uk&mode=xml&units=metric.
  5. The last step would be to add the API key found on your account with OpenWeatherMap, the final API call would look like this:,uk&mode=xml&units=metric&appid=2de143494c0b295cca9337e1e96b00e0

Importing the data

The result from querying the API from the browser is shown below.

<city id=“2643743" name=“London">
<coord lon=“-0.13" lat=“51.51"/>
<sun rise=“2015-09-11T05:29:10" set-"2015-09-11T18:23:58"/>
temperature value=“19.91” n=“19" max=“22.22" unit=“metric”/>
<humidity value=“52” unit=“%”/>
<pressure value’”1015" unit=“hPa"/>
<speed value=“6.2” name=“Moderate breeze”/>
<direction value=“90" code=“E” name=“East"/>
<clouds value=“20" name=“few clouds"/>
<visibility value=“10000"/>
<precipitation mode=“no"/>
<weather number=“801" value=“few clouds" icon=“02d"/>
<1astupdate value=“2015-09-11T12:00:40"/>

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:

  1. We’ve written Weather 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 get the result our ImportXML function. This is made up of the URL of the page to examine:,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:


  3. Once we press enter, the temperature value will be imported.Import_temperature_value.png
  4. 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.


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

Step 1: Set up your Google Sheet

  1. Open a new sheet and give it a name.
  2. Like our example above, update cells A1 with a descriptive title.Set_up_google_sheet.png
  3. 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: Set up your script

  1. Click on the Tools menu and select the option Script Editor.
  2. A Google Apps Script window will appear. Click the Blank Project option.Setup_script_blank_project.png
  3. Replace the default function with the following script:
    function getData() {
    SpreadsheetApp.getActiveSheet().getRange('B1').setValue('=IMPORTXML("", "/*/temperature/@value")');
  4. Save your script and give it a name.Save_script.png


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:,uk&mode=xml&units=metric&appid=2de143494c0b295cca9337e1e96b00e0",

Step 3: Set up a Trigger

  1. From inside the Script Editor, click on the Resources menu and select the option Current project's triggers.
  2. In the window that appears, click the link No triggers set up. Click here to add one now.
  3. This will create a trigger for your function. Change the default Hour timer to Minutes timer.
  4. Click Save.Trigger_setup.png
  5. You'll be prompted to authorize your app by clicking Continue and then Accept. More information on Google Apps authorization can be found here.Authorize_app_continue.png
  6. You can now close the Script Editor. 

Step 4: Add a Google Sheets Number Widget in Geckoboard

  1. From your Geckoboard dashboard, click Add widget.
  2. Choose the Spreadsheets integration.
  3. Select the Google Sheets file that you've just created.
  4. Select the Number widget.
  5. Select the appropriate Sheet
  6. Click on cell B1
  7. Configure the other widget options.
  8. Disable the Comparison Visualization and adjust the title
  9. Click Add to dashboard to add your Number widget to your dashboard.Add_number_widget.png
  10. The Geckoboard dashboard is now displaying the information found in Cell B1 of the Google Sheet: the current weather in London, UK.Number_widget_result.png

You're done!

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 15 minutes with the new data from your Google Sheet. 


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

Was this article helpful?

Awesome! 👍  Thanks so much for your feedback!

Sorry about that! Start a conversation now.