Google Maps Distance & Duration Calculator - Google Sheets, Build Apps Script Function - Part 14
HTML-код
- Опубликовано: 13 сен 2024
- This tutorial will show you how to create a new Google Sheets function called GOOGLEMAPS which will calculate the distance or duration between 2 addresses or zipcodes using Google Maps data. The function will be built using Apps Script Maps object and JavaScript programming language.
The fact that you let us in through your thought process, including caveats and being puzzled over unexpected results and figuring them out, rather than simply presenting a flawless step-by-step go-through without any glitches at all is priceless and has immense, inequivalent value in itself, way beyond the direct defined goal of the task at hand for itself. Kudos a million.
I know I'm kinda randomly asking but do anyone know a good website to watch new series online ?
@Garrett Mateo thanks, I went there and it seems like they got a lot of movies there :D Appreciate it !!
@Santiago Donald Glad I could help :D
Incredibly helpful! I used this and build an simple for loop to get directions to a big number of routes (did over 2000 routes the other day).
For me this piece of code worked fine. Keep in mind that my company uses G-suite, which is why I haven’t run into any daily limits for this function.
*note that I placed the origin in column C, the end in column D and set the values of the calculation in column E
function googleMaps() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("YOUR_SHEET_NAME");
var lr = sheet.getLastRow();
var lc = sheet.getLastColumn();
var range = sheet.getRange(2, 3, lr-1, lc);
//The first origin address was in the second row, third column.
var values = range.getValues();
for (var i = 0; i < values.length; ++i)
{
var row = values[i];
var start = row[0];
var end = row[1];
var mapObj = Maps.newDirectionFinder();
mapObj.setOrigin(start);
mapObj.setDestination(end);
var directions = mapObj.getDirections();
var getTheLeg = directions["routes"][0]["legs"][0];
var meters = getTheLeg["distance"]["value"];
var km = meters / 1000;
sheet.getRange(2 + i, 5).setValue(km);
//this sets the outcome in column E, startin in the second row
SpreadsheetApp.flush();
}
}
Really no limit bro? I am using API called from Excel VBA and stuck over 100,000 requests per day.
Hey man. You've probably saved my life. Lemme know when you are in Toronto Ontario Canada, imma buy you a beer.
Cheers!
This was amazing and 4 years later today it still works like a charm! Thank you saved me a ton of time! I ran it on 400 addresses and it worked well. I wish there was an easy way to make it return the shortest distance specifically because now Google serves the shortest trip time instead if I use the same code as in the video
Could you share the coode for copy paste in the description or as Link to a textfile? Would be great
We do logistics costs every day! This is a lifesaver. Ditto to Ice Xiao comment below, when you are driving up to Toronto from the US, stop in Buffalo NY and I'll buy you a beer too and some original Buffalo chicken wings!
I followed the video and learned how to create the function on my own using your step by step instructions. This video was exactly what I was looking for. Thank you so much!
Now limited on number of runs :-(
This is exactly what I needed!!!!! I will keep you posted and if what im doing takes off.....except SOME kinda payment. Not even playin.
For those who it did not work, try to input :
=GOOGLEMAPS(A1;A2;"miles")
instead of , put ;
I don't know why but it worked for me !
ruclips.net/video/ljvB2arCsNQ/видео.html
Thank you so much!! Amazing. I thought the troubleshooting was going to be annoying, but I actually learnt a lot. Awesome.
Excellent step by step example. Thanks
Thanks for posting the video it was really helpful . however i have a quick questions.. how would go about getting distance for 4points. for example from a to b , b to c and c back to a ?thanks in advance.
Hi, is there anyway i can add waypoints in the route? So "start Address", "waypoint1", "waypoint2" and "end Address". Thank you!
Would love to see that function too!
Did You get answer ?
I'd love to know how to add stops...
Great hands on tutorial, very useful. Many thanks.
It's been helpful and I was wondering do have video that has more than 2 direction. Thank you
Great video, I learned a lot
Dude, this was so freaking helpful! Thank you so much!
Hello
I see many people here are looking for a way to add a google map api key in order to avoid the daily quota. Any help would be mostly appreciated.
Thanks
Hey great video. the trial and error method was maddening. then actually started learning to debug. so silver lining for sure.
getting error cant get the legs.... how to fix?
I have never done anything like this before, but my company is doing 100's of routes a day and this would make my life so much easier...is is able to be copy and pasted?
Hai Sir, this tutorial free for a large amount of data? or is there a limit? Thank You
Thank You fro your tutorial. Its Help me alot..
Is there any way how to choose two-wheeler mode in maps on DirectionFinder? my region is Indonesia
Hi, how to modify the script to ensure that the shortest route in distance and not in time is taken? Thank you
Cool, thank you for this video! It works perfectly for me!
Thank you so much from viet nam
Im running into the issue where it works once, and then as I try another line, I get the error message "TypeError: Cannot read property "legs" from undefined. (line 11)." Any ideas on how to fix? I tried changing the calculation method per Nick below and error is not fixed
I'm having this same issue.
any update on this?
I had that issue because I was trying to run the function with a missing argument. One of my cells on the spreadsheet was empty. I was referencing the wrong column with my start point variable. I'm also a newbie. Hope this helps somebody.
Also, I use lat and long for starting point, and an address for destination. Appsheet allows me to use their here() function to get my lat and long w/o using my geo call.....
I Know that the distance is constant but I want to know that Is the duration a real time approximation? or is just calculated once for each pair of origin, destination??
What about measuring the distance between two zip codes?
Could you please update the code with API-key?
Is there any way to get drive time based on Traffic Conditions of a certain part of the day. I live in Los Angeles and traffic hour makes a huge difference. I would really appreciate if you could help me with that.
Nice one. Thank you so much.
How can we calculate "duration" in real time basis, not just the average time? In other words, how can we obtain actual duration of journey using the departure time from the origin??? Can you paste the code for the same pls?
Hi Thanks for the video! The route it selects is the shortest by time. However I want the route with the shortest distance. How can I program it like that?
Up
Sorry, can it work to find direct distance ? (vs travel distance)
Possible to find as the crow flies distance vs drive route as depicted?
Hi its posible get the current time to on a specific place?
Hey man, this looks amazing - Do you have a website where I can copy this code from?
why can you paste the final code in the description or a attachment if you wanted us to make so much rectification
Would love to add multiple routes and pull time inbetween each stop and also be able to pull Postal codes, since log shows postal codes for address. Thanks.. can you post a video for this. and also add stop times onthe route. Each place requires a different amount of time. thanks
I want to create a google form where I can extract the coordinates of the photo uploaded by user considering a construction project having several site location. In order to track progress by photo documentation.
Hey man, I am just wondering on how to make these made functions work with arrayformula. It seems that it doesn't expand the formula throughout the selected cells. Please help. Thanks!
I am building a travel route list using MyMaps...and it gives route, but no times or distance...why not? Why isn't that built in? Is the only way to do what you're doing?
How to make these made functions work with arrayformula. It seems that it doesn't expand the formula throughout the selected cells or how to make it work with the code that have triggers on change and write distance value to cell. Please help. Thanks!
@
Learn Google Spreadsheets
Is it possible to get distance from the link of the location?
Very helpful tutorial ! Do you know how change the travel mode by bus or bike?
If you still need that info you can change the mode in the DirectionFinder objet like so:
mapObj.setMode(Map.DirectionFinder.Mode.WALKING)
you can set it to BICYLING/TRANSIT/DRIVING/WALKING
@@Rahinser Thanks for the start man! however, mapObj.setMode(Maps.DirectionFinder.mode.WALKING); gives me "TypeError: Cannot read property "WALKING" from undefined. (line 15, file "Code")" any help on writing walking differently?
hey, you forgot to put ""var" before the assignment of the start_address and end_address, but why it still worked?
I wondered too but it's because they're already defined in the function, within the brackets.
7:59 is where you reveal the easiest formula to get distance and direction :D:D:D
Hi,
Nice tutorial. I liked the whole trial n error method of explaining..
However, let’s say we have got a list of addresses and we need to calculate the distances between them, in a matrix form. So column A1-A1000 would have one list and Row B1-BBZ1 will have one.
Now if your are supposed to calculate the distance using the same set of codes, do you just need to change the inputs from a specified cell to a range or something else? Will this same code work in excel, the way I just explained? Any limits to daily number of iterations? Much thanks!
Yes, there are limits. Depends on your GSuite account level you'll have different limits. With regular google account I think it's 50 or 100 calls per day or something like that. Not sure exactly how many it is, but not too many. This code will not work in Excel. You'll need to use Google API or Bing API and connect with VBA code to pull the data, so it's very different in case of Excel.
great video! I have to make a list for mileage. I always have to take the shortest route and not the fastest. how can I enter this in the script, that he always takes the shortest distance.
Thanks for your help
Are you able to add an autocomplete address function?
I wish this would work for me but app script does not have the view log function so i cannot find my error
can you make a video to obtain the directions of .... to set a var to get the directions in it....just right or left will do.....pls
Like a few others have mentioned I am getting a "TypeError: Cannot read property "legs" from undefined. (line 6)".
Line 6 looks like this: var getTheLeg = directions["routes"][0]["legs"][0];
The code preceding this:
function GOOGLEMAPS(StartAddress, EndAddress, return_type) {
var mapObj = Maps.newDirectionFinder();
mapObj.setOrigin(StartAddress);
mapObj.setDestination(EndAddress);
var directions = mapObj.getDirections();
var getTheLeg = directions["routes"][0]["legs"][0];
var meters = getTheLeg["distance"]["value"];
I have made sure the addresses are correct and using the Logger.log function I can get the "directions" to log and I see the:
"legs=[{duration={text=18 mins, value=1082}" and a bit further I see "distance={text=6.0 mi, value=9617}"
Check for Tolls
Dear Sir,
Can you please direct us on how to create a heat map layer with sheet and map with different data
What is the limit of distances that we can find in a day ?
Hi Love this video! Can we have another tut on how to lookup postcode and output suburbs
myrouteonline is great price enter address and postal codes will be found quick.
Hi, how do I have to change the script to get the walking destance?
Thank you for the script! May I ask you how I could get it to avoid tolls?
var directionFinder = Maps.newDirectionFinder().setAvoid(Maps.DirectionFinder.Avoid.TOLLS);
Plz help i need your help, i have a video of something moving and i want to find its speed but i can't because the sky is so big to measure with the frame i have, i have the time it taken it to travel from point A to point B but not the distance, can u please help me?
Good day - so i followed your video, reviewed several times, before and after. I get strange results - nothing. no error, no calculated distance, nothing. I do not have a billable account for API calls to google, so i am wondering if that is the problem? I see you have not responded to anyone since about 10 months ago, so not hopeful that 1. i am able to do this, 2. that you are going to even tell me its not going to work without the billable google api call. Great video by the way - very easy to follow, just sucks that google is more interested in my money.
At the time of making this video you could just use it with a regular account. Haven't had to use this since, so I'm not sure.
can you convert the URL location to Coordinates?
Great video! however, Google recently introduced the API key in Google Maps Distance matrix. How do I incorporate this into Apps script? thanks!
Your google account linked to email get API account should automatically work
Great tutorial! thank you!
Only when I want to test the function and use an actual input from my spreadsheet I get and error "Action not allowed (line 9)." and it refers to " var directions = mapObj.getDirections();
"
Any advice?
Maya: Been trying to work though the same issue, but no luck. I though it had something to do with clientID & key credentials but every example I have tried fails.
I have the same problem. Can't solve it.
Jacob N Grundfos Any luck?
THAAAAAAAAAANKSSS!!! Love it!
how can I add multiple points?
can you change the travel mode?
see my comment above :-)
Good luck !
what would you do if you want cycling distance or walking distance?
Hi Nice Video,
I was able to run this but I'm getting miscalculated time. The distance was correct but the value is not correct. It shows all the routes around more or less 2hours and 28mins but I'm getting only 1hour and 30min only on the first route. Whats the idea about this. Already checked this with the Logger.log and same result.
possibly it's using a walk function instead of drive, test it with the same routes on gmaps and try different transport methods and see if the time matches your unexpected output
Hi There. Excellent tutorial. Is there a way to collect the Phone number, Email, and Address of a given registered company on Google Maps using a variation of this script?
I haven't tried it with places but you can test if it returns the info in the object then yes. Otherwise you'll need to hook into Placed API
@@ExcelGoogleSheets I have no ideia how to try to get this data from the APIs. Looking into documentation it does not explain nothing similar to my needs.
Do you have any tutorial showing how to work with Sheets and Placed API? If so, could you please share the RUclips link?
@@mvgiugni65 I just checked the docs, it doesn't support places, so you would have to use Place API developers.google.com/maps/documentation/places/web-service/search-find-place
I don't have a video on this.
Note that Place API is not a free service.
How can I get the link to Google Maps? Only via API?
thank you!!!
It doesn't work with two countries e.g. USA, Bangladesh
I get the below error
TypeError: Cannot read property 'legs' of undefined (line 16).
I haven't really tested it with other countries. You'll need to check the JSON response and see what you get.
please give me the solution of this error it is urgent TypeError: Cannot read property 'distance' of undefined (line 53, file "Code"
I did the tutorial step by step and still not working, the function =GOOGLEMAPS ends up in error.
Is there any place that I can find the project to compare and see what is different?
Exception: Invalid argument: origin
GOOGLEMAPS
@
just like me...Unfortunately, I could not find a solution either.
I want to find distances for thousand pairs of origin and destination and it is giving me an error "you have exceeded your daily limit". Please tell me a way by which i can include my API key in the script
I'v got the same problem..
I haven't actually tested this but I think this should work GOOGLEMAPS(A2,B2,"miles","clientId","signingKey")
Using the function below in your script editor
/**
* Get Distance between 2 different addresses.
* @param start_address Address as string Ex. "300 N LaSalles St, Chicago, IL"
* @param end_address Address as string Ex. "900 N LaSalles St, Chicago, IL"
* @param return_type Return type as string Ex. "miles" or "kilometers" or "minutes" or "hours"
* @customfunction
*/
function GOOGLEMAPS(start_address,end_address,return_type,clientId, signingKey) {
// www.chicagocomputerclasses.com/
// Nov 2017
// improvements needed
Maps.setAuthentication(clientId, signingKey);
var mapObj = Maps.newDirectionFinder();
mapObj.setOrigin(start_address);
mapObj.setDestination(end_address);
var directions = mapObj.getDirections();
var getTheLeg = directions["routes"][0]["legs"][0];
var meters = getTheLeg["distance"]["value"];
switch(return_type){
case "miles":
return meters * 0.000621371;
break;
case "minutes":
// get duration in seconds
var duration = getTheLeg["duration"]["value"];
//convert to minutes and return
return duration / 60;
break;
case "hours":
// get duration in seconds
var duration = getTheLeg["duration"]["value"];
//convert to hours and return
return duration / 60 / 60;
break;
case "kilometers":
return meters / 1000;
break;
default:
return "Error: Wrong Unit Type";
}
}
Hi Nithish were you able to find a solution for a large number of origin and destination pairs
@@ExcelGoogleSheets For this to make it work one would have to purchase a premium plan right? Cant figure out where to get my client ID. API key would be no problem...
the fonction plz
How can i calculate google distance between two points using latitude and longitude
in the mapobj.getdirections method there's a version that uses lat/long points instead of addresses
hi i keep getting this error message "TypeError: Cannot read property "legs" from undefined. (line 19)." can you please help? thanks
Did you ever get a reply that fixed this issue? I have the same problem!
no i didnt.
@@NoProbsMedia Tolls were the issue for me
Hello, I built the code exactly how it's in the video but I'm getting the following error: "UrlFetch failed because too much traffic is being sent to the specified URL" to the line "var directions = mapObj.getDirections();". Could you please help me fix it? I'm new to javascript and google APIs
How many addresses do you have? It seems like you may have reached your daily quota for UrlFetch.
I only have 2 addresses (start and end addresses) as per your tutorial
the limit is per account, so if you have other functions that fetch external data through UrlFetch it will affect the limit as well. Also make sure those addresses are valid.
I registered for the Google account yesterday and don't have any other functions enabled other than this tutorial. Thank you anyway.
maybe share the spreadsheet so we can see what's happening?
the code plz
I am trying to use this code to help get distances from events to teams. I am getting an Error every time I try to use it. I am getting the service invoked to many times for one day. I am unsure if i am doing something wrong can anyone help me?
Hi @Noah Verhine, any update on this? I am also getting the same error. :(
How do i get the maps object in appscript?
It should be in there by default.
@@ExcelGoogleSheets It's not there for me... Searching Google doesn't help... :(
now I need a script (button) that does this for a large column, but sleeps between every row
I'm getting an Error: "TypeError: Cannot read property "legs" from undefined. (line 16).
could it have something to do with how the address is formatted in the cell? for example, I've noticed problems when I input address like "Street Address, City, State Abbreviation"
The same here, but Sometimes it works, Dont know why it didn't work anymore after several times testing.
Hey, I found a work around to the problem.. Go in to your spreadsheet where you're using the custom function. under 'File' select 'Spreadsheet settings'. Then select the 'calculations' tab. You should see an option to turn 'on' the 'Iterative calculation'. I have my max number of iterations set to '10' and the threshold set to '.05'. Making that adjustment did the trick for me. I'm not 100% sure of how this limits the functionality of other formulas, but it certainly helps with this one.
Also, I'm still not 100% confident in using the custom formula still, because I've run into a couple of errors here and there with it.. Most likely because of the way in which the address, state, zip, is parsed together, but it's definitely better with the work around. It'd be great if there was some kind of add-on that was more consistent.
Videos by Nick Thanx, I will try.
Hello moderator. Can you build me a custom sheet along these lines? I'll pay you.
Lat long to Zip code script please
send the script Please
You need to host your code to some other place your website has an UI bug it does not copy the complete code that is why most of the people including myself had issues with the script.
Copy it from the comments:
/**
* Get Distance between 2 different addresses.
* @param start_address Address as string Ex. "300 N LaSalles St, Chicago, IL"
* @param end_address Address as string Ex. "900 N LaSalles St, Chicago, IL"
* @param return_type Return type as string Ex. "miles" or "kilometers" or "minutes" or "hours"
* @customfunction
*/
function GOOGLEMAPS(start_address,end_address,return_type) {
// www.chicagocomputerclasses.com/
// Nov 2017
// improvements needed
var mapObj = Maps.newDirectionFinder();
mapObj.setOrigin(start_address);
mapObj.setDestination(end_address);
var directions = mapObj.getDirections();
var getTheLeg = directions["routes"][0]["legs"][0];
var meters = getTheLeg["distance"]["value"];
switch(return_type){
case "miles":
return meters * 0.000621371;
break;
case "minutes":
// get duration in seconds
var duration = getTheLeg["duration"]["value"];
//convert to minutes and return
return duration / 60;
break;
case "hours":
// get duration in seconds
var duration = getTheLeg["duration"]["value"];
//convert to hours and return
return duration / 60 / 60;
break;
case "kilometers":
return meters / 1000;
break;
default:
return "Error: Wrong Unit Type";
}
}
Thanks for letting me know,
I'd love to be able to call on a custom route based on my spreadsheet
Bruh
Doesn't work. Just returns "Wrong unit type" no matter what addresses and return type I enter. Junk script.
Works fine! You're doing something wrong!