Hello, I know this is not a glide problem by itself but maybe somebody can help me with this. In my sheet I have a column Address (I) ,a column Coordinates (J) and a Backup Coordinates column (K).
I’m using a script to create a custom formula to calculate the coordinates from the address using Google localization services. In my Coordinates column (J) I have an array formula like this
=ARRAYFORMULA(IF(LEN(K2:K) > 0,K2:K,GEOCODE_GOOGLE(I2:I)))
So, if K has a value then I copy that value to my column else I use the GEOCODE custom formula. My problem is that I’m gettis this error:
Service Invoked too many times for one day: geocode. (line 5)
I know there are limits for the use of this service but my app only have like 20 or 30 new rows per day, so I think maybe the problem is in my arrayformula?, my sheet have 200 rows right now, so maybe the formula is executing 200 times every time a new row is inserted?
Please any help, thank you
Hola @AlfredoZGC
The daily limit is 1000 queries. You say that your sheet has 200 rows but how many cells are using your geolocation fórmula?
I saw that phenomenon in my tests, when I moved, deleted or added a new row or column, all cells using a geolocation formula were fired and tried updating their values consuming the daily limit set by Google very fast.
Suerte con esto, saludos
Gavp
The sheets are probably running the script on every line whenever the sheet data changes, regardless of it needs it or not. I would add a second parameter into your script containing the coordinates (K2:K). Then add an IF statement in the script to leave the function or skip the geocoding code is the second parameter is filled.
Hola @gvalero, Yesterday my sheet had 200 rows, now it has 235 rows. If the limit is 1000 there should be no problem. I have an entire column using this formula with an array formula but my array formula validates if the cell is filled it will not use the geolocation formula or at least is what I want to do.
=ARRAYFORMULA(IF(LEN(K2:K) > 0,K2:K,GEOCODE_GOOGLE(I2:I)))
Gracias por la ayuda!
@Jeff_Hager You gave me a very good idea, I will modify my script to do this, I hope this solves my problem. Thank you very much!
1 Like
Can you share the script.?
I have a customer which I’m developing app to and I need exactly this solution as glide maps don’t recognize addresses in IL.
Thanks!
@yinon_raviv Of course, here it is:
function GEOCODE_GOOGLE(address,bkp) {
if (bkp.trim() == “”) {
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
}
}
}
}
Then in my Sheet I call the function like this GEOCODE_GOOGLE(parameter)
Thanks. Will look I to and will come back with questions which I’m sure I’ll have 
No problem. I have modifed the previous post with my new script, this will possible solve my problem. I have to wait one day to use the function again so I don’t know if it works yet.
1 Like
Jeje @AlfredoZGC… Google te castigó 
But you weren’t the first and won’t be the last one!!
My question is: how many cells does each row have using your geolocation formula?
@gvalero jeje así es, castigado 
each of my 235 rows have only one cell using this geolocation formula
1 Like
@AlfredoZGC
are you looking for with your formula the best closest places to your user or only the closest one?
I think your problem is the 1st case!
The code is trying to find many places in each execution (maybe it finds 3-4?) and the other 235 rows (cells) do the same causing the Google blocking
Saludos
@gvalero Actually what I’m trying to do is, every time a new person fill his information in my form this persons writes his address, so I’m trying to get this address location to show this person on a map.
I’ll review this code.
Thank you!
The For loop has a return within it, so it should be leaving the function after reading the first result. In any case it’s already done querying the geocodeder function at that point. I would think that google is counting queries and not the number of returned results.
I really think the problem was the function being called for all rows whether or not it was needed. The IF statement he added exit the function of there are already coordinates should prevent this going forward because the code won’t make it to the geocoder call.
@Jeff_Hager my new script is this:
function GEOCODE_GOOGLE(address,bkp) {
if (bkp.trim() == “”) {
if (address.map) {
return address.map(GEOCODE_GOOGLE)
} else {
var r = Maps.newGeocoder().geocode(address)
var res = r.results[0];
if (res) {
return res.geometry.location.lat + ", " + res.geometry.location.lng
}
}
}
}
It works fine when using it like this:
GEOCODE_GOOGLE(A2,B2)
but if I use it inside an array formula it doesn’t work, it gives me an error:
TypeError: bkp.trim is not a function (line 2)
I’m trying to chane my script, if I remove the trim part the error disapers but I’m not getting any result
This is my final script, I hope it works, so far no problems but is too soon to be sure if it is the solution. I’m posting it here if anybody needs it. It appears that the parameter address is an array of all the address of my column of addresses (because in using the geocode formula inside an array formula), so every time i call this fuction it locates all the addresses, I do not know how to pass two parameters to my function (2 ranges) because I’m using array formula so I had to add a column to my sheet, if my backup coordinates column is filled I add the word …DoNotLocate… to the address, in that case the geocode does not locate that address
function GEOCODE_GOOGLE(address) {
if (address.map) {
return address.map(GEOCODE_GOOGLE)
} else {
var n = address.search("…DoNotLocate…");
if (n == -1) {
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
}
}
}
}
2 Likes
Yeah, I was trying to figure out the script with 2 parameters too, but I wasn’t having any luck either. Hopefully that works out for you.
@AlfredoZGC
As plan C, you could use this fantastic Add-On to do what you want to do, it’s a little late but I just remembered what I used time ago to your case.
It’s a GeoCode add-on developed by Awesome Table guys and it’s free so far (I think). Here the instructions https://support.awesome-table.com/hc/en-us/articles/360000141365-Walkthrough-Automatically-Geocode-Form-Entries
Saludos a todos
1 Like
Yes I didn’t figure out the 2 parameters also. So far my “solution” is working, we’ll see. Thanks a lot for your help!
1 Like
That Plan C looks very good! I will definitely check it out tomorrow morning. Thanks a lot for your help!
