Google Spreadsheet OnEdit函数功能实现及故障修复脚本模板请求

Hey there! Let's get your onEdit function working properly. I noticed a couple of small issues in your original code, plus we can optimize it to be more reliable using the event object provided by the trigger.

Issues in Your Original Script #

• Case-sensitive typo: offSet should be offset (JavaScript is case-sensitive, so this was breaking the date cell reference)
• Over-reliance on active elements: Using getActiveSpreadsheet() and getActiveCell() can cause issues in some scenarios (like bulk edits); using the event object e is more robust
• Redundant sheet fetching: You were grabbing the sheet twice, which we can clean up to make the code more efficient

Corrected Script #

function onEdit(e) {
  // Get key details from the edit event
  const editedRange = e.range;
  const targetSheet = editedRange.getSheet();
  
  // Exit early if we're not on the "Hub" sheet or editing column D (column index 4)
  if (targetSheet.getName() !== "Hub" || editedRange.getColumn() !== 4) return;
  
  // Check if the edited cell value is exactly "completed"
  if (editedRange.getValue() === "completed") {
    // Fetch the value from cell B1 once
    const b1Value = targetSheet.getRange("B1").getValue();
    
    // Fill E column with current date (1 column right of edited cell)
    editedRange.offset(0, 1).setValue(new Date());
    // Fill F column with B1's value (2 columns right of edited cell)
    editedRange.offset(0, 2).setValue(b1Value);
  }
}

Step-by-Step Implementation Guide #

1. Open the Script Editor: In your Google Sheet, click Extensions > Apps Script to launch the editor.
2. Replace Your Code: Delete your existing script and paste the corrected code above.
3. Save the Project: Click the save icon (💾) and give your project a name (e.g., "CompletedRowAutoUpdater").
4. Test the Function: Go back to your sheet, type completed into any cell in column D, then verify:
   • The same row's column E gets populated with the current date
   • The same row's column F gets the value from cell B1

Important Notes #

• Simple Trigger Rules: The onEdit function is a simple trigger, so it can't run actions that require authorization (like accessing external services). Our script only interacts with the sheet itself, so this isn't a problem here.
• Sheet Name Accuracy: Make sure your sheet is exactly named "Hub" (case-sensitive). If your sheet has a different name, update the string in the script to match.
• Flexible Value Matching: If you need to handle variations (like "Completed" or " completed" with spaces), adjust the condition to:

  if (editedRange.getValue().toString().trim().toLowerCase() === "completed")
  

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