Detect Note changes with onChange

user_14916399 0
Viewed 289

Is it possible for Google Sheets to detect when you've edited your notes or inserted a new one using onChange?

function onChange(e) {
  var ss = SpreadsheetApp.getActiveSheet();
  var s = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Current");
  var r = ss.getActiveCell();

  if(e.changeType == 'OTHER') {
    if ((r.getColumn() < 7 || r.getColumn() > 7) && (ss.getName()=="Completed") && (r.getRow() > 1)) { // 2. If Edit is done in any column before or after 6th Column (F) And sheet name is Sheet1 then:
      var celladdress ='G'+ r.getRowIndex() // Add time stamp to cell in Column F
      ss.getRange(celladdress).setValue(Utilities.formatDate(new Date(), "GMT-08:00", "MM/dd/yyyy HH:mm"));
      ss.getRange(r.getRow(), 17).setValue(s.getRange(row, 2).getNote());
    }
  }

I tried changeType FORMAT, EDIT, and OTHER and none of them works. If onChange is not possible to detect note editing, is there a function that does?

google-apps-script google-sheets
1 Answers

Explanation / Issue:

It took me an hour to figure this out, but actually your logic is correct.

  • When editing/inserting a note e.changeType returns OTHER, therefore this part of the code is correct.

I tested your code and I also tried to get the active cell with many different ways: getActiveCell(), getCurrentCell() and getActiveRange() since the event object of an onChange trigger does not support e.range.

  • But every single time r.getColumn() and r.getRow() returned 1 and 1 and when I ran the exact same code manually, I was getting the correct cell.

  • I searched online and I found that this behaviour has not only been reported but also accepted by google as a bug and it concerns the onChange trigger. You can find the link HERE and make sure you star (top-left corner) the issue to make it more popular.

Since r.getRow() is 1 your code does not do anything because you have r.getRow() > 1 as a condition which always evaluates to false because of this bug.

As a proof of this bug, you can use this code instead and you will see that when you insert or modify a note in the sheet Completed, the timestamp will be pasted in cell G1 of the same sheet.

function onChange(e) {
  var ss = SpreadsheetApp.getActiveSheet();
  var s = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Current");
  if(e.changeType == 'OTHER') {
    if (ss.getName()=="Completed") {
      var celladdress ='G1';
      ss.getRange(celladdress).setValue(Utilities.formatDate(new Date(), "GMT-08:00", "MM/dd/yyyy HH:mm"));
    }
  }
}

While this answer does not answer your question, it proves that your logic is correct but due to a bug you are not getting the expected results. Therefore, I thought it would be useful for you and other future readers to be aware of the issue. Unfortunately, I didn't come up with a solution but I hope another reader would do.

For all the above, I assume you have installed the onChange trigger since it is an installable trigger.

user_112252910
Related