The method makes use of:

• OpenWeatherMap API calls
• Google Sheets (and Sheet scripts)
• Automating the process with Google App Scripts Triggers

As the process is a little complicated (requiring a few quick edits to code), I have created YouTube video instructions (below) and have created/posted the full script and instructions on my Github page.

How it works in practice?

Whenever the Google Sheet script function is run, it queries OpenWeatherMap for the weather for your latitude/longitude. Data is then returned in JSON format, which our Google Sheet script then enters as a new row in your Google Sheet.

To automate the process, we create a Google App Script (with a timer trigger) to repeat the process as frequently as you want. For most weather stations, it is pointless querying them more than every 5 minutes; thus we recommend only setting the trigger for every 10 or 15 minutes.

The video shows how you can download the JSON data and output it to an Excel file.

Taking the method further

You can take the process even further by querying the API response to output individual variables to individual cells. With this, you can even create automatically updating widgets by creating a second sheet which calls the latest values from the Google Sheet:

The example above was developed for the POR EL Páramo project (porelparamo.org). This is really a much, much more in-depth process and one which I unfortunately lack the time to document fully here, particularly as it’s use for student research is limited and I would inevitably be inundated with API queries and how to query JSON data. The only difference with the instructions above is some mechanics within the Google Sheets script for those wanting to pursue it.

Summary

As the above demonstrate, Google Scripts are superb ways of automating storage of API responses. I’ve shown how it can be used for querying, storing and displaying meteorological data. However, thousands of data-gathering API’s exist, whose responses could be saved. Google Scripts can even leverage other APIs to act upon data, such as posting or tweeting under different circumstances.