Formula-based cell changes in Google Sheets is not firing onEdit script

Question

I'm completely new to Google script writing, but I've used various posts here to piece together what I need: something that will add a time stamp to a row when a certain column changes. Here's what I'm currently using:

function onEdit() {
  var s = SpreadsheetApp.getActiveSheet();
  if( s.getName() == "test" ) { //checks that we're on the correct sheet
    var r = s.getActiveCell();
    if( r.getColumn() == 16 ) { //checks the column
      var nextCell = r.offset(0, 1);
      if( nextCell.getValue() === '' ) //is empty?
        nextCell.setValue(new Date());
    }
  }
}

This works perfectly when I manually change the data; however, the column that the script is monitoring pulls data from another sheet and this fails to fire the trigger/script. How can I get around this so that cells with formulas (that reference other sheets) will still fire my script?

Any help is greatly appreciated. Thanks!

Answer 1

The onEdit trigger works only when an actual user edits the spreadsheet. Depends of your use case, but you can be use a Time-driven trigger and set it in a recurring interval and with a function monitor the column for changes, here's an example:

function monitorColumn() {
  // Add the trigger from the Resources menu in the Script Editor
  // Script Editor > Resources > Current oroject's triggers > Add trigger
  // [monitorColumn] - [Time-driven] - [Hour timer] - [Every hour]

  var s = SpreadsheetApp.getActiveSpreadsheet();
  var ss = s.getSheetByName("test"); // Get the sheet by name

  // Get the values of the columns P & Q with getRange(row, column, numRows, numColumns)
  var columnValues = ss.getRange(1, 16, ss.getLastRow(), 2).getValues();

  // Loop through the values
  for (var i = 0; i < columnValues.length; i++) {

    // If cell in column P is empty and cell in column Q is not empty set todays date
    if(columnValues[i][0] != "" && columnValues[i][1] == ""){

      // While a range index starts at 1, 1, the JavaScript array will be indexed from [0][0].
      ss.getRange(i+1,17).setValue(new Date());
    }
  }
}