onEdit(e) not generating trigger event when cell value changes due to inbuilt function

Question

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)

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.

Thanks

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

This is functioning as designed. The edit trigger will not fire unless the change is directly by a user. — tehhowch
Is there any other way or option where we can get trigger for cell value change even the value is being changed by other function — Hiten Savaliya

ziganotschka ziganotschka · Accepted Answer · 2019-12-11T14:20:09

There is a workaround:

Create two separate spreadsheets - spreadsheet number 1 contains your original data and formula, spreadsheet number 2 contains your script and an empty SheetA
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")
Use Scriptproperties to store cell values
Find the modified cell by comparing old values against new values, each time there is a change in the sheet of interest
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);
          }
        }
      }
    }     
}

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

EXPLANATION:

onEdit trigger cannot detect changes in values triggered by a formula

onChange cannot detect changes caused by cell formulas, but it can detect changes triggered by IMPORTRANGE

onEdit(e) not generating trigger event when cell value changes due to inbuilt function

1 Answers

There is a workaround: