Google Apps脚本 - 使onEdit（）识别setValue（）更改 [英] Google Apps Script - Make onEdit() recognize setValue() changes

问题描述

我有一个包含一些功能的电子表格。其中之一是onEdit（事件）功能，可根据条件将一些值复制到其他工作表。这是代码（简化，但重要的部分完好）：

  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（））; 
 
 
 $ / code> 

当我手动编辑细胞。但是，当用户按下侧栏中的按钮时，我有一个编辑单元的代码，这是代码：



 函数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）; //注意此行
} 
 else if（cellStatus.getValue（）==Aguardandosaída）{
 cellStatus.setValue（fora）; 
 var cellExitDate = cell.offset（0,6）; 
 cellExitDate.setValue（getDate（））; 
 
 
 
 
 
 
 $ b 
正如你所看到的，这个函数改变了单元格的内容与setValue（），但是，当我使用这个函数，单元格的值改变，但onEdit（）触发器不起作用。
 
 
 
我该如何onEdit（）触发器是否可以识别setValue（）所做的更改？
解决方案
你是对的。 onEdit（）仅在手动编辑范围时触发。正如此处所示，onEdit（）在值被更改时触发 用户 。 
 
 
 
我测试函数的功能是将值插入到我的onEdit响应并没有任何反应的列中。包括我能想到的各种其他技术。这里要做的最好的事情就是将其作为应用脚本的增强功能问题跟踪器。
 
 
 
然而，当脚本中的另一个功能改变工作表时，我编写了另一个函数来调用它。这些是我写的测试函数：
 
 pre $函数addValues 
 
 var ss = SpreadsheetApp。 getActiveSpreadsheet（）; 
 var sheet = ss.getSheetByName（Sheet1）; 
 var range = sheet.getDataRange（）; 
 
 var book =Book; 
 var cancel =取消; 
 
 var maxRow = range.getLastRow（）+ 1; （i = 0; i< 4; i ++）
 
如果（i％2 == 0）
 {
工作表，则为
 
。 getRange（maxRow，1）.setValue（book）; 
 autoChanges（maxRow）; 
} else {
 sheet.getRange（maxRow，1）.setValue（cancel）; 
 autoChanges（maxRow）; 
} 
 
 maxRow ++; 
 
 
 
 
 $ b 
 autoChanges函数：
 
 
 函数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--）; 
 
 
  
不是最优雅的解决方案，但这似乎是只有你想要做的解决方法。
 
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.

                        
这篇关于Google Apps脚本 - 使onEdit（）识别setValue（）更改的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！