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.
Also see our companion article that discusses importing weather forecast data into Google Sheets.
The estimated time to complete this exercise is about 5 minutes.
Step 1 – Sign in or sign up
We’ll start by going to the Visual Crossing homepage and logging into our weather account. If you don’t already have a Visual Crossing Weather account you can easily create one at this point. The account is entirely free and you can use it immediately to query up to 1000 weather forecasts per day without any cost. Your free account will give you instant access to the full 15-day forecast for any location anywhere on the Earth.
Step 2 – Open the Query Builder
Next, we will click on the link to go to weather Query Builder near the top of the page. The Query Builder is where anyone can create and run weather queries.
Step 3 – Enter you location
To begin building a query you will need to provide the Query Builder with the location for which you want to find weather data. This location can be as simple as a Zip Code, a latitude/longitude pair (in DD format and comma separated), a city name with either state or country for completeness or a full street address of any worldwide location. The locations information that you enter will be converted to a specific geographic point so that the weather engine can look up the weather data for that exact location.
Once you have entered a location, press the search button to move on to the next step.
Step 4 – Introduction to the Query Builder
After entering a location we can now see that the Query Builder has already built and run a query for us. We will not cover the entire feature usage of the Query Builder in this tutorial but we will refer to the different sections shown in the screenshot above.
Step 5 – Selecting an Historical Date Range
Visual Crossing’s Query Builder uses the Weather Timeline API. This powerful API allows you to choose any date range desired from decades into the past and well into the future. In this exercise we will only include dates in the past as we are looking to query historical weather data. To do this, we click on the title of the Time Period section which shows “Next 15 days” be default. This default query is for a forecast. When we click on this section we can see various time period options:
By choosing “Date range” we are telling the Query Builder that we want to select our own dates for this query. We do this by manually choosing our start and end dates:
In this example we will choose a starting date of January 1st, 2015 and an ending date of December 22nd, 2021. When using the Date Chooser, you can use the arrows to change months or click on the Month-Year title to move quickly by months or years and.
We now have completed specifying our Location and our Time Period for our query.
Step 6 – Previewing your query results
Any time we make a change to the query options, the Data Preview Window automatically updates with a sample of the result data for our query. By default our view is in “Grid” mode and the time resolution is set to “daily” to show us one result for each day for our time period.
Note that the Preview Window is just a view of the many possible data sets that this query can return. The actual query can bring back data for different sections including daily weather data, hourly, station info, and query information. It can also bring back Current Weather, Events, and Alerts.
The Viewer Mode buttons can show you other options beyond the Grid. This view can show the JSON results in raw form, Charts of summary data, and even a copy/paste URL query string that you can paste into a browser or a script for dynamic queries. It is this URL query string the we will use to load our data into Google Sheets.
Step 7 – Obtaining the query URL
To obtain the query URL that matches this historical weather period, simply click on the API button in in the in the View Mode toolbar. This shows you the exact URL that can be used to retrieve the historical weather results. Before copying the URL, however, make sure that the Output Content Type is set to CSV. If it isn’t, go to the dropdown and change its value to CSV. To copy it to your clipboard, simply hit the Copy button to the right. Copying this way will ensure that you have the entire string and didn’t cut off any part.
Note that in the screenshot, the API Key is not shown. However, if you are logged into your Visual Crossing Weather account as instructed above, this URL will already contain the API Key for your account. This will make it easy to use the URL in Google Sheets. If for some reason, you see a placeholder instead (YOUR_API_KEY) like shown in the screenshot above, simply go into your Visual Crossing Weather account page, copy your API key, and paste it into the URL in place of the placeholder. Note also that you can easily test this URL by pasting it into any browser window.
Step 8
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.
NOTE: The Google Sheets =IMPORTDATA function offers 3 parameters (URL, DELIMETER, LOCALE) but only the first parameter is truly required. If you enter =IMPORTDATA(“myURLQuery”) into a cell it is likely to work but in some specific locales for non-US regions it may assume the incorrect delimeter. To make certain that it parses properly you can force all 3 parameters such as:
=IMPORTDATA(“https://weather.visualcrossing.com/VisualCrossingWebServices/rest/services/timeline/Munich,VA/2021-07-10?unitGroup=metric&key=%3CYOUR_API_KEY%3E&contentType=csv&include=days”, “,”, “en_US”)
To be certain that the query parses decimal and separators properly the above sample uses the following format: = IMPORTDATA(“myURLQuery”, “,”, “en_US”)
Step 9
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.
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.