Pinterest Stumbleupon Whatsapp
Ads by Google

“Computer! Show me all current weather conditions within 300 miles of my current location”
“Yes Captain”
“Now, show me the humidity levels for those locations, charted by longitude and latitude on a map”
“Yes Captain”

Would you like to turn your Google Drive account into an intelligent weather analysis system Become An Expert Data Analyst Overnight Using Google Spreadsheet Report Tools Become An Expert Data Analyst Overnight Using Google Spreadsheet Report Tools Did you know that one of the greatest tools of all to conduct data analysis is actually Google Spreadsheet? The reason for this isn’t only because it can do nearly everything you might want to... Read More , 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 How To Build A Basic Web Crawler To Pull Information From A Website (Part 1) How To Build A Basic Web Crawler To Pull Information From A Website (Part 1) Read More , 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. National Weather Service 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 How To Create An Automated Google Spreadsheet Report From Analytics Data How To Create An Automated Google Spreadsheet Report From Analytics Data Read More 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.

weather-dashboard1

Ads by Google

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.

weather-dashboard2

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 Figure Out Website Problems With Chrome Developer Tools Or Firebug Figure Out Website Problems With Chrome Developer Tools Or Firebug If you've been following my jQuery tutorials so far, you may have already run into some code problems and not known how to fix them. When faced with a non-functional bit of code, it's very... Read More 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:

=importxml(B12; “//p[@class=’myforecast-current-lrg’]”)

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.

weather-dashboard4

 

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:

=importxml(B12; “//ul[@class=’current-conditions-detail’]//li”)

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.

weather-dashboard7

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.

weather-dashboard8

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).

weather-dashboard10

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:

=mid(C2,1,find(“F”,C2)-2)

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.

weather-dashboard11

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.

weather-dashboard12

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.

weather-dashboard13
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 Perform Amazing Feats With These Useful Google Spreadsheet Functions Perform Amazing Feats With These Useful Google Spreadsheet Functions I am ashamed to admit that I've only recently decided to start experimenting with Google Spreadsheets, and what I've discovered is that whatever allure or power Excel had over me due to the amazing things... Read More 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!

  1. david henderson
    April 28, 2015 at 3:16 pm

    Get an error - "We cannot parse this operator in this context" and highlights the // before the p[@... Any ideas?

    • Joe Powles
      July 2, 2015 at 4:40 pm

      i noticed that if you just copy and paste the formula from above. there are some syntax errors.

      =importxml(B12; “//ul[@class=’current-conditions-detail’]//li”)

      for one, change the semicolon to a comma. ; change to ,
      next, the wrong quotation mark is used to start the string. erase “ and replace with a ".

      i'm still having troubles getting it to work correctly. the cell now has an error "#N/A": Imported XML content cannot be parsed.

      • Joe Powles
        July 2, 2015 at 4:57 pm

        ok both quotation marks need to be replaced. just erase and use keyboard to replace. Also apostrophes need to be replaced in the same matter

        • Joe Powles
          July 2, 2015 at 4:59 pm

          *Manner not matter... i need to proof read before i post.

        • Joe Powles
          July 2, 2015 at 6:12 pm

          I'm using the formulas :

          Temp: =importxml(A1, "//p[@class='myforecast-current-lrg']")

          Conditions: =importxml(A1, "//p[@class='myforecast-current']")

          The current-conditions-detail line from above doesn't seem to work, it says the xml data is empty. but just a single line return from the conditions formula i'm using is all i need.

        • Joe Powles
          July 2, 2015 at 6:17 pm

          I think the formatting of this webpage is causing the incorrect quotemarks and apostrophes to appear. when i copy and paste from my previous post it doesn't work. so please after you copy and paste into google spreadsheets be sure to replace the " and ' with freshly typed ones.

  2. Grant Kaye
    January 15, 2015 at 8:19 pm

    Fantastic article! I was wondering how difficult it would be to automate this so that the data is scraped once a day for a few months to be able to analyze trends in weather readings over time?

Leave a Reply

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