Detect Note changes with onChange

Question

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?

It appears to me that neither onEdit nor onChange will detect that event. But rest assure if I'm wrong, someone will let us know. — Cooper, Jan 20 '21 at 21:50
If you want to test both onChange and onEdit at the same time you can do something like this: `function onMyChange(e) { e.source.getActiveSheet().getRange('A1').setValue(JSON.stringify(e)); } function onMyEdit(e) { e.source.getActiveSheet().getRange('B1').setValue(JSON.stringify(e)); }` Then you can make changes to a sheet and watch the event object be written directly into your sheet. — Cooper, Jan 20 '21 at 21:54

Marios · Accepted Answer · 2021-01-21T00:45:58.850

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.

Thank you for your reply. What I realized was that ss always returns the leftmost sheet regardless of which active sheet it was, so if I have multiple sheets, `ss.getName()=="Completed"` will always fail the condition. Do you know how to fix that? — Brandon Park, Jan 21 '21 at 19:44
Can't reproduce your issue. Active sheet is the sheet you have selected in the `ui` ( in the spreadsheet file) @BrandonPark — Marios, Jan 21 '21 at 19:51
Here's a sample sheet that reproduces my issue. https://docs.google.com/spreadsheets/d/1pVfIv6NRybK8JH-FgzTLYiTkzu73-8iD77HFZOGk3iA/edit#gid=0 — Brandon Park, Jan 21 '21 at 20:37
@BrandonPark could you please post a new question regarding that issue so we can have two different threads? In this way future readers can find them both easier. Stackoverflow does not allow for follow up questions. Sorry for the inconvenience — Marios, Jan 21 '21 at 20:39
I have created a new question: https://stackoverflow.com/questions/65851513/active-sheet-always-being-leftmost-sheet-on-google-sheets — Brandon Park, Jan 22 '21 at 19:23

Detect Note changes with onChange

1 Answers1

Explanation / Issue:

Linked