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