google-apps-script - 我想将用户 ID 添加到注释到单元格的注释中。我这可能>? 我对应用程序脚本很陌生

Question

function onEditNote() {
  
  // get spreadsheet
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('MCD Current Stock');
  
  // get active cell
  var activeCell = sheet.getActiveCell();
  
  // get any existing cell value
  var currentCellValue = activeCell.getValue();
  Logger.log('Current cell value is: ' + currentCellValue);
  
  // get any existing cell Note
  var currentCellNote = activeCell.getNote();
  Logger.log('Current Note contains: ' + currentCellNote);
  
  // set Note on the edited cell with above information
  activeCell.setNote('Last modified: ' + new Date() + '\n' + 'Cell value: ' + "'" + currentCellValue + "'" + '\n\n' + currentCellNote);
  
}

标签： google-apps-script

Answer 1

需要一个可安装的 onEdit 触发器

function onMyEdit(e) {
  //e.source.toast('entry');
  const sh = e.range.getSheet();
  //sh.getRange('A1').setValue(JSON.stringify(e));
  if (sh.getName() == 'MCD Current Stock') {
    //e.source.toast('sheet');
    let note = e.range.getNote();
    let user = Session.getUser().getEmail();
    if (note != '') {
      //e.source.toast('note')
      e.range.setNote('Last modified: ' + new Date() + '\n' + 'User: ' + "'" + user + ' Cell value: ' + "'" + e.value + "'" + '\n\n' + note);
    } else {
      e.range.setNote('Last modified: ' + new Date() + '\n' + 'User: ' + "'" + user + ' Cell value: ' + "'" + e.value + "'")
    }
  }
}

仅在编辑单元格时有效，而在编辑附加到单元格的注释时无效。