google-sheetsgoogle-apps-scripttriggers

Make onEdit() recognize setValue() changes


I have a Spreadsheet with some functions. One of them is a onEdit(event) function that copies some values to other sheets based on conditions. This is the code (simplified but with the important parts intact):

function onEdit(event) {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var s = event.source.getActiveSheet();
    var r = event.range;
    if(s.getName() === "Lista" && r.getColumn() === 9 && r.getValue() === "Posicionada") {
      var sheetname = s.getRange(r.getRow(),3).getValue();
      var columnRef = s.getRange(r.getRow(),4).getValue();
      var row = s.getRange(r.getRow(),5).getValue();
      var targetSheet = ss.getSheetByName("Mapa " + sheetname);
      var headers = targetSheet.getRange(1, 1, 1, targetSheet.getLastColumn());
      for (var i = 0; i < headers; i++) {
        if (headers[i] === columnRef) {
          break;
        }
      }
      var column;
      if (columnRef === "A1") {
        column = 2;
      }
      else if (columnRef === "A2") {
        column = 3;
      }
      else if (columnRef === "B1") {
        column = 4;
      }
      else if (columnRef === "B2") {
        column = 5;
      } 
      if (sheetname === "N2") {
        row = row - 30;
      }
      if (sheetname === "N3") {        
        column = column - 10;
        row = row - 42;
      }
      targetSheet.getRange(row,column).setValue(s.getRange(r.getRow(), 1, 1, 1).getValue()); 
    }
}

The code works as it should when I manually edit the cell. But, I have a code that edit the cell when the user press a button in a sidebar, this is the code:

function positionMU(){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var cell = ss.getActiveCell().activate();
  var cellLevel = cell.offset(0,2);
  var cellLetter = cell.offset(0,3);
  var cellNumber = cell.offset(0,4);
  var cellStatus = cell.offset(0,8);
  var dbq = "Posicionada";
  var fora = "Pendente de recebimento";  
  if (cellStatus.getValue() == "Aguardando posicionamento"){
      cellStatus.setValue(dbq);    //attention in this line  
  }
  else if (cellStatus.getValue() == "Aguardando saída"){
      cellStatus.setValue(fora);
      var cellExitDate = cell.offset(0,6);
      cellExitDate.setValue(getDate());
  }
}

As you can see, this function change the cell content with setValue(), but, when I use this function, the value of the cell changes, but the onEdit() trigger doesn't work.

How can I make the onEdit() trigger recognize changes made with setValue()?


Solution

  • You are right. onEdit() only triggers if the range is edited manually. As can be seen here, onEdit() triggers when a value is changed by the user.

    I tested the function by making function to insert values into a column for which my onEdit responds and nothing happens. Including various other techniques that I could think of. Best thing to do here is to suggest this as an enhancement on App Script's Issue Tracker.

    However, I made it work by writing another function to be called when another function in the script makes changes to the sheet. These are the test functions I wrote:

    function addValues()
    {
      var ss = SpreadsheetApp.getActiveSpreadsheet();
      var sheet = ss.getSheetByName("Sheet1");
      var range = sheet.getDataRange();
    
      var book = "Book";
      var cancel = "Cancel";
    
      var maxRow = range.getLastRow()+1;
    
      for(var i=0; i<4; i++)
      {
        if (i%2 == 0)
        {
          sheet.getRange(maxRow, 1).setValue(book);
          autoChanges(maxRow);
        }else{
          sheet.getRange(maxRow, 1).setValue(cancel);
          autoChanges(maxRow);
        }
    
        maxRow++;
      }
    }
    

    autoChanges function:

    function autoChanges(row)
    {
      var ss = SpreadsheetApp.getActiveSpreadsheet();
      var sheet = ss.getSheetByName("Sheet1");
      var range = sheet.getDataRange();
      var data = range.getValues();
    
      var response = "";
    
      sheet.getRange(row, 2).protect();
    
      response = data[row-1][0];
    
      if (response == "Book")
      {
        sheet.getRange(row, 2).canEdit();
      }else{
        sheet.getRange(row, 2).setValue("--NA--");
      } 
    }
    

    Not the most elegant solution but this seems to be the only workaround for what you are trying to do.