Get Travel Time and Distance in Excel Using Google Maps Routes API

A few years ago, I made a tutorial on this website and also published to YouTube showing you how to create a function to calculate travel time and distance using the Google Maps API. That remains the most popular tutorial on this website. Google is in the process of retiring its Directions API, which is replaced with a new API called the Routes API. The new API is very similar, and we can use it to get travel time and distance in Excel just as we could with the previous API. This time, rather than using VBA, I’m going to show you how to do this with PowerQuery. I think PowerQuery is a bit more friendly for the average user, although in this case we are still going to need to do a bit of coding which you can simply copy and paste.

Step 1: Setup the Routes API

The first thing you’ll need to do is head over to the Google documentation and setup the routes API. You’ll need to setup a Google Cloud project, enable billing, enable the Routes API, and create an API key. The API key will allow you to identify your application (in this case Excel) to the API, and that way Google can bill you appropriately. Careful about sharing any resulting Excel files that might contain the API key, as that would mean you could get billed for any usage. When in doubt, simply delete the query before sharing out the file. Google requires billing to be enabled but there is a free quota, 10k requests at the time of this writing which should be enough for the average user.

Step 2: Create a Query in Excel to Pull Data

The first thing you’ll need to do is open a workbook and navigate to Data > Get Data > From Other Sources > Blank Query.

From there, click the Advanced Editor. In the window that opens, paste the code below:

(Origin as text, Destination as text) =>
let
    url = "https://routes.googleapis.com/directions/v2:computeRoutes",
    body  = "{
    ""origin"":{
        ""address"": """ & Origin & """
    },
    ""destination"":{
        ""address"": """ & Destination & """
    },
    ""travelMode"": ""DRIVE"",
    ""routingPreference"": ""TRAFFIC_AWARE"",
    ""computeAlternativeRoutes"": false,
    ""routeModifiers"": {
        ""avoidTolls"": false,
        ""avoidHighways"": false,
        ""avoidFerries"": false
    },
    ""languageCode"": ""en-US"",
    ""units"": ""IMPERIAL""
    }",
    Response= Web.Contents(url, 
        [
            Content=Text.ToBinary(body),
            Headers=[#"Content-Type" = "application/json",
            #"X-Goog-Api-Key"="YOUR-API-KEY",
            #"X-Goog-FieldMask"="routes.duration,routes.distanceMeters"]
        ]
    ),
    Json = Json.Document(Response)
in
    Json

Ensure that you replace the text YOUR-API-KEY above with your actual API key. You may also wish to change what is returned by the API by modifying the portion with the X-Goog-FieldMask. You can find a list of things that can be returned in the documentation for Route. Of particular note is staticDuration, which will give you a duration that will remain more consistent over time as it will ignore traffic conditions.

If you close the editor, you will now be able to test the function by manually invoking it in the query editor. At the moment, it will return the entire response which will show in the query editor as a List of routes. We can modify the bottom part of the query to return a single Record instead, which will make it easier to join it to a table. Change the bottom of the query as follows:

    ...
    Json = Json.Document(Response),
    routes = Json[routes],
    routes1 = routes{0},
    routeRecord = [Distance = routes1[distanceMeters], Duration=routes1[duration]]
in
    routeRecord

Note that if you added additional fields to be returned by modifying X-Goog-FieldMask, you will want to add those into the routeRecord and potentially omit any you don’t need.

Now that we have our query set up, we probably want to actually use it with some data from our spreadsheet. Otherwise, what’s the point?

Step 3: Use the Query to Pull Travel Time and Distance for Addresses in the Spreadsheet

To use our new function query in Excel, we need to join it with another query. If you have a list of origins and destinations in Excel, you can make that into a query.

Highlight your data, with origin and destination in their own columns, and go to Data > From Table/Range. Ensure you check “My table has headers” if your table has a header row. When the query editor pops up, go to Add Column > Invoke Custom Function.

Setup the function by mapping your columns to the variables your function takes. In this case, my columns are named the same but yours might not be. Note as well that function query should be set to whatever you called your query earlier, I named mine RouteDetails.

Once the result loads, make sure you expand the new column to reveal the distance and duration information. You may also get a warning about data privacy that you have to address before you can expand the column. Once expanded, you can close and load to load the data into your Excel spreadsheet!