in this video let's create some scripts to hide and unhide bunch of worksheets really quickly so
usually hiding on hiding worksheets is not really a big deal so if you want to
hide one you can just right click on this and do a hide sheet that will hide that worksheet
if you want to unhide it you open this and it's going to show up here see grayed out i'm going
to click on it and it's going to unhide it so if you didn't know now you know now the
problem that we're trying to fix today is when you have many of these and then you need to
hide bunch of them and then unhide how do you do something like that so what
i'm gonna do here i'm gonna first create another worksheet i'm gonna call this one sheet manager
and what i'm gonna do on this one i'm basically first gonna get a nice list of all worksheets
we have any spreadsheets here so let's just do something like sheet names and we need
those listed here and we're gonna do that with a script so we'll go under tools and script editor
let's name this project
so let's start by a function that's just gonna list
our worksheets here so i'm just gonna call this get list of sheets
and here what i'm gonna do i'm gonna first get my spreadsheet to a variable
so we'll take our spreadsheet app and do get active spreadsheet then
i need a reference to this sheet manager worksheet that i just named here so i'm gonna say
ws manager equals and then i'm gonna take that ss variable and do get sheet by name and paste
that sheet manager name here so now what i'm gonna do i'm just gonna go to my spreadsheet
and here we have this get sheets method which basically gets an array of worksheets
let's just save that in a variable again so now because this is an array we should be able to
loop through that array and get all the names of our worksheets and then hopefully we'll plug
that information in this worksheet manager so let's first get a list of names so for that i'll
take the sheets variable and map since it's an array we should be able to map it to a new array
with some new results and here i'm gonna do a callback function so for each sheet in this array
we're going to do a callback function i'll do arrow function here
and what we're going to do we're just going to return that sheet dot get
is it name or sheet name let's see it's probably name but let's see if there's a sheet there is a
sheet name so let's use that so that's just going to give us the name of the worksheet it's going
to return that and we're going to map it to a new array here i'm just going to call it sheet names
so right now just to show you what we have in the
sheet names i'm just going to do a console log for the time being
let's save and run this get list of sheets function right here i'm going to click run
we're going to have to deal with all the permission stuff
and now as you can see what i got is an array of worksheets now i want to take this list
basically and put it right here and for that i need this to be in a form of array of arrays
because it's a worksheet we need to have a row column structure which right now we don't have
so for that reason i'm going to go back to this and instead of returning that as a single
element i'm gonna return that as an array in square brackets and if i do this and run this
you'll see now each one has its own array so basically it's a column row structure now so
that means now we should be able to take that and put it in this worksheet manager
sheet so i'm gonna take that ws manager in that worksheet we're gonna get the range
so we need to start from the second row first column
the reason for that see it's gonna start from here second row and it's in the first column
and then we need to provide the number of rows it's going to be
the number of sheets so we'll do this dot length and then the number of columns i guess will just
take the first column from this list we could just say one but just to make sure in case we update
something later let's just do it this way i'm gonna take the first row and count how many things
we have in it so it's going to give us the number of columns so that should give us a range now to
do information in that range of cells we need to do set values
and as values will pass this array of sheet names so at this point if i run this script
if i go back to my spreadsheet see we have a list of worksheets all listed here including this one
i'm gonna probably move the sheet manager a little bit to the left
and now if i go back and rerun this you should see it should update this
so so far i was able to get a list of worksheets
now what i'm gonna do here i'm gonna do another column of checkboxes where i'm gonna choose
whether that should be hidden or not hidden let's just call it hidden and basically we'll just
select this whole area i'm just gonna do command shift down and go under insert and do checkbox
so now we have a bunch of checkboxes here so the logic is going to be we could just come in here
and select let's hide this this this this this this this whatever and then as we click something
it should just hide those worksheets and it should also display the ones that are not checked
so for that we're going to do a separate function here
so we'll do a function we're going to call it show hide sheets
and again we're going to start by getting
these two things we'll get the current spreadsheet we'll get that sheet manager worksheet
this one then i want to get this data from here this worksheet names and basically checkboxes
here so to do that i'm gonna take that ws manager worksheet and i'm gonna do get range
and i'll just do it like a regular excel range i'll do a2b all the way down so i'm going to
say a2 colon b and then we'll do get values to actually get those values from those two columns
i'm going to go ahead and save that in a variable here we'll just call this ws manager
data so let's just do a quick console log of this data to see what's in it and we'll go from there
so i'm going to take this ws manager data and console log that now we need to run this function
and as you can see i get all of these falses and then some of them are trues which are
our check boxes but i also get all those empty rows down here so first i want to make sure i
clear those so i only want to just get this part where i actually have some worksheet names
and for that we should be able to just take whatever that list of values is
let's just pass this to the next line and do dot filter to filter those results and again i'm
just going to pass this to the next line so it's easier to read and here for each row in that array
we're going to run a filter and i want to make sure that the first
column is not blank i'm going to save this and rerun this so you can see what happens
so now you can see those empty ones are gone now i only get this list of worksheets and true and
false values right here so basically the idea is going to be now i need to loop through this
and if that says true i need to make sure that i basically make that worksheet hidden
and if it's false i have to make that worksheet visible so what i'll do i'll go right here
and we'll have this ws manager thing and i'm going to loop through that ws manager data so i'll just
do a for each loop and for each line i guess i'll just call that it's a variable name we're gonna
do some magic here and what we need to do for each line inside of that data set let's bring this back
so for each line here i want to make sure that i go grab the worksheet with this name and then
if this says false i want to make sure i make it visible or the other way around if it says true
invisible so i'll take my spreadsheet and do get sheet by name and i'm gonna get that
name from the first column from those results so that will be from my line the first column
zero so that should get me the worksheet so i'm just going to put this in a variable
and then in that worksheet that doesn't seem to give me the right autocomplete for some reason so
probably because i did a mistype here so this should be get sheet by name not
name there it is so now let's see if we get the autocomplete we do so now for each worksheet here
see if i scroll down there is this method height sheet and then if i keep looking
see there's also the opposite let's just do one of them
i'm going to say if the line
1 which is the second column in this data right here if this is true
then i want to hide the worksheet so i'll take the worksheet and do a hide sheet
and otherwise we'll just do this
just a one-liner here so let's just save this and see what happens and take it from here
so i have this function now let's go and see these are checked now so everything that's checked
should be set to hidden if i run this function so let's go and run this
so let's go back and check what happened so sheet five six and seven
see doesn't seem like they're here anymore but they should be here
under hidden see now let's hide more stuff i'm just gonna hide a bunch of this regular ones
let's just only keep 22 and one more and these three let's actually also do this one
so let's go back and rerun this thing show hide sheets
go back and take a look see those four are left the rest are now hidden
just for testing that it on heights too let's bring back this start here
so if i go back see start here is back so there's one last thing i want to do here i want to update
the previous function i did this one that gets my list of sheets and preferably when i update it i
want to also update the status whether it's hidden or not as of right now if i make some changes here
and run it it's going to update this list but this check boxes are just going to stay as they are
so let's see if we can do that so i'm going to go back so right here when we list the worksheet name
as a second column here i want to also get whether that worksheet is hidden or not and
for that i'm just going to do a comma here to add a second column and i'm going to say sheet
for each sheet and here for each sheet we should have this function is sheet hidden so that should
basically give us true or not so if it's hidden it should bring back true which is going to be
excellent because hidden ones are checkboxes that are trues and the rest should be false
so let's just save this go back and try to re-run this thing mess around with this check
boxes a little bit and then let's go back and run this get list of sheets
let's see if it updated this so as you can see we got it updated nicely it lists what's
hidden what's not and finally to make this user friendly let's just make a couple of buttons here
so that's one let's do another one
move it here and at this point i'm just gonna go grab this get list of sheets function name
go back here and for this one i'm gonna click on this assign script paste it here
and then i'll go back and grab my second function copy it go back click on this and
assign script paste on this and now we should have two buttons here so now if i
wanted to unhide bunch of sheets i should be able to just check this boxes click on this
and then if i change some of these
then i can go back to my sheet manager and just click update worksheet list and see it should just
get me all that information and also what's hidden what's not and then i should be able to just
hide whatever i want so let's just hide everything other than the sheet manager hide and hide
and there you go thanks for watching and i'll see in the next video