How to hide Multiple columns in Google Sheet with Multiple checkboxes

Question

I hope I can explain this well as I have absolutely no coding background. Luckily, I found a script on here that will hide a column, based on the status of a checkbox.

The code is:

 function onEdit(e) { if (e.range.getA1Notation() != "B5") return; if (e.value == "TRUE") { SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().hideColumns(8,4); } else if (e.value == "FALSE") { SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().showColumns(8,4); } }

This will hide columns H through K, if B5 is checked.

My issue is that I have different checkboxes that need to hide different columns, but I can only get 1 script to work at at time.

Here's what I need:

When B5 is checked, I need Columns H, I, J, and K hidden.

When B6 is checked, I need Columns L, M, N, and O hidden.

When B7 is checked, I need Columns P, Q, R, and S hidden.

And so on... If multiple cells are checked, then multiple ranges, listed above, need to be hidden.

Since I have no clue what I am doing, I figured I would just make a new script for each scenario. I realize that there is probably an easier way, but this works for me if needed.

So, if I have one script saved as

 function onEdit(e) { if (e.range.getA1Notation() != "B5") return; if (e.value == "TRUE") { SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().hideColumns(8,4); } else if (e.value == "FALSE") { SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().showColumns(8,4); } }

and another saved as

 function onEdit(e) { if (e.range.getA1Notation() != "B6") return; if (e.value == "TRUE") { SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().hideColumns(12,4); } else if (e.value == "FALSE") { SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().showColumns(12,4); } }

Only the second script works. Likewise, if I add a third script, then only the 3rd one works and the first two don't.

How can I make each one work independently of each other?

Do you have a sample of a Sheet where we can work or test the code or make a copy of it? — Ricardo Jose Velasquez Cruz
– Ricardo Jose Velasquez Cruz, Commented Jul 9, 2022 at 0:50

TheWizEd · Accepted Answer · 2022-07-09 14:08:58Z

You can include multiple cells to check in you onEdit(e)

function onEdit(e) { let col = 0; switch(e.range.getA1Notation()) { case "B5": col = 8; break; case "B6": col = 12; break; case "B7": col = 16; break; default: return; } if( e.value === "TRUE" ) { e.source.getActiveSheet().hideColumns(col,4) } else { e.source.getActiveSheet().showColumns(col,4) } }

If this answers your question please mark it as the answer so others with similar question can use it to help them resolve their question. Thanks.

Collectives™ on Stack Overflow

How to hide Multiple columns in Google Sheet with Multiple checkboxes

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related