CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetstriggers
Hiten Savaliya
Hiten Savaliya

Reputation: 9

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

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

Upvotes: 0

Views: 1089

Answers (1)

ziganotschka
ziganotschka

Reputation: 26836

There is a workaround:

  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 range of 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. 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

Upvotes: 2

Related Questions