求助：基于Sheet1复选框控制Sheet2列显示/隐藏的脚本问题

Let's break down what's going wrong with your current code and fix it step by step:

Key Issues in Your Original Code #

1. Checkbox value mismatch: Google Sheets checkboxes return boolean values (true/false), not string literals like "TRUE"/"FALSE". Your string comparisons won't correctly detect the checkbox state.
2. Unnecessary range activation: The line sheet1.getRange('A7').activate(); doesn't serve any purpose here—you don't need to select a range to read its value.
3. Case-sensitive sheet names: If your sheets use the default capitalized names ("Sheet1", "Sheet2"), using lowercase "sheet1"/"sheet2" in getSheetByName() will fail to locate the sheets.
4. Missing automatic trigger: Your script won't run when the checkbox is toggled unless you manually execute it. We'll use a simple onEdit trigger to make it work automatically.
5. Column range discrepancy: Your requirement specifies only column P, but your code targets columns P to AA. I'll adjust this to match your stated need, but note how to expand it if that was intentional.

Corrected Script #

This version will automatically trigger when Sheet1's A7 checkbox is toggled, and correctly show/hide Sheet2's P column:

function onEdit(e) {
  // Only proceed if the edited cell is Sheet1!A7
  const editedSheet = e.range.getSheet();
  if (editedSheet.getName() !== "Sheet1" || e.range.getA1Notation() !== "A7") {
    return;
  }

  const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  const sheet2 = spreadsheet.getSheetByName("Sheet2");
  const isCheckboxChecked = editedSheet.getRange("A7").getValue(); // Returns true/false boolean

  if (isCheckboxChecked) {
    sheet2.showColumns(16); // Column P is the 16th column in the sheet
  } else {
    sheet2.hideColumns(16);
  }
}

Adjustments If Targeting Multiple Columns #

If you actually intended to show/hide columns P to AA (as in your original code), replace the showColumns and hideColumns lines with:

// Show columns P (16) to AA (27) — 12 columns total
sheet2.showColumns(16, 12);
// For hiding:
sheet2.hideColumns(16, 12);

Important Notes #

• Sheet name accuracy: Double-check that your sheet names match exactly (case-sensitive) what's in the script. If your sheets are named "sheet1" (lowercase), update the strings in getName() and getSheetByName() to match.
• Simple trigger functionality: The onEdit function is a built-in simple trigger, so it will run automatically without needing to set up an installable trigger. Just save the script, and it will work when you toggle the checkbox.

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