Apartment hunting with Google Sheets and the Google Maps Distance Matrix API
However, moving out from my parents’ place, I now had to hunt for an apartment to live in, which is no trivial task with a low budget during peak season in Melbourne! In addition to this, I was sick of commuting for over ten hours a week on expensive buses, so I had to take this into consideration. I was also doing this with potential roommates and needed to work concurrently with them, collaborate, and share information.
To aid myself, and all of us, I hacked together (at two in the morning, of course), a quick Google Sheets spreadsheet that automatically scrapes apartment information from realestate.com.au. I do this with the beautiful
Using this, the spreadsheet automatically gets the address, weekly price, availability date, bond value, as well as the number of each type of room (bed, bath, carpark) of the apartment. It then assigns penalties and values to each of these variables (an extra bath might be worth an extra $100 to me), and computes the “weighted weekly price” of the apartment.
Google Maps Distance Matrix API
I didn’t stop there! In addition to just including these variables, I also connected to the Google Maps API, where I set up a free tier API key to connect to the Distance Matrix API. I set an hour of commuting time to be worth approximately $25. I really do hate wasting time on public transport. I then used the
importxml function along with the
xml output option to get the walking time from Google Maps with the following formula:
=importxml("https://maps.googleapis.com/maps/api/distancematrix/xml?units=metric&mode=walking&origins=<starting address>&destinations=<ending address>&departure_time=<travel time>&key=<API key>","//distance/value")/60
My actual queries looked like the following:
Here I first instruct the API to send back
xml that I can parse with
importxml, then to use metric units, and walking (instead of driving). I set the travel time to be
1490965200, which corresponds to the number of seconds between midnight 01/04/17 AEDT and the Unix epoch (1970-01-01 00:00:00 UTC). I set it to midnight as I tend to rampantly jaywalk and traffic does not really influence my walking times. I then extracted the walking seconds between the two addresses using the XPath
//distance/value. Finally, I divided this by
60 to get the result in minutes. To add these to the computation, I computed several distances, and added the cost of walking to the total weekly cost, using about 10 trips to my university every week, as well as 2 trips downtown.