Google Sheets联动下拉菜单控制行列显隐脚本问题求助

Hey there! Let's get your intake form working properly. I spotted a few key issues in your code that are causing the row controls to fail and preventing automatic triggers:

Key Problems in Your Current Code #

• Duplicate Function Names: You have two functions named HideRows—in JavaScript, the second one overwrites the first, so your NamedRange3 logic never runs.
• Empty onEdit Trigger: Your onEdit function doesn't call any of your visibility functions, so nothing happens when you edit the dropdowns.
• Potential Worksheet Name Mismatch: Double-check that your sheet names (like "Sheet1") match exactly what's in your spreadsheet (capitalization, spaces matter!).

Corrected Script #

Here's a revised version that fixes all these issues and ensures automatic triggering when you edit the dropdowns:

function onEdit(e) {
  // Only run if the edit happened in one of our target named ranges
  const editedRange = e.range;
  const namedRanges = SpreadsheetApp.getActiveSpreadsheet().getNamedRanges();
  
  // Check which named range was edited and run the corresponding function
  namedRanges.forEach(range => {
    if (range.getName() === "NamedRange1" && editedRange.getA1Notation() === range.getRange().getA1Notation()) {
      HideColumns();
    } else if (range.getName() === "NamedRange2" && editedRange.getA1Notation() === range.getRange().getA1Notation()) {
      HideRowsForNamedRange2();
    } else if (range.getName() === "NamedRange3" && editedRange.getA1Notation() === range.getRange().getA1Notation()) {
      HideRowsForNamedRange3();
    }
  });
}

function HideColumns() {
  const ss = SpreadsheetApp.getActive();
  const nameValue = ss.getRangeByName("NamedRange1").getValue();
  const targetSheet = ss.getSheetByName("Sheet 2");
  
  if (nameValue === "A") {
    targetSheet.showColumns(3);
  } else {
    targetSheet.hideColumns(3);
  }
}

function HideRowsForNamedRange2() {
  const ss = SpreadsheetApp.getActive();
  const nameValue = ss.getRangeByName("NamedRange2").getValue();
  const targetSheet = ss.getSheetByName("Start Here >>"); // Updated to match your main sheet name
  
  if (nameValue === "Yes") {
    targetSheet.showRows(15, 5); // 15 to 19 is 5 rows (19-15+1=5)
  } else {
    targetSheet.hideRows(15, 5);
  }
}

function HideRowsForNamedRange3() {
  const ss = SpreadsheetApp.getActive();
  const nameValue = ss.getRangeByName("NamedRange3").getValue();
  const targetSheet = ss.getSheetByName("Start Here >>"); // Updated to match your main sheet name
  
  if (nameValue === "Yes") {
    targetSheet.showRows(26, 15); // 26 to 40 is 15 rows (40-26+1=15)
  } else {
    targetSheet.hideRows(26, 15);
  }
}

What Changed? #

1. Unique Function Names: Renamed the row control functions to HideRowsForNamedRange2 and HideRowsForNamedRange3 so they don't overwrite each other.
2. Functional onEdit Trigger: The onEdit function now checks which named range was edited and runs the correct visibility function automatically. We use the e event object to get the edited range, so we only trigger changes when relevant dropdowns are edited.
3. Fixed Row Count: In your original code, you used showRows(15, 10) which would show rows 15-24 (10 rows). I corrected it to showRows(15,5) to target rows 15-19 as you requested.
4. Consistent Sheet Names: Changed the target sheet for row controls to "Start Here >>" (your main sheet) instead of "Sheet1"—make sure this matches your actual sheet name exactly!

How to Set It Up #

1. Open your Google Sheet.
2. Click Extensions > Apps Script to open the script editor.
3. Replace all existing code with the corrected script above.
4. Save the project (click the floppy disk icon) and give it a name like "IntakeFormVisibility".
5. Go back to your sheet and test the dropdowns—they should now show/hide rows/columns automatically when you make a selection!

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