Google-sheets – Using Google Apps Script in with arrayformula

google sheetsgoogle-apps-scriptgoogle-apps-script-triggersgoogle-sheets-arrayformula

I'm using an array formula in Google Sheets to determine when a cell shows 'Expired' based on date.

When the cell shows Expired, I've a script which should transfer rows with Expired to another sheet.

The script works to transfer when I manually enter Expired into the relevant column, but doesn't recognise when the arrayformula inputs this.

Wondering if there are any solutions to this?

Script below:

function onEdit(event) {

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s = event.source.getActiveSheet();
  var r = event.source.getActiveRange();

  if (s.getName() == "Open" && r.getColumn() == 5 && r.getValue() == "Expired") {
    var row = r.getRow();
    var numColumns = s.getLastColumn();
    var targetSheet = ss.getSheetByName("Expired");
    var target = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);
    s.getRange(row, 1, 1, numColumns).moveTo(target);
    s.deleteRow(row);
  }
}

Best Answer

onEdit / onChange triggers aren't triggered by formula result changes, they are only triggered when a user edits the spreadsheet by using the Google Sheets UI.

The solution is instead of monitoring for formula results changes look for the cells being edited by the user whom will make the formula results change.

Related Topic