Posted on June 15th, 2012 in Housekeeping | 2 Comments »
One of my companies sells animal onesies (buy one, because they are awesome), and it imports a good quantity of goods into Australia via EMS.
I hunted around for a website that would let me instantly see the status of my packages waiting to be delivered. Australia’s post website is pretty impressive, and I take my hats off to the agency responsible because they’ve done a good job; but I wanted an overview without clicking each box and/or reentering my package number a dozen times.
I tried a few iPhone apps, but having to type all of the numbers in one-by-one was a real pain, what I really wanted was a spreadsheet. Could Google Docs help me?
It turns out, yes it can. Google recently implemented a importXML function which lets us do all sorts of fancy things.
Column A. Enter your tracking numbers
Column B. Construct our “API” endpoint URL,
Column C. Fetch the package status,
Column D. Fetch the latest date/activity/location row,
=importXML(B2, "//div[@class='trackingDetails clearfix']//table/tbody/tr")
The last column will spread into Column E and F automatically. Brilliant, this works perfectly and our staff can now see the status of all our deliveries in a single glance.