Calculating Distance between two locations in Google Spreadsheet

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 or contact me on twitter.

97 comments:

  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!

    ReplyDelete
    Replies
    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.

      Delete
    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?

      Delete
    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.

      Delete
    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?

      Delete
    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.

      Delete
  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.

    ReplyDelete
    Replies
    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.

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

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

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

      Delete
    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.

      Delete
    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.

      Delete
    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

      Delete
  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

    ReplyDelete
    Replies
    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.

      Delete
  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/

    ReplyDelete
  6. 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!

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

      Delete
  7. 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!

    ReplyDelete
  8. 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

    ReplyDelete
    Replies
    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?

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

      Delete
    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;
      }

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

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

      function DrivingHoursFromHome(destination) {
      // replace address in next line with your home address
      return DrivingHours("1 edenton st, raleigh nc", destination);
      }

      Then in the sheet you can use it like

      =DrivingHoursFromHome(A2)

      Delete
  10. 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 ?

    ReplyDelete
    Replies
    1. Are you looking to just do something like

      =DrivingMiles("B16 8EW",A2)

      in the spreadsheet? If not, can you start with what you want the function in the spreadsheet to look like?

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

    ReplyDelete
    Replies
    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)

      Delete
  12. 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.

    ReplyDelete
    Replies
    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.
      https://developers.google.com/maps/faq#usagelimits

      Delete
    2. Thanks Alex, this is really great,

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

      Adam

      Delete
    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

      See also: https://developers.google.com/maps/documentation/directions/intro

      Delete
  13. 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)

    ReplyDelete
    Replies
    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.

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

    ReplyDelete
    Replies
    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.

      Delete
    2. Thanks for your quick answer.
      Sounds perfect, but i get the typeerror: Cannot read property "legs" from undefined. Any idea?

      Delete
    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?

      Delete
  15. 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

    ReplyDelete
  16. 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

    ReplyDelete
    Replies
    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.

      Delete
    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!

      Delete
    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.

      Delete
  17. 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?

    ReplyDelete
    Replies
    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 sheet = SpreadsheetApp.getActiveSheet()
      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);
      }
      }
      }

      Delete
    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.

      Delete
  18. 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?

    ReplyDelete
    Replies
    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.

      Delete
    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

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

    ReplyDelete
    Replies
    1. Thanks. Yes, you can add way points. The API is
      .addWaypoint('Lincoln Center, New York, NY')

      Delete
    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.

      Delete
    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.

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

    ReplyDelete
    Replies
    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.

      Delete
    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!

      Delete
    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.

      Delete
  21. 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?

    ReplyDelete
    Replies
    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.

      Delete
  22. Once I input the Script it's still coming up with "#NAME?" Please help.

    ReplyDelete
    Replies
    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.

      Delete
  23. 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?

    Thanks in advance!

    ReplyDelete
    Replies
    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 "";
      }

      Delete
    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.

      Delete
  24. 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)."

    Please help.

    ReplyDelete
    Replies
    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.

      Delete
    2. https://docs.google.com/spreadsheets/d/1i6p5K7zqojRs8SHyMzUenZq70eaF_8gXUPJxdhw6k6c/pubhtml

      Delete
    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!

      Delete
  25. 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.

    ReplyDelete
    Replies
    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.

      Delete
  26. Hello Alex, works great but can you please help me with this:

    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.

    ReplyDelete
    Replies
    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.

      Delete
  27. 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.

    ReplyDelete
    Replies
    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;
      }

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

    ReplyDelete
    Replies
    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.

      Delete
  29. 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.

    ReplyDelete
    Replies
    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.
      https://developers.google.com/apps-script/reference/maps/maps#setAuthentication(String,String)

      Delete
  30. 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.

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

      Let's say:

      A1= Address 1 (starting point)
      A2= Address 2
      A3= Address 3
      A4= Address 4
      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

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

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

      Delete
    2. 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.

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

    ReplyDelete