Google-sheets – How to to get coordinates from addresses coming in from importrange

geocodinggoogle sheetsgoogle-apps-script-triggersimportrange

I have addresses coming in from importrange and I am using this script to get coordinates.

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 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('O1').offset(0, 0, latitudes.length).setValues(latitudes)
sheet.getRange('P1').offset(0, 0, latitudes.length).setValues(longitudes);
}
}

The issue is that I have this running on a trigger every 6 hours but I need something that would get coordinates as soon as one address changes without running the whole column and using the 1000 geocodes you get per day. I have about 350 addresses constantly but maybe three change a day.

Best Answer

A spreadsheet recalculation, like the one that makes IMPORTRANGE results be updated, usually will not activate a trigger, only changes made by the user using the Google Sheets UI or changes to the spreadsheet structure like inserting a row. How about the following algorithm?

Preparation

  1. Make a copy of the results of IMPORTRANGE

    This could be done manually or by using a another script function.

On every time-driven trigger execution

  1. Read the current results of IMPORTRANGE from the spreadsheet and the values from the copy of the results of IMPORTRANGE

  2. Using a loop

    1. Compare one by one the current results of IMPORTRANGE with the copy of the results of IMPORTRANGE
    2. For each result that have changed, get the coordinates
    3. For each result that have not changed do nothing

  3. Update the spreadsheet

    1. Replace the old with a copy of the results of IMPORTRANGE
    2. Replaces the old coordinates with the new coordinates
Related Topic