in this video we're going to create a button to check or uncheck a bunch of
check boxes you have in a column so basically the whole idea is going to be instead of going
to this column and checking each one of these boxes one by one or checking them for that matter
we're just gonna create a button that we can click on and make that happen and to be able
to do this you need to first understand the idea of the checkbox a checkbox will be a value stored
in a cell which will be basically true if it's checked and false if it's unchecked and with that
in mind we're going to go to tools and script editor i'm going to start by naming the script
let's zoom in a little bit here so what i'm going to do first i'm going
to just create this piece of code here we're going to take the spreadsheet app
and in that we're going to get our active spreadsheet that should refer to this entire
spreadsheet with all its worksheets in this i want to refer to this worksheet that's called my work
so i'm gonna copy that name go back to my script and do well let's actually save this in a variable
and then on the next line we'll create a variable for our worksheet
and to get to our worksheet we'll take our spreadsheet and get sheet by name
and basically just put the name of the spreadsheet in quotes like this
now if you just wanted to work with the current worksheet instead of doing this
you could do get active sheet like that and that should be
just the current worksheet that you're on i'm just gonna go with the name of the worksheet my work
so that's what i'm gonna do so now in this worksheet if i wanted just to basically check
let's say this box in see row 10 and this column g so column g would be one two three four five
six seven so column number seven row ten what i would do i would go to this worksheet
and do get range and i would do row 10 comma column number seven
that would be the cell i'm targeting and then i will do set value of that cell to be i'm trying
to check the box so i'm going to set it to true if i was trying to uncheck it i would set it to false
so right now let's just test this script to make sure it works so i'm going to save this so let's
just go ahead and check see this is not checked right now i'm gonna go back and run the script
do all permissions and that should run that script let's go take a look
see this box is checked if i go back and change this to false
save this and run this that should uncheck that same box so now we have a script that can check
or uncheck a single box so let's change this to true to check the boxes instead of unchecking them
so what i'm going to do now i'm going to go back to this get range and instead of targeting
a single cell i'm going to convert it to a range so i can do that by doing a comma after this and
then i can do how many rows do i want to select so for example if i do 10 that would be 10 rows and
that would be 10 rows starting from this 10th row in the seventh column so that would basically go
row 10 like here column number seven this cell and then 10 down now what i'm going to do instead
i'm going to start with this first cell and then say let's go like 12 down so to do that i'm just
going to change this to 2 instead of 10 to start from the second row and then i'm gonna do 12
and that's actually not 12 i think that's 11 because that's 12 rows we have the header so let's
just change it to 11 for now now let's try to just run this just like this to see what happens
so as you can see that went through let's go back and check and you can see the whole thing is now
checked now i want to add another piece to this if we go ahead and add more
rows to this and then we have a situation like this now if i go back and rerun this function
because now we have more rows these two are no longer checked so instead of hard coding
the number of rows here to 11 let's just say go to the end of the data and we'll do that by
taking this worksheet instead of this 11 and then this will have this get last row method
which should basically get us the row number for the last row in this worksheet so that is
going to give us 14. now one thing you're going to notice here if i count how many i have here
starting from the top like 1 2 3 4 5 6 7 8 9 10 11 12 13 it's not 14 it's 13 because we have this
header on top so i need to compensate for that so if it says 14 the actual number should be 13
so i'm gonna do this then minus one to get to that 13. so this way now if i run this function
see that will just get the whole thing and if we do add or delete
more lines
like this if i run the function
you'll see it will automatically figure out the height of this data and basically just check all
the boxes so so far this function should check all boxes and that's what i'm going to rename this to
i also want to have another one that is going to be unchecking all the boxes so i guess i'm
gonna rename this function to check boxes and what i'm gonna do here i'm gonna pass
an argument to this function i'm gonna call this check so that argument that i'm gonna pass
i'm just gonna replace this true with that argument check
and the reason for that is that now i could reuse that same function to check or uncheck those boxes
by using that function within other functions so i'm going to create two more functions here
one is going to be to check all the boxes and for that we're going to take this check boxes function
and because i want to check those boxes this should be true so i'm going to pass
true to this and then i'm going to just copy this
paste it below and this will be uncheck all boxes and we'll do false
so now if i run this uncheck all boxes function by basically selecting it here on top if i run that
see that should uncheck all the boxes and if i run this check all boxes function
that should check all those boxes so not the only thing left to do is to just assign these
two functions to a button so it's easier to use so i'm going to copy this function name
check all boxes go back to this and i'm gonna add some sort of drawing
and we'll just do some formatting here
i'm going to save and close this
i can zoom out at this point and then i'm going to make another one for on checking
and just put it in here doesn't matter and what i'm going to do here i'm going to click on
check all click on this little icon on the right corner and assign script and paste the script for
well apparently it's not saved anymore let's go copy it again
paste it here check all boxes press ok and i'm gonna do the same for the second one so
this one is on check all boxes so i'll copy that go to assign script paste it here and press ok
and we can close this one at that point so now we have two buttons if i click uncheck
that should uncheck all of them if i click check that should check all of
them and again just to test this if we check a few then do uncheck all see it's gone and
if we do check all it just works and that should be it for this video thanks for watching
please subscribe and i'll see in the next one