I'm pretty new to learning app script and looked over/tried to edit this script, but I'm not getting my desired result. I have a sheet titled "Menu" where I'm wanting a user to select from three different drop down options in Cell A2 (e.g. Blue, Yellow, Green). I then want to hide the different sheets based on the selection. So if a user selects "Blue" I want only the sheets that start with the word "Blue" to be visible + the "Menu" sheet and the rest to be hidden. Same for Yellow and Green. As a note there are 13 sheets for each color.
Any help with this is much appreciated.
This is an alternative implementation of @JSmith's answer, using the Sheets REST API to more efficiently hide & unhide a large number of sheets.
To use the Sheets REST API from Apps Script, you will first need to enable it, as it is an "advanced service."
The Sheets API approach enables you to work with the JavaScript representation of the data, rather than needing to interact with the Spreadsheet Service repeatedly (e.g. to check each sheet's name). Additionally, a batch API call is processed as one operation, so all visibility changes are reflected simultaneously, while the Spreadsheet Service's
showSheet()
andhideSheet()
methods flush to the browser after each invocation.There are a fair number of resources to be familiar with when working with Google's various REST APIs:
A little testing in a workbook with 54 sheets, in which I used the Sheets API to apply some changes and @JSmith's code to revert the changes, showed the API approach to be about 15x faster, as measured with
console.time
&console.timeEnd
. API changes took from 0.4 to 1.1s (avg 1s), while the Spreadsheet Service method took between 15 and 42s (avg 20s).try this code: