google-sheetsgoogle-apps-scripttriggers

onChange Script keeps returning to certain sheet


Every time I make edits to any of the 5 other sheets, as soon as I finish editing a cell, I am returned to sheet name "HOLDING". I would like this to stop happening.

function onChange(e) {

  DeleteRow(e);
}

function DeleteRow(e) {
  // This Code will delete any "Y" in Column E.

  var sheet = SpreadsheetApp.getActive();
  sheet.setActiveSheet(sheet.getSheetByName('HOLDING'), true);
  var rows = sheet.getDataRange();
  var numRows = rows.getNumRows();
  var values = rows.getValues();
  var rowsDeleted = 0;
  for (var i = 0; i <= numRows - 1; i++) {
    var row = values[i];
    if (row[4] == "Y") {
      sheet.deleteRow((parseInt(i) + 1) - rowsDeleted);
      rowsDeleted++;
    }
  }
}

Solution

  • The call to setActiveSheet() is what's causing the active sheet to change.

    Try replacing the first couple lines of DeleteRow() with an approach that will allow you to interact with the HOLDING sheet without making it active:

    function DeleteRow(e) {
      var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('HOLDING');
    
      <...continue with the rest of the original implementation...>