1

Here's what I want to do: I have a Google Sheet with a column that has computed values with formulas (I don't manually update the values in this column), and I want to trigger a script to run when one of those values changes. I quickly learned that onEdit() would not work well for this, since that only triggers when you manually change a value in the UI.

I set up my onChange() script to only run like this, and it works as expected when you manually update values in that column:

sheet = SpreadsheetApp.getActiveSpreadsheet();
sheet1 = sheet.getSheetByName("Sheet1");
sheet2 = sheet.getSheetByName("Sheet2");
lastRow = sheet2.getLastRow();

function setUpTrigger() {
  ScriptApp.newTrigger('logChanges').forSpreadsheet("1WxQxpJxAvEtBWo5a1eD1zuX9HMS6LVCC7SGZzzCkvMk").onChange().create();
}

function logChanges(e) {

  if(e.source.getActiveSheet().getActiveRange().getColumn() != 2.0 || e.source.getSheetName() != "Sheet1") {
    return;
  } else {
    sheet2.getRange(lastRow +1, 1, 1, 4).setValues([
    [ e.changeType, e.source.getSheetName(),e.source.getActiveSheet().getActiveRange().getA1Notation(), new Date()]
    ]);
    return;
  }
}

However, if one of the values in that column is changed based on some other change in the sheet (all of those columns are just formulas), the onChange() function is not triggered. My guess is that using "e" as a reference in the formula makes onChange() behave like onEdit()? Am I wrong about that? Is there a workaround here?

Also open to other ideas if you have thoughts on how to accomplish my original goal a different way!! Thanks

0 Answers0