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 == "") {
} else {
var geocoder = Maps.newGeocoder().geocode(address);  
var res = geocoder.results[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.

1 Answer 1


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?


  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
  • Sorry Im new to scripts and writing code. I familiarized myself with some stuff using google and this website. I tried googling the steps to your solution and think the scripts for this is out of my range.
    – John K
    Dec 31, 2020 at 3:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.