How to dynamically show/hide rows in Google Sheets based on dropdown selection?

Other Questions Google Sheets
1

I’m a newbie to Google Sheets scripting and need some guidance. I’ve got a sheet named ‘Test’ with a dropdown in cell B3. What I want to do is:

  1. When ‘No’ is selected, hide the next 9 rows
  2. When ‘Yes’ is selected, show those 9 rows

I’ve looked around for solutions but haven’t found one that fits my needs exactly. Here’s a basic example of what I’m working with:

| A | B      | C |
|---|--------|---|
| 1 | Label  |   |
| 2 | Option |   |
| 3 | [Y/N]  |   |
| 4 | Data1  |   |
| 5 | Data2  |   |
...
| 12| Data9  |   |

Any tips on how to set this up would be super helpful. Thanks!

2

I’ve implemented something similar in my sheets before. Here’s a brief outline of the approach:

Open the script editor (Tools > Script editor) and create a new function, for example, ‘onEdit’. Using the event object, check if cell B3 was edited. If it was, retrieve the sheet and the edited range, then verify the cell value (either ‘Yes’ or ‘No’). Depending on this value, call either sheet.hideRows() or sheet.showRows() to toggle the visibility of the subsequent rows.

Be sure to account for any header rows, and remember to set up an automatic trigger so the script runs on each edit.

3

hey zoeStar, i can help u with that! you’ll need to use Apps Script for this. create a script that triggers on edit of B3. check the value, then use sheet.hideRows() or sheet.showRows() to toggle visibility. lemme know if u want more details on the code!

4

I’ve tackled this issue before, and here’s what worked for me:

Create a new script by going to Tools > Script editor. Then, paste in this function:

function onEdit(e) {
var sheet = e.source.getActiveSheet();
if (sheet.getName() == ‘Test’ && e.range.getA1Notation() == ‘B3’) {
var range = sheet.getRange(‘4:12’);
e.value == ‘No’ ? range.hideRows() : range.showRows();
}
}

This script automatically runs when B3 is edited. It checks if the sheet is ‘Test’ and if B3 was changed. When ‘No’ is selected, it hides rows 4-12. If ‘Yes’ (or any other value) is selected, it shows those rows.

I’ve found this approach to be straightforward and reliable. The built-in trigger for onEdit keeps it seamless without needing extra configuration. It saved me a lot of time when I implemented a similar solution in one of my projects. Let me know if you have any questions or if something doesn’t work as expected.