如何修复Google Sheets单元格批注编辑历史脚本异常问题？

Let's break down the issues in your script and fix them step by step:

Key Problems in Your Modified Script #

1. Restricted to only one worksheet: You're hardcoding the script to target the first sheet with ss.getSheets()[0], which is why it only works on a single tab.
2. Incorrect cell reference: Using sheet.getActiveCell() doesn't reliably grab the cell that was just edited (especially if the user navigates away quickly or edits via keyboard shortcuts). The e.range object from the onEdit event is the correct, reliable way to access the edited cell.
3. Invalid quote syntax: The " HTML entities don't work in Google Apps Script—you need to use regular single or double quotes instead.

Fixed Script #

function onEdit(e) {
  // Check if the edited column is column 18 (R)
  if (e.range.getColumn() === 18) {
    // Get the exact sheet where the edit occurred (no more hardcoding to the first sheet)
    const editedSheet = e.range.getSheet();
    // Directly use the edited cell from the event object
    const editedCell = e.range;
    const currentValue = editedCell.getValue();
    const existingNote = editedCell.getNote();
    
    // Log values for debugging (optional but helpful for troubleshooting)
    Logger.log('Edited cell value: ' + currentValue);
    Logger.log('Existing note content: ' + existingNote);
    
    // Build the new note with clean, valid syntax
    const newNote = `Last Modified: ${new Date()}\nCell Value: '${currentValue}'\n\n${existingNote}`;
    
    // Update the note on the edited cell
    editedCell.setNote(newNote);
  }
}

What Changed & Why #

• Target the right sheet: editedSheet = e.range.getSheet() ensures the script works on whichever worksheet the user edits column R in, not just the first one.
• Use the edited cell directly: e.range is the exact cell that triggered the onEdit event, so we avoid mismatches between the "active" cell and the actual edited cell.
• Fixed quote formatting: Replaced " with standard single quotes (') inside a template literal (${}) for cleaner, valid syntax that Google Apps Script can interpret.
• Simplified variable names: Used descriptive names like editedCell and currentValue to make the code easier to read and maintain.

Testing Tips #

1. Save the script, then return to your Google Sheet.
2. Edit any cell in column R (18th column) on any worksheet—you should see the note update with the timestamp, cell value, and preserve existing note history.
3. Check the script editor's Logs (View > Logs) if you need to debug any unexpected behavior.

内容的提问来源于stack exchange，提问作者Nate