onEdit(e) не генерирует триггерное событие, когда значение ячейки изменяется из-за встроенной функции

Question

Код ниже отслеживает изменения значений ячеек между ними (от строки 1 до строки 5, от столбца 1 до столбца 5), а также отслеживает и регистрирует событие на другом листе. Это работает только тогда, когда изменения вносятся вручную в ячейки электронной таблицы. (поскольку функция onEdit(e) отслеживает только изменения значения ячейки, редактируемые вручную, а не другими функциями)

Если значение ячейки изменяется из-за некоторых встроенных математических функций (пример: B2 = C2+D2, где значение ячейки B2 будет автоматически изменяться при изменении C2 / D2) Но с этим кодом я не вижу, чтобы событие запускалось для значения ячейки B2.

Может ли кто-нибудь помочь найти решение или обходной путь с помощью кода ниже.

Спасибо

Код:

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()]);
}
  }

Answer 1

Есть обходной путь:

1. Создайте две отдельные таблицы: таблица № 1 содержит исходные данные и формулу, таблица № 2 содержит ваш сценарий и пустой SheetA
2. Назначить ячейке A1 в SheetA из Spreadsheet2 формула =IMPORTRANGE(IMPORTRANGE(spreadsheet_url, range_string), согласно которому spreadsheet_url это URL-адрес Spreadsheet 1 а также range_string диапазон интересов (например, "SheetA!A1:E")
3. Используйте свойства скрипта для хранения значений ячеек
4. Найдите измененную ячейку, сравнивая старые значения с новыми значениями каждый раз при изменении интересующего листа.
5. Измените свой сценарий следующим образом:

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);
          }
        }
      }
    }     
}

6. Добавить в новую функцию Edit() устанавливаемая триггер onChange.

ПОЯСНЕНИЕ:

• Триггер onEdit не может обнаружить изменения значений, вызванные формулой
• onChange не может обнаруживать изменения, вызванные формулами ячеек, но может обнаруживать изменения, вызванные IMPORTRANGE