all I can say is WOW!!!!!!!!!!!! thanks! so cool and easy the way you show and explain. I download what you mentioned and toggled back and forth from your video to the sheet. Wonderful.
You just saved my occupational medicine thesis! Thanks a LOT! I'm subscribing to support you and will go to your channel for all my questions on Google tools.
First of all, I'd like to thank you for this video. As an inexperienced coder, I'm trying to figure out how to get the walking times as well. Any help would be greatly appreciated.
There are specific add ons to the function you can use. Try var mapObj = Maps.newDirectionFinder() .setDepart(new Date(2020,2,3,9,0,0,0,0)) .setMode(Maps.DirectionFinder.Mode.TRANSIT) (or WALKING in your case)
First of all thank you so much for getting this video out! I just opened my google sheets and all my "Minutes" and "Miles" results have a #ERROR! on them. Was there an update that's making everything wonky?
Wow! Really easy and is still working for me! Thanks. If there is also a way to show the driving directions in an embedded map or link, it would be appreciated.
It still works but Google made some changes it looks like. The script editor is no longer under Tools, it's under Extensions as App Script. Another thing, I noticed is that App Script would not load with my regular Gmail Google Sheets but it works perfectly fine using my Gsuite domain Google Sheets
How do I overcome the "Exception: Service invoked too many times for one day: route. (line 14)." Error message? I'm trying to create a distance grid of 31 addresses to use to solve a "traveling salesman problem". I really like how this was created and tested. Your method was very educational for me!
thank you so much, it works! I wonder if there is anything we can add to that script to say "generate the miles for the SHORTEST route" for example, when we search any address in Maps, Google gives several routes, one is usually always less miles. maybe a script could somehow pick up the "shortest miles distance" route?
Thank you so much for the script it’s really useful! Is there any way to calculate multiple stops and also when some of formulated cells empty still calculate rest of the locations ?
@LearnGoogleSpreadsheets - thank you so much for so many great tutorials. Any chance you have figured out how to use the Google Maps API to update a Google map from a Google spreadsheet?
Thanks so much for this - very helpful. When I opened the sheet the day after I ran it first, it gave me this message...."Exception: Service invoked too many times for one day: route. (line 113)." - any thoughts to fix it?
Try this var mapObj = Maps.newDirectionFinder() .setDepart(new Date(2020,2,3,9,0,0,0,0)) ---> Or setArrive in your case .setMode(Maps.DirectionFinder.Mode.TRANSIT)
Is it possible to accomplish this same thing with just zip codes instead of addresses? I tried just using zip codes but get errors. It works fine with addresses. Let me know what I need to do to fix this. Thanks!
I haven't found that the duration (in minutes) changes appropriately to real time. Currently, the calculation is 18 minutes; however, when I go to Google Maps, there's a lot of red and it shows 26 minutes.
yes, this is awesome. My only concern is that when I go to add a new row via an app to google sheets. The miles don't read to that column. Is there a way to add the script to upcoming columns without doing it manually or no?
Exception: Service invoked too many times for one day: premium route. (line 18). Getting this error in Google sheet, Please let me know how can I fixed it.
This script has recently stopped working for me. I'm getting a "Invalid Argument: origin" on line 18 "mapObj.setOrigin(start_address);" I'm wondering if it's linked to the recent V8 migration.
Hello, Can we have an updated versions since I've got an error " Service invoked too many time for one day " i am using this method on a sheet contain 24k rows
Hi there. I copied the code into appsscript, but im trying to use the formular i cant see =Googlemaps appearing. Am i doing something wrong? Do i have to switch the language or something else?
Hello. In addition to Distance and Time I need to know whether or not the route has a Toll or Ferry. In this spreadsheet, how to create a function in the VBA module to put Y or N in column G for Tolls or Ferry?
Hi Big fan of yours, I used this script on my sheet and works fine, but I am encountering issues, My issue is that I get error saying script invoked too many times in a day, how do we avoid this. Pls reply ASAP, VERY URGENT SUPPORT MEEDED
Is there a way to save that function to a blank cell so when the above cells have the approriate data entered it auto populates without inputting the data each time?
How to modify the script so that it recalculates the distance only once when changing the value of the start or finish place? Unfortunately, in the example above, it recalculates too often, which in the end blocks the script.
Thanks a lot! I was looking for something like that. Do you have any idea how to create an image in cell with the route map? I created a dynamic links, but how to create something like screenshot / in cell? Do you have any idea?
Well this script has been awesome for a long time. In the last month or so I am getting errors when using certain zip codes or different combinations of zip codes. Any Idea what I can do to resolve this?
Usually when you get errors for this that's because you run out of your daily limit. But in that case it will stop working for all zipcodes, not just for some.
Hi, I had the same problem! The script shows a 10 min quicker arrival then the actual arrival when I checked in Google Maps on my phone - does anyone have a solution to why that is?
Hi there! I'm struggling with an error code. It says "Exception: Service invoked too many times for one day: premium route. (line 18)." and now the distances won't calculate. Can you share more how to address this?
thank you so much for this video! I have a really large dataset however and will need to implement google maps api. is there any way you can make a video/ a function script that includes a spot for me to put the google maps api?
take in consideration that if you have large database of address, and if you do not have google account paied, Gsuite. This will not work probably with large data. they put a limit.
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!
hi wanna ask how to set the departure time like 6pm, as for the date, can it set like =today, so it will automatically change the date, kinda urgent🤣🤣if can please do reply
AMAZING VIDEO ... ONE QUESTION, i WOULD LIKE TO ADD HOURS FROM STARTING ADDRESS TO ENDING ADDRESS TO A PROJECT BUT I AM RECEIVING THIS MESSAGE. PLEASE TELL ME HOW I CAN FIX THIS. THANK YOU IN ADVANCE Exception: Invalid argument: origin (line 189, file "Maps")
As in the previous video relating to this topic, I am still getting mostly this error: TypeError: Cannot read property "legs" from undefined. (line 20). {which is the proper line # in my code where "legs" is first referenced} However, 2 of the five addresses do give me the proper mileage but ALL 5 of their "minutes" give me the same error: TypeError: Cannot read property "legs" from undefined. (line 20).
5 years later and this video is still valid. It's direct, to the point, easy, and it works. Thank you.
Great to hear!
Please tell me if one can use this method and wants to get driver real time data. Will that update on spread sheet?
@@ExcelGoogleSheets, Getting error, Please let me know how to fix it ??
i just wanted to say thanks!!!!!!!!! i use this for my daily dispatch of 100+ stone and asphalt trucks. Its a great help tracking driver productivity
Best guy ever. Had been trying to get this for so long. Finally got this video. Love you man.
This works very well! I was able to save myself too much time by searching for distances and times directly on Google Maps! Thank you a lot
Excellent!
@@ExcelGoogleSheets i hava one quarry whatapp no
I tried this out without expecting myself to make it work. I MADE IT WORK! Thanks for the great video!
Thank you so much for digging me out of the rabbit hole of finding this solution! Works perfectly.
Thank you very much. You saved me alot of time! Greetings from Germany!
all I can say is WOW!!!!!!!!!!!! thanks! so cool and easy the way you show and explain. I download what you mentioned and toggled back and forth from your video to the sheet. Wonderful.
This script still works fine. No problem with him!
You just saved my occupational medicine thesis! Thanks a LOT! I'm subscribing to support you and will go to your channel for all my questions on Google tools.
This works great, I am not very familiar with scripts, but this was a super easy tutorial! Thanks for sharing.
This was a GREAT tutorial! Thank you for assembling this.
First of all, I'd like to thank you for this video. As an inexperienced coder, I'm trying to figure out how to get the walking times as well. Any help would be greatly appreciated.
Hi Dustin, any luck in figuring out walking distances/times? Thanks
There are specific add ons to the function you can use. Try
var mapObj = Maps.newDirectionFinder()
.setDepart(new Date(2020,2,3,9,0,0,0,0))
.setMode(Maps.DirectionFinder.Mode.TRANSIT) (or WALKING in your case)
@@swaroopinii where in the function is to be pasted?
is there a way to save a cache to avoid the error message "Service invoked too many times for one day" ?
Thank you so much.
I wanted to make a trip plan for the summer, this function will be very useful for me.
greetings from Brazil 👊, you helped me a lot!!!
I Love you! This is the best channel ever!
Thank you so much! You've made my manager (and therefore, me) very happy haha
Thankyou! This saved me a tonne of time and effort!
First of all thank you so much for getting this video out! I just opened my google sheets and all my "Minutes" and "Miles" results have a #ERROR! on them. Was there an update that's making everything wonky?
you are our savior
Thank you for this explanation .... We have a problem... Exception: Service invoked too many times for one day: route
Wow! Really easy and is still working for me! Thanks. If there is also a way to show the driving directions in an embedded map or link, it would be appreciated.
Script works great. Thank you so much!
Excellent tool - thanks.
If you are watching this in 2022, the script editor become Apps Script and it is in 'EXTENSIONS'.
👍
Been looking for this thanks very much!
It still works but Google made some changes it looks like. The script editor is no longer under Tools, it's under Extensions as App Script. Another thing, I noticed is that App Script would not load with my regular Gmail Google Sheets but it works perfectly fine using my Gsuite domain Google Sheets
Thanks, worked for me. Using UK post codes.
This is a great function! Thank you!!!
Thanks!!!!!! i love this script!! :)
Thank you so much!!
Thank you! Great.
first time in youtube i am able to found out to find all the distance
Thanks! Worked for our solar firm....
Thankyou you are such a great blessing
Thanks for making it so easy
How do I overcome the "Exception: Service invoked too many times for one day: route. (line 14)." Error message? I'm trying to create a distance grid of 31 addresses to use to solve a "traveling salesman problem". I really like how this was created and tested. Your method was very educational for me!
This worked great, but for some reason now a year later it doesn't seem to work, says Invalid argument origin (line 16, file "code")
Same here!
you must tune up the code. from time to time probably google change order of the arrays
@@casianreport3318 What would be the new arrays?
@@elisabethcai-pippin3063 yeah does anyone have the working code?
Thank you so much...This one really works..Very3x useful
👍
It works great! Thank you!
Hi im getting Exception: Service invoked too many times for one day: route. do you have any workaround for this? thank you
thank you so much, it works! I wonder if there is anything we can add to that script to say "generate the miles for the SHORTEST route" for example, when we search any address in Maps, Google gives several routes, one is usually always less miles. maybe a script could somehow pick up the "shortest miles distance" route?
Thank you so much for the script it’s really useful!
Is there any way to calculate multiple stops and also when some of formulated cells empty still calculate rest of the locations ?
i am getting this error please help.
(Exception: Service invoked too many times for one day: route. (line 18).)
Thank you so much! you a great!
@LearnGoogleSpreadsheets - thank you so much for so many great tutorials. Any chance you have figured out how to use the Google Maps API to update a Google map from a Google spreadsheet?
thank you!!! exactly what im looking for. is there a way to add avoidance such as "Tolls"?
Thanks so much for this - very helpful.
When I opened the sheet the day after I ran it first, it gave me this message...."Exception: Service invoked too many times for one day: route. (line 113)." - any thoughts to fix it?
How do we fix this?
Me to.
i have similar issue. Exception: Service invoked too many times for one day: route. (line 18). did you fix yours?
For fixe this, you must by a offer to Google. In fact, you can't calculated more than 2500 opération per day.
Subscribed dude..
Hi, rather than getting the distance from 2 places, is it possible to get a screenshot of the map of an address within the cell?
can you add a depart or arrive by time? then calculate miles and minutes according to that
I would really like to be able to do this also.
Try this
var mapObj = Maps.newDirectionFinder()
.setDepart(new Date(2020,2,3,9,0,0,0,0)) ---> Or setArrive in your case
.setMode(Maps.DirectionFinder.Mode.TRANSIT)
Does anyone know how to use this Function in an ARRAYFORMULA? If I try to use GOOGLEMAPS(A2:A,B2:B,"miles") i only get the first row's calculation.
Did you figure this out?
@@cookeryfoodtruck5356 any answers yet?
Great video thanks for sharing
Awesome work, thank you
Glad you liked it!
Any way to get public transit travel duration?
Is it possible to accomplish this same thing with just zip codes instead of addresses? I tried just using zip codes but get errors. It works fine with addresses. Let me know what I need to do to fix this. Thanks!
Very useful.Thanks for sharing..:)
Thank you!
Using coordinates is the same? Thanks
I haven't found that the duration (in minutes) changes appropriately to real time. Currently, the calculation is 18 minutes; however, when I go to Google Maps, there's a lot of red and it shows 26 minutes.
yes, this is awesome. My only concern is that when I go to add a new row via an app to google sheets. The miles don't read to that column. Is there a way to add the script to upcoming columns without doing it manually or no?
Very confusing. For some addresses I get only km but shows error for miles, while for the others only hours can be calculated
Great video, really helped. Also with the km, You could just converter the mile result into km?
Bravo !
Can we get the latitude and the longitude? I think it s possible, could you explain it?
Thanks for your video, i'm corius how we can get toll prices after we know the distance? Please your answer. Thank you
TypeError: Cannot read property "legs" from undefined. (line 20, file "Code")
Dismiss
can you tell me how to solve
me too
Exception: Service invoked too many times for one day: premium route. (line 18). Getting this error in Google sheet, Please let me know how can I fixed it.
This script has recently stopped working for me. I'm getting a "Invalid Argument: origin" on line 18 "mapObj.setOrigin(start_address);" I'm wondering if it's linked to the recent V8 migration.
Throwing an Error now because of Line 16 - set origin. Hopefully somebody can fix this bug anytime. Thanks!
Did you ever find a fix?
I am getting error : cannot read properties of the undefined (Reading Legs) ( line 20)
Thank you.
Hello,
Can we have an updated versions since I've got an error " Service invoked too many time for one day "
i am using this method on a sheet contain 24k rows
Hi there. I copied the code into appsscript, but im trying to use the formular i cant see =Googlemaps appearing. Am i doing something wrong? Do i have to switch the language or something else?
Hello.
In addition to Distance and Time I need to know whether or not the route has a Toll or Ferry.
In this spreadsheet, how to create a function in the VBA module to put Y or N in column G for Tolls or Ferry?
thanks
Hi
Big fan of yours, I used this script on my sheet and works fine, but I am encountering issues,
My issue is that I get error saying script invoked too many times in a day, how do we avoid this.
Pls reply ASAP, VERY URGENT SUPPORT MEEDED
Other then having a paid G-Suite account not sure what else you can do.
Is there a way to save that function to a blank cell so when the above cells have the approriate data entered it auto populates without inputting the data each time?
mapObj.setOrigin(start_address); I TRIED BROTHER BUT ITS SHOWING MISTAKE IN 16TH LINE WHAT SHOULD I DO NOW KINDLY HELP
PLEASE
Hey, is there a way to select the longest route rather than the quickest?
I would really like to know too please have you been able to achive it?
How to modify the script so that it recalculates the distance only once when changing the value of the start or finish place? Unfortunately, in the example above, it recalculates too often, which in the end blocks the script.
Thanks a lot! I was looking for something like that.
Do you have any idea how to create an image in cell with the route map?
I created a dynamic links, but how to create something like screenshot / in cell? Do you have any idea?
I found this to be a static calculation that does not take traffic conditions into account.
hi thanks for this, i've tried by entering pin code. its not working in sheets but working in maps
Thanks!
Well this script has been awesome for a long time. In the last month or so I am getting errors when using certain zip codes or different combinations of zip codes. Any Idea what I can do to resolve this?
Just for some zip codes? The rest work fine?
Usually when you get errors for this that's because you run out of your daily limit. But in that case it will stop working for all zipcodes, not just for some.
@@ExcelGoogleSheets Yes only for some including my personal zip code no longer works 37334
@@ExcelGoogleSheets SO it seems the issue is only when using zip codes. If I use the "city, state" I have no errors.
Keep getting "Service invoked too many times for one day: route.
How to resolve?
làm tốt lắm anh bạn, cảm ơn bạn nhiều
Hi ! Are you sure that the function returns the current travelling time ? In there a delay ? does it apply the time it is in my time zone ?
Hi, I had the same problem! The script shows a 10 min quicker arrival then the actual arrival when I checked in Google Maps on my phone - does anyone have a solution to why that is?
Hi there! I'm struggling with an error code. It says "Exception: Service invoked too many times for one day: premium route. (line 18)." and now the distances won't calculate. Can you share more how to address this?
You get limited number of requests per day with free service. You'll have to use paid service for a large number of requests.
Can you do something similar with addresses For example to see how many mins it would take to drive from depot to site and back.
thank you so much for this video! I have a really large dataset however and will need to implement google maps api. is there any way you can make a video/ a function script that includes a spot for me to put the google maps api?
take in consideration that if you have large database of address, and if you do not have google account paied, Gsuite. This will not work probably with large data. they put a limit.
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!
is it possible draw route between diferentes locations in this sheet?
hi wanna ask how to set the departure time like 6pm, as for the date, can it set like =today, so it will automatically change the date, kinda urgent🤣🤣if can please do reply
My cell spits out loading regardless of what I change... Any suggestions?
Im not sure why but this is not working for me it keeps saying error even though it is a real address
AMAZING VIDEO ... ONE QUESTION, i WOULD LIKE TO ADD HOURS FROM STARTING ADDRESS TO ENDING ADDRESS TO A PROJECT BUT I AM RECEIVING THIS MESSAGE. PLEASE TELL ME HOW I CAN FIX THIS. THANK YOU IN ADVANCE Exception: Invalid argument: origin (line 189, file "Maps")
How do I add the api key to look for more than 1000 records
it's giving me an (Error: Wrong Unit Type) even with your address, can you help, please !!
same here
I was using an upper case M for my Miles and when I changed it to a lower case, that fixed my issue!
As in the previous video relating to this topic, I am still getting mostly this error: TypeError: Cannot read property "legs" from undefined. (line 20). {which is the proper line # in my code where "legs" is first referenced}
However, 2 of the five addresses do give me the proper mileage but ALL 5 of their "minutes" give me the same error: TypeError: Cannot read property "legs" from undefined. (line 20).