Max, a visitor here, recently left a comment asking about accessing the public transit travel time option in the Google Directions API. Up until then I hadn’t used it and assumed it worked the same as the walking and driving time options, but that turned out not to be the case.

The key difference is that you have to supply a time parameter as well as the start and end location. This can either be a departure or an arrival time.

The format required for the time parameter is seconds since midnight on 1/1/1970 (the start of the UNIX epoch), while Excel times are in days since midnight on 1/1/1900. This difference meant it seemed worth writing a function to do the conversions.

So far so good. So what next?

A day of coding an interface for Google’s experimental Time Zone API, that’s what. This is because I can’t assume that everyone’s in the UK – Max is interested in public transport in Singapore for example.

The results are the following functions for your spreadsheeting delight. They’re also fine for VBA for Excel.

=LOCALTIME(Location As String)
Returns the current local time for a given location, including daylight saving

=DST_OFFSET(Location As String)
Returns any daylight saving time offset to the time at a given given location

=UTC_OFFSET(Location As String)
Returns the time difference between UCT and the time at a given given location

=TIMEZONE_ID(Location As String)
Returns the ID of the timezone for a given location

=TIMEZONE(Location As String)
Returns the name of the timezone for a given location

These functions all ought to be pretty stable. They don’t cache results, obviously – since they’re to do with current time. But they do use recursion to keep extending a wait between repeated calls if you have reached Google’s soft limits for the API. These soft limits are where Google throttles the service if you appear to be accessing it too often in a short period of time. The module also includes a fallback to spot if you seem to have hit the hard limit for the day of 25,000 calls. In that case it will return the error message OVER_HARD_QUERY_LIMIT. If that happens then you need to go away for a while and try again the next day (or change your IP address if you’re feeling particularly sneaky).

You can download a workbook here with all of these functions.

This was all something of a distraction from the main purpose of figuring out how to access travel times for public transport. That’s the next task on the horizon in the ever-expanding set of Google API interfaces for Excel that I’m building up here. It’s actually pretty simple, it just needs tidying up so if that’s what you’re waiting for, keep checking back here over the next week or so.

Jamie Bull |

Related Posts

WiGLE is a popular platform which can be used for finding the location of a device using the names of WiFi networks in its vicinity. I’ve written about this before, and wrote some Python code to interact with their API. This API has since been retired and replaced with a new one, as of December […]

Just a quick post to point out a couple of really useful tools. The first is a web-based tool for finding weather files for a location of interest. It’s similar to the Excel EPW finder tool we created a few years back, but much more modern looking. It is however missing a few of the […]

Eppy is a really useful library which I’ve written about several times, since before I really had anything to offer in terms of contributing code. Over the past year or so though, I’ve started to contribute back some of the changes and additions I’ve made while using eppy on academic and commercial projects. This post […]

Leave a Reply

Your email address will not be published. Required fields are marked *