Archive for October 14th, 2010

Maybe you’ve heard, it’s all over the interwebs – I’m getting married to MariaG.  In keeping with my deeply held principles of “why do something the easy way if I can learn new stuff instead”, I started organizing using a whole bunch of Google Docs spreadsheets.  While I use Google Docs all the time, my principal use of spreadsheets is as a gigantic table.  It turns out that they’re also pretty good at being a calculator.  And, thanks to our Google overlords, we can also bring data from the web into our spreadsheets.

While the first spreadsheet I made was a guest list, the second (and more interesting one, in my opinion) was a scheduler.  Several very important guests are on academic schedules, whether as teachers or students, and of course no two of them appear to be the same.  Having decided to try for a Saturday event, and having selected the location (big enough to accommodate the entirety of the draft guest list), I made a tool to help narrow down the candidate days.  It looks like this:

So the idea here is that I want to let the spreadsheet do the work of identifying all candidate dates and hunting down some useful information to use in making the decision.  Given any set of start and end dates, it will identify the first Saturday (hardcoded at the moment) in the range and every following Saturday.  Then it will go look up in the Old Farmer’s Almanac (http://www.almanac.com/) the temperature, rain/snow, and wind for that day last year.  It’s not perfect, but I like where it’s going.  If you want to play with the formulas or copy it for your own use, you can grab the template here.

Most of the stuff in this spreadsheet is run-of-the-mill formulas and number crunching that you can do just by looking at a calendar, but what puts this into the “useful” category is Google’s “importXML” function.  While maintaining the well-ordered and well-understood spreadsheet functions like rows, columns, formulas, etc., it also allows me to operate on data pulled from arbitrary URLs using XPath functions.  I’m no power user but I’ve run across XPath before, and it’s pretty neat: it’s basically a way to navigate to a particular element or set of elements in an XML document with highly configurable granularity.  Since the Old Farmer’s Almanac website is conveniently rendered in XHTML (thanks!), and the data is available through RESTful and deterministic URLs (again, thanks!), it’s a snap to extract the info I need and pop it into the cells.  You could easily see how this could be useful; more historic data and predictive data as well as charts would quickly make this a great tool for picking dates for all kinds of activities such as picnics, camping trips, etc.  Hopefully it’ll also help us with the wedding planning.

Enjoy!