当单元格值由于内置函数而变化时，onEdit（e）不生成触发事件 [英] onEdit(e) not generating trigger event when cell value changes due to inbuilt function

问题描述

下面的代码监视单元格值之间的变化（行1到行5，列1到列5），并在不同的工作表中跟踪和记录事件。
仅当在电子表格单元格中手动完成更改时才起作用。 （因为onEdit（e）函数仅跟踪不由任何其他函数手动编辑的单元格值更改）

Below code monitors cell value changes in between (Row 1 to Row 5, column 1 to column 5), and it tracks and log event in different sheet. Which is working only when changes are being done manually in spreadsheet cells. (because onEdit(e) function only tracks the cell value changes edited manually not by any other functions)

如果单元格值由于某些内置的数学函数而发生了更改（示例：B2 = C2 + D2，其中C2 / D2更改时B2的单元格值将自动更改），但是使用此代码，我无法看到事件触发了B2单元格的值。

If cell value changes due to some inbuilt mathematical functions (Example : B2 = C2+D2 where cell value of B2 will change automatically when C2 / D2 changes ) But with this code i can not see event getting triggered for value of B2 cell.

任何人都可以使用以下代码来帮助找到解决方案或解决方法。

Can anybody help to find solution or workaround with below code.

谢谢

代码：

function onEdit(e) {
  if (
    e.source.getSheetName() == "SheetA" &&
    e.range.columnStart >= 1 &&
    e.range.columnEnd <= 5 &&
    e.range.rowStart >= 1 &&
    e.range.rowEnd <= 5
      ) {
    //Logger.log("the cell is in range");
      var sheetsToWatch = ['SheetA'];
      var changelogSheetName = "Changelog";  
      var timestamp = new Date();
      var ss = SpreadsheetApp.getActiveSpreadsheet();
      var sheet = ss.getActiveSheet();
      var cell = sheet.getActiveCell();
      var sheetName = sheet.getName();

        // if it is the changelog sheet that is being edited, do not record the change
      if (sheetName == changelogSheetName) return;

        // if the sheet name does not appear in sheetsToWatch, do not record the change
      var matchFound = false;
      for (var i = 0; i < sheetsToWatch.length; i++) {
      if (sheetName.match(sheetsToWatch[i])) matchFound = true;
       }
      if (!matchFound) return;


      var columnLabel = sheet.getRange(/* row 1 */ 1, cell.getColumn()).getValue();
      var rowLabel = sheet.getRange(cell.getRow(), /* column A */ 1).getValue();

      var changelogSheet = ss.getSheetByName(changelogSheetName);
      if (!changelogSheet) {
      // no changelog sheet found, create it as the last sheet in the spreadsheet
      changelogSheet = ss.insertSheet(changelogSheetName, ss.getNumSheets());
      // Utilities.sleep(2000); // give time for the new sheet to render before going back
      // ss.setActiveSheet(sheet);
      changelogSheet.appendRow(["Timestamp", "Sheet name", "Cell address", "Column label", "Row label", "Value entered"]);
      changelogSheet.setFrozenRows(1);
       }
     changelogSheet.appendRow([timestamp, sheetName, cell.getA1Notation(), columnLabel, rowLabel, cell.getValue()]);
}
  }

推荐答案

有一种解决方法：

There is a workaround:

1. 创建两个单独的电子表格-电子表格编号1包含您的原始数据和公式，电子表格编号2包含您的脚本和一个空白 SheetA


2. 分配给 SheetA中的单元格 A1 Spreadsheet2 的公式 = IMPORTRANGE（IMPORTRANGE（spreadsheet_url，range_string），其中 spreadsheet_url 是 Spreadsheet 1 和 range_string 感兴趣的范围的URL （例如 SheetA！A1：E ）


3. 使用脚本属性，用于存储单元格值


4. 每次都通过将旧值与新值进行比较来查找修改后的单元格是兴趣表中的更改


5. 按如下所示修改脚本：

1. Create two separate spreadsheets - spreadsheet number 1 contains your original data and formula, spreadsheet number 2 contains your script and an empty SheetA
2. Assign to cell A1 in SheetA of Spreadsheet2 a formula =IMPORTRANGE(IMPORTRANGE(spreadsheet_url, range_string), whereby spreadsheet_url is the URL of Spreadsheet 1 and range_string the rang eof interest (e.g. "SheetA!A1:E")
3. Use Scriptproperties to store cell values
4. Find the modified cell by comparing old values against new values, each time there is a change in the sheet of interest
5. Modify your script as following:

var ss=SpreadsheetApp.getActive();
var sheetsToWatch = ['SheetA'];

function initialSetUp(){//run this function only once, unless your range of interest changes
  for (var k = 0; k < sheetsToWatch.length; k++) { 
    var sheet=ss.getSheetByName(sheetsToWatch[k]);
    var range=sheet.getRange(1,1,5,5); //change if required
    var values=range.getValues(); 
    for(var i=0;i<values.length;i++){
      for(var j=0;j<values[0].length;j++){
        PropertiesService.getScriptProperties().setProperty('values '+sheet.getSheetName()+i+"-"+j,values[i][j]);
      }
    }
  }
}

function Edit() {
  var sheet=ss.getActiveSheet();
  var sheetName = sheet.getName();
  var matchFound = false;
  for (var k = 0; k < sheetsToWatch.length; k++) {
    if (sheetName.match(sheetsToWatch[k])) 
      matchFound = true;
   }
  if (matchFound == true) {
    var range=sheet.getRange(1,1,5,5); //change if required
    var values=range.getValues();
    for(var i=0;i<values.length;i++){
      for(var j=0;j<values[0].length;j++){
        var scriptValue=PropertiesService.getScriptProperties().getProperty('values '+sheetName+i+"-"+j);
        var newValue=sheet.getRange(i+1,j+1).getValue();
        Logger.log(scriptValue);
        Logger.log(newValue);
        if(newValue!=scriptValue){ 
          var cell=sheet.getRange(i+1,j+1);
          var timestamp = new Date(); 
          var columnLabel = sheet.getRange(1, cell.getColumn()).getValue();
          var rowLabel = sheet.getRange(cell.getRow(), /* column A */ 1).getValue(); 
          var changelogSheetName = "Changelog"; 
          var changelogSheet = ss.getSheetByName(changelogSheetName); 
          if (!changelogSheet) { 
            changelogSheet = ss.insertSheet(changelogSheetName, ss.getNumSheets());
            //Utilities.sleep(2000); // give time for the new sheet to render before going back
            changelogSheet.appendRow(["Timestamp", "Sheet name", "Cell address", "Column label", "Row label", "Value entered"]);
            changelogSheet.setFrozenRows(1);
            } 
              changelogSheet.appendRow([timestamp, sheetName, cell.getA1Notation(), columnLabel, rowLabel, cell.getValue()]); 
              PropertiesService.getScriptProperties().setProperty('values '+i+"-"+j,newValue);
          }
        }
      }
    }     
}

1. 添加到新函数 Edit（）和可安装触发器 onChange 。

1. Add to the new function Edit() an installable trigger onChange.

EXPLANATION：

EXPLANATION:

• onEdit触发器无法检测到更改由公式触发的值中


• onChange无法检测到由单元格公式引起的更改，但可以检测由IMPORTRANGE触发的更改

这篇关于当单元格值由于内置函数而变化时，onEdit（e）不生成触发事件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！