Latitude and longitude values for given address using the Google Maps Geocoder

Hi all,

What is wrong with this script? or maybe there is a better way to do this

/**
 
* Returns latitude and longitude values for given address using the Google Maps Geocoder.
 
*
 
* @param {string} address - The address you get the latitude and longitude for.
 
* @customfunction
 
*/
 
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
 
   }
 
 }
 
}
 
 
/**
* Function to make GEOCODE_GOOGLE arrayFormula without loosing performance
*/
function onEdit(e) {
 
 //Grab sheet data
 const ss = SpreadsheetApp.getActiveSpreadsheet();
 const sheet = ss.getSheetByName('קריאות');
 // Take the last email row from Data validation tab
 let lr = ss.getSheetByName('הגדרות').getRange('C2').getValue();
 let cordiantesCol = ss.getSheetByName('הגדרות').getRange('D2').getValue();
 let finalAddressCol = ss.getSheetByName('הגדרות').getRange('E2').getValue();
 let data = sheet.getRange(2, 1, lr - 1, cordiantesCol).getValues();
 //Loop over the data to get the geocode of each address
 data.forEach((row,i) => {
   let address = row[finalAddressCol-1];
   let cordinates = row[cordiantesCol-1];
   /*
   if(range.columnEnd == 11 || columnStart == 11){
     sheet.getRange(range.rowStart,11,range.rowEnd,1)
   }
   */
   SpreadsheetApp.flush();
 
   if (address !== '' && cordinates == '') {
     //Call GEOCODE_GOOGLE function to get the geocode
     let geoCode = GEOCODE_GOOGLE(address);
     //Print the geocode on the right column
     sheet.getRange(i+2,cordiantesCol).setValue(geoCode);
   }
   if(address == '' && cordinates !== ''){
     //If the address is empty, delete the value of the cordinate
     sheet.getRange(i+2,cordiantesCol).setValue('');
   }
 })
 
 
 
}

Probably nothing wrong with the script, but it’s most likely running for every row after every data change. You can only call the geocode function a certain amount times within a day, unless you pay google extra. The email is telling you that you called the function too many times for the day. It would be better to restructure your script to check if you have already geocoded that row, and then only run the geocode if it hasn’t been done yet. The script will still run, but you can have it immediately leave if you already have coordinates, or if you maybe have a timestamp or something to indicate that the row was recently updated. There are a few threads in the forum that discuss how to modify the script so you don’t exceed your geocode limit.

Hola,

We have walked on this sidewalk before and I think it can helps you Automatic geocoding using Google Scripts

3 Likes