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 »
I have set the region to US and I am using V3. Which tab do you suggest I do this in? I have been using the trip log.
Thank you,
Cynthiya
It appears not all the services can deal with entering only a zip code. I will put this on the list for a future version. Workaround: add the country name, e.g. “90210, USA”.
Hello Winfred,
This spreadsheet is so helpful! I am trying to do the same thing however in the US. When I use the zip code it is giving me an error. Would you be able to advise on what to do?
Thank you,
Hi Cindy, the spreadsheet should work internationally.
When you use Google as the provider, set the region to “us”. This so it will search first in that region.
Are you using v3 of the spreadsheet?
Hi Winfred,
when I open the link to your spreadsheet, I am not able make a copy of it 🙁
Most GUI elements are disabled (shown grayed) and there is an icon on the top of the page that says “You are offline”.
Do you have any idea how to solve this? Maybe you could publish the script code somewhere else.
Thanks for the great work anyway.
Regards,
Greg
Greg, I am having the same problem!
Ah! Greg,
Try this link:
https://drive.google.com/previewtemplate?id=0AjmrJQ99vtxhdHBTZEg3SmRicS00MVNESXZJYVhvY0E&ddrp=1#
I then selected “Use this template” and it loaded it into my own Drive documents.
Hope this helps!
Molly
Molly, that’s the old version of the template (v2.3). The new one is (not yet) available as template, only as sheet that you can create a copy of. Greg reported that the issue he saw disappeared after a while, maybe a Google glitch?
Hi,
I opened the link on this website and the spreadsheet was marked as offline.
Then I closed the spreadsheet and opened the google spreadsheets overview: https://docs.google.com/spreadsheets/
There you will find a link to Winfreds distance calculator. When I opened it, I was able to make a copy.
Cheers,
Greg
Thanks Greg! I have included this tip in the download information section.
Hi Winfred,
This is a fantastic program Tahnks.
Off on driving vacation to europe and this is just what I needed.
For some reason the directions tab isnt working for me. there is an Error in cell B1 the upper left hand cell. I am also not familiar with the continue function…
Thanks
steve
This is fantastic ! I could use this to analyse a spreadsheet of my Google agenda (exported with GTimeReport) to calculate the business use of my car !
I go from my residence to client’s homes and back to my residence.
I can’t do this alone. I only need the “Multiple addresses” function. Would someone help me ?
In the included example, before I apply the function, I would need lines added if I have time to go back home (H2V) between two appointments so the driving distance to and from home is considered… can this be done in script ?
https://docs.google.com/spreadsheets/d/1nU27VatV7K-KVJP-g5oxpkgJDtMMQ4xj5N90GBLriXk/edit?usp=sharing
Thank you !
I am about to publish v3.0, which has support for multiple destinations. Stay tuned!
I really appreciate all of the time you have put into this. Is it possible to use my google maps API when requesting distance calculation from Google? Where would I add that API key? I have hit the daily limit! Thanks a million.
Do you have a business API key? Only then would it help. I have put it on the request list, but no plans to implement it as of now.
Great Work Man,
I have been looking for such kind of convenient way for a long time. But still I am facing some problems. I am going to input about 600 point with lat-lon. and i want to know driving distance from each of them individually. Its about 36000 calculations. all i have, just the lat-lon of the location. But as per your instruction I changed the country code to BD=bangladesh, but it didn’t worked..
please reply me how can I work on it?
36000 calculations is probably beyond the scope. But in general, you can use the routeDistance function to calculate the distance between lat/lon locations. No need to set the region for that.
Do you have any idea how many hours this has saved me over the past year or so. Life SAVER! Thank you so much.
Both my pc and phone have GPS. Is there a way to replace one of the addresses with “get GPS location”?
Thank you.
Nice idea. But sorry, Google Spreadsheet does not offer that functionality.
Can you do that in Excel … for a price of course 🙂
Not that I am aware of.
Ok, ok, then I will double my previous offer. Let me know.
🙂
I built something similar (not even close) in Excel mostly to get the ETA to some location based on what google says. Having that online makes things a little faster because I don’t have to pull out 2 devices. The only downside is that is freezes from time to time. It doesn’t pull out the address. It just “working”. It was worst on Chrome. Firefox is better.
I get to use it for one or two addresses a couple of times per week.
Thank you
Good news! The other day Google made some changes and now it is possible to determine the current location, and copy it to your spreadsheet. In v3.2 use the new “Distance calculator” menu button.
Lucian: time to hit the donation button. 😉
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.
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.
There were only about 10 in 500 that didn’t work. I fixed them up manually before I saw your message, sorry. I’ve used up my transaction limit for today, but I’ll do some more tomorrow, and see if I get any then. Do you know if there’s a way to get passed the 2500 transaction limit, like with a subscription? Thanks.
Good to hear. This spreadsheet is targeted at the free Google Maps API. For more information about Google Maps for Business: http://www.google.com/enterprise/mapsearth/products/mapsapi.html
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
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”)
– 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
Google Spreadsheet indeed sometimes has trouble with the images. The image location itself is valid, and you can open it in a separate window (see rows 43-45).
The spreadsheet shows the location as Google returns it, which is including the country name.
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 🙂
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.
just check new release! working perfect!!!! You’re master 🙂
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!
Dear Winfred – just to share with you about some extra feature which was build ontop to your script.
Inside my file i had a maximum 7 locations (which i put on cels A1 to A7). Then there is additional function routeMapUrl7.
To have a link to image just put in spreadsheet:
=routeMapUrl7(A1;A2;A3;if(A4=””;A3;A4);if(A5=””;if(A4=””;A3;A4);A5);if(A6=””;if(A5=””;if(A4=””;A3;A4);A5);A6);if(A7=””;if(A6=””;if(A5=””;if(A4=””;A3;A4);A5);A6);A7))
* – hint – with those if’s you dont need to put data to all A1-A7 cells (only A1;A2 is needed)
function script to paste inside script tool:
// ———————————————————————————
/**
* return image url for map showing from, thru and to locations
*
* @param {string} from the departure location
* @param {string} thru1 location
* @param {string} thru2 location
* @param {string} thru3 location
* @param {string} thru4 location
* @param {string} thru5 location
* @param {string} to the arrival location
* @param {number} width width of the image
* @param {?number} height optional: height of the image
* @return {string} url the image URL
* @customfunction
*/
function routeMapUrl7(from,thru1,thru2,thru3,thru4,thru5,to,width,height) {
if (!from || !to) throw “provide location(s)”;
if ( (width && typeof width != “number”) || (height && typeof height != “number”) ) throw “width & height should be numbers”;
var width = width || 300; // default width = 300
var height = height || width; // default height = width
var map = Maps.newStaticMap()
.setSize(width, height)
map
.setMarkerStyle(Maps.StaticMap.MarkerSize.MID, Maps.StaticMap.Color.GRAY , ‘0’)
.addMarker(from)
.setMarkerStyle(Maps.StaticMap.MarkerSize.MID, Maps.StaticMap.Color.GREEN , ‘1’)
.addMarker(thru1)
.setMarkerStyle(Maps.StaticMap.MarkerSize.MID, Maps.StaticMap.Color.GREEN , ‘2’)
.addMarker(thru2)
.setMarkerStyle(Maps.StaticMap.MarkerSize.MID, Maps.StaticMap.Color.GRAY , ‘3’)
.addMarker(thru3)
.setMarkerStyle(Maps.StaticMap.MarkerSize.MID, Maps.StaticMap.Color.GRAY , ‘4’)
.addMarker(thru4)
.setMarkerStyle(Maps.StaticMap.MarkerSize.MID, Maps.StaticMap.Color.GRAY , ‘5’)
.addMarker(thru5)
.setMarkerStyle(Maps.StaticMap.MarkerSize.MID, Maps.StaticMap.Color.GRAY , ‘6’)
.addMarker(to);
map.beginPath()
.addAddress(from)
.addAddress(thru1)
.addAddress(thru2)
.addAddress(thru3)
.addAddress(thru4)
.addAddress(thru5)
.addAddress(to)
.endPath();
Thanks! I am about to publish v3.0, which has lots of new features including a generic way of dealing with multiple locations.
Dank voor de perfecte tool!!
Is dit ook mogelijk voor de ANWB (routeplanner) site?
Graag gedaan Michiel. Bij mijn weten heeft de ANWB geen externe toegang, dus dat zal niet lukken. Waarom wil je specifiek ANWB?
Vanwege reiskostenvergoeding berekenen van medewerkers. Dit kan natuurlijk net zo goed met GMaps, maar toevallig is er door HR ooit voor gekozen om de ANWB aan te houden..
Ok, begrepen. Zou inderdaad niet veel moeten uitmaken.
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.
Thanks Tim, made my day!
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
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.
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!