How to fix cell history in cell note?

Tags: , , ,



I found a script that’s supposed to add cell history in a note, the original one found here(https://www.pbainbridge.co.uk/2019/07/add-note-to-cell-on-google-sheet-edit.html) does not work or I’m doing something wrong. I modified the script and is supposed to add notes to cells in Column 18(R) seems to work but only on one of my sheets and it works properly only on page 2, in page 1 it makes a note but with no content other than date. Any help to fix is greatly appreciated!

function onEdit(e){
  if (e.range.getColumn() === 18) {
      var ss = e.source; 
      var sheet = ss.getSheets()[0];
      var range = e.range;
  var activeCell = sheet.getActiveCell();
  var currentCellValue = activeCell.getValue();
  var currentCellNote = activeCell.getNote();
  Logger.log('Current cell value is: ' + currentCellValue);
   
  Logger.log('Current Note contains: ' + currentCellNote);
 range.setNote('Last Modified: ' + new Date() + 'n' + 'Cell Value: ' + "'" + currentCellValue + "'" + 'nn' + currentCellNote);
    
  }

Answer

function onEdit(e){
  const sh=e.range.getSheet();
  const A1=sh.getRange(e.range.rowStart,e.range.columnStart).getA1Notation();
  const rg=sh.getRange(e.range.rowStart,18);
  const dt=Utilities.formatDate(new Date(),Session.getScriptTimeZone(),"MM/dd/yyyy HH:mm:ss")
  const newNote=Utilities.formatString('Last Modified:%s cell:%s Value:%sn',dt,A1,e.value);
  rg.setNote(rg.getNote() + newNote);
}


Source: stackoverflow