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 »
When using the multi address distance finder how would I show more decimals? I need more than just rounded to the mile.
Would love to “all at once” calculate distance from about 700 GPS coordinates (my customers) to 8 GPS coordinates (my stores). Alternately 700 to 1 completed 8 times. Can that be done? If so, how.
In short, see the bottom example of the ‘multi address’ tab.
– A2-A701: customer addresses
– B1: address shop 1
– B2: =routeDistance($B$1,A2)
– copy B2 to B3-B701
– copy column B to column C *as values*
– repeat for shops 2-8, copy values to column D-J
I was trying to modify your spreadsheet but I got some error message in the cell that calculate the distance. Can anyone tell me how I can fix it? https://docs.google.com/spreadsheets/d/1P_0riwZPKRckKPrDjP6i0e29lQ_POoLNj9-ann3RJ5A/edit?usp=sharing
Your example did not have the “distance” tab. The spreadsheet functions relied on having that tab, which contains the setting. This is fixed in v3.2.1.
is there a way to manipulate this spread sheet, so I can paste in GPS coordinates of two locations for approximately 10000 pairs and simply summarized in the next column: ie just shortest distance and time. Thank you!
You could have “lat, lon” pairs for from-to in columns A-B and then use e.g. =routeSummary(A1,A2,,true)
But, keep in mind there are quota limits and when using a formula the spreadsheet tries to call the function every time you open the spreadsheet.
I would recommend creating a custom little script that goes through all 10000 rows and puts the results in column C and D (as values, not formula’s).
You will still have to split running that over a couple of days (in the case of Google).
Good day sir,
First, this sheet is awesome!
Second, and here’s the questions, I’m trying to modify the function locationmapurl_g to return the satellite image, but what ever i try I either get an error or no change. Any and all help would be appreciated!
in Google.gs, after:
var map = Maps.newStaticMap()
add:
.setMapType(Maps.StaticMap.Type.SATELLITE)
I keep getting emails that there are new comments, but I can not find any new comments. Could someone please steer me to them?
Thanks!
To Winfred and Other Readers (continued)
I have uncovered an error message in my use of the distance calculator that may explain the completely erroneous results I am receiving from MapQuest. I am located in the Chicago area of the US. The language preference in the distance calculator is set to en_US. The region preference is blank, which I believe is then by default US. I am getting many error messages that suggest that MapQuest is interpreting the US addresses as in UK. Here is an example. When calculating the distance from 6373 Green, Lisle, IL to other locations within about a 20 mile radius, the error message says that it is unable to route with one of the given locations being The Green, Vale of White Horse. (The Vale of White Horse is in southern England I.) I am at a complete loss as to how this can happen. I can go to the MapQuest site and enter the same locations and receive accurate distances and routes. Are there perhaps other settings in the distance calculator or in Google Sheets, Drive, etc. that could be creating this error? At this point, I am receiving mostly error messages and wildly incorrect distances for the few result fields that actually contain a number. Again, any ideas would be greatly appreciated. It is hard to imagine at this point that no more than three weeks ago I was getting error free results (after some significant work on the addresses) from MapQuest with mostly the same addresses I am using now.
Thanks for the report and the troubleshooting. Strange that the behavior is suddenly different. The region option is used by Google, not Mapquest. With your example address Mapquest’s API indeed comes back with an address in the UK. But could it be the address does not exist? Neither Google nor Mapquest (web) know the ‘6373 Green, Lisle, IL’ address. For all ‘Lisle, IL’ works fine, just not the ‘6373 Green’ address. Do you have another example?
PS. with v3.1 you no longer have to manually add a sleep delay.
To Winfred and Other Readers
The distance calculator has recently been a great help to me with my wife’s business. Every week or so I help her calculate distances for destinations on her pet sitting routes. Two weeks ago I was using the distance calculator with 140 customer addresses to update route distances using the multi address section, lower part. All of the distances calculated without error in both Google and MapQuest.
For the last couple days, I have been unable to get any addresses to calculate well. At best, I can get city to city distances in MapQuest. For all Google requests, I get the error message about exceeding my daily limit as others have reported. For MapQuest, I get only two kinds of results. The addresses either cannot be found or the distances reported are off by a minimum of 100 miles. This happens on any number of addresses, down to just a couple. I have downloaded clean copies of the calculator multiple times just today. I have an internet connection that appears to be working normally and that is the same as I have previously used. I have changed the script for the sleep utility without improvement in the basic problem. I think I must be missing something obvious and would appreciate any advice. Many thanks.
Excellent Script! For those of us who have a paid google API. How can we add that API to your script to avoid the Free API allotment errors? Thanks again!
I must be doing something wrong. I keep getting the error that I have exceeded my daily limit using Google, but I haven’t even tried to open the sheet in 24 hours and I certainly don’t have anywhere near 2500 calls. This has been going on for several days!
There are two types of query limits. 1) max 2500 per day and 2) too many per second. I have updated the spreadsheet to deal with the latter. If it fails it will try a couple of times -and include a delay- before giving up. Similar for Mapquest (too many UrlFetch calls in a short period of time). Could that be it? If so, please try v3.1.
I downloaded v3.1 and it did calculate some of the mileages, but not all of them. I let it sit for a couple of hours and when I went back, none of the mileages or times were there. They had all been replaced by an error message that said:
Error: Service invoked too many times for one day: route. (line 483).
I looked at line 483, but I have no idea what it means or how to address the problem.
During that 2 hour “lull”, I had been working on a form, which is tied to the workbook. I refreshed it several times, but did nothing to the actual sheets where the calculations take place. I suppose that they could somehow be related.
I would really appreciate your help in resolving this matter. I am bouncing back and forth between using Excel and Google Sheets, but both of them have their drawbacks at this point. I really think that Sheets is going to be best for my particular application, but everything is dependent on the Google mileage calculation for consistency.
Thanks for your help!
Eric
This (using the paid API) should be possible, but is difficult for me to implement as I don’t have a paid API.
Feel free to contact me by mail if you (temporarily) want to share your client ID and private key. You can change your private key once I have implemented it.
Since the paid API is likely work related: a donation is appreciated.
Good news: Google Drive templates now supports new Google Sheets. v3 of the spreadsheet has been published as a template, which makes it easier to download.
Can you do me a favor? If you like the spreadsheet, click the Download button on this page and rate the template. Thanks!
I am getting the following error:
Error: Exception: Service invoked too many times in a short time: urlfetch. Try Utilities.sleep(1000) between calls. (line 744).
I see the Mapquest file using the script editor, but I don’t know the first thing about how to implement what it is telling me. Could you please provide some additional information.
I switched from Google to Mapquest, due to the daily limit, which I have no idea how I was reaching in the first place on Google. I don’t like MQ and it certainly seems to be a little more picky and has trouble finding addresses.
Also, it seems like they both indescriminately want to recalculate and when they do, mileage and time calculations that were there are no longer there. Do you know what is causing this?
Your script should certainly provide me with exactly what I am looking for…with just a little tweak. Thanks for putting this together!
Hi Eric, thanks! Agreed, Mapquest is more picky. For the limits: if you run into an urlfetch error then it appears you have many addresses that you deal with. Is that indeed the case? If so then it also explains why you run into the daily Google limit. For the urlfetch error: indeed open the script editor and locate the Mapquest file. For v3.0 of the spreadsheet, go to line 742 (//Utilities.sleep(500);) and remove the // and then File > Save.
Sorry that I kind of fell off of the face of the earth there. Something about 16 hour work days that kind of takes you away from other tasks.
Thanks so much for helping me with the “sleep” code. However, I decided to try to switch back to Google. Mapquest is so picky on the address, that I don’t feel that I can get my installers to take the time to code it absolutely correctly. So I am back to dealing with Google.
I only have about 40 lines in my spreadsheet so far. (It will grow by 2-3 lines per day and I would eventually like for it to hold at least a year of data, 5-6 days per week.) Now granted, there is some duplication on 2 sheets during this development stage. In addition, I am trying to calculate Google driving times.
That being said, even if when I reload the sheet to begin working and it recalculates all of the instances, I don’t think it could exceed 400 at a time. So, I don’t know how I hit 2500 in a day.
Which brings up a question, if you reload the sheet, does it recalculate all of the mileages and driving times? This would be a problem if the installer opens the sheet and it is also opened by the manager.
Thanks for your help
Welcome back. Pretty sure all formulas are (re)calculated when opening a spreadsheet. If you have many addresses you may want copy the formula outcomes to plain text / numbers. Similar to how I use the button to add results to the trip log.
So…”copy, paste special, values” on to the same selection/field?
I was thinking about doing that anyway. It seems that when I make a change to the sheet, a lot of the Google Mileages and Times go away and need to be recalculated. I was having to go through all of my sheets and manually recalculating them, line by line.
Is there a way to recalculate an entire sheet? I’ve tried to use “Calculate Now” and “Calculate Sheet”, but these don’t seem to work with the Google Functions.
Thanks for taking the time to work with me. I really appreciate your help!
Winfred I jumped the gun on my testing, either the public key 20K limit gets used up quick or my connection was shoddy to Mapquest yesterday. Today using my key the calls are quick and accurate. The only issues I have seen have to do with address formatting and some GPS coordinates. Here is a short list.
Address formatting
-issues with “Apartment” or any form of the word, APT, Suite etc..
-Issues with some GPS coordinates, some GPS coords I pulled with the Google call will kick back an error when routeDistance is used between 2 GPS location coords using mq. If I adjust the GPS coords slightly by a 1/10th of a mile or so mq is fine with it.
More testing and data to come
~P
I’m getting some mixed results. It seems to be very picky with addresses, apt & apartment throws an error. I’m doing some extensive research, would you like me to share my results document with you?
~P
I’ve been using this sheet for some time now, today I hope to get the mapquest side working to get past the daily limit. Thanks for putting this together Winfred.
Lucian when you use the API calls for Google sheets it is directly related to your gmail account so you can’t be using his key.
~PSH
Are you running into Mapquest issues? It should be sufficient to set the provider field in the settings on the main tab. And then change the from/to to force recalculation.
I duplicated your form three times. One for point A to B. Than from home to point A and from point B to home.
I deleted all the pages except the one with maps on it. I created an online form to populate the addresses.
While playing with the form I got an error saying “daily limitservice invoked too many times line 483”.
Since I only use the first optimized route and I don’t need the GPS coordinates are there any lines of code I should delete to minimize the hitting google to many times?
I think every time I move between the sheets it refreshes (hitting google for directions).
I only used sheet for less than addresses. I modified them and saved a couple of versions and than came back to view them. I think Google doesn’t like me doing that to much.
Any suggestions?
Thank you for the incredible work.
In general (based on v3 of the spreadsheet): if you leave the yellow from/to fields empty then there is no calculation performed.
And, I am not 100% sure but I believe recalculation is only done if from/to fields are modified.
Most of the functions only use a single call to get the directions.
Also, feel free to use Mapquest instead of Google. No quota there.
For your specific case, you can create a custom sheet for your needs, e.g.:
A1: home
A2: A
A3: B
A4: =routeDistance(A2,A3) [from A to B]
A5: =routeDistance(A1,A2) [from home to A]
A6: =routeDistance(A3,A1) [from B to home]
Is it possible that we are all using your google key?
This may explain why the search limit error? I used it less than 10 times a day while testing it.
I added my own mapquest key. How do I see the mapquest.
Can you point out where can I switch from google to mapquest ?
Thank you.
Lucian