Calculate distance in Google spreadsheet | Winfred van Kuijk

Calculate distance in Google spreadsheet

By • Published: December 3, 2010 • Last updated: March 27, 2017 • Filed in: Software

Seeing “internal error executing the custom function”? Appears to be an intermittent Google issue. Try again later.
Using Mapquest as provider and seeing errors? Most likely monthly quota of requests is used. See background tab for details of limitations.

Distance calculator v3.2This 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 spreadsheet 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 options and the 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, to me or your favorite charity.


Download the spreadsheet from the Google template directory.

December 2014: it is now possible to determine your current location. See release overview.
Got a minute? Do you mind rating the template?

Ready to go

The template can be used as-is:

  • Calculate driving/walking/bicycle distance & duration.
  • Add the results to a trip log. Great for preparing a road trip or your mileage report.
  • Quickly lookup an address for an attraction, or find out the latitude/longitude coordinates.
  • Specify a list of destinations and let the spreadsheet calculate the shortest route.
  • Get a list of turn-by-turn driving instructions.
  • When visiting multiple states/countries: see what distance/duration is spent in each.
  • Use either Google or Mapquest as the provider.

To use the template as-is: just open the template and enter the locations / settings.


The second way the template can be used: change it to your liking.
All functionality is provided using custom functions. Simple example:

A1: Paris
A2: Berlin
A3: =routeDistance(A1,A2)

In fact, you could use these functions in your own spreadsheet as well.
In that case: go to Tools > Script Editor… and copy the scripts (miscellaneous, google and mapquest) to your own spreadsheet.
Or the other way around: delete the sheets you don’t need and then copy the sheets from your other spreadsheet to this one.


When you use the template for the first time, it will ask for permissions.
This so the script can read + write your spreadsheet.
I have no access to your data. The only data that is transmitted is the data to Google/Mapquest to get the directions etc.




  • Google UrlFetch: is used to contact the Mapquest API. UrlFetch has a quota of 20,000 requests per day.
  • too many requests in a short time: the spreadsheet tries to deal with many simultaneous requests, but it is still possible to get a message “Service invoked too many times in a short time: urlfetch”.
  • custom functions: Mapquest has recently introduced usage limits. The spreadsheet uses a shared API key, good for 15,000 requests per month. Heavy user or error messages? You can create your own API key and put it in field E34 (hidden by default). I am working on a more elegant solution.
  • Mapquest has trouble calculating driving directions for a place in a pedestrian zone (e.g. “disneyland”), and in general the geocoding from Google is more helpful.
  • When you lookup an address, it is best to use one of the supported single-line location formats.


  • 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.
    note: v2.3 is the last version using the old version of Google Sheets. It will stay available but is no longer maintained. Use v3 or higher instead.
  • 3.0: major update:
    • Uses the new Google Sheets. It takes advantage of better formatting, conditional formatting and last but not least: autocomplete for custom functions and built-in help for function parameters.
    • Multi-location support. Either select a range of cells in the spreadsheet (e.g. A2:A6), or enter a destination separated by underscore, pipe or slash (e.g. “berlin/hamburg / hannover”).
    • Support for optimizing routes. Say you visit Paris, Berlin, Zurich and Amsterdam. The spreadsheet will suggest Paris, Zurich, Berlin, Amsterdam as it is 600 kilometers shorter.
    • Extra settings in the dashboard, no need to add them as parameters in the custom functions.
    • Better image support. It shows the waypoints, and the detailed route. The latest Google image API is used.
    • Better and transparent support for both Google and Mapquest. No need anymore for your own Mapquest key, it is built in. No need to switch custom functions (e.g. add _mq to the function name if you want to use Mapquest), just define what provider you want to use in the settings. Location lookup for Mapquest has improved, it now recognizes addresses, lat/lon locations, but also attractions. Easily switch between Google and Mapquest. Both have their pros and cons.

    Note: the custom functions have been changed quite a bit. As a result the parameters have changed since v2.3. See the built-in help for the latest syntax.

  • 3.1: if there are too many requests in a short period: add a delay and try again.
  • 3.2: implementation of user request: determine current location. Click on lat+lon or address button to copy the result to the active cell in the spreadsheet. Does not work in older browsers (including Internet Explorer 9). Available through new “Distance calculator” menu.
    Distance calculator - current location - annotated
If you use Google as the directions provider:
there is a daily limit of 2500 direction requests and 2500 location requests per user.
If you use Mapquest as the directions provider: 15,000 requests per month for the shared API key, you can use your own key. See background 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 results
locationAddress full address (or only zip code) for searched location
zipAddress zip code for location
locationLatLon string with latitude + longitude combination of searched location
locationMapUrl URL of image showing a single location
routeMapUrl URL of image showing the departure and arrival locations
routeDistance distance between locations in miles or km
routeDuration duration between locations in minutes
routeSummary summary of route(s): which route, distance, duration
routeDirections table with direction steps
asTheCrowFlies as the crow flies (straight line) distance between locations
asTheCrowFliesLatLon as the crow flies (straight line) distance between coordinates
routeCrossing_mq Mapquest only. table with distance and duration per state/country


example result
=routeDistance(“paris”,”berlin”) 1048.172 [km]
=routeDistance(“paris”,”berlin”, “tolls|highways”) 1384.4 [km] – avoiding toll & highways
=routeDistance(“paris”,”berlin / zurich / amsterdam”,,true) visit Paris, Zurich, Berlin and then Amsterdam (=optimized route)
=routeDuration(“paris”,”berlin”)/60 573 [minutes]
=routeDuration(“paris”,”berlin”,,,,”bicycling”)/3600 51 [hours]
=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.8976831, -77.0364972”) The White House, President’s Park, Washington, DC 20502, USA
=locationLatLon(“white house, washington”) 38.8976831, -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
This page gives details about using importXML, which was the original method of getting directions information. It has since been replaced by custom functions which directly access the API. Therefore, importXML is not really needed anymore. Will keep the instructions online in case you want to learn about importXML.

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.

Note:In the old version of Google Sheets there is a limit of 50x importXML in a spreadsheet.
It does not always work reliably, you then get #N/A.

Google Maps API for directions

Example URL:

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


  • 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.

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.

=importxml(B20,"//leg/step[1]/start_location/lat") &" "& importxml(B20,"//leg/step[1]/start_location/lng")

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()].

=importxml(B20,"//leg/step[last()]/end_location/lat") &" "& importxml(B20,"//leg/step[last()]/end_location/lng")

253 Responses to “Calculate distance in Google spreadsheet”

  1. Naresh Kumar says:

    Dear Sir

    I would like to thank for you amazing work,But the distance calculator sheet has certain limitations which hampers our work,Please give a light on an issue which requires a fix.We are able to download only 50 directions. Which needs to be increased.

    Do let us know your feedback or any suggestion or alternative on the same is much appreciable.

    Naresh Kumar

  2. Naresh Kumar says:

    if we make several route plan a day then show message
    “service invoked too many time of the day: route (line 485)”

    please give solution

  3. Blaine says:


    Fantastic sheet. We would like to use it in a possible product. Can you email me so we can discuses what possibilities there are.


  4. Brenda says:

    Hi Winfred,
    Great template. I’m having a lot of trouble with the asTheCrowFlies function giving incorrect answers. I’ve tried both the original formula =if(and(C$21″”,C27″”),asTheCrowFlies(C$21,C27),””) with routeDistance replaced, and another from this page =asTheCrowFlies(C$21,C27,,,,true). routeDistance works fine in both, asTheCrowFlies returns a number that is consistently larger than the routeDistance (3,508 for Vancouver BC to New Orleans LA, whereas routeDistance is 2,750.
    Any suggestions?

  5. avram says:

    Great job winfred!
    I already made a donation!

  6. Jake says:


    This is an impressive spreadsheet. I wonder if you could help trouble shoot one I currently have? Its a spreadsheet that I am trying to do something similar and I am not having luck. My spreadsheet has 20 of my companies plant locations set in cells and then one variable cell. The idea is to set the shipping location in the variable cell and have the spreadsheet calculate the distance to each plant. I have been using the google built in api but I get the error – TypeError: Cannot read property “legs” from undefined. This error pops up at random times for random locations, as in it works 60% or so of the time and it will fail on locations and work on the same locations randomly.

    Do you know if there is a way I could use a portion of your mapquest api to accomplish this? I was going to attempt to set up a mapquest api but there site is shut down for free keys. Or do you know how this problem can be solved with the google api?

    Any help would be great and again nice spreadsheet. Wish I could put one that indepth together.

    • winfred says:

      Thanks Jake. You can use the spreadsheet, create a new sheet and put there the one variable and 20 locations. You could remove the other sheets, although I would recommend keeping the first one. E.g. use B1 as the variable cell, put the company plant locations in A2-A21. Then put the formula in B2-B21, e.g. in B2: =routeDistance($B$1,A2,,,,true) [the true to make sure miles are used].

      • Jake says:

        Thanks for the quick response. Is there anyway I could remove the small portion of code that I would need from the script and bring that function into my spreadsheet? I ask because I have a lot of other data and sheets in the spreadsheet that I am looking to add this to.

        • winfred says:

          Because it is set up to be used in many situations + configurations, it difficult to extract a small portion of the code. You could copy all the code though (go to script editor, create project and the script files and then copy all files). Otherwise use the contact form to reach out (for custom work a donation to me / your favorite cause is appreciated).

  7. Patrick says:

    How do I use the =asthecrowflies function with the multi-address tab?

    Everything I get is driving miles when I want short miles

    • winfred says:

      E.g. in cell D16 of multi-address (or any tab):
      =asTheCrowFlies(B6,C6) ==> results in 311 (km) -from Memphis, TN to Brentwood, TN- versus 334 km driving distance.

  8. TonyS says:


    Excellent work it’s a beautifully crafted spreadsheet. I am trying to create a simple mileage estimator for driving between locations in the UK, so that I input 2 locations and it autogenerates a mileage. How do I get it to default to, for example, Birmingham UK rather than Birmingham AL (I’ve set region to GB, is that enough?).

    Link here:

    Please give me details for donating 🙂

    Many thanks

    • winfred says:

      Thanks! Setting region to GB should do it, but that does not work when using MapQuest as provider. You could always enter “Birmingham, UK” as
      Details for donating: see the PayPal link or send me direct message.

  9. MBS says:

    Thanks for the quick reply.

    Im assuming that when you say user, that is each separate Google account?


  10. MBS says:

    Hi, is it the individual sheet that has a limit of 2,500 requests per day?

    Is there a way around this? Maybe by creating several sheets.


  11. MBS says:

    Great work!

    Is there a way to choose any of the alternative routes and have these posted to the log sheet?

  12. Steve says:

    Is there a way to incorporate estimated drive time between multiple locations?

  13. SteveR says:

    Hi Winfred,

    As with many of the other comments here – great work, thank you!

    Is there any way of working out the travel time at different times of the day to account for traffic?


  14. Hans says:

    Hi Winfred,

    Nice functions and useful. I noticed unfortunately 2 issues:

    1. Almost every time when I set Google as a provider (or it has been set) and I’m opening the sheet the first time of the day, I see that the function has already been called too many times for that day. Not by me, since I couldn’t even start using it. I suppose this is shared by all users if the sheet and they must make many requests? If so, would it be possible to set your own key or something within this service, making the requests of a person unique?

    2. The quality of the Maprequest service is in many cases very poor (for the Netherlands). Even tough it catches the correct zip, then it still displays a wrong location. For example compare looking up ‘ Nijverdal’ with Google and Maprequest (situated in the East whereas Maprequest shows it in the South). The same applies to the city of ‘Hilversum’ (situated about the Middle of the Netherlands whereas Maprequest shows a village in the very North).

    Kind regards,

    • winfred says:

      Thanks Hans.
      1. I see+hear this more often. Every user should have his/her own quota. Go to Tools > Script Editor. In the script editor go to Resources > Developers Console Project. This should show a popup with the project the script is currently associated with. Click that link. Under APIs & auth, click API’s and make sure the following are enabled:
      – Directions API
      – Geocoding API
      You can then select either, and click on Usage to see how much is being used. When I tested just now the usage stayed at 0, not clear why that is.
      I will keep an eye on:

      2. Agreed, Mapquest data is not always great. In what situation do you get the incorrect Hilversum?
      This is also because their different services can have different results (and pros and cons). Especially the nominatim vs the regular geocoding service.
      I plan to look at that for a future version.

      • Hans says:

        Hi Winfred,

        Thanks a lot!! While I verified the access to the Directions and Geocoding APIs, it appeared first when I clicked on the link for the project the script is associated with, that there has been an updated terms of service that I had to agree with, and a checkbox that I needed to check in order to agree with these updated terms. After doing so, I found out that these APIs were turned OFF for me. So then I simply switched them ON and then revisited the sheets with your distance calculator functions and yes it does work now!
        I am unsure whether this may or may not be applicable to other visitors but these steps are definitely worth checking!

        In regards to your question about Hilversum: when I enter this as “to” field in the “distance” tab (and for example choose ‘ Utrecht’ as “from” field, then the Mapquest data shows a village in North-Holland (in the neighbourhood of ‘ Alkmaar’ whereas expected would be slightly North above ‘Utrecht’). Honestly I must say that Google also plots ‘ Soest’ in Germany by only providing that name, but when further specifying the region, that is ‘ Soest, Utrecht’ then it does return the correct data. So I suppose both have their perks, be it that I still prefer Google. Many thanks for your background information!

        Thank you and kind regards,

  15. Ron says:

    When using the mass address distance calculator how can I make it show the distance in more detail? I would like to see the exact driving distance not just rounded to the nearest mile.

    Thank you so much for this amazing program.

Leave a Reply

« | Home | »