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 »
Thanks for all the nice feedback. The Google Template has been updated and now includes map images.
Hi Winfred,
Are you able to use 1 source location and tell the milage of several destinations? I am doing something that requires me to get the distance for several locations from 1 location. For example I have a starting zip code of 14612 and I have a colum of zip codes i need to see the distance all in one shot
Hi Thomas,
You would have to create a custom spreadsheet for that.
A1: 14612 (your starting zip code)
A2: 10019 (example of destination zip code)
B2: ="http://maps.googleapis.com/maps/api/directions/xml?origin=" & A$1 & "&destination=" & A2 & "&sensor=false"
C2: =importxml(B2,"//leg/distance/value")*0.621371192/1000
A3: 14201 (another destination zip code)
B3: copy from B2
C3: copy from C2
C2 shows the distance in miles. Copy row 2 as many times as you have destinations (max 50).
Thanks so much, I’ve just sent the email through your contact section of this site.
Winfred, your spreadsheet is amazing and your idea to create a script to log the trips was equally as brilliant. I was attempting to move some of the key cells the script is measuring into different locations to suit my “natural tendencies” in terms of how I like to enter the data and realized that the script wasn’t adjusting with me. Is it possible to A.) have access to the actual script to add to the button? This script is amazing in and of itself… spreadsheet aside. OR B.) amend the script to look to different cells to retrieve the data to be logged? I hope that makes sense… I’m not a programmer. Thanks so much for your creation.
~Jason
Hi Jason, I am more than happy to amend the script. What kind of fields would you want to use? Feel free to drop me an e-mail.
-Winfred
Is there way way to use this to track multiple zip codes from 1 main zipcode? I am trying to find the driving distances in an area from a central zipcode.
(Hoi Winfred, of )
Hi Winfred, your tool is really helpful! i’m trying to use it to calculate the distance between multiple destinations and i figured so far this is done through waypoints. I actually have a little bit of a problem with them, well, that is: the way i used these waypoints it doesn’t seem to work.
This is what i’d done:
G2
=”http://maps.googleapis.com/maps/api/directions/xml?origin=”&A2&”&destination=”&B2&”&waypoints=location(“&C2&”)&sensor=false&units=metric®ion=be&mode=walking”
a2, b2 and c2 are different locations.
the result i get out if this is 0 km’s
through: =sum(importxml(G2,”//leg/distance/value”)/1000,0)
I hope it’s a not too big of a problem, and was hoping you to be able to correct me in this.
Groeten Erik
I fixed one step. that is: waypoints=”&C2&”, and not what is mentioned above.
looking at the xml results i noticed that i now have two legs, one extra for the additional waypoint.
so =sum(importxml(G2,”//leg/distance/value”)/1000,0) is not working out, since i only ask for one distance value, while i actually need to sum two (that is one more for the additional legs). is there a way to sum these values with only one importxml function? I reckon not because of the total distance isn’t parsed back as a result.
You probably noticed i’m not a programmer, so i once more ask for a ‘corrigerende tik’, and maybe a solution… 🙂
Hallo Erik,
Here’s a working example:
A1=
=”http://maps.googleapis.com/maps/api/directions/xml?origin=gent&destination=oostende&waypoints=brugge&sensor=false&units=metric®ion=be&mode=walking”
A2=
=sum(importxml(A1,”//leg/distance/value”))/1000
groet,
-Winfred
it look good for me but i have same hack to do…
i have a sheet with two column: origin, destination
about 500 row of address
how can i modify your tool if the address are always in the cell b16
sorry. with this tool can i found also the km and min for any kind of veichles (car, bus, bicycle, pedestrian)? and can i discover lat long of the two points?
The API does support travel modes: driving, walking, bicycling. E.g.
...&mode=walkingGood question about the lat/long, I have now added this as an example in the post above.
Winfred,
I think this is exactly what I have been looking for, so thank you. One question I need to import a number of starts and stops from an excel spreadsheet. Can you give me a brief how to on that? I’m desperate I have to recreate a mileage log from 5year old records and doing them one at a time is driving me crazy. Thanks in advance.
You would have to make a custom spreadsheet for that. But even then: you can only process 50 at a time.
Assume you have start location in column A and the destination in column B. Now add to column C something like:
="http://maps.googleapis.com/maps/api/directions/xml?origin="&A1&"&destination="&B1&"&sensor=false"
And add to column D:
=importxml(C1,"//leg/distance/value")/1000
Important restriction: you can only have importxml 50x in a spreadsheet.
The nicest way of doing it would be to create a script for it (which eliminates that limitation), using the Google Apps Scripts.
HI Winfried, I have just the same problem right now – I have a bunch of starting/destination locations which I need to calculate distance for. I tried using the instruction above you gave to jason, but for some reason it does not work… Excel won’t insert the distance into field C1… It keeps informing me that the formula contains an error… The API works fine, I checked the googleapis link and it gives correct data, but I think there is something not right with the importxml function you said to copy into column D… Or I’m doing something wrong?
Hi Michael, the example I gave had an incorrect period at the end, I fixed that now. You say Excel, just to be sure: importxml is specific for Google spreadsheet. In the example spreadsheet I have added a tab with 2 quick examples of using multiple addresses. Hope this helps!
Thanks so much! Now it’s working. I only have one last small thing. The distance is diplayed like this: 354.768. Any way of skipping the value that goes after .? I’d like to just have the “354” in the column.
Sure, just use the standard Google format functions. E.g. Format > Number > Rounded
Is it possible for the out put to show miles in each state for US? For example, Memphis, TN to LA, CA the result would show the total miles as now plus the miles in each State?
No, not really possible. Definitely not with the spreadsheet, but also tricky to deduce from the API output or maps website. E.g. http://maps.googleapis.com/maps/api/directions/xml?origin=Memphis+TN&destination=LA+CA&sensor=false You would have to process the XML output and manually add the distances per state until you encounter “Entering …” in the instructions.
Actually, even filtering for “Entering …” will not work. In the Memphis-LA example, after entering Arkansas, there is a single record for the next 1605 miles, where it simply says: “Passing through Oklahoma, Texas, New Mexico, Arizona. Entering California”.
Very useful, thanks!
the only problem I face, is that google limits the number of imports to a hundred. For my purpose I need more. I can expand but Google API premier is quite expensive. So my only hope is that Google will change their policy.
You might be a genius, dude.
Nice work. I put together a template for a cross-country (US) road-trip a couple of years ago but I hacked a distance formula by parsing an html page. The XML is *much* nicer and it was very easy to update my spreadsheet. Thanks for posting.
How do you change the units from metric? I would like my results in miles.
Thanks, Gary
See row 26, click on cell F26 to switch from metric to imperial.
Looking for a way to extract the zip from the =index(importxml($B$16,ʺ//leg/end_addressʺ),1) results. Do you know of any way to do this. The destination is just keyed as city and state. The results show the zip code. I need a way to just extract the zip code.
Thanks,
Tim
Hi Tim, unfortunately the output seems to be unpredictable and unstructured.
Thanks for the reply. So far I haven’t found a way.
Thank you for building this. It works really well. I am using it to track the mileage I use which is not reimbursed by my company, and it helps me in getting a mileage deduction on my taxes.
But I’ve wondered if there is a cleaner way to do this.
You see, I use 2 columns: START END
Under START, I type in my starting address. Under END I type in my ending address. But I go to 3 addresses a day, so there are 4 lines per day (A to B, B to C, C to D and D to A).
With 4 lines per day (minimum of 4 importXML commands) I hit the 50 command limit very quickly.
Is there a script that could be used to run the importXML command on each column, and return the mileage value without having to keep so many commands in the spreadsheet?
Let me know.
Thanks,
Todd Higley
todd.higley@gmail.com
Hi Todd, Was thinking about this as well, it’s doable using a script, where you copy the results to the bottom of a list on a separate sheet. Have not found the time to create the script though.
So, until that time you’ll have to copy the results manually. 🙁
Hi, have you created this script? I am trying to do exactly the same thing, but not being able because my programming knowledge is not that good. Thanks in advance!
Your question was exactly the trigger I needed to work on this. Good news: I have added a button that copies the results to a separate log sheet. Enjoy!