Geolocalization

Hola @AlfredoZGC

Did you fix your problem with your new script?

I modified mine and have almost the same code you have but I am still having the same problems with the daily queries limit (1000). Yesterday after some tests, Google punished me again!!

@ThinhDinh btw… have you ever faced this uncomfortable problem with your APP?

Gracias!!

I have used my script on a very small amount of queries daily due to it being only test cases. My friend used my script and was blocked after a while as well.

1 Like

Hola @gvalero

My final script is this

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().setRegion('pe').geocode(address)
          if (r.results.length != 0) {
        	   var res = r.results[0]
         	   return res.geometry.location.lat + ", " + res.geometry.location.lng      	    
          } else {
              return "ERROR"
          }
       }
    }  
}

I have two columns K “Coordinates” and L “BKPCoordinates” and I use this formula on column K

=ARRAYFORMULA(IF(LEN(L2:L) > 0,L2:L,GEOCODE_GOOGLE(J2:J)))

So, if “BKPCoordinates” is empty i use my GEOCODE script if not I Copy the value of “BKPCoordinates” to “Coordinates”.

Then I have a J “BKPAddress” column that uses this formula,

=ARRAYFORMULA(IF(LEN(L2:L) > 0,I2:I&"..DoNotLocate..",I2:I))

If “BKPCoordinates” has data I concatenate “…DoNotLocate…” to my original “Address” column and this is the column that I use in my GEOCODE script. So if the script detects …DoNotLocate… it will do nothing.

In addition to all this I have a second script that runs every minute and reads my “Coordinates” column and copies its value to “BKPCoordinates” doing this I make sure “BKPCoordinates” is always updated so “BKPAddress” always has …DoNotLocate… in all the rows except for new registers.

My second script is this

function onEdit() {
  
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName("FORM registro para recibir");
var criteria = sheet.getRange("FORM registro para recibir!K2:K").getValues();
var data = sheet.getRange("FORM registro para recibir!L2:L").getValues();
var outData = [];

for (var i in data) {

  if (data[i] == criteria[i]) {

    outData.push(criteria[i])

  }

  else if (data[i] == '') {

    outData.push(criteria[i])

  }

  else {

    outData.push(data[i])

  }

}

  sheet.getRange(2,12,outData.length).setValues(outData);
}

I know is pretty tangled, please let me know if you need more explanation

Since I did this changes I’ve never had the 1000 coordinates per day problem again

2 Likes

I got it @Alfredo, let me try simplifying that logic using a 2nd column as a flag to avoid the chaos :thinking:.

Gracias por el consejo

2 Likes

That was my first attempt, to send a second column (flag) to my first script but I never managed to get it work, if it works for you please let me know!

De nada!!

1 Like