Friday, July 27, 2012

ImportHTML and Google Spreadsheets

We're getting ready to go on a family vacation to Alaska, and one of the big questions in the months leading up to the trip has been what the weather will be. Last week my wife and I were reviewing our trip todos and I stumbled on a great feature in Google Spreadsheets that I'd never used before: ImportHTML.

Before each trip, my wife and I work from a shared spreadsheet. We list out the packing details, transportation, itinerary, etc., and then divvy up the tasks. (I should point out that my wife almost always shoulders the vast majority of these tasks.) As of a week ago, the long-range forecast at Accuweather.com was showing rain for the entire time we'd be in Alaska. Not awesome, but at least we'd be prepared.

But as I looked at the spreadsheet with our itinerary, I was annoyed that I had what we'd be doing listed out, but not the weather (which could dramatically affect what we'd pack, and what we'd need for various days). That's when I found ImportHTML and fell in love.

The premise behind the function is simple: in a cell, type =ImportHTML("[URL]","[query]","[index]"), where "query" is the element within the HTML that you want to import, and "index" is which element within the page you want to import. Here's how it works:

I found this page at Accuweather.com that lists out the month's extended forecast for Anchorage, Alaska. Conveniently, it's laid out as an HTML <table>.


A quick look at the HTML source from that page confirmed that the table containing the weather data is the first table in the page, so in Google Spreadsheets I entered this:

=ImportHtml("http://www.accuweather.com/en/us/anchorage-ak/99501/august-weather/346835?view=table","table",1)

That parses the HTML data into individual cells in the spreadsheet; and from there it was a trivial matter to associate an individual day's weather (high/low/forecast) with its entry in the itinerary, giving us one screen that shows where we're staying, what we're doing, what the weather will be on each day, and what we'll need to pack. Here's a snippet of the spreadsheet:


Best news of all? Now that we're just a week away from our arrival, the forecast is getting increasingly positive: a week ago all of these cells showed rain for the entirety of the trip; today when I opened the spreadsheet, just two days show rain and several days look to be pretty warm and sunny!

2 comments:

  1. Nice job of using ImportHTML! Inspired me to write one myself on using ImportHTML() to make a nice map visualization of facebook usage data.

    Let me know what you think: http://spreadsheetpro.net/using-importhtml-to-scrape-facebook-usage-numbers/

    ReplyDelete