Google Sheets脚本适配行增删：自动调整行隐藏/显示范围

The problem with your current script is that hardcoded row numbers break whenever you insert or delete rows. Instead, we can use named ranges in Google Sheets—these automatically adjust their row/column references when you edit the sheet, so you’ll never have to manually update your script again. Here’s how to set it up:

Step 1: Create Named Ranges #

First, define two named ranges in your sheet:

• ToggleCell: Select your dropdown cell (originally L77), go to Data > Named ranges, name it ToggleCell, and save.
• ControlledRows: Select the range of rows you want to hide/show (originally rows 100-125), go to Data > Named ranges, name it ControlledRows, and save.

These ranges will automatically expand or shrink if you insert/delete rows within or around them.

Step 2: Updated Script #

Replace your existing script with this version, which leverages the named ranges for dynamic behavior:

function onEdit(e) {
  const activeSheet = e.source.getActiveSheet();
  
  // Exit if we're not on the correct sheet
  if (activeSheet.getName() !== "Sheet 1") return;
  
  // Get the toggle cell named range (check if it exists)
  const toggleRange = e.source.getRangeByName("ToggleCell");
  if (!toggleRange) {
    console.error("Named range 'ToggleCell' not found! Did you set it up correctly?");
    return;
  }
  
  // Exit if the edited cell isn't the toggle cell
  if (e.range.getA1Notation() !== toggleRange.getA1Notation()) return;
  
  // Get the controlled rows named range (check if it exists)
  const controlledRange = e.source.getRangeByName("ControlledRows");
  if (!controlledRange) {
    console.error("Named range 'ControlledRows' not found! Did you set it up correctly?");
    return;
  }
  
  // Hide or show the controlled rows based on dropdown value
  if (e.value === "Yes") {
    controlledRange.showRows();
  } else {
    controlledRange.hideRows();
  }
}

Key Improvements: #

• Automatic Adjustments: Named ranges handle row insertions/deletions for you—no more manual script edits.
• Simpler & Faster: Instead of looping through each row, we call showRows() or hideRows() directly on the entire range, making the code cleaner and more efficient.
• Error Checks: The script includes basic validation to alert you if named ranges are missing (check the script editor’s console for error messages).

How It Works: #

1. When you edit the dropdown cell (marked by ToggleCell), the script triggers.
2. It verifies we’re on the correct sheet and that the edited cell is the toggle.
3. It retrieves the ControlledRows range, which now reflects any recent row changes.
4. It shows or hides the entire range based on whether the dropdown is set to "Yes" or another value.

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