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
« iTunes Match: remove low bitrate files after upgrade | Home | 2e Kamerverkiezingen: woorden in beeld »
Hello Winfred,
The spreadsheet is great!!!
I would like to calculate the distance between two zip codes in Germany. But I have not found a solution. Do you have an idea?
Thanks for your time! Regards Mario
Hallo Mario, thanks! Simply change the “region” setting to “DE” (cell F23). After that setting e.g. “from” to “80995” will result in “80995 Munich, Germany”. And if you set the “language” to “DE” as well (cell F24), it will show the results in German.
thanks!! 🙂
Brilliant spreadsheet. I was just getting started on a similar project and you’ve done 87% of the work for me. Nice.
Now if I could pick your brain for a few tweaks.
Do you see any way of using your spreadsheet to log when I cross a boundry? A US state line, for example?
I found this with coordinates:
https://www.google.com/fusiontables/data?docid=17aT9Ud-YnGiXdXEJUyycH2ocUqreOeKGbzCkUw#rows:id=1
My ultimate end project is to use IFTTT to automatically log my trips to a spreadsheet, at have it breakdown the state-by-state mileage for me, which I need for work.
Thanks in Dutch.
L
Hi L, thanks! For crossing state/country borders: neither the Google API nor the Mapquest API provide reliable info esp. if you stay on the same road. Google’s API can contain text like: “Passing through Georgia, South Carolina, North Carolina. Entering Virginia.”, with just one number: 784 miles (all I-95). Mapquest’s API has specific parameters for crossing borders, but same result as Google.
I’ve been looking for other data sources and… good news! In v2.2 created a new function =routeCrossing_mq() which gives the distances per state/country. It uses the Guidance API from Mapquest. That one does have detailed information about when state/country borders are crossed. Unfortunately that API gives inaccurate distances, so I calculate the distances from border to border using =routeDistance_mq(). There is a lot of compute work, so it will take a while before you see the result.
wow. That was quick! Thanks, Winfred!
THANKS WINFRED! These functions are great. I really appreciate your contribution!
The spreadsheet is great. I do have some issues with the multiple tab. Some times I will get an error but when I type the same exact zip code on the distance calculator tab I get an answer.
I also tried the MapQuest version. I got a key from the website but I still have FALSE in the mapquest key ok cell.
Thank you for your time.
Dan
Hi Dan, thanks! For the Mapquest version, I made a small change (v2.1.1) that should help with that issue. Can you check? For the multi address tab, feel free to send me a mail with details.
Just got the fix. Thanks.
Thanks for letting me know and for the kind words. Enjoy!
Hi Winifred,
I’m a big fan of your work and can’t wait to try out the new 2.0 version of this sheet with the custom functions. However, when I downloaded the new template, the functions were not built in to the sheet. Before I use the functions tab to re-write all of them, I wanted to see if there was an automatic way to load all of the functions. Please advise. Thanks.
-Max
Hi Winfred! Template looks great but when I use it in my account, the copy it creates has no scripts so none of the functions work. Am I missing something obvious here?
There was indeed an issue. Not sure what the cause was, but when I checked just now it was working again. Can you download the template again? Thanks for reporting the issue.
It’s working perfectly now! Thanks. It was driving me crazy trying to get it working!
Hi, Winfred!
Thanks for the article. It helped but i can’t get it to work. The distances I get are not right.
I’m a truck driver and wanted to built the google drive spadsheet to calculate my driven miles
I know that it would be impudent, but maybe you could take a look at this document: https://docs.google.com/spreadsheet/ccc?key=0AnotdPv6RbM_dHRLTWlwM2pXdEo3ZWtPNTluWjZxRnc&usp=docslist_api
If you can help I need your email to add you.
Thanks
Can you let me know what is not right? Could it be miles vs. kilometers? Feel free to send mail (see contact page).
One more question. Where does you “asTheCrowFlies” formula come from? It doesn’t seem to be working for me.
should be fixed in version 2.0
Hi Winfred,
Is it possible to get the total miles separated by state? For example, if the route is from New York to California, will this application be able to tell me how many miles were driven per state in between point A and B. If not, is there a chance we might see a similar feature in the near future?
I am not aware Google offers this functionality in the API. Sorry!
Hi Tony, the feature you requested was added in v2.2 of the spreadsheet. Enjoy!
Hi Winfred,
I have used your spreadsheet for quite a while now, and in the last three months have had quite a few issues with the spreadsheet not working. I tried downloading your copy (a clean copy, as I have made quite a few changes), but the results are exactly the same. Sometimes I get an answer, but more often now I get #N/A for all results and the maps are very spotty. sometimes they show up, sometimes they don’t.
Have you had any technical issues with your spreadsheet? My friend seemed to think that perhaps the importxml command might be having an issue.
Thanks for your time
Thanks for reporting it Corry. I am seeing similar issues recently. I have been working on another solution. Stay tuned.
In version 2.0 I use custom functions instead of importXML. This seems to be more stable. Maps are still spotty, seems to be a Google spreadsheet issue.
There still is a limitation: 2500 requests per day (before: 50 importXML functions per spreadsheet).
I am doing some research on distances between (many) households and (many) healthcare providers — an automated solution whereby one inputs the locations of households, and in a separate column the locations of providers would be very helpful. Thanks.
Unfortunately that would be outside the scope of this spreadsheet. In general, with free tools you will run into limitations related to the geocoding (from address to lat/long location) or calculating directions.
OK, thanks. I will see if I can find a commercial vendor.
I need to get just the mileage between two addresses – starting address H2, ending address is on Sheet 2 cell B1. What’s the best way? I’ve tried several different formulas I found in the Google Docs forums, but with no luck!
Hello Kim, it maybe that I misunderstand your question but here goes.
1) in my spreadsheet I use the generic URL structure:
=”http://maps.googleapis.com/maps/api/directions/xml?origin=” & cell_from & “&destination=” & cell_to & “&sensor=false”
2) to refer to a cell in another sheet use: ‘Sheet2’!B1
3) in your case the URL would then look like:
=”http://maps.googleapis.com/maps/api/directions/xml?origin=” & H2 & “&destination=” & ‘Sheet2’!B1 & “&sensor=false”
Thank you! I don’t suppose there’s any other option than importxml, is there? I know there’s a 50 importxml limit per sheet and I’ve got about 680 records I need to perform this function on. I’m comparing mileage from 3 of our different warehouses to one location to see which is the shortest distance. I copied and pasted the importxml statement, got the mileage and then copied the values to another cell, erase the xml and went to the next group. I’d prefer to leave a formula plugged in the cell, in case some of the locations change.
I appreciate your help so much!
Kim
hey did you ever find a solution? i have over 2000 records i need the distance for!
any site that lets you upload a spreadsheet?
or
any free enterprise license from google maps or mapquest?
I am working on a solution, stay tuned.
In version 2.0 I use custom functions instead of importXML.
There still is a limitation: 2500 requests per day (before: 50 importXML functions per spreadsheet).
I am also in the need of running more than 50 distances. Other than that, this app is great.
This is amazing work. and if the solution for running more than 50 is found, please let us know! Thanks!
Thanks! The function based solution (in version 2.0) might be the solution for you. There still is a limitation, 2500 requests per day.
and I forgot to subscribe to replies…:)
In version 2.0 I use custom functions instead of importXML.
There still is a limitation: 2500 requests per day (before: 50 importXML functions per spreadsheet).
Hello Kim, in case it helps: in version 2.0 I use custom functions instead of importXML.
There still is a limitation: 2500 requests per day (before: 50 importXML functions per spreadsheet).
Hi,
Great spreadsheet, if i change the mode in settings from driving walking, will that give me a nearest figure available to ‘as the crow flies in a populated area?
Thanks!
No, not ‘as the crow flies’ but actual walking distance using Google Maps directions. To see the as the crow flies distance (same for driving and walking), unhide the bottom rows (row 43).
Really an fantastic spreadsheet! I’m actually amazed this is even possible in google docs! Thanks so much for sharing!
I have a question about the map zoom levels…
For example on your default template, if you adjust the zoom to ’10’ you’ll see that the New York map no longer displays in cell C14, even though the URL is cell A32 still links to a functional image.
Do you have any idea why this might be happening? For my application, I’ll probably be looking for zoom level of around 15 and it would be great to figure out how to get it to display correctly within the spreadsheet.
Many thanks,
Ben
Thanks for the kind words!
I can not test at the moment, could it be the image is being cached by Google? You could try to add a random parameter at the end, to fool Google in thinking it is a unique URL. E.g. url-to-image?1234
Hi Winfred,
Many thanks for getting back to me!
If the original URL inserted in the image is… http://maps.google.com/maps/api/staticmap?markers=color:green|1600 Pennsylvania Avenue, DC&zoom=12&size=280×140&sensor=false
…where should I add the ?1234
I tried the following but got an error message…
http://maps.google.com/maps/api/staticmap?markers=color:green|1600 Pennsylvania Avenue, DC&zoom=12&size=280×140&sensor=false?1234
By the way the error message displaying the in C14 map image cell is “error: No image was found at the URL: ” … I should’ve mentioned that in the initial comment sorry. That message comes up despite the fact that the link works just fine if used directly.
Best Regards,
Ben
Sorry about the delay, I was out of town.
In general: when including images in a Google Spreadsheet your mileage may vary (no pun intended).
After you unhide the bottom rows, when clicking on the links (A32-A34) you will probably see that the images open fine in a new window.
I indeed see the same N/A message and the error message as you see.
Sometimes it helps to add a random parameter at the end, which forces to load the link instead of caching it.
For this spreadsheet you could change the image link (but it seems that does not really help):
…sensor=false&random=” & rand()