How can I load historical weather data into Google Sheets?

In this article, I’m going to show you how to easily load historical weather data into Google sheets.  We’ll walk through the process step-by-step showing you how to construct the query in Visual Crossing Weather and then use the query URL to import the weather results into Google Sheets.

If you would like to follow along with a video as well, open a browser on the matching YouTube video here: https://www.youtube.com/watch?v=HAqRoZ8iVcI

The estimated time to complete this exercise is about 5 minutes.

Step 1

mceclip1.png

We’ll start by going to the Visual Crossing Weather Data page.  We then need to click on the link to go to weather data download page near the top of the page.

Step 2

mceclip0.png

Once on the log-in page, you will need to sign into your Visual Crossing Weather account.  If you don’t already have an account simply click on the orange button on the right-hand side of the login page.  Your free trial account will give you instant access to historical weather data for any location worldwide.

Step 3

blobid0.png

For this example, we’ll select the option to manually enter a location to use for the historical weather query.  However, you could instead load a sheet of addresses or paste in a list as plain text.

Step 4

blobid2.png

As for the address itself, we will let the system use our current location by clicking on the “Populate from your location” link below the location text box.  However, we could instead manually enter an address, a city name, or a postal code.  We can optionally also give the location a friendly name for our own reference in the output data.  However, there is typically little need to provide this alternate name if we are only working with a single location.

Step 5

blobid3.png

Next, we need to choose the query type.  Since we are interested in weather reports from the past, we’ll select the Historical Data option.  However, in this panel there are other options as well such as weather forecast queries, historical data summary reports, and hourly data.  These are among the many options covered by our other tutorials and videos.

Step 6

blobid4.png

When we select the Historical Data option, the panel opens for us to set the parameters for an historical query.  The primary option that we need to select is the Date Range.  Use the calendar selectors for the start and end points to select a range that interests you.  The left-side calendar selects the starting date and the right-side calendar selects the ending date.  Each day in the range will return a single row of output data, so for an example, you will likely want to select a range of only a month or two.

Step 7

blobid5.png

When we run the query the default view is the weather calendar.  This view provides a simple overview of the result data.  It is also valuable for comparing data from different locations side-by-side.

Step 8

blobid6.png

To see more details we can change to the grid view by clicking the “Raw Grid” button near the top of the page.  This view gives a single row for each day of the requested range and at the location we selected earlier.  You can now see the various weather metrics that are provided in the output data.  These include common values such as temperature, precipitation, and wind as well as less common value such as heat index, cloud cover, and wind gusts.  For more information on the details and how to use these less common weather metrics, please see our documentation here: https://www.visualcrossing.com/weather-data-documentation

Step 9

blobid7.png

We could now download the data as a CSV by pressing the “Download all data” button for import into any analysis tool, but instead, we’ll switch to the API view to generate the query URL.  Using a URL instead of a data import will allow Google Sheets to fetch the data live and allow us to refresh the data at any time within the Google Sheets interface.  Since the default query URL output is CSV, we can simply press the “Copy full query” button to copy the query URL to the clipboard.

Step 10

blobid8.png

We can now use this URL to load the historical weather query results directly into Google Sheets.  Loading the live result data is as simple as entering the URL into Google Sheets ImportData function.  To do so, we would enter the following into the first sheet cell:

=ImportData(<URL>)

Where the “<URL>” is the value the we copied from the Visual Crossing Weather API page.

Step 11

blobid9.png

The final step before we can use this data is to format the date column as a Google Sheet date.  To do so, we simply select the date column and then select the Date option under the Number options in the Format menu.

blobid10.png

Now our historical weather data is loaded and ready for analysis within the Google Sheets environment.

If you would like to learn more about using Visual Crossing Weather options such as weather forecasts, multiple location import, and use within other analysis tools please see our other tutorials.

Leave a Reply

Your email address will not be published. Required fields are marked *