如何修复单元格注释中的单元格历史记录? [英] How to fix cell history in cell note?

问题描述

我找到了一个脚本，该脚本应该在笔记中添加单元格历史记录，原始脚本位于此处(

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 + "'" + '\n\n' + currentCellNote);
    
  }

推荐答案

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:%s\n',dt,A1,e.value);
  rg.setNote(rg.getNote() + newNote);
}

这篇关于如何修复单元格注释中的单元格历史记录?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！