请求编写Google Sheets的onEdit()脚本实现特定列时间戳功能

Here's a script that meets all your requirements:

function onEdit(e) {
  const lock = LockService.getScriptLock();
  if (!lock.tryLock(10000)) return; // Wait up to 10 seconds for concurrent edit lock

  try {
    const targetSheetName = "Your Sheet Name"; // Replace with your actual sheet name
    const sheet = e.source.getActiveSheet();
    if (sheet.getName() !== targetSheetName) return;

    const row = e.range.getRow();
    if (row < 11) return; // Skip rows above 11

    const col = e.range.getColumn();
    const timezone = e.source.getSpreadsheetTimeZone();
    const now = new Date();

    // Add Creation timestamp (only first edit to Name column, if Creation is empty)
    if (col === 2 && sheet.getRange(row, 3).getValue() === "") {
      const creationStamp = Utilities.formatDate(now, timezone, "M/d");
      sheet.getRange(row, 3).setValue(creationStamp);
    }

    // Update Updated timestamp on edits to specified columns
    const triggerColumns = [3, 7, 8, 9]; // Creation[C], Sign Ups[G], Reminder[H], Inactivity Notice[I]
    if (triggerColumns.includes(col)) {
      const updatedStamp = Utilities.formatDate(now, timezone, "MM/dd h:mm");
      sheet.getRange(row, 6).setValue(updatedStamp);
    }
  } finally {
    lock.releaseLock();
  }
}

Key Features #

• Multi-user Safe: Uses LockService to prevent race conditions during concurrent edits.
• Sheet Restriction: Only runs on the specified worksheet (update targetSheetName to match your sheet).
• Row Filtering: Ignores any edits in rows above 11.
• One-time Creation Timestamp: Adds M/d format time to Creation[C] only when Name[B] is first edited (and C is empty).
• Dynamic Updated Timestamp: Refreshes Updated[F] to MM/dd h:mm format every time edits are made to C, G, H, or I columns.
• Spreadsheet Timezone: Uses your sheet's native timezone instead of UTC.
• Non-Destructive: Only replaces timestamps when conditions are met; existing values are preserved unless explicitly updated.

Setup Instructions #

1. Open your Google Sheet.
2. Navigate to Extensions > Apps Script to open the script editor.
3. Replace the default code with the script above.
4. Replace "Your Sheet Name" with the exact name of your target worksheet.
5. Save the script (click the save icon) and assign a name like TimeStampAutomation.
6. The onEdit trigger is automatically enabled for simple edits; ensure the script has edit permissions for your sheet.

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