Calculate distance in Google spreadsheet

By • Published: December 3, 2010 • Last updated: March 14, 2014 • Filed in: Tips & tricks

flattr this!

Share
This post started as a description of how to use Google’s API for distance calculations (with importXML), and there was a template as a proof of concept.

It has evolved into a distance calculator template with custom functions using either Google’s or Mapquest’s API, with the ability to add results to a trip log.

Fill out From and To locations and the spreadsheet will calculate the distance and driving time based on the recommended route. You can copy the result to a trip log. Google account required.

Read the background to learn more about the two methods and their limitations.
For issues or requests, write a comment or contact me directly.


Is this spreadsheet saving you time/money?
Feel free to make a donation, use the Flattr button above or donate to your favorite charity.

February 2014: v2.3 implements two user requests, easier “asTheCrowFlies” function and the ability to specify what road types to avoid. See the release tab.

background

This post describes two ways to perform distance calculations:

  • custom functions: in Google spreadsheets you can create custom functions.
    It appears to be more reliable and more flexible than the second method.
    Example function:


  • importXML: Google Maps has a nice API and Google spreadsheets has a function (importXML) that we can use to parse the results.
    Example:

limitations

Google

Mapquest

  • Google UrlFetch: is used to contact the Mapquest API. UrlFetch has a quota of 20,000 requests per day.
  • custom functions: no usage limits. The address lookup appears to be less flexible, sometimes Mapquest can not lookup a location when Google can look it up just fine.

releases

  • 2.1: experimental support for Mapquest, which has no daily limits. Requires free Mapquest developer account and application key.
    Unhide rows 29-57 and enter the Mapquest appkey. It supports the same functions as before, just add …_mq at the end. E.g. =routeDistance_mq(“paris”,”berlin”)
  • 2.2.1: has a new Mapquest function =routeCrossing_mq(). This was requested by a couple of users, display a table with the distance and duration per state/country. See functions tab for details.
  • 2.3: implementation of two user requests:
    1) asTheCrowFlies now uses locations =asTheCrowFlies(from, to, miles), for lat+lon parameters use =asTheCrowFliesLatLon(fromLat, fromLong, toLat, toLong, miles).
    2) you can specify what types to avoid. For Google: tolls, highways. For Mapquest: tolls, highways, seasonal, unpaved, ferries, borders. E.g. -=routeDistance(“berlin”,”paris”,,,”tolls,highways”) or =routeDistance_mq(“berlin, germany”,”paris, france”,,,”tolls,highways,ferries”). Note, the “avoid” parameter is after the “mode” parameter, for some functions this can mean the remaining parameters have shifted.
The custom functions use Google’s geocoding API.
There is a daily limit of 2500 direction requests and 2500 location requests.
v2.1 offers Mapquest support which has no daily limits, see release info.

The following functions are available in the template. They can be used as custom functions for a variety of distance calculation functions. For example: to calculate the distance, duration (driving/walking/bicycling), lookup an address (or only the zip code).

See the functions tab in the template for details.

function parameters results
routeDistance from, to, [miles],[mode],[avoid] distance between locations in miles or km
routeDuration from, to, [mode], [avoid] duration between locations in minutes
routeSummary from, to, [alternatives], [miles], [mode], [avoid] summary of route(s): which route, duration, distance
routeLeaveWhen from, to, arrivalTime, [miles], [mode], [avoid] departure time in order to arrive by a certain time
routeMapUrl from, [to], [width], [height] URL of image showing the departure and arrival locations
locationMapUrl location, [width], [height] URL of image showing a single location
routeDirections from, to, [miles], [mode], [avoid], [lang], [region] table with direction steps
locationAddress location, [lang], [region], [onlyZipCode] full address (or only zip code) for searched location
zipAddress location, [lang], [region] zip code for location
locationLatLon location string with latitude + longitude combination of searched location
asTheCrowFlies from, to, [miles] as the crow flies (straight line) distance between locations
asTheCrowFliesLatLon from lat, from long, to lat, to long, [miles] as the crow flies (straight line) distance between coordinates
routeCrossing_mq from, to, [miles], [mode], [avoid], [lang], [region], [adjust] Mapquest only. table with distance and duration per state/country

The functions above use the Google API. To use the Mapquest version add …_mq to the function. E.g. routeDistance_mq().

examples

example result
=routeDistance(“paris”,”berlin”) 1048.172 [km]
=routeDuration(“paris”,”berlin”) 573 [minutes]
=routeDuration(“paris”,”berlin”,”bicycling”) 3663 [minutes]
=locationAddress(“white house, washington”) The White House, 1600 Pennsylvania Avenue Northwest, Washington, DC 20500, USA
=locationAddress(“white house, washington”,”fr”) Maison Blanche, 1600 Pennsylvania Avenue Northwest, Washington, District de Columbia 20500, États-Unis
=zipAddress(“white house, washington”) 20500
=locationAddress(“38.89768309999999 -77.0364972″) The White House, President’s Park, Washington, DC 20502, USA
=locationLatLon(“white house, washington”) 38.89768309999999 -77.0364972
=asTheCrowFlies(“white house, DC”,”times square, NYC”) 332.679816922244 [km]
=asTheCrowFliesLatLon(38.898,-77.036,40.759,-73.985) 332.679816922244 [km]
=routeCrossing_mq(“atlanta, GA”, “memphis, TN”) Georgia 57 miles, Alabama 190, Mississippi 118, Tennessee 16
There is a limit of 50x importXML in a spreadsheet.
It does not always work reliably, you then get #N/A.

How to use importXML to get distance data from Google Maps in your spreadsheet?

In short:

  • create a URL that uses the Google Maps Directions API which returns XML output;
  • use the Google spreadsheets function importxml() to parse the XML file, and use an XPath command to extract the info you need.

Google Maps API for directions

Example URL:

Nice: no API key is needed. See results (unformatted XML output).

Explanation

  • the Google Maps API for directions is well documented
  • in your spreadsheet point the origin=… and destination=… to spreadsheet cells that contain the origin and destination, the importxml() commands will be run every time you change the URL. Nice.
  • sensor=false: don’t use browser location sensor (required).

Some of the optional parameters:

  • units=metric: show km (alternative: imperial)
  • region=nl: country that should be used as default for origin or destination
  • language=nl: language to use for the results
  • alternatives=false: do not show alternative routes, only show the recommended one

The resulting XML file contains 1 route (or if you ask for alternatives: 1 or more), and for each route 1 leg (I am not using via waypoints), and each leg consists of steps (turn left at …, etc.).

Example parse commands

Assumed: the URL is created in cell B20. Please note: you can have a maximum of 50 importxml commands in a spreadsheet.

The base command to use to get e.g. the distance (depending on your units used: in feet or meters):

The first argument is the URL, the second argument is the XPath command.

Show the start and finish (e.g. when using “brentwood” as origin, it will show: “Brentwood, TN, USA”)

The XPath predicate [1] and [last()] are used in case the route has multiple legs (in our examples they don’t)
Alternatively you could use the spreadsheet index() function:

Show the label of the preferred route (e.g. “A28 and A73″):

Calculate the distance of the preferred route:

The result of this field is always in meters, so we divide the result by 1000 to get kilometers, and we round it up.

And calculate the duration (the result is in seconds, so divide the result by 60 to get the minutes):

You can also ask for alternatives, assuming &alternatives=true is added to the URL (stored in B21), the same function that showed 1 route summary, now shows a list.
The spreadsheet automatically sees it is an array of results that need to be displayed.

Let’s calculate the average distance of the routes:

Average works fine with an array.
The spreadsheet will get confused if you try to perform a calculation on an array, but that is easy to fix using arrayformula().

The XML contains latitude + longitude information, each step of the route has a lat+long pair.
The lat/long of the start location can be found in the the first step of the route list.


The lat/long of the destination can be found in the last step of the route list, there’s an XPath predicate for that: [last()].

130 Responses to “Calculate distance in Google spreadsheet”

  1. Mark says:

    Windfred,

    Wonderful work. I was working on a similar project, decided to look for some help online and found your spreadsheet. Exactly what I needed. Thank you for making your work available to everyone.

    Im curious if there is a way to measure “asTheCrowFlies” in the multi address tab. I have played with a number of the inputs and even tried to mess with the queries to no avail. That would be a very valuable function.

    Thanks in advance. Mark

    • winfred says:

      Thanks Mark! For asthecrowflies, this function is currently expecting lat+lon arguments for from&to. See the web page or function overview tab. In a next version I will make it so you can use regular locations.

    • winfred says:

      Mark, v2.3 has an updated asTheCrowFlies() function. You can now use regular from+to parameters. If you want to use lat+lon, use asTheCrowFliesLatLon(). Enjoy!

  2. Tim says:

    This tool is incredible. I need to calculate mileage for my wife’s business and this will vastly reduce the time it takes to do so. The add to trip log button with corresponding log is AWESOME.

  3. Michiel says:

    Dank voor de perfecte tool!!
    Is dit ook mogelijk voor de ANWB (routeplanner) site?

  4. RabiSan says:

    Hi Winfred,
    very helpful tool – really appreciate your work. This file help me a lot with my internal tool for measurement of drivers work/efficiency.
    I had a small/kind request: Any chance to implement inside your “routeDistance” function additional parameters? I mean those which are described here: https://developers.google.com/maps/documentation/directions/#Restrictions (choosing one or all of them: avoid=tolls, avoid=highways, avoid=ferries). If happened – will save me a lot of time :-).

    Regards – contented user :-)

    • winfred says:

      Thanks! Your request has been implemented (v2.3). Note the Google API does not support “ferries”. The Mapquest API supports: highways, tolls, seasonal closures, unpaved, ferries and country borders.

      • RabiSan says:

        just check new release! working perfect!!!! You’re master :-)

        • RabiSan says:

          BTW: i’m using new version of google spreadsheet – your functions works perfect (and also no problems with Image function when inputing pictures of adresses or directions). Best Regards!

  5. lucian says:

    Thank you. Great job. This is so helpful.

    I am not a smart man. I struggling to learn. I have no idea how to use the functions to avoid tolls always with all the addresses. I can see your reference but I don’t know where to put them. I see square and round parentheses …I can’t get it to work. I don’t even know where to start.
    Any suggestions will be highly appreciated. Thank you, please !

    I was able to add another field with a google real clock + transit time to tell me the actual ETA.

    Next I am going to duplicate your “distance” sheet.

    I always leave from point A to a pickup at B and a drop off at C and back to A
    Do the first “distance” duplicated sheet will tell me time and distance to point B It will have point A is a static address.
    The second “distance” duplicated sheet will tell me time and distance to point A where A is a static address

    A

    • winfred says:

      Sorry, not sure what your question is. Here is an example of a function avoiding tolls: =routeDistance(“McKinney Avenue, Dallas”, “DFW airport”,true,”driving”,”tolls”)

      • lucian says:

        - The reason it didn’t work is because it will replace the (“) with a (*)

        - for some reason only the tolls needed the ” around it =routeSummary(from,to,false,miles,mode,”tolls”)

        - Is it possible to prevent from displaying the country name?

        - The 3 little maps don’t always show up. Specially the 1st one.
        I tried to poke around but since I don’t know what I am doing, I couldn’t figure it out.

        Thank you for your help.
        Lucian

  6. Ben says:

    Thanks for this file. I think it will be very useful for planning an office relocation, and finding new driving distances for workers. I’ll have to do it over several days, due to the 2500 transactions per day limit, but it will be a lot faster than looking them all up individually :) I just need to find out why some routes aren’t being found, given that they do work when I enter the addresses directly into Google Maps.

    Great work, you’ll save me lots of time. Thanks.

    • winfred says:

      Thanks! Would you happen to have an example (send me by mail) that did not work? Mapquest has fewer limits, although that one seems to be more picky about location lookups. See Background tab for details.

Leave a Reply

« | Home | »