## Pages

Google Spreadsheets provide a lot of useful functions, but unfortunately nothing that calculates the distance between two locations. However, this is easy to add as a custom function using the Maps service available in Google Apps Script.

Most of the other approaches I found online seemed to involve using the external APIs provided by Google Maps or MapQuest. But both these approaches required registering for a Developer API Key. The approach I describe here seems a lot simpler.

I'll show how to create the following functions and how they can be used in Google Spreadsheets

DrivingMeters(origin, destination) - Driving Distance in Meters
DrivingMiles(origin, destination) - as above but in Miles
DrivingSeconds(origin, destination) - Driving Time in Seconds
DrivingHours(origin, destination) - as above but in Seconds

Using these functions in a Google Spreadsheet will be straightforward. If you have "New York" in cell A2, and "San Francisco" in cell B2, then you could put "=DrivingMiles(A2, B2)" in another cell to see the driving distance in miles between those cities. Locations can be expressed in any way that Google Maps recognizes as valid. So where a city name is sufficiently unique, the name alone will suffice. If a city name is common, then append the state or country, like "Lexington, NC". Street addresses and zip codes will also work.

To get started let's create a Google Spreadsheet with some city names arranged so as to form a matrix. The locations will be listed vertically in Column A, and horizontally in Row 1. For example:

In Cell B2 we'll insert "=DrivingMeters(\$A2, B\$1)". Since we haven't written the custom function yet, this will return "#NAME?". Before we write the function, let's copy this into all the cells. Here I'm showing it copied into all cells below the diagonal line from upper left to lower right, since the values above the line would just be duplicates.

Now let's open the Google Script Editor and write the custom function. So click Tools > Script Editor and create a blank script. This will create an empty script which you will replace with the following

function DrivingMeters(origin, destination) {
var directions = Maps.newDirectionFinder()
.setOrigin(origin)
.setDestination(destination)
.getDirections();
return directions.routes[0].legs[0].distance.value;
}

If you save this and switch back to the spreadsheet you should now see that the driving distances are filled in.

This shows a matrix of driving distances between the cities entered in meters. However, you probably want to see the distances in miles. Or you might want to see the driving times. Both of those can be done by going back into the Script Editor and adding the following functions.

function DrivingMiles(origin, destination) {
return DrivingMeters(origin, destination)/1609.34;
}

function DrivingSeconds(origin, destination) {
var directions = Maps.newDirectionFinder()
.setOrigin(origin)
.setDestination(destination)
.getDirections();
return directions.routes[0].legs[0].duration.value;
}

function DrivingHours(origin, destination) {
return DrivingSeconds(origin, destination)/(60*60);
}

To use these you'll just replace "=DrivingMeters(" in your spreadsheet with the corresponding function, like "=DrivingMiles(". That's all there is to it. I've put this sample code in a public google spreadsheet which you can copy by clicking the link. Feel free to ask any questions below.

Note about quota allowance: a lot of people ask about how to increase their quota allowance to be able to call this function many times. This used to be possible by purchasing a Google Maps APIs Premium Plan and passing the client ID and signing key from this to the Maps.setAuthentication method. However, Google no longer offers this plan, and there is no alternative. The method does not work with API keys. So unless you already have a Google Maps APIs Premium Plan, then you are stuck with the default quota allowance.

1. THIS IS GREAT! This reduces the time to calculate the data drastically. (I have over several hundred entries/calculations in my document).

I Can't thank you enough for posting this!

1. You're welcome. Just be aware that calls to the API are metered. So if you call too frequently it will stop working for a period of time.

2. Thanks a lot..
Could you provide how to do for choosing between going by car or walking or by bus?
Also, could the date/time be specified?
Finally, google spreadsheet blocks you when you do too many requests, how could we avoid that?

3. The "mode" of travel is set by adding

.setMode(Maps.DirectionFinder.Mode.DRIVING)

in the example above on the line before ".getDirections();". The available "modes" are WALKING, DRIVING, BICYCLING, TRANSIT.

You can add an arrival time or a departure time with

.setDepart(depart)

or

.setArrival(arrive)

In each case adding these as above before the .getDirections() line. Each is passed a Date object.

I don't know of any way to avoid the limit that Google has imposed on how many requests you can make in a given time period.

4. Hi, this is a great script thanks for taking the time to explain it.

I'm trying to add in TRANSIT as above. However the values in the spreadsheet do not change regardless of the form of transport selected. Please can you help?

5. You need to add something like
.setMode(Maps.DirectionFinder.Mode.TRANSIT)

If you take another copy of the spreadsheet I've added commented out lines with this code which you can uncomment to test.

2. I started to run into that problem, but I now have it wrapped at the end of a multi-layered if/then statement to reduce the number of calls drastically.

1. Can you supply that code? I'm thinking an If/then statement that would simply determine if the value has already been calculated would be sufficient. I don't need it to constantly recalculate the values. I just need it one time.

2. What is your formula for IF/THEN to keep the Sheet from fetching duplicate data?

3. Great stuff! Are there any others controls for routes - avoid highways, time of day, etc?

1. Yes, setAvoid and setDepart. Check the API docs at https://developers.google.com/apps-script/reference/maps/direction-finder#

2. Sorry to come in on this late, but I've just discovered it! It's great, but I keep getting random ERRORs in some cells. The error text reads "TypeError: Cannot read property "legs" from undefined. (line 18)." even though it is the same code as in other cells which work fine.

3. This happens if the cell being referenced by the function (e.g. cell A8 in the pubic spreadsheet I provided) is made an invalid location (e.g. "XASD*&^"). So perhaps one of our locations isn't something that Google Maps can uniquely identify as a location. Try cut/pasting it into maps.google.com to see how it responds.

4. Alex
I hear what you're saying. Strangely, some cells come up as an error sometimes when I load the sheet, and other times, loading the same sheet, different cells come up as errors and the earlier ones are OK!
Alan

4. This API seems to be more sensitive than maps.google.com. E.g. "North Reading, MA" works in maps, but gives me an error in Sheets. But, if I use a more specific address, it works in Sheets

1. Yes, I've noticed similar things. I think when you are on maps.google.com, Google is using other contextual information about your location or profile or something to narrow down results.

5. That's a useful tutorial, thanks. I created a spreadsheet that started similarly, and has grown into quite an extensive set of custom functions (using Google or Mapquest). Feel free to check it out if you are interested: http://winfred.vankuijk.net/2010/12/calculate-distance-in-google-spreadsheet/

1. Looks good, thanks for commenting.

6. Very useful, thanks for sharing!

7. Thank you very much for the tutorial! Is there a way to factor in traffic data from the Google Maps API? I am hoping to use this for a school project where we need to collect time duration between two addresses at various times throughout the day and variation due to traffic is key. Any assistance would be greatly appreciated, thank you!

1. Unfortunately the Google Maps DirectionFinder doesn't seem to currently support traffic data.

8. Thanks so much. Your examples were easy to follow. Everyplace else was so much more complicated and involved. I'm now able to easily calculate my work truck mileage for taxes from my customer's addresses!

1. Glad it's working for you.

9. This looks really powerful, can you think of any way I can't use the DrivingSeconds to calculate between two lat,long locations?

Thank you

1. In the code above I'm using the setOrigin and setDestination methods that take one "address" parameter. However, there are also versions of each method that take two parameters (latitude, longitude). So you'd just need to change the code to use those methods and pass them the appropriate information. Does that help?

2. Hi, I am trying to change it to take the parameters (latitude, longitude). Could you help out?

3. function DrivingMetersLatLong(oLat, oLng, dLat, dLng) {
var directions = Maps.newDirectionFinder()
.setOrigin(oLat, oLng)
.setDestination(dLat, dLng)
.getDirections();
return directions.routes[0].legs[0].distance.value;
}

4. This is so helpful, but when I try this ...

function DistanceMetersLatLong(oLat,oLng,dLat,dLng) {
var directions = Maps.newDirectionFinder()
.setOrigin('oLat,oLng')
.setDestination('dLat,dLng')
.getDirections();
return directions.routes[0].legs[0].distance.value;
}

... I get the following error:

TypeError: Cannot read property 'legs' of undefined

5. Remove the single-quotation marks around oLat,oLng and dLat,dLng inside the parentheses

10. Hi How would I adjust the code on sheets and the script to have a set origin eg my home town ?

1. If you added the following function to the above script

function DrivingHoursFromHome(destination) {
return DrivingHours("1 edenton st, raleigh nc", destination);
}

Then in the sheet you can use it like

=DrivingHoursFromHome(A2)

11. I'm actually using postcodes and it seems to work fine.
Is there not just a way of changing the "origin" in the existing code ?

1. Are you looking to just do something like

=DrivingMiles("B16 8EW",A2)

12. Thats exactly what I want. To be able to have my postcode as the set origin

1. Ok, well it can do that without any code change. Just use each of the functions in the example above and specify your postcode instead of a cell:

=DrivingMeters("B16 8EW",A2)
=DrivingMiles("B16 8EW",A2)
=DrivingSeconds("B16 8EW",A2)
=DrivingHours("B16 8EW",A2)

13. Thanks a lot for hint. Unfortunately, I dealt with following error :
Service invoked too many times for a day.
Could you please let me know how I can fix it.

1. Google imposes rate limits on map api usage. They publish some information about paying for additional map loads, but I don't know if this applies to using the maps api from google apps script.

2. Thanks Alex, this is really great,

Did anyone get to the bottom of this? i had the same problem!

3. I've got the same problem. Since I only need to calculate the distance one time I started replacing the results with the absolute number, but that is not so elegant.

If it's very important to you it could be a solution to get an google api key and use IMPORTXML to import the results of your own service.

This could work because there is a much higher quota when using google webservice api and you can buy more if you need (https://developers.google.com/maps/documentation/directions/usage-limits). Just an idea. I've not tested it. But it shouldn't be too complicated.

A request could look like (untested, but used similar code without API_KEY):
=importXML("http://maps.googleapis.com/maps/api/distancematrix/xml?origins=" & A1 & "&destinations=" & B1 & "sensor=false&"&key=YOUR_API_KEY; "//DistanceMatrixResponse/row/element/distance/value")/1000

14. This is my code:
distance =routes[0].legs[0].duration.value;

I am getting distance value as 11421.But i want convert into miles(ex 103.9)

1. .duration.value is a measure of time (in seconds) not distance. You need to use .distance.vaue in your code to get distance. That returns meters and then you need to add /1609.34 to the end of the line to get miles.

15. Hey Alex, could you help me with the lat & longitude method? How can i change the origin to two parameters?

1. You could do something like

function DrivingSeconds(origin_lat, origin_long, dest_lat, dest_long) {
var directions = Maps.newDirectionFinder()
.setOrigin(origin_lat,origin_long)
.setDestination(dest_last,dest_long)
.getDirections();
return directions.routes[0].legs[0].duration.value;
}

And then in the spreadsheet include something like

=DrivingSeconds(40.759011, -73.984472, 40.777052, -73.975464)

Where the numbers could obviously come from cells. Hope that helps.

Sounds perfect, but i get the typeerror: Cannot read property "legs" from undefined. Any idea?

3. Odd. I did have a typo above (should be "dest_lat" not "dest_last" on the 4th line). But once I fixed that I just tested the above example using the specific long/lat I put above and it came back with "587.00".

Did you perhaps try different long/lat and maybe Google Maps couldn't find a route from the origin to the destination?

16. Hi, Alex!

Thank you so much for writing this post. It was phenomenally useful to me. I used this code to create a tool at my job that has potential to be very useful, but I am having some problems getting it to work consistently. I work for a tutoring company, and we have lots of part-time tutors who travel to students' homes. When deciding which of our tutors to staff for a tutorial, one of the considerations is the distance between the student and tutor. The tool I built automatically display the distances between all qualified tutors and the student in question once a manager inputs the student's home address into a specified cell on the sheet.

Sometimes the tool works amazingly well. Other times, it returns errors for half the tutors and distances for the other half. Also, I often run into the "service invoked too many times for one day" error, even when I haven't used the service all that much. According to Google's published guidelines, you're supposed to be able to call the function 2500 times per day, but, at best, I've only been able to get (roughly) between 500-1000 before I get the error message.

Additionally, there are various other mechanisms at play. My drivingmiles functions are all nested in "if" and "filter" functions that also respond to user-controlled triggers, and one theory I have is that the interactions between the various filters, triggers and if statements in my code is responsible for the malfunctions. However, I don't have the expertise to say this for sure.

In short, I've been at this for weeks, but still haven't been able to fully wrap my head around the behavior of the program I wrote or get it to consistently do what I want.I would love your input. Do you do consultations? I.E. would you be willing to look at my program and my code and help me figure out what the problem is and how to fix it for good? I'm willing to pay you if you can actually fix it.

Let me know!

-Mike

17. Hi,
Every few give me an error, and when I go back to delete & re-enter =DrivingMiles it still gives me an error. I have 1000 students addresses to compute for a report that is due. Any suggestions would help greatly. :) Thank you -Janell

1. I can't be much help I'm afraid. Perhaps someone else reading this can be. I've similarly seen some flakiness in the response from the Google Maps API. Sample Student (comment above) was having similar issues. The exact same calls will work sometimes, and fail others. If you re-run it will often work, but eventually you run into the rate limit and have to wait a day to try again. All I can suggest is reporting the issue to Google.

2. I solved the problem by replacing the formula in the spreadsheet with something else, then changing it back again - it worked, but not ideal for a large spreadsheet!

3. Yes, this flakiness is irritating. I've seen it even with built-in functions like =GoogleFinance(). I'll cut/paste 20 or so cells using this formula, and 10 or so will load correctly, and the others will report errors. Replacing the failed ones with other values and then putting back the formula seems to eventually fix it, although even then you sometimes have to do this multiple times.

18. I've seen some references to limiting the calls or prevent recalculation, but I have not seen a solution.

Has anyone figured out how to do this?

1. I solved this by putting the call to the function behind a button instead of in the cell. The following is the code behind my button and calculates the distance from the value in C1 to any list that is highlighted. It puts the values in the cell to the right of the current value.

function CalcDistances() {
var origin = sheet.getRange("C1").getValue();
var range = sheet.getActiveRange();
var numRows = range.getNumRows();
var numCols = range.getNumColumns();
for (var i = 1; i <= numRows; i++) {
for (var j = 1; j <= numCols; j++) {
var destination = range.getCell(i,j).getValue();
var result = DrivingMiles(origin,destination);
range.getCell(i,j).offset(0,1).setValue(result);
}
}
}

2. This is the answer I've been looking for. We have to have a way of controlling when the sheet uses the API. I have a project where I'm trying to compare driving distances for 130+ addresses to six different destinations. I put them all in the spreadsheet, set up the code in the cells, then defined the functions using the API calls. I put a sleep in between calls so as not to max out the per second limit. I let the sheet start gathering the data, and I could see some data was beginning to populate. I went off to eat breakfast. When I came back, all the cells had the ERROR message saying I had exceeded the calls for the day. Why did the sheet keep calling the API functions for the cells that had already been calculated? I don't know, but it did. Perhaps all the cells were supposed to recalculate every time another cell was calculated (based on the "Recalculate on Change" trigger). Whatever the issue was, it seems I need tighter control of the API calls when I'm dealing with a sheet full of them. The for-loop iteration method triggered by a button seems to be the way to go.

3. I have this same issue of exceeding calls per day. I"m curious to know what those limits are (daily and per second).

I'd also be curious to know what solutions there are to this. If you can't calculate all of your cells in one day, then I would guess some sort of manual trigger for the calculation is required. I'm not familiar with how that would be done.

19. This saved me! Thanks!

20. I have tried to use the .setDepart(depart) function but can't seem to get it to work. I have placed it in the script editor right above .getDirections();

Has anyone got this working?

1. Can you post a snippet of your code. When I get a moment I'll try it out and see if I can work out why it isn't working for you.

2. Hey Alex,

I also didn't have any luck with setDepart. Based on documentation for the Mode Enum (driving, walking, cycling, transit), I think that setDepart and setArrive might only apply to public transit directions. I was interested in driving directions and getting min and max transit times for a specific departure time, but it looks like when driving mode is selected, the Maps class ignores setDepart and setArrive. So there's not even a way to get a typical transit time at a specific departure time other than the present, let alone min and max times in traffic. Let me know if you're able to come to a different conclusion.

Kinda stinks, since I was really looking hoping to find this sort of functionality somewhere other than manually doing it in the GMaps webpage interface. I'm apartment hunting with my girlfriend right now and the both of use combined have to regularly commute to several places in various locations across our city, so being able to quickly get a total weekly driving time estimate for us given a particular apartment address would have been pretty neat.

Anyhow, thanks for this post otherwise! Gathering typical transit times programmatically has still been pretty helpful.

For reference, here's the snippet I was trying:

function DrivingMinutesDepartAt(origin, destination, depart) {
Utilities.sleep(100);
var departDate = new Date(depart);
var directions = Maps.newDirectionFinder()
.setOrigin(origin)
.setDestination(destination)
.setDepart(departDate)
.getDirections();
return directions.routes[0].legs[0].duration.value/60;
}

- Dom

21. This is brilliant. Would it be possible to include multiple destinations as you would in maps? E.g.: .setDestinationB(destinationB) or something?

1. Thanks. Yes, you can add way points. The API is

2. Hey Alex, I know you started this post a long time ago and I am not sure you are still following, I am trying to use this and getting a blank, All I need is to know the distance between two addresses in my spreadsheet in miles. I am lost as can be.

3. Just blank, no error message? Did you try copying the public google spreadsheet that I included a link for in the post? If you copy that as-is and open it, do the distances get populated? I just tried it again, and it still works for me.

22. Thanks, much appreciated

23. Hi, I've just tried using =DrivingMiles(A2, B2), but get an error message "Unknown Function: Drivingmiles". Anyone got any ideas?

1. Dunno. Still works for me. I assume you've copied the spreadsheet with that function in it by clicking the link at the bottom of the article. That function will only be in a spreadsheet that has that script added.

2. I got the same thing, but in my situation the problem was that it was case sensitive: it looks to me like your M in DrivingMiles is capitalized in one place but not in the other!

3. Can you tell me what line you see this on? Because I just rechecked the scripts and it looks consistent to me that I capitalize the M in Miles. I also tested that I can successfully use any of the following from the spreadsheet =drivingmiles() =DRIVINGMILES() =Drivingmiles() - so it isn't case sensitive anyway.

24. Thank you for this wonderfully helpful article.

Was there ever a solution to the departure time question? If you go to Google Maps, it gives you the option to see what anticipated traffic will be at a given departure time. I am trying to write this into my formula. Any suggestions?

1. You can add an arrival time or a departure time in the scripts with

.setDepart(depart)

or

.setArrival(arrive)

Where the parameter passed is a Date object.

1. Without seeing your spreadsheet I can't really tell, but the most likely cause is a typo between the function name in the script and the function put into the spreadsheet cell.

26. This is awesome! Thanks! However, I do get the #ERROR! message that others have mentioned as "TypeError: Cannot read property "legs" from undefined. (line 6)."

As others have mentioned, it's random and different cells that have the error each time you open the sheet and clearing that cell first and then putting the formula in seems to fix it, but not practical for a large spreadsheet with many zips.

Would it be easy/feasible to write script that would find all the error cells, clear them out, and then replace with the formula? I did a quick getValues formula, and that seems to come up with a number everytime, so maybe just a script that uses your formula and then hardcodes the result into the cell?

Trying to come up with ways to patch up this error - any other ideas?

1. I think what must be happening is that occasionally the call to the Maps.newDirectionFinder() fails, or returns invalid directions. I haven't been able to reproduce the error (if anyone else has and can share a link to a spreadsheet I'll take a look) but I've tweaked the sample code to loop 3 times attempting the call to Maps returning as soon as one of them works. Perhaps that will address it?

function DrivingMeters(origin, destination) {
for (var attempt=0; attempt<3; attempt++) {
Utilities.sleep(1000);
var directions = Maps.newDirectionFinder()
.setOrigin(origin)
.setDestination(destination)
.getDirections();
if (directions && directions.routes && directions.routes[0] && directions.routes[0].legs && directions.routes[0].legs[0] && directions.routes[0].legs[0].distance)
return directions.routes[0].legs[0].distance.value;
}
return "";
}

2. OK -- for me, this helped for sure. But I kept getting the same error again for some of the cells. Putting "Utilities.sleep(2000);" instead of (1000) seems to have resolved the issue, for me at least.

27. I am having an issue with line 24 when I try to run the =DriveHours I get the following error in the spreadsheet: "Error
TypeError: Cannot read property "legs" from undefined. (line 24)."

1. If anyone can share a spreadsheet with me (with edit privileges) that demonstrates the TypeError they are seeing then I'll take a look and try and figure it out. I have yet to see it happen.

3. I'm curious what came of this -- I keep getting the same error on Line 6, and sometimes line 7. Other times, it tells me I need to Utilities.sleep(1000) -- which I have tossed into various places of the script with no real success.

Great script anyway though, thanks to OP!

28. Any way to save the value once calculated in a cell and don't recall/recalculate everytime I open the sheet unless was a change done?
I'm a trucker and I got a list of all my trips and i use drivingmiles for distances and I want to save the distances once calculated and not recalculate everytime i open the sheet.

1. It shouldn't recalculate each time you open the sheet. The way custom functions work is they are only recalculated if the value in one of the cells referenced in a parameter changes. So, for example, if you are using =DrivingMiles(A1, A2) then this will calculate once, and only recalculate if the values in A1 or A2 are changed.

is there a way how to connect this to my google API so that I can get 17.000 request at once?
I need to fill column X with values that will show how far is J(n) from AA\$1\$ but it keeps giving me an error that I used the function for too many times...
Thanks!
O.

1. If you have a Maps API for Business account, then you could try adding the following call, with your client id and signing key into each of the functions just before the call the Maps.newDirectionFinder() function

Maps.setAuthentication("YOUR_CLIENT_ID", "YOUR_SIGNING_KEY");

I haven't tested this, since I don't have a Maps API for Business Account.

30. do how should the code look like?

like this?
function DrivingMeters(origin, destination) {
Maps.setAuthentication(ID,API)
var directions = Maps.newDirectionFinder()
.setOrigin(origin)
.setDestination(destination)
.getDirections();
return directions.routes[0].legs[0].distance.value;
}
thanks a lot!
O.

1. Yes, but you should put a semicolon after the Maps.setAuthentication() line. So it should end up looking something like this

function DrivingMeters(origin, destination) {
Maps.setAuthentication('gme-123456789', 'VhSEZvOXVSdnlxTnpJcUE');
var directions = Maps.newDirectionFinder()
.setOrigin(origin)
.setDestination(destination)
.getDirections();
return directions.routes[0].legs[0].distance.value;
}

31. Thank you from the bottom of my heart.

32. is there a function that provides drive times WITH traffic?

1. Theoretically yes. Instead of "return directions.routes[0].legs[0].duration.value" you would use "return directions.routes[0].legs[0].duration_in_traffic.value". You would also need to add .setDepart(time) (with a time in the future) to the code. But the biggest issue is that you need to have an API key to get traffic data, and from what I can tell there is no way to provide an API key in Google Apps Script for this service.

33. Hi , Thanks for posting this and for being so active in your replies. This is something which could save me a huge amount of time. I'm very new to scripts, but did have them working but now the daily limit has been reached. I then i waited for the limit to reset at midnight PST but no change. I've got the function working in about 200 cells. DO you know what kind of limit there is as doesnt seem to be working in any even post the reset of quota. Thank you in advance.

1. According to Google "Daily quotas are refreshed at the end of a 24-hour window; the exact time of this refresh, however, varies between users" but I don't know exactly what the quota is for the Maps Direction Finder. I did notice that the Maps service has a setAuthentication method for specifying a Maps API for Business Account key. So it might be possible to pay for a key with a larger quota and use that in the script.

34. Many thanks for this script!

I copied the code from your example and just wanted to post some feedback.

It does appear that the cells do refresh, regardless of any of the cells changing.

I completed filling in a hundred or so cells with this, about 60% brought back a result and the rest came back with some sort of quota message, which I was expecting and assuming would refresh and fill in the following day.

However, this morning, every single cell now has a quota error, even the ones that were complete last night. I also have notifications turned on for this sheet and had a ton of emails pinging over night, saying the sheet had been updated "by a user", which I guess was the script firing and refreshing cells, triggering the notification.

My thought was to copy and then blank the sheet, then copy and paste the results as they get returned by the script. Hoping I can find something more elegant though!

Will post back if I find anything.

1. Hello Alex! How are you doing? I need a formula to calculate the SHORTEST route between several addresses.

Let's say:

and so on.... (there might be even up to 15 different addresses)

I found this formula (down below) wich works perfectly but won't calculate the shortest route but the fastest. And also, this formula is good only for calculating point A to point B, I'm not able to add multiple addresses.

=importxml("http://maps.googleapis.com/maps/api/directions/xml?origin=" & A1 & "&destination=" & C1 & "&sensor=false&alternatives=false","//leg/distance/value")/1000

It could be either a formula or a script, I don't mind. But please, keep in mind my knowledge is limited. So please: help for dummies =)

Thankyou very much!

Martin

35. I keep getting a distance of 0 miles on multiple different source/destination combinations.

1. Can you post an example source and destination that you are using which is returning 0 miles?

2. Source: 77075
Destination: 36507

3. I get 507.26 using the spreadsheet I link above and just changing B1 to 77075 and A2 to 36507. Not sure why it's not working for you. If you can post a link to your spreadsheet I can take a look.

36. This saved me hours of work, thank you very much for posting!

37. Hey, get a bunch of errors, I export my calendar then run the point a to b, some address it works great, then some I go in and simply add a space then it calculates...any thoughts?

1. Do the errors give a particular message? if you are trying to calculate a lot of distances then you are probably exceeding the allowed quota for the API. There isn't really a work around for this. You just have to wait 24 hours and try again.

If you have a Maps API for Business Account - which allows a higher quota - then it's supposed to be possible to get a client id and signing key and specify this in the code using Maps.setAuthentication(clientId, signingKey). However, I've yet to hear of anyone doing this successfully.

38. Hello,
First, than you for the post and following up for so many years.
Second, I'm not super concerned with it working perfectly. This is not mission or business critical. You did ask for some examples where it wasn't working all the time.

The purpose is for people in my organization to calculate their milage to each of the tracks we race at. The standard locations are hard coded. The destination addresses are in K1-K4, in white. The only editable cell is GHI1 to enter your address.

I think you should be able to see the script but this is what I have for the loop and timeout settings:
for (var attempt=0; attempt<5; attempt++) {
Utilities.sleep(2000);

Thanks again!
MikeD

1. Thanks for sharing, MikeD. Took a quick look and as fate would have it, it was working. But I understand you say it usually works. Every failure I've seen so far in other examples people have shared has been due to the limits imposed by Google on the number of calls that can be made to the API within a certain time windows. That's probably what's happening to you as well. But let me know if you see a different error message.

39. Hi Alex - I have one origin and want to look that up to all US zips (so like, 32k zips). In both Excel and Google spreadsheets I'm running into a wall. I can get some zips, but then everything else is returned as "0" or an error. Any thoughts or suggestions? TIA

Natasha

1. With that many zips you're definitely exceeding the quota allowed. So the first few distance checks will work and the rest will fail.

If you have a Maps API for Business Account - which allows a higher quota - then it's supposed to be possible to get a client id and signing key and specify this in the code using Maps.setAuthentication(clientId, signingKey). However, I've yet to hear of anyone doing this successfully.

2. Thank you for your response! Can you think of anyway around it? Yesterday I was able to "max out" my limit on the google spreadsheet, then also used a VBA script to get more. I ended up with about 10k results but I am still missing 2/3's. The VBA script worked for about 5k results, then just stopped returning values. Is this from the same API issue?

3. Yes, it's the same issue. Only way around the API limitations is to register for a Maps API account and pay for a higher API quota.

40. Hi Alex,

thanks so much, taking the time to answer all the questions. Just one thing, I can't figure out how to get the Traveltime (public transport) instead of the distance out of it. Any ideas? Thanks, David

1. I think you just need to specify the mode of travel at the same time you set the origin and destination. So add the following below the setDestination() line: .setMode(Maps.DirectionFinder.Mode.TRANSIT)

41. Hello, thanks for sharing this API script!
I've been using it quite successfully, but have run into a couple of challenges.
Sheets says that these functions cannot refer to a cell that uses the RANDBETWEEN function. Is there any way to allow this?
I have been having the #ERROR! issue that others have mentioned. Has anyone found a solution?
Thanks again!

1. I'm not familiar with any limitation regarding RANDBETWEEN. Can you provide any more details about what you are doing and what you are seeing?

There's various kinds of errors that you could be encountering. If you hover over the #ERROR does it provide more information? The most common one that people run into is the quota exceeded. There's nothing you can really do about that.

42. Hi Alex, thanks for your response!

I have a cell using the DRIVINGMILES function, which refers to two cells with addresses in them. The addresses are randomly pulled from a list of addresses using a combination of the INDEX and RANDBETWEEN functions.

The cell using the DRIVINGMILES function gives the following error message: This function is not allowed to reference a cell with NOW(), RAND(), or RANDBETWEEN()

I'm hoping to find a way around this, while still being able to use RANDBETWEEN.

Thanks!

1. This is a limitation imposed by Google. Specifically the rule is that "Custom function arguments must be deterministic" [https://developers.google.com/apps-script/guides/sheets/functions]. So you can't use RANDBETWEEN to pick an address that is passed to DRIVINGMILES or any custom function. Perhaps you can describe what you're trying to achieve and someone might be able to suggest an alternative approach which doesn't involve using RANDBETWEEN.

43. Like many others I have the TypeError: Cannot read property "legs" from undefined. (line 8) message using all sorts of variations on this code from around the web. I have tried your code via copy/paste and numerous others and all suffer from the same erratic behavior.

What seems to be flakey is not the address used but the ability to get a valid return on any variation of this command/methode: var directions = mapObj.getDirections();

I say this because with a set of 9 addresses attempting to return 8 sets of mileage AND duration, sometimes mileage will be returned and on the same pair the duration will give an error.

Sometimes both mileage & duration give an error

On very rare occasions both mileage & duration return valid results.

If the code were bad it should fail all the time. If the addresses were bad then both mileage & duration should fail but that is not the case.

It has to be the date returned from google is easily corrupted.

I even placed Utilities.sleep(2000) as the very first line then another Utilities.sleep(5000) after the directions call to google. No change other than it took a long time for the error msg or the occasional valid data.

1. I think what most people experience is that they exceed the quota. Either calling too many concurrently, or most often, calling too many in a day. Google seems to trigger custom functions in an unpredictable order. But if you have a lot of DriveMiles() in various cells in the spreadsheet, then whenever the parameters to the formula are changed (i.e. the locations) then Google will call the code. If multiple change at the same time, then some number of them might end up running concurrently and trigger the quota. So you might have 5 cells, and cells 1, 4 and 5 happened to run at the same time causing all but one of them to fail. Then cells 2 and 3 run slightly later and so they go through. Now Google caches those results. So reloading the page or the spreadsheet doesn't force it to rerun them. But you can see that in this way - because of the unpredictable timing of when Google runs the custom functions - the ones that work and fail will also be unpredictable.

The bottom line is that calling an API like this which has quite a strict quota from a custom function is not ideal. It was useful for the very limited usage scenario I had which led me to write it. But it would probably be better to instead put all the calls to the Maps API into a function which is triggered from a menu, so you can control when the API gets called, and correctly pause between each call.

anyone know of a way to calculate flying distance between to airports using their 3-digit airplane code?

1. I meant airport code. i.e. EWR for Newark Airport.

2. So you first need to use Maps to geocode the airport code into latitude/longitude, and then use the Haversine formula to calculate the distance between the two locations. If you take another copy of the spreadsheet linked in the article above I've just added two new custom functions FlyingMeters() and FlyingMiles(). They can be used like the Driving equivalents described in the post.

For example if you put =FlyingMiles("RDU","CLT") into a cell it calculates 129.8077498 which looks about right for Raleigh-Durham to Charlotte.

45. If I had google development license how would I write this in so I can do a lot of calculations for a bid

1. I think you need a Maps API for Business account to be able to exceed the regular quota limits. I don't have one so can't say for sure, but it's my understanding that if you do you can add a line like the following to the script
Maps.setAuthentication("YOUR_CLIENT_ID", "YOUR_SIGNING_KEY");

46. Thanks Alex, excellent work.
I have been trying that code but have been running into the quota too much so I aquired an API key. I can't figure out how to easily obtain a clientID, so I am using the importxml function to calculate travel duration between two locations:

=IMPORTXML("https://maps.googleapis.com/maps/api/distancematrix/xml?&origins="& A20 & "&destinations=" & B20 & "&mode=transit&key=mykey"; "//duration/text")

This works and the output value is formatted as "4 hours 33 mins".
I would like it to be formatted as 4:33.
Do you know how I would do that?

1. If you have "4 hours 33 mins" in cell A1 then the following formula should format it as desired:

=trim(left(A1,2))&":"&trim(mid(A1,find(" ",A1,5)+1,2))

If it's elsewhere in the sheet just replace all cases of A1 in this formula with the appropriate cell.

47. Do you know how to use "Maps.setAuthentication("YOUR_CLIENT_ID", "YOUR_SIGNING_KEY");" without a client ID and only the API key, or in other words how to build authentication into your code with only an API key?

1. No, you must provide a client id and signing key. According to the documentation "Your client ID and signing key can be obtained from the Google Enterprise Support Portal." Unfortunately I've never tried to do this, and have no idea how to go about it. [https://developers.google.com/apps-script/reference/maps/maps#setAuthentication(String,String)]

48. Thankyou very much! This is awesome!!

49. Hello - Is it possible to do this where the search is the distance between an address and a text name. Like the name of a supermarket? Using something like - following example shows a Find Place request for "Mongolian Grill", using the locationbias parameter to prefer results within 2000 meters of the specified coordinates:

1. This script deals purely with the distance between two locations. I have written about another script which provides a "radius" around a specific point. http://www.gadgetsappshacks.com/2015/09/how-to-draw-travel-time-boundary-on.html but that also doesn't really sound like what you want.

50. This code is great! Thanks for sharing. The number of requests I need to make required that I get a Distance Matrix API key...got it, but I don't know how or where to integrate it into the code. Would you please provide the lines of code necessary, where the key is replaced with "Your API key" and tell me where to insert it? Thank you in advance.

1. I've never done it, but there's a "setAuthentication" method on the Maps service. It takes a client id and signing key, so I'm not sure how you're supposed to generate those from an API key. But if you can find them, then theoretically the following code would work

function DrivingMeters(origin, destination) {
Maps.setAuthentication("YOUR_CLIENT_ID", "YOUR_SIGNING_KEY");
var directions = Maps.newDirectionFinder()
.setOrigin(origin)
.setDestination(destination)
.getDirections();
return directions.routes[0].legs[0].distance.value;
}

If you do manage to get this to work, please post back, because the question has come up several times before.

51. This is phenomenal!!! ... thank you!!!

52. Do you have advice on calculating distance & duration with setAvoid?

It looks like routes[0] will give info on the best route, but i need the route that avoids highways (for example)

if (formObject.avoid1 == "TRUE"){
mapObj.setAvoid(Maps.DirectionFinder.Avoid.HIGHWAYS);
}

var directions = mapObj.getDirections();
var bestRoute = directions["routes"][0];
var numLegs = bestRoute["legs"].length;

for (var c=0; c<numLegs; ++c){
var legNum = directions["routes"][0]["legs"][c];
var legDistance = legNum["distance"]["value"];
var legDuration = legNum["duration"]["value"];
totalDistance += legDistance;
totalDuration += legDuration;
}

53. Wow! Absolutely outstanding! A million thank you's Alex for your initial post in 2014 and continued dialogue clear into 2019!

54. Thank you for your public service. You have saved me an immense amount of time. I truly appreciate this creation.

55. hello
first, it's amazing
second, i have a problem like many before me, however, i have an API key

56. This is awesome!! Solved my problem with a couple tweaks. Your function call for the FlyingMiles function is mis-named.

57. I come up with the following error when I run this.

Drivingmeters is not defined on line 2
Can anyone tell me what I am doing wrong?

1. Could there be a typo? Function names are case sensitive and it should be "DrivingMeters" (capital M) in all places in the script.

2. Alex - I am running the =drivingmiles script. Seems to be working ok - but if you calculate the actual miles between my two points - its waaay off. For Example:

Cell 1 (Location 1) = 77651
Cell 2 (Location 2) = 77640

Your script says these two points are 80 miles apart. They are only a few miles apart. Any thoughts?

3. When I enter =DrivingMiles("77651","77640") I get 12.91212547 which looks about right on a map to me. When you do directions from a generic zip code I don't think it's entirely predictable what points in the zip code it uses to do the distance calculation.

58. Many thanks for your work.

59. This comment has been removed by the author.

60. have a question !
I am using Map function within Googlesheets using Google App Script Editor for Distance Calculations while being signed in using my educational ID, for which the qouta is 10000/day. My question is, “If I get Distance for 10000 locations, I will be billed for this or the Qouta limit is free of Cost ? “

61. why doesn't work?

1. I meant to post the message below as a response, so just posting here so that you get a notification and can see the message I posted below.

62. Because you are using an Italian locale you need to use semi-colons instead of commas to separate function parameters. So in cell B2 instead of

=DrivingMeters(a2, b1)

=DrivingMeters(a2; b1)

That will fix the error. You've also miss-spelled "Raleigh" so you might need to fix that before getting a valid result.

1. Thanks, i use your model and work.
Some data about hours is little strange if I control in google maps.

Other question is that in italy bike time and chilometer doesn't exist but my question is i can change .setMode(Maps.DirectionFinder.Mode.DRIVING)
dinamically or only in other sheet

i.e.

2. You can have it all in one sheet. I suggest you create multiple functions (DrivingMeters, BikingMeters, etc.). Each a copy of your existing one, but with just that one change made to the mode.

3. I try! It works!

63. It didn't work for me

1. Feel free to post a link to the google sheet with your script (make it viewable to all) and I'll take a look

64. but the error in times has a reason?

65. Hey Alex, this is great. Thanks. I noticed at times this script is selecting a route that is longer than the first route listed when I use maps.google.com (aka the " highest ranked route"). Any reason why your script would not use the "highest ranked route'?

1. I've noticed that maps.google.com is more forgiving of address formatting. When using a precisely formatted address I've found maps.google.com and the script return the same distance. But sometimes leaving out commas or miss-spelling a city name or something results in difference. Don't know if that's what you're seeing. if you can share an example of two addresses that give different results I could take a look.

66. Hi Alex, Thank you for this! I copied the code exactly as you have it, but keep getting an error on .setOrigin(origin)

Any clue why that would be and how to fix?

Here's what I have in my script editor:

function DrivingMeters(origin, destination) {
var directions = Maps.newDirectionFinder()
.setOrigin(origin)
.setDestination(destination)
.getDirections();
return directions.routes[0].legs[0].distance.value;
}

function DrivingMiles(origin, destination) {
return DrivingMeters(origin, destination)/1609.34;
}

function DrivingSeconds(origin, destination) {
var directions = Maps.newDirectionFinder()
.setOrigin(origin)
.setDestination(destination)
.getDirections();
return directions.routes[0].legs[0].duration.value;
}

function DrivingHours(origin, destination) {
return DrivingSeconds(origin, destination)/(60*60);
}

1. The code looks right to me. What's the exact error you are getting?

You might want to try clicking the link to the public google spreadsheet at the end of the article which will make a copy of the spreadsheet with the code already in there. It has a few enhancements over the original code in the article and might provide more details in the error message.

67. Hi Alex , Thank you for providing this method, its awesome
my doubt is : i had copied the full code from your public google spreadsheet to my new google spread sheet's script editor after when i execute i will get few result ie distances between cities ,but in your spread sheet i got more results is there any additional setup i need to do priorly if so ,please mention

1. No additional steps. But there's a limit to how many requests you can make to the Maps API in an hour/day/second. So unfortunately this leads to unreliable results, since custom functions run at unpredictable times, and sometimes run too close together and trigger the API quota limits.

2. Hi Alex,
Because of the issues with limitation of requests that can be made to the Maps API, would it be possible to solve this by creating another script which would copy Driving Miles dynamic result from column D and paste it into adjacent E column as static value only, and then delete the D column dynamic result so that it doesn't make additional requests to the Maps API?

3. Something like that would be possible, but you might as well do away with using the custom formula altogether, and just have a script which reads the origins/destinations from a sheet and uses the Maps API (in a manner similar to my custom formula) to lookup distances for any rows where there isn't already a number in column C and writes it there as a static value.

68. This is a fantastic script, thank you! Is there a VBA version of it available to use in Excel?

1. Sorry, I don't work with Excel or VBA, so wouldn't know if one exists, or how to make one.

69. This is great!, but how can this be modified so that it can be used with ARRAYFORMULA?

1. I've modified all the functions in the sample public google spreadsheet linked in the article above so that they handle being called in an arrayformula. If you take another copy you can see it being used that way in column D.

70. Thank you so much for this!!! So so useful!

71. Dear Alex,
Its almost 6 years since this article was published... and its still so relevant. But more wonderful is you availability to patiently answer each question.. Thats really awesome. Be blessed for the help I recd and the many like me. ! God bless

72. Very simple and powerful code! Thanks.

73. Surprised you're still replying after all of this time but I've been looking at this for months trying to figure out how to do this. I use
=iferror(DrivingMiles(\$C356, \$B356)*2, )
To get distance from my location to a delivery and back to that same location. It calculates the distance from the origin to the address I put in then I just multiply by 2 to get the total. The problem I'm having is that I had a list of the address I delivered to but I got audited and have to prove mileage. When I select a list of destinations and put it in the column, I dont have zip codes. It's about 50/50 that my addresses show up as over 1,000 miles away when the furthest is maybe 8 miles, if that. It happens almost all the time with numbered streets but still quite frequently with others as well. I have the zip code in the departure cell since it's the same every time but is there a way to set maximum distance or set the sheet to use a certain zip code or distance from that zip code? Thanks in advance.

1. It sounds like Google Maps is interpreting the destination as being in another state. Are you providing city and state in the destination addresses? If you're not providing state, then perhaps it is finding another city by the same name that is more commonly searched for and using that. If you are providing state, can you give an example that is being miss-calculated so I can see if I can tell what is happening.

74. I only have the street name and address in the cell, just wondering if there's a way I can set a destination radius maximum instead of typing zip code or city in.

2769 front street 44221 is in "B" and 1653 2nd st Is what I have in "C" It returns 4,757.86, which is mileage x 2.

1. No way to limit by radius unfortunately.

75. Amazing, thank you. For some reason it didn't work when I typed up the code, but copying your google sheet worked. Thank you for posting it!

76. OMG Thank you for your help! I

77. So many years active, I can imagine how many people this helped. I downloaded the public google spreadsheet and I am having problem expanding the array formula. If I enter new cities in A5 and B5 and expand array formula to D5 it will work, but if I try to expand it to D5 or just to D before there are origins and destinations entered in A and B it will just give an error: Invalid argument: origin (line 8). Is it possible to adjust the script so that we wouldn't have to expand the range in array formula every time we enter cities in the new row?

1. Thanks. Yes, if you take a copy I've just updated the script so that it should work with an arrayformula like =ARRAYFORMULA(DrivingMiles(A2:A,B2:B)) which you'll also now see in cell D2.

2. Hello Alex,
I use your script with array formula which is very helpful, but for the past few days it stopped working with error: exceeded maximum execution time. It happened after 60 rows of origins and destinations entered. I noticed that anytime I enter new origin and destination it would recalculate all of them from the begin. Would you be able to make it calculate only the latest entry so that it doesn't breach request limit? Thank you for sharing great script.

3. I can't think of a good way to do that. The custom script isn't told which are "new", Google just passes it all the origins/destinations each time. Only way to do it perhaps would be to cache previously calculated values, and refer to the cache. It's not a simple thing, so I doubt I'll get to it anytime soon.

Usually in my own use what I do is just copy/paste-static all the distances/times I've already calculated and remove the formula.

78. Alex,
I would like to use this to figure drive times for our event catering,
My sheet collects data from a google form. Every time data is entered it inserts a new line, with that said its great you now offer the Array version. My question is how do I set our shop address as the origin in the formula so that it does not need entered each time??? The destination comes from the customers submitted form. Thanks Again

1. If you put the shop address into a fixed cell on some tab - say Data!A2 then you should be able to use that in the arrayformula something like

=ARRAYFORMULA(DrivingMiles(Data!\$A\$2,B2:B))

Alternatively you can just specify the address right in the formula like

=ARRAYFORMULA(DrivingMiles("123 Main St, Chicago",B2:B))

2. Thanks for your quick response, however I tested both of these into your shared sheet.... Both gave a result in D2 but would not run down the D column. Seems like I am missing something. Could really use your expertise.

3. You're right, I should have tested it first. But now I see what you're seeing. Not really sure what the "correct" way to fix that is in the script. But for now you're going to need to have a column (which you could hide I suppose) which includes your shop address in every row so that you can refer to it with the arrayformula. To avoid having to copy/paste it into each new row you could add it with an arrayformula using something like

=ARRAYFORMULA(IF(B2:B<>"","123 Main St,Chicago",""))

79. Alex, Thank you for having this thread and continuing to help us. I commend you Sir!
My question is, is there a way to use your "current location" for the "From" field instead of typing in a starting address/zip code. I'm designing a spreadsheet to use while in the field being used by cellphone.

Chris

1. I don't know anyway to do that

80. This is wonderful, Alex. Makes life so easy to sort child cares distances by a distance from home to call for availability of a spot. Unfortunately I just came here to read about limits after encountering it. And it is interesting I tried the same function on another google account on a same computer (shared the file without function calls, and made a copy) and it still showed the limit error. Wondering if the limit is per computer per day.

1. I would have expected it to be per account, but haven't ever tested that. Unfortunately no real way around it other than waiting.

81. Thanks for your responses, Alex.

I copied your updated code on the sheet you provided, and some things seem to be working, but others not. For instance, distance between a lot of zip codes, such as [92108, 92049] reads as 0. I'm wondering why this is.

I'm also getting the Invalid argument error on .setOrigin(origin). I can't figure out the debugger to understand why.

Thank you.

1. When I call DrivingMiles("92108","92049") it returns 36.132824636186264 for me. Not sure why you are seeing 0. What function are you calling and with what parameters when you see the .setOrigin error? If you're just running the DrivingMiles function through the debugger in the script editor then you would get that error since origin and destination would be undefined. You would need to write a little test function like the following and run this instead. I've added checks for origin/destination being blank to prevent the error, so you can take a new copy of the code.

function testDrivingMiles() {
Logger.log(DrivingMiles("92108","92049"));
}

82. I just found this and love this thread as it as been going on for about 7 years. I am sure I will have questions about drive time. Thanks very much Alex!! Being in the ground transportation business, this is way better than writing to those pesky API's. :) Best!! Paul

83. I have been watching many videos on this and your explanation is so much easier to follow and the only one that actually worked. We have over 300 address we traveled in 2021 and this is a great help to calculate mileage. Thank you!

84. Hello, Alex. This is a wonderful piece of code and, I am sure, has been very helpful to many people. I am very new to google sheets and Apps Script. I've read all the comments above but I was not clear whether this code can work with multiple locations.
Sometimes I need to map not just LocationA to LocationB
Sometimes it is 3 or 4 or even 5 points:
Start at LocationA go to B then to C
or A to B to C to D

I do have a work around I just map each point separately then add them together, but I was curious if there was a way to code it.

Thank you again for your code and your dedication to keeping up with the responses.
Gray

1. The DirectionFinder API has an addWaypoint() method - in addition to the setOrigin() and setDestination() that I'm already using in the code. So you could probably do something like

function DrivingMeters(origin, waypoint, destination) {
var directions = Maps.newDirectionFinder()
.setOrigin(origin)
.setDestination(destination)
.getDirections();
return directions.routes[0].legs[0].distance.value;
}

It also looks like you can chain multiple addWaypoint() methods to add multiple waypoints, and then also call setOptimizeWaypoints(true) to have the waypoints automatically rearranged in a more efficient order.

85. Using this =ARRAYFORMULA(if(ISBLANK(A2:A),"",(drivingmiles ( AB2:AB , BE2:BE )))) and the same distance is listed for each set of addresses (1st row of data)

Address format AB=origin BE=destination is street, town state(initials)

Works great otherwise.

1. Can you post a link to a public google sheet that demonstrates that problem? Because I've updated the example linked above in the article to have essentially the same formula and it seems to work.

=ARRAYFORMULA(IF(ISBLANK(A2:A),"",(DrivingMiles(A2:A,B2:B))))

86. Hello and thankyou for your work, can I write :
.setDepart("08:00am")
And does it even matter if traffic data is not available?

1. setDepart expects a JavaScript date object, so you would need to write something like

var now = new Date();
var depart = new Date(now.getTime() + (1 * 60 * 60 * 1000));
var directionFinder = Maps.newDirectionFinder().setDepart(depart);

I assume without traffic data this will not affect the duration returned.

87. Hello! This was working great for me but like you mentioned previously, it is metered and stopped working. For my purposes I need to run it thousands of times per day, is there a way to stop the metering or request increased usage?