2

I use the custom function:

 if (!address) {
   throw new Error("No address specified!");
 }
 if (address.map) {
   return address.map(LATLONG);
 }
 const key = ["address", address].join(",");
 const value = getCache(key);
 if (value !== null) return value;

 const { results: [data = null] = [] } = Maps.newGeocoder().geocode(address);
 if (data === null) {
   throw new Error("Address not found!");
 }
 const { formatted_address } = data;
 setCache(key, formatted_address);
 return formatted_address;
};

After apply the function =GOOGLEMAPS_ADDRESS(B59) in the whole column with 2000 more line, the gs server down, with the Error Exception: Service invoked too many times for one day: geocode. (line 71). It is happens not because the address not right, it is happens because the request occur after each moving of rows, deleting or adding too. On this pic shows that some cells get value, but mainly goes to the ERROR. https://i.stack.imgur.com/YSoRQ.png

How it is possible to store the obtained value in the active cell and delete the custom function from active cell after value will store there?

AlexOn
  • 21
  • 4
  • Look at [this](https://stackoverflow.com/questions/41470693/service-invoked-too-many-times-for-one-day-geocode-line-24) please – Muhammet Yunus Jul 18 '21 at 13:25
  • Please provide access to your spreadsheet (remove confidential data from it). – Boris Baublys Jul 18 '21 at 14:45
  • Sure, please try this out, this is simplified version: https://docs.google.com/spreadsheets/d/1HRTLZ13JFD0Z5J7U1-6Xm677Sc2hh11tgepRdSew8ik/edit?usp=sharing – AlexOn Jul 18 '21 at 19:46
  • @Muhammet Yunus Tunca, there is good subjects reviewed but this is not exactly my case. – AlexOn Jul 18 '21 at 19:49

1 Answers1

2

Use a trigger instead of formulas:

function onEdit(e) {
  e.range.offset(0,1).setValue(GOOGLEMAPS_ADDRESS(e.value));
}
Boris Baublys
  • 952
  • 10
  • 22