Google Apps Script - Make onEdit() recognize setValue() changes

1
votes

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()?

2
google-apps-scriptgoogle-sheets

2 Answers

2
votes

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.

1
votes

There are some very good reasons why calling range.setValue() doesn't trigger the onEdit event, and most of them have to do with infinite recursion. In fact, you call setValue() yourself WITHIN onEdit(). This would trigger a recursive call, and from what I can see, you have no provision for handling the base case and thus your code would explode if setValue() did what you want.

Why not simply take all of your code out of your event handler, and put it into another function:

function onEdit (e) {
  return handleEdits(e.range);
}

function handleEdits(r) {
  s = r.getSheet();
  ss = s.getParent();
  //the rest of your code should drop right in.
}

then, inside your autoChanges function, go ahead and call handleEdits, passing it an appropriate range, after your call to setValue().