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?
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.
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
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
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!