Tag: shouldnt

I have a google spreadsheet that I used an importrange function to get addresses from another sheet. Then I added this into the script editor to convert those addresses to coordinates using a custom function:

 * 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

        }

    }

}

And when I put into the cell the function =geocode_google(L3), where L3 is the full address joined together from a function using the street number and street name from importranage, it spits out the coordinates in one cell that I then use another function to split into 2 separate columns. And then I autofilled that formula to the next approximately 350 addresses. The problem is that when I first did it it worked great but now its saying the service is being invoked too many times in a day for every single cell I have that custom function in except maybe 3 or 4 cells. I know there’s a limit of 1000 per day times I can use the google geocoder service but the original spreadsheet only changes maybe 1 address every 3 days and I don’t see why all the cells from the beginning of the sheets containing the custom function would come up as being invoked too many times.

Is it because the importrange fucntion refreshes every 30 minutes even though the values of the cells doesn’t change and that triggers the custom function which causes it to run every 30 minutes and hit that 1000 max per day?

Is there anyway to limit the custom function to run only when the value of the full address cell changes or any other work around?

I also tried to use this code as well:

function myFunction() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Geo 4')

var range = sheet.getDataRange();
var cells = range.getValues();

var latitudes = ();
var longitudes = ();

for (var i = 0; i < cells.length; i++) {
var address = cells(i)(11);
 
if (address == "") {
latitudes.push((""));
longitudes.push((""));
} else {
var geocoder = Maps.newGeocoder().geocode(address);  
 
 
var geocoder = Maps.newGeocoder().geocode(address);
 
var res = geocoder.results(0);
if(res){
latitudes.push((res.geometry.location.lat));
longitudes.push((res.geometry.location.lng));
}
else
{
latitudes.push((0));
longitudes.push((0));
}
}

sheet.getRange('M1').offset(0, 0, latitudes.length).setValues(latitudes)
sheet.getRange('N1').offset(0, 0, latitudes.length).setValues(longitudes);
}
}  

But it wasn’t recognizing some of the addresses so I don’t run it anymore and I prefer the custom function because I can see if there’s a specific address not being found instead of the code just not running .

Is there any other workaround to just import addresses and get the latitude and longitude as the addresses change? Thank you!