I don’t know if you had a problem with putting addresses in the map component, but for me, the location pins were showing everywhere but the place I was aiming for.
After figuring out how to make it work properly I wrote a very short story about it in Medium:
This is an awesome tip. I just added this script to one of my municipal apps where citizens can report illegal parking issues during a snowstorm. It seems to handle blocks, intersections, and specific addresses without an issue (so far).
Excellent your article, Dan. The solution I have used is to write the address on the map of the mapbox website (https://docs.mapbox.com/search-playground/). It gives you the correct way to write the address. Although some addresses cannot be found, in my case it has only been 10% of my searches.
I will test your formula with the addresses that I could not find. Thanks Dan.
Hey guys/gals, Im brand new here, and I have a google sheet with all of my club members addresses. They are in separate columns, street, city, state and zip.
They are as the original poster mentioned showing up all over the globe too.
So my question is this. I paid for the premium app, how does a non-coder like myself get this to work properly?
Coordinates are best and most reliable, but if you use addresses, you need to make sure the entire address is in one column. If your address consists of multiple columns and you can only point the map to one of those columns, then it’s expected to have erratic results. Either use a formula in the sheet or use a template column in the Glide data editor to join all the address columns into one complete address column.
I am having an issue with the script though and was wondering whether you have an idea how to solve it:
I use array formula to apply the formula to new rows. Every time a new row is added, the formula re-calculates the entire column’s coordinates which causes:
a. some down time before Glide refreshes and shows the pins on the map. This means that maps are not shown for a couple of minutes when a new row is added.
b. “Exception: Service invoked too many times for one day: geocode. (line 11).” - since my app is sending hundreds of requests each day, I reached a limit I wasn’t aware of.
I think it would be great if newly added rows would use geolocation for themselves only without recalculating the entire column. Any idea how to do that?
Thanks Jeff, I currently use a script that creates a custom formula, then apply it to the address column using array formula. The array formula recalculates the entire column every time a new row is added, which is the issue.
I don’t know how to write code (that’s why I’m here ) so I asked a friend to help but he couldn’t figure it out
Maybe I will try this next week when I have time. The idea is the same as yours, but add in that after the calculation we copy the final value and paste it back into the cell so it won’t recalculate again every time a new formula is added.
I’m working on app where I have already successfully managed to achieve the following:
Get user to enter either post code or use current location.
Get driver to do as User in 1 above.
Show pick up point and drop off point. Here i show both coordinates and addresses for the same.
Show distance to driver
Show distance from pick up to drop off
6 show price by mile
Not use time as roads have different speed zones and traffic was another issue but have included the formula.
My template is now out of the production stage.
Now, addressing your problem.
I experienced exactly what you are experiencing.
I’m sure you are using getAdress and/or either geoode yandex or google. I prefer yandex as I miss can make more calls on it than I can on google.
Get rid of the array formula if you are using a script as you would have to. Instead use a copydown script. That gets rid of the recalculation of the coordinates each time a new row is added.
If you need to split the coordinates to get the address, use an arrayformula with iferror. For this you will not need a script. I use both text join and split to make sure both ends are tied.
Hope it woks for you as it has for me. When I showcase my app, you can it all in action.
That’s exactly the solution I am looking for - thanks @Wiz.Wazeer!
The part I’m having trouble with is #3 in your solution, I’m not sure how to create a copydown script. For the matter, I am using the script below to create a custom formula, which is implemented in a sheet called “Homes”. Column I contains the full address entered by the user, column J should return the coordinates using the custom formula.
function myFunction() {
}
function GEOCODE_GOOGLE(address) {
if (address!=""){
if (address.map) {
return address.map(GEOCODE_GOOGLE)
}
else {
var r = Maps.newGeocoder().geocode(address)
for (var i = 0; i < r.results.length; i++) {
var res = r.results[i]
return res.geometry.location.lat + ", " + res.geometry.location.lng
}
}
}
}