all right so in this video let's use some check boxes to delete rows so what i'm going to do here
for this data i'm just going to add another column on the left and just add some check boxes here
i'll go under data validation
check box and reject any other input save and i don't need this first one so i'll just clear that
so at this point i want to be able to just go in here and check whichever rows i need deleted
from my data and run my script and take care of that so one thing you need to keep in mind
when you're dealing with deleting rows is that you have to be careful if i just do a duplicate
the way you go about deleting rows because usually when you delete rows
you're gonna delete them by using the row number let's just go through a basic example let's say
you just wanted to delete this empty rows so that would be c8 and then 10 and then 12
right so the tricky part here between these three numbers is how you go about deleting those rows
if you start deleting this from the top you're going to start having problems with this so let
me show you what i mean so if i want to delete row 8 then i'm going to go here and delete row 8.
and now at this point if i delete row 10 see that's not the right row anymore because
we deleted the row above and this shifted so now this row 10 is no longer the right row to delete
so for this reason i'm going to undo this when you do this sort of stuff you want to go
bottom up instead of top down so what that means is that instead of starting from 8 and
going to 12 i'm going to start from 12 and go to 8. and by doing this if i go from this way
see i'm going to start with 12 and delete 12 and then i can go to 10 and delete 10 and because 10
is above whatever i was deleting before nothing is changing in this section so i'm good and then
i'll go to 8 and delete 8 and see it works out so if i go bottom up it works fine if i go top
down then i have a problem so now that we know this let's do this with a script so i'm going to
go ahead and do some check boxes it can be really any rows even though i'm selecting the blank ones
so i'm gonna go to tools and script editor and what we'll do we'll just first of all get
a reference to this worksheet so i'm going to take spreadsheet app do get active spreadsheet
and let's just save it in a variable ss for our spreadsheet then we'll get our worksheet
so i'll take our spreadsheet and get sheet by name you could also do get active sheet
if you wanted to get the active one but i want to refer to this particular worksheet name so my work
so that should get us the worksheet now this worksheet should have a method to delete row
where you just pass the row number unit deleted so to be able to use this though we need the row
numbers that should be deleted and we're going to get those by tracking these check boxes in
this first column so what we need to do we need to get this a column and everything in that and get
which rows have the checkboxes checked so let's just get the data from this column a
and for that we'll take our worksheet and get
the range and i need the whole column a so i'm just going to keep it simple and do this
that should give me the whole column a and for this i'm going to get the values
from that range so at this point if i just do a console log of this so you can see what's in it
so i'm going to run this function it's called my function for me
now when you run this script you're likely to get a prompt to give permissions to run it
for me i've done this before i don't want to open a new project every time i do videos so it's not
gonna ask me and what you're gonna see here we get this array of arrays and basically this first one
is blank then we have all these falses which are basically check boxes that are not checked
and then we have this true see true true those would be check boxes that are actually checked
so now let's take this range of data over here and look through it and what i'm going to do
i'm going to loop backwards remember because when we delete our rows we want to go bottom up so to
do that i'm going to do a regular for loop and what we'll do here we'll start with a variable
and initially i'm gonna start from bottom so that's gonna be
this length the length of the array over here minus one because array indexes start from zero
so if we have three things let's say three rows in our spreadsheet then this is gonna go zero one two
so when we get the count of three we're gonna go up to two because we start from the second index
so that's that i'm gonna do then semicolon and we need to keep looping this i
until we get to zero so while this i is greater than or equal to zero
we wanna keep doing this and then finally we're gonna decrement the i
by one so i minus minus for now let's just do a quick little thing i'm gonna do a console log
and what i'll do here i'll console log two things so i'll console log the i which is the index
and i'll also cancel log from our array whatever is in that position i
like this so we're gonna cancel lock the i here and from this array that we get in here we'll get
in this eye position the item so let's get rid of this console log and run this and see what we get
so just to show you here see i got 34 false 33 false and this false is in this array
sort of structure if you look here now i want to get that false to just be false without this array
structure so what i can do here i can just go inside of this which gives me false in an array
and basically just get things in the zero index which is the first item in that array so by doing
this if i just save and run this see now i don't get those brackets because it's just the false
so now let's take a look what this does right see it goes from 34 up to zero let's go take a look
at our spreadsheet see in our spreadsheet we get 35 up to one remember indexes in array
start from zero so basically in the array this goes like zero one two three and this is going
to be 34 instead of 35 because we start from zero instead of one so let's just try to find the first
one that's checked from the bottom here right we start from the bottom let's find the first true
there it is see it says 11th index is checked and then ninth index is checked so if i go check where
is our checkbox see that's 12 and then 10 again that offset of 1 because of that 0 index in the
array so if we want to basically match those with actual row numbers what we're going to have to do
we need to add 1 to this i
so i'm going to do that and save let's run this again just to verify that this works
so now if i check my true boxes
see i got row 12 is checked row 10 is checked and row 8 is checked so 8 10 12. that's correct okay
so basically by doing this we can say that this is going to be the row number and this is going
to be the actual checkbox whether it's checked or not so based on these two things we can now
use them in an if condition so i can just say if so i want to see if the box is checked and
if you remember this right here gives me true or false if it's checked it's going to give me true
so i'm going to copy this and paste it over here and then i can just check if it equals to true
meaning if it's checked then we want to delete the row right and the row is going to be this i plus
1. now we're going to delete the row from this worksheet so we'll take that ws and do delete row
and what we need to do is pass the position here which is going to be this i plus 1. so i'm going
to do i plus 1. so we don't need this console log anymore i'm gonna get rid of that so with this
let's try to run and see what we get so before we run let's just check how many lines we have so
with all the blanks and everything we got 15 lines so let's just go ahead and type 15 here so if we
delete this check boxes which are three boxes we should end up with basically 12 lines let's
go check if that works correctly so i'm going to take this function my function and run that
so that ran let's go check the results see empty rows are gone and we got 12
rows total excellent so that works just fine so finally what i'm gonna do i'm gonna assign
this to a macro so we can easily run it from someplace in here instead of
going in the back end and running all of this from here so we could create a menu do all of this but
i'm gonna keep doing all that and do this with a quick method so we'll just go in here under tools
macros to record macro and what i'll do in here it doesn't really matter i'll just select something
and hit save and call this delete checked items or rows or something like that so name the macro
you can also assign a shortcut if you wanted to i'm not gonna do that i'll just hit save
and i'm gonna edit the script and what you're gonna see here is we got to this
this is the code we wrote but now there's this macros dot gs and if i click in there see it's
basically me activating that cell that i recorded with that macro so i'm just going
to delete those steps inside of that macro go to our code copy all the code in our function
not including the bracket for the function and the opening of the function go to macros.gs and
paste it right in here and save it and then go to code.gs and delete all of the stuff
from here so now i have that in here i'm going to close this
so what we can do now we can just check whichever lines we need to delete it go to tools macros
and see there's this delete checked rows and again we can do this as many times as we want
and that should cover this thanks for watching please subscribe and i'll see in the next video