Apartment hunting with Google Sheets and the Google Maps Distance Matrix API
I just recently moved to Melbourne, Australia to complete a Master in Science in Statistics and Mathematics, hopefully involving a bunch of Machine Learning, at the University of Melbourne.
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.
Google Sheets
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 importxml(url, xpath) function in Google Sheets. You give it the URL to scrape, as well as the XPath of the element, and it gets the content of the given element. I’m pretty certain it does these requests on my computer with JavaScript, or something.
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:
=importxml("https://maps.googleapis.com/maps/api/distancematrix/xml?units=metric&mode=walking&origins="&$AJ12&"&destinations="&cbdAddress&"&departure_time="&travelTime&"&key="&apiKey,"//distance/value")/60
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.