# Geolocalization

**URL:** <https://community.glideapps.com/t/geolocalization/7541>\
**Category:** Ask for Help\
**Created:** [April 21, 2020, 7:16pm UTC](https://community.glideapps.com/t/geolocalization/7541 "2020-04-21T19:16:06Z")\
**Posts on this page:** 20\
**Page:** 1

<div class="post-metadata">

**Author:** ![AlfredoZGC](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/alfredozgc/32/4958_2.png) [@AlfredoZGC](https://community.glideapps.com/u/AlfredoZGC)\
**Post date:** [April 21, 2020, 7:16pm UTC](https://community.glideapps.com/t/geolocalization/7541/1 "2020-04-21T19:16:06Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![gvalero](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/gvalero/32/1037_2.png) [@gvalero](https://community.glideapps.com/u/gvalero)\
**Post date:** [April 22, 2020, 12:49am UTC](https://community.glideapps.com/t/geolocalization/7541/2 "2020-04-22T00:49:00Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Jeff\_Hager](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/jeff_hager/32/43_2.png) [@Jeff\_Hager](https://community.glideapps.com/u/Jeff_Hager)\
**Post date:** [April 22, 2020, 3:28am UTC](https://community.glideapps.com/t/geolocalization/7541/3 "2020-04-22T03:28:11Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![AlfredoZGC](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/alfredozgc/32/4958_2.png) [@AlfredoZGC](https://community.glideapps.com/u/AlfredoZGC)\
**Post date:** [April 22, 2020, 3:33pm UTC](https://community.glideapps.com/t/geolocalization/7541/4 "2020-04-22T15:33:15Z")

</div>

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!

---

<div class="post-metadata">

**Author:** ![AlfredoZGC](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/alfredozgc/32/4958_2.png) [@AlfredoZGC](https://community.glideapps.com/u/AlfredoZGC)\
**Post date:** [April 22, 2020, 3:34pm UTC](https://community.glideapps.com/t/geolocalization/7541/5 "2020-04-22T15:34:41Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![yinon\_raviv](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/yinon_raviv/32/1627_2.png) [@yinon\_raviv](https://community.glideapps.com/u/yinon_raviv)\
**Post date:** [April 22, 2020, 3:55pm UTC](https://community.glideapps.com/t/geolocalization/7541/6 "2020-04-22T15:55:33Z")

</div>

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!

---

<div class="post-metadata">

**Author:** ![AlfredoZGC](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/alfredozgc/32/4958_2.png) [@AlfredoZGC](https://community.glideapps.com/u/AlfredoZGC)\
**Post date:** [April 22, 2020, 4:10pm UTC](https://community.glideapps.com/t/geolocalization/7541/7 "2020-04-22T16:10:56Z")

</div>

@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)

---

<div class="post-metadata">

**Author:** ![yinon\_raviv](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/yinon_raviv/32/1627_2.png) [@yinon\_raviv](https://community.glideapps.com/u/yinon_raviv)\
**Post date:** [April 22, 2020, 4:53pm UTC](https://community.glideapps.com/t/geolocalization/7541/8 "2020-04-22T16:53:54Z")

</div>

Thanks. Will look I to and will come back with questions which I’m sure I’ll have 😉

---

<div class="post-metadata">

**Author:** ![AlfredoZGC](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/alfredozgc/32/4958_2.png) [@AlfredoZGC](https://community.glideapps.com/u/AlfredoZGC)\
**Post date:** [April 22, 2020, 5:02pm UTC](https://community.glideapps.com/t/geolocalization/7541/9 "2020-04-22T17:02:09Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![gvalero](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/gvalero/32/1037_2.png) [@gvalero](https://community.glideapps.com/u/gvalero)\
**Post date:** [April 22, 2020, 5:23pm UTC](https://community.glideapps.com/t/geolocalization/7541/10 "2020-04-22T17:23:23Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![AlfredoZGC](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/alfredozgc/32/4958_2.png) [@AlfredoZGC](https://community.glideapps.com/u/AlfredoZGC)\
**Post date:** [April 22, 2020, 5:29pm UTC](https://community.glideapps.com/t/geolocalization/7541/11 "2020-04-22T17:29:12Z")

</div>

@gvalero jeje así es, castigado ☹

each of my 235 rows have only one cell using this geolocation formula

---

<div class="post-metadata">

**Author:** ![gvalero](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/gvalero/32/1037_2.png) [@gvalero](https://community.glideapps.com/u/gvalero)\
**Post date:** [April 22, 2020, 5:50pm UTC](https://community.glideapps.com/t/geolocalization/7541/12 "2020-04-22T17:50:51Z")

</div>

@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

![image](https://us1.discourse-cdn.com/flex002/uploads/glideapps/original/2X/2/27790b0e1ff5cb404ca252fbca19c1bd241e9a26.png)

Saludos

---

<div class="post-metadata">

**Author:** ![AlfredoZGC](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/alfredozgc/32/4958_2.png) [@AlfredoZGC](https://community.glideapps.com/u/AlfredoZGC)\
**Post date:** [April 22, 2020, 7:39pm UTC](https://community.glideapps.com/t/geolocalization/7541/13 "2020-04-22T19:39:08Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Jeff\_Hager](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/jeff_hager/32/43_2.png) [@Jeff\_Hager](https://community.glideapps.com/u/Jeff_Hager)\
**Post date:** [April 22, 2020, 7:53pm UTC](https://community.glideapps.com/t/geolocalization/7541/14 "2020-04-22T19:53:42Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![AlfredoZGC](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/alfredozgc/32/4958_2.png) [@AlfredoZGC](https://community.glideapps.com/u/AlfredoZGC)\
**Post date:** [April 22, 2020, 8:00pm UTC](https://community.glideapps.com/t/geolocalization/7541/15 "2020-04-22T20:00:51Z")

</div>

@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

---

<div class="post-metadata">

**Author:** ![AlfredoZGC](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/alfredozgc/32/4958_2.png) [@AlfredoZGC](https://community.glideapps.com/u/AlfredoZGC)\
**Post date:** [April 22, 2020, 11:41pm UTC](https://community.glideapps.com/t/geolocalization/7541/16 "2020-04-22T23:41:24Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Jeff\_Hager](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/jeff_hager/32/43_2.png) [@Jeff\_Hager](https://community.glideapps.com/u/Jeff_Hager)\
**Post date:** [April 23, 2020, 12:01am UTC](https://community.glideapps.com/t/geolocalization/7541/17 "2020-04-23T00:01:18Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![gvalero](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/gvalero/32/1037_2.png) [@gvalero](https://community.glideapps.com/u/gvalero)\
**Post date:** [April 23, 2020, 12:33am UTC](https://community.glideapps.com/t/geolocalization/7541/18 "2020-04-23T00:33:45Z")

</div>

@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](https://support.awesome-table.com/hc/en-us/articles/360000141365-Walkthrough-Automatically-Geocode-Form-Entries)

 ![image](https://us1.discourse-cdn.com/flex002/uploads/glideapps/original/2X/9/9452c24ba2ab7e6f25d924feb50351664788686d.png)

Saludos a todos

---

<div class="post-metadata">

**Author:** ![AlfredoZGC](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/alfredozgc/32/4958_2.png) [@AlfredoZGC](https://community.glideapps.com/u/AlfredoZGC)\
**Post date:** [April 23, 2020, 1:25am UTC](https://community.glideapps.com/t/geolocalization/7541/19 "2020-04-23T01:25:12Z")

</div>

> [@Jeff\_Hager](#):
>
> 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.

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!

---

<div class="post-metadata">

**Author:** ![AlfredoZGC](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/alfredozgc/32/4958_2.png) [@AlfredoZGC](https://community.glideapps.com/u/AlfredoZGC)\
**Post date:** [April 23, 2020, 1:26am UTC](https://community.glideapps.com/t/geolocalization/7541/20 "2020-04-23T01:26:55Z")

</div>

> [@gvalero](#):
>
> @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](https://support.awesome-table.com/hc/en-us/articles/360000141365-Walkthrough-Automatically-Geocode-Form-Entries)
> 
> ![image](https://us1.discourse-cdn.com/flex002/uploads/glideapps/original/2X/9/9452c24ba2ab7e6f25d924feb50351664788686d.png)
> 
> Saludos a todos

That Plan C looks very good! I will definitely check it out tomorrow morning. Thanks a lot for your help!

[Next page](https://community.glideapps.com/t/geolocalization/7541.md?page=2)