Calculate distance in Google spreadsheet
Using Mapquest as provider and seeing errors? Most likely monthly quota of requests is used. See background tab for details of limitations.
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 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.
Instructions
Download the spreadsheet from the Google template directory.
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.
Customize!
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.
Permissions
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.
Limitations
- custom functions: 2500 requests per day per system (2500x directions and 2500x address lookup).
- not really used anymore but just in case (if you use the old version of Google Sheets), importXML: 50 importXML instances per spreadsheet.
Mapquest
- 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.
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.
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.
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 |
examples
| 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 |
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:
http://maps.googleapis.com/maps/api/directions/xml?origin=miami&destination=atlanta&sensor=false
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.
The base command to use to get e.g. the distance (depending on your units used: in feet or meters):
=importxml($B$20,"//leg/distance/value")
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”)
=importxml(B20,"//leg[1]/start_address") =importxml(B20,"//leg[last()]/end_address")
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:
=index(importxml(B20,"//leg/start_address"),1) =index(importxml(B20,"//leg/end_address"),1)
Show the label of the preferred route (e.g. “A28 and A73”):
=importxml(B20,"//route/summary")
Calculate the distance of the preferred route:
=roundup(importxml(B20,"//leg/distance/value")/1000,0)
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):
=roundup(importxml(B20,"//leg/duration/value")/60,0)
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:
=roundup(average(importxml(B21,"//leg/distance/value"))/1000,0)
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().
=arrayformula(roundup(importxml(B21,"//leg/distance/value")/1000,0))
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”
Leave a Reply to winfred
« iTunes Match: remove low bitrate files after upgrade | Home | 2e Kamerverkiezingen: woorden in beeld »
Soo, I am only using this application once a week. I get this error that I reached my limit. Where is this API key? It’s either everybody is using mine or I am using someone else’s key. Is it?
How to schedule copy paste for every minute between two time reference in google spreadsheet?
Function routeSummary() don’t work only with from: and to: address why? Return #REF Error and routeMapUrl return the “Provide Locations” error. This don’t make any sense.
Is there a way to use addresses instead of just city and state in the ‘multi addresses’ tab. Also can it calculate time?
Oh by the way thanks for taking the time to do this spreadsheet.
Is there a way to incorporate traffic in the estimates, same way Google Maps does?
Thanks!
[…] I used a custom function from Calculate distance in Google spreadsheet | Winfred van Kuijk The biggest challenge was working around the API […]
Can you share how you worked around the API? That’s the single most frustrating part of this otherwise quite promising looking spreadsheet. It worked fine for a while but now I am getting all the same error messages with no obvious way around it.
I’ve searched through the rest of the comments and really haven’t found an answer for this.
Just copied a number of addresses (approximately 300) to multiaddress tab. Most of them returned the distances while others returned an Error (“Service invoked too many times in a short time: urlfetch protected host rateMax. Try Utilities.sleep(1000) between calls. (line 486)).” So then I tried created distance calculation in my own tab and again copying in the address. Then I used =routeDistance_g (A1,A2) and still got an Error (Service invoked too many times for one day: route. (line 486). If the limit is 2500 requests, then how is it possible I’m going over with only 300 request? If the issue is too many request in a short period of time then why did the multitab go from having some populated with distances to now all of them are populated with an Error (Service invoked too many times for one day: route. (line 486)).?
In the past I think I’ve gotten this to work by playing with it over multiple days but I feel like I should be able to get everything calculated in one sitting. Any advise you could provide would be greatly appreciated.
Did you figure this out? This sheet appears useless to me because it always says to many requests in one day.
Hello , first thank you very much your Google spreadsheet is very well made and super efficient.
Is there a way to have direct time without taking into account the time including traffic?
Thanks in advance
Mathieu
can you tell me if you have any sample code on how to overcome the 2500 request limit from Google. I have an API key but I don’t know where to insert it into your script/functions. Thanks, Brian
Winfred,
I am trying to import this to an excel format sheet and I keep getting errors and it does not work in excel. Do you have it in an excel format?
This is a Google specific spreadsheet (e.g. the images, Google specific functions, the custom functions), not compatible with Excel.
Hi Winfred,
I really like the template, but the only modes are Driving, Walking and Biking. Is there a way to add Transit?
Thanks! Not at the moment no.
Your welcome .
Okay, that’s really too bad. I hope it’s something that can be done later on.
Hi there. I was hitting the transaction cap so I generated my own MapQuest key. Everything says to put it in E34, but I still get the errors. There are other cells around F25 to G26 that looked promising based on labels and highlighting, but they did not work.
I have not spotted the original key. I will keep looking. If I find it before you get a chance to respond, I will post again.
It looks like this moved to F26 in the sheet (although the code still looks at E34). Indeed confusing. Alternatively, go to Tools > Script Editor, open “mapquest.gs”, scroll to line 741 and insert your key there.
I found the default in the script and replaced yours with mine. That restored functionality.
Saw that the script is coded to check E34, but the “checker” is actually looking at F26. I have my key in E34 and F26, but it did not work until I put it in the script. Not sure where the glitch is, but wanted to provide what I found.
Thanks for reporting it!
Hi, I’m trying to use your google spreadsheet but I’m not sure it’s built to do what I want. I have a table of about 60 addresses and I’m trying to find out how far away each is from my home. Is there a way to fill in the trip log and have the distance to drive there calculated backwards for each? For example: could I fill in the trip log by pasting the table of addresses I already have in the “To” column and then pasting my home address in the “From” column and then have the distance and duration columns filled in for all of the addresses I pasted? Or is there another google sheet that you know of that would be able to do this for me? Thank you!
It should be possible to do what you want. The “trip log” sheet is intended to copy the results of a single from-to on the main sheet for logging purposes.
If you have 1 to-address (your home) and 60 from-addresses, you can mimic the functionality in the “multi-address” tab (bottom half).
e.g.
A1: Durham, NC [=to]
A2: Charlotte, NC [=from1]
A3: Fayetteville, NC [=from2]
Then B2 could contain =routeDistance(A2,A$1), C2 could contain =routeDuration(A2,A$1). Copy these formulas to B3 and C3, etc.
As you start typing the formulas you should see the various options you can use in each formula.
Hmm… this may be easier if i can just send you the google sheet of addresses and you’ll probably know what to do in 2 seconds. is there a way to send you the link that isn’t public?
You can use the contact form to send a mail. I can take a quick look next week or so.
I will. I do need it soon so I’ll try asking another question and see if I can do it. I’m using the Multi address tab but for the route duration it is coming out in seconds (eg: Duration is 8,347 instead of 2:19 or 2 hr and 19 min) and I don’t know how to change it into an hour and minute format. any shortcuts for that? thanks!
NVM! found it on the functions sheet. I think i have it working.
Great. For others wanting to do the same thing: divide the seconds by 86400 (= number of seconds in a day: 60*60*24), and apply the format “duration”.
Last question 🙂 I’m doing all of these on copies so I don’t damage my master address list. Once I have all the formulas/functions figured out, can I copy the Muli-address tab out of your google sheets or will those functions only work within your google sheet? Thank you for all your help!
The “multi address” tab is just an example. You can put the data (addresses + =route… functions) in any sheet you like (and indeed, it is fine to create new tabs). You need that spreadsheet because it contains the code to do the calculations. You don’t need any of the sheets, you can delete them although I recommend you keep the “distance” sheet. You can make a copy of the spreadsheet if you like, this copy will then include the code as well.
So I can copy the entire spreadsheet but not individual pages out of it? then it would loose the code, is this correct?
Winfred,
I have times when using the calc where the functions are not registering. the error that shows in the multi address sheet for instance says internal error executing custom function. If I hover over the formula it shows an prompt saying “unknown function route distance”. The script is still attached to the sheet, not sure why the sheet is not seeing the function. Any ideas?