Google Sheets脚本问题：无法将Y列计算值复制到Z列（onEdit触发）

It sounds like your core issue is that the onEdit trigger doesn't fire when a cell's value updates due to a formula recalculation—this is a key limitation of simple onEdit triggers, as they only respond to manual user edits, not changes from formulas, scripts, or external data updates. Let's walk through two solutions to fix this:

Option 1: Corrected onEdit for Manual Edits to Column Y #

If you want the script to run when a user manually edits column Y (column 25), here's a polished, working version of your script:

function onEdit(e) {
  // Exit if the edit isn't on Sheet1
  if (e.source.getActiveSheet().getName() !== "Sheet1") return;
  
  // Exit if the edited column isn't Y (column 25)
  const editedColumn = e.range.getColumn();
  if (editedColumn !== 25) return;
  
  // Get the adjacent Z column cell (same row, column 26)
  const targetCell = e.range.offset(0, 1);
  
  // Copy the calculated value (not the formula) from Y to Z
  targetCell.setValue(e.range.getValue());
}

Why this works: #

• It uses the event object (e) properly to access the edited range.
• It exits early if the edit isn't on the right sheet or column, making the script efficient.
• setValue() copies only the computed value, not the formula itself.

Option 2: Installable onChange Trigger for Formula Recalculations #

To handle updates from formula recalculations, you'll need an installable onChange trigger (since simple triggers can't detect formula-driven changes). Here's how to set this up:

Step 1: Create the onChange Function #

Add this code to your script editor:

function copyYToZOnChange(e) {
  // Target Sheet1 specifically
  const sheet = e.source.getSheetByName("Sheet1");
  if (!sheet) return;
  
  // Respond to both manual edits and formula recalculations
  if (e.changeType !== "EDIT" && e.changeType !== "OTHER") return;
  
  // Get all populated rows in column Y
  const lastRow = sheet.getLastRow();
  const yValues = sheet.getRange(`Y1:Y${lastRow}`).getValues();
  
  // Paste the values into column Z
  sheet.getRange(`Z1:Z${lastRow}`).setValues(yValues);
}

Step 2: Set Up the Installable Trigger #

1. In the Google Apps Script editor, click the clock icon (Triggers) in the left sidebar.
2. Click Add Trigger in the bottom right.
3. Configure the trigger:
   • Choose function: copyYToZOnChange
   • Choose deployment type: Head
   • Select event source: From spreadsheet
   • Select event type: On change
4. Click Save and authorize the script when prompted.

Notes on this approach: #

• This will copy all values from Y to Z whenever any change happens in Sheet1 (including formula recalculations).
• If your sheet is very large, you might want to optimize this to only copy the rows that changed, but that requires more complex logic to track previous values.

Why Your Original Script Likely Failed #

1. onEdit doesn't detect formula changes: As mentioned, simple onEdit triggers ignore updates from formulas.
2. Incomplete code: Your snippet cuts off at var ca..., so the actual copy logic might have been missing or incorrect.
3. Event object usage: While naming the parameter CopyCourse is allowed, using the standard e makes the code more readable and avoids confusion.

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