Google Sheets脚本需求：仅当B列单元格从Approved改为Reject时写入时间戳

Got it, let's tweak your script to exactly match what you need—adding a timestamp only when a cell in column B is changed from "Approved" to "Reject", not any other time the value switches to "Reject".

Key Issue in Your Current Code #

Your existing script triggers when the cell is set to "Approved", but we need to verify two specific conditions instead:

1. The cell's previous value was "Approved"
2. The new value being entered is "Reject"

Luckily, the onEdit event object (e) provides access to both e.oldValue (the value before the edit) and e.value (the new value)—perfect for this exact check.

Modified Script #

function onEdit(e) {
  // Exit early if we're not working with "Sheet1"
  const activeSheet = e.source.getActiveSheet();
  if (activeSheet.getName() !== "Sheet1") return;

  // Exit early if the edited column isn't B (column index 2)
  const editedColumn = e.range.getColumn();
  if (editedColumn !== 2) return;

  // Only trigger the timestamp if the edit is from "Approved" to "Reject"
  if (e.oldValue === "Approved" && e.value === "Reject") {
    e.range.offset(0, 12)
      .setValue(new Date())
      .setNumberFormat("MM/dd/yyyy hh:mm:ss");
  }
}

What Changed & Why? #

• Added dual value checks: The condition e.oldValue === "Approved" && e.value === "Reject" ensures we only act on the exact transition you care about.
• Early exits: If the sheet or column doesn't match our criteria, the script stops immediately—this makes it faster and cleaner.
• Modern syntax: Switched from var to const for better variable scoping (a standard practice in current JavaScript).

Quick Note #

The e.oldValue property only exists if the cell had a value before the edit. If a blank cell is changed directly to "Reject", the script won't trigger—which aligns perfectly with your requirement, since it wasn't a transition from "Approved".

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