Service Invoked too many times for one day: geocode ARRAY FORMULA

0
votes

I hope somebody can help me I have this problem in Google sheets. 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 sheet 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

1
google-sheetslocalizationgeocode
Could you share a sanitized copy of the spreadsheet you are working on? – Iamblichus
Sadly I can't share my spreadsheet beause is from a Client. But is very simple. I have 3 columns I,J,K, column I (Address), Column J (Coordinates), column K (Backup Coordinates), I use GEOCODE to get coordinates from column I, I do this using an Array Formula like this: =ARRAYFORMULA(IF(LEN(K2:K) > 0,K2:K,GEOCODE_GOOGLE(I2:I))) So If I have data con the column K i don't use the GEOCODE – Alfredo Zea García-Calderón
Well, could you at least share the code related to the custom formula? (GEOCODE_GOOGLE). That's where the issue is happening. – Iamblichus
I appreciate your help. I already have found a solution to my problem – Alfredo Zea García-Calderón

1 Answers

1
votes

If anybody needs the solution, I've found one. I was using this custom formula inside an array formula so on every new row on my sheet the formula was calculating coordinates for all the rows, So I had to found a way to validate if a row was already calculated. I have added a text to my address column. For example if the address is "123 Mapple Street" now it says "123 Mapple Street..DoNotLocate.." if it has already been located so my script won't geolocate it.

Here is my script with the validation:

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