扩展Google Sheets时间戳脚本至多列 仅更新空单元格

I've revised your script to support multiple trigger columns and only update the timestamp when the target cell is empty. This makes it more flexible and prevents overwriting existing timestamps:

function onEdit(event) {
  // Configuration - adjust these values as needed
  const timezone = "GMT+2";
  const timestamp_format = "MM-dd-yy";
  // Define trigger columns and their corresponding timestamp columns (header names)
  const triggerColumnMap = {
    "Emailed": "E-Date",
    // Add more pairs here, e.g.:
    // "Completed": "Completion-Date",
    // "Reviewed": "Review-Date"
  };

  const sheet = event.source.getActiveSheet();
  const actRng = event.source.getActiveRange();
  const editRow = actRng.getRowIndex();
  const editCol = actRng.getColumn();

  // Skip header row (row 1)
  if (editRow === 1) return;

  // Get all headers to find column indices
  const headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
  
  // Check if the edited column is a trigger column
  const editedHeader = headers[editCol - 1];
  if (!triggerColumnMap.hasOwnProperty(editedHeader)) return;

  // Get the target timestamp column name and its index
  const targetTimestampHeader = triggerColumnMap[editedHeader];
  const targetColIndex = headers.indexOf(targetTimestampHeader) + 1;

  // If target column index is invalid, exit
  if (targetColIndex === 0) return;

  // Get the target cell
  const targetCell = sheet.getRange(editRow, targetColIndex);
  
  // Only update if the target cell is empty
  if (targetCell.getValue() === "") {
    const timestamp = Utilities.formatDate(new Date(), timezone, timestamp_format);
    targetCell.setValue(timestamp);
  }
}

Key Improvements & How to Use: #

• Multi-Column Support: The triggerColumnMap object lets you define as many trigger-timestamp column pairs as you want. Just add new entries like "YourTriggerHeader": "YourTimestampHeader" to the object.
• Empty Check: The script will only write a timestamp if the target cell is blank, so existing timestamps won't be overwritten accidentally.
• Header-Based Matching: Instead of hardcoding column numbers, the script uses header names to find columns. This means you can reorder columns in your sheet without breaking the script.
• Header Row Skip: The script ignores edits to the first row (header row) to avoid unintended updates.

To add more trigger columns, simply expand the triggerColumnMap with your desired header pairs. For example, if you want the "Completed" column to trigger a timestamp in "Completion-Date", add that line to the object.

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