Hi. I appreciate this may be a Google Apps script problem rather than an appsheet one but I think there's some crossover.
I'm trying to use a google apps script to geocode from an appsheet address string to return a Latlong value that I can use in Appsheet to calculate DISTANCE().
The script works. I added it to the googlesheet and watched it populate... and then after a while it doesn't.
The spreadsheet then returns the ERROR# "Exception: Service invoked too many times for one day: premium geocode. (line 11)."
Clearly the script is being invoked repeatedly but due to my lack of knowledge of the use of scripts I don't know what is repeatedly triggering it. Is appsheet triggering the spreadsheet formula (and therefore the script) on each line every time there is an app sync? Or is is there some setting I can change for the script to get it to trigger only on a change. I've tried adjusting the trigger in Apps Script but I don't find it very clear how or if this is working. Sorry, loads of script-newbie issues.
Here's the script:
function GEOCODE_GOOGLE(address) {
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
}
}
}
Essentially I want it to run on the existing address string column which has <1000 entries which are concatenated from part-address columns. I also need it to run each time a new line is added via a google form. Then it shouldn't need to run again on each entry as the address data doesn't get changed.
Thanks in advance and sorry if this isn't an appsheet issue at all.
LinkedIn
Twitter
