Hide multiple sheets based on a cell value

Question

Good Afternoon,

I am fairly new to scripts for Google Sheets (getting used to Excel differences) and am trying to hide multiple sheets ("Week 1", "Week 2", "Week 3"... all the way to week 9) and only show one based on a cell (j10 in a sheet "Control Panel"). If j10 has a "4", only "Week 4" will show.

I see a way to do this one sheet at a time based on this old Stack Overflow question using 9 "if statements" and each statement having 9 commands to either hide all but one "Week" sheet. Basically, I'm looking to considerably simplify that code to not be so slow.

So I tried to use some script from this post that was written before the hidesheet() method existed by looping a command, but I'm having trouble.

Thank you for considering!

JPV JPV · Accepted Answer · 2017-02-07T08:23:59

I assumed you don't want to hide the sheet 'Control Panel' ? Try below script

function onEdit(e) {
if (e.source.getActiveSheet()
    .getName() == 'Control Panel' && e.range.getA1Notation() == 'J10' && e.value > 0 && e.value < 10) {
    e.source.getSheets()
        .forEach(function (sh) {
            (sh.getName() !== 'Week ' + e.value && sh.getName() !== 'Control Panel') ? sh.hideSheet() : sh.showSheet();
        })
}
}

NOTE: this script uses a simple onEdit trigger. So don't try to run it from the script editor (by clicking the 'play' button). Instead, go to sheet 'Control Panel' and enter a value (> 0 and < 9) in J10.

Hide multiple sheets based on a cell value

1 Answers