Google Sheets脚本问题：获取选中行首单元格值并实现模板复制

Hey Kevin, let's work through fixing your script—there are a handful of small but critical issues causing the problem with fetching the row's first cell value and overall logic flow.

Key Problems in Your Original Code #

• Wrong condition check: You used currentCell = "YES" (this is an assignment, not a comparison) and tried comparing a cell object directly to a string. You need to check the cell's value instead.
• Undefined variable: The cell in ss.getRange(cell,1) isn't defined anywhere—you intended to use the active row index stored in sourceRow.
• String concatenation error: Google Apps Script uses + for joining strings, not & (that's Excel/VBA syntax). Also, getValues() returns a 2D array, so you need to extract the actual value instead of using the array directly.
• Unnecessary sheet activation: Activating sheets is redundant here and can lead to unexpected behavior—you can duplicate/hide sheets without activating them first.

Corrected Working Script #

function onEdit(e) {
  // Use the edit event object to access the modified cell directly
  const editedCell = e.range;
  const cellValue = editedCell.getValue();
  
  // Only trigger if the edited cell is set to exactly "YES"
  if (cellValue === "YES") {
    const ss = SpreadsheetApp.getActiveSpreadsheet();
    const sourceRow = editedCell.getRow();
    // Fetch the value from the first cell of the edited row
    const tabName = ss.getRange(sourceRow, 1).getValue();
    
    // Handle template duplication and hiding
    const templateSheet = ss.getSheetByName("CCTemplate");
    templateSheet.showSheet();
    const newSheet = templateSheet.copyTo(ss);
    templateSheet.hideSheet();
    
    // Rename the new sheet with "CC" prefix
    newSheet.setName(`CC${tabName}`);
    
    // Show success toast
    ss.toast("New change control sheet added to workbook.", "Change Control", 15);
  }
}

What We Fixed & Improved #

1. Leveraged the onEdit event object: The e parameter gives reliable direct access to the edited cell, which is better than relying on getCurrentCell() or getActiveRange().
2. Fixed the condition check: We now compare the cell's actual value (cellValue === "YES") instead of assigning a value.
3. Properly fetched the row's first cell: We use the edited cell's row number to target the first column of that row, and use getValue() to get the raw string value (no messy array handling needed).
4. Simplified sheet operations: We directly reference the template sheet, duplicate it with copyTo(), then hide the original—no unnecessary sheet activation steps.
5. Fixed string concatenation: Used template literal syntax (CC${tabName}) for clean, readable string joining (you could also use "CC" + tabName if you prefer).

Optional Enhancement #

If you only want this script to trigger when a specific column is edited (e.g., column 5, which is column E), add a column check to the if statement:

if (cellValue === "YES" && editedCell.getColumn() === 5) {
  // Rest of the script logic
}

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