Build Your Own Weather Dashboard In This Google Spreadsheet Master Class
“Computer! Show me all current weather conditions within 300 miles of my current location”
“Now, show me the humidity levels for those locations, charted by longitude and latitude on a map”
Would you like to turn your Google Drive account into an intelligent weather analysis system , and live out this Star Trek fantasy? Okay, you might not be able to turn Google Spreadsheets into an artificially intelligent computer system that will respond to your voice commands, but in this article I’ll show you how you can import weather data from the Internet and have your own customized dashboard with all of the information that’s important to you.
Accomplishing this task may sound complicated at first if you’re unfamiliar with using ImportXML or Xpath for web page scraping , but I’ll show you a few very simple tricks you can use to extract the weather information you want specifically off of the U.S. website. If you are outside the U.S., the same techniques should work for your own local weather service, you’ll just need to determine the structure of how the weather data is laid out, and tweak the query accordingly.
This is similar to the automated spreadsheet reports I described not long ago, but in this case it uses web-scraped data rather than Google Analytics data. So, if you’d like to customize your own automatically-updated weather dashboard in your own Google Drive account, let’s get started!
Finding Source Weather Data
In this exercise, you’re going to scrape weather data from the National Weather Service website by looking for specific CSS classes inside of the page that identify the data you want. Thankfully, this site organized the weather results using classes when you search for the local forecast in your area.
The format of the HTML we’re interested in this case is either a “span class” or a “p class” which identifies the specific data. For example, shown below you can see the class “myforecast-current-lrg” identifies the last recorded temperature for the queried region.
So each time you run a query on the site, you’ll see a URL showing the longitude and latitude of that location – keep a copy of that URL because you’ll use it to build your dashboard. You can either view the HTML source code directly, or use your browser developer tools to find the classes you need.
Setting Up the Weather Source Data
The way this works is you’ll create a data sheet for each location that you want to collect data for. Type the labels for that data in the left column, and in the next column, you’ll need to type the ImportXML formula to pull in that data from the website.
For Temperature, for example, the following works:
B12 contains the URL after conducting the location query. Using “//p[@class=” is how to set up the Xpath query to extract the “p class” in the page with the specified name. This query returns the temperature on the page.
Extracting Humidity, Wind Speed and the rest of the data points actually required extracting list items inside of a UL list with a class name “current-conditions-detail”. The following ImportXML statement accomplishes this:
Again, “ul” identifies the base element, and then the class name is specified in brackets as shown. This is followed by the “//li” indicating that you want to extract all list items inside of that UL class.
This fills out the rest of the data items in the source sheet for that location.
You should also include the URL in this case, because the latitude and longitude is included in the URL, and you can use that the use Google Spreadsheet maps to chart data points on a map (shown below).
Repeat the process above for every location that you want to pull weather data for. Run the query for that location on the website, copy the URL, and create a new sheet for the location, and populate it with the necessary ImportXML statements to scrape the data you need.
Building the Main Dashboard Spreadsheet
Once you’ve created all of your location data sheets, you’re ready to build the main spreadsheet of data for your dashboard. This is basically to lay out the data in table format so it’s easier to chart things like temps, humidity, wind speed, etc.
Create a header row for all the data, and then simply map each cell to the location data sheet locations that hold that data.
Once you’re done, all of the data that you’ve scraped from the website for each location will be laid out in this single table for easy review (and charting).
There is some tweaking required in some cases, because the data comes in as strings including units. There are a few tricks to extract just the data values. The first is to strip the ending units using a spreadsheet formula like this:
This basically finds the location of the unit, and then extracts the actual value up until the unit text starts. Once you’ve extracted the number only, you’ll need to convert it to an actual value in the spreadsheet so that you can do calculations or chart it. Doing this is simple with just a “value” function as shown here.
Once you’ve got all of your values created in the main dashboard spreadsheet, you’re done setting up your data. Now you can move on to creating the charts and gauges that’ll make up your graphical weather dashboard.
Creating Your Weather Dashboard
This is the simple part – just chart the location along with whatever data you want to display. You can do things like show a bar chart of all locations – in my case I can see the warmest spot in the state at a glance using this kind of chart.
You can create temp, humidity or wind speed gauges, which are always fun to display on a dashboard. You can extract the longitude and latitude data from the URL using the same “mid” command I described above, insert those locations into the main spreadsheet (with a comma between them), and then use that column to chart out data on a map.
Below, I’ve charted out temperatures by GPS coordinates on the map charting widget available in Google Spreadsheets. Larger circles on the map indicate locations with the higher temperatures.
As you can probably imagine, you can pull together some really cool-looking dashboards showing you all kinds of information about the weather either in your State, Country, or whatever region you’ve collected your data from. You can make use of some of the many useful Google Spreadsheet functions that are available to develop a really cool-looking dashboard.
While this article is focused on scraping data from weather websites, it’s also an example of how you can use ImportXML to import information from really any website out there, and using Xpath gives you much more flexibility to import very specific information off of a web page that ImportFeed or ImportHTML just don’t provide.
Can you think of some fun and creative uses for ImportXML and Google Spreadsheets? Share some of your ideas and maybe we can try to complete one of your project ideas in a future article!