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('');
}
})
}