all right so in this video let's try to see how we can actually figure out last rows last columns
maybe first row or first column in our data so let's start by removing some of this so so far
i'm going to start with this workbook variable we got here so from this workbook we're going to do
get worksheet and we're going to get this worksheet data tab which is this tab right here
and at this point the source sheet will be the variable that's going to hold this worksheet for
us so if we go here in our next line and do source sheet dot and do get we should be able to find
this method called get used range and as you can see it has this boolean optional in it and i'll
get to it in just a second so for right now we're going to leave it empty like so we want
to store this in a variable for now so let's just create a variable i'm going to call it used range
and for now let's try to cancel log that used range and see
what range that actually is so i'm going to do console.log
and here we'll do used range now that's going to be an object so to be able to
see what that range is let's try to do something like get is it a1 or what is it called let's see
this this should work so let's try that so hopefully that will be
readable range for us so we can see what it is so i'm going to go ahead and save this and
run and you can see we got this through this console log so using this console log we can print
things in our console if for whatever reason you don't have this showing up you should be able to
open that by clicking under this more options and then logs and that should open this panel so as
you can see with console log we can basically see that the range here is from a4 through g16 so a4
through g16 so what that means is that it was able to find us the range of cells that has been used
in this particular worksheet and you can see on the top here the first cell we have here is this
cell in this corner and then the last column we got is this g column and then finally the last
cell used in the spreadsheet is this color-coded cell so therefore this is the range it found
now even though there is nothing in this cell it's just formatting it's still thought
that this is used this is where that other parameter comes in here so i can pass true here
and what this means is basically only look at cells that have something in them some value in
them we don't care about their formatting so at this point if i rerun this let's save it first
see we get a new range and you can see it goes from a4 through g11 so from here through here
and now using this now we could figure out what's the first row what's the last row what's the last
column or first column in this range so the way we could do that we could take that used range
and on this we can do get last row and this get last row is gonna take this used range
and out of that it's gonna get this last row now at this point if we could just figure out
which row number this is we should be able to get the correct number so the way we can
do that we can do get row index and i'll just go ahead and save this in a variable
so let's try to console log this last row so you can see what's in it
so i'm going to save this run it let's click away
so now if you look here i got 10. so if you look here it looks like the row is actually
11 right but you want to remember that these are indexes and indexes for us start from zero
so the way this goes it goes zero one two three four five six seven eight nine ten
so with that same logic now we could get different parts of this so for example
if i wanted to get the last column i would do some variable here let's just do short
last col and here what we would do instead of getting the last row in our data range we'll get
the last column and then we can get our column index and that should get us the last column so
let's go ahead and cancel log this now so you can see what that is i'm going to save this and run
and as you can see we get six and again the column here is actually going to be seven
with zero count zero one two three four five six that would be the last column here let's see if
we can also get the first row so again we're gonna get used range and for this one there is no way to
get first row method here but if we just do get row index directly on this see if you look here
what it says it says returns the row number of the first cell in a range so that would be this
top left cell that should be the correct row for us so if i go here and do const first row
and we simply just console log that so you can see what's in it that should get us what we are after
0 1 2 3. that's where we are and then finally we can get the first column using the same
sort of logic so if i just repeat this
and simply just change this to get column index
similar to what's down here only we'll do it on our used range and it's going to take this
first cell and get us what we actually want to get out of it so if i do this save and run
that gives us 0 because our data starts in the first column so now let's try to use one
of those so let's say what we want to do let's actually simplify this let's delete some of these
so let's say we want to be able to run some script that will find the last
row in our data go to the next line here and place something here in this next line
so for that we can figure out the last row so i'm not going to use
all of this we'll just use that last row part and that last row is going to give me the index
if you remember it's going to give me 3 so 0 1 2 3. so just to refresh your memory let me run this
c3 i want to now go here in this next line what i can do here i can take that source
sheet and do get range by indexes we have to give it a starting row number if i wanted to start from
here that would be 0 1 2 3 4 right so here we get three but what we need is the one after four so
what we could do we can simply take that last row and add one to it to get to the next line comma
and then i got starting columns so that's gonna be always the first column so we'll just do one
then we can do row count so i'm just planning to add a single line to this so i'll do one row
comma and i'm gonna be adding three columns so i'll do three so this will be the range
here so we'll just save this in a variable so we'll say next row range or something like that
and at this point now within this we can set some values so we'll do set values since it's not just
a single value i want three columns of data i will do set values and here we're gonna pass an array
and it's only gonna be a single row of data so it will be one array in this array and within that
row we're gonna have three columns so the first column let's say i want this to say line five then
this should be let's say 22 and this should be 33. i'm gonna save this we'll go ahead and run this
and what should happen it should hopefully find this next line and put that information there so
it says the number of rows or columns in the input array doesn't match the size or dimensions of the
range so basically it's saying that whatever range was selected here doesn't match this and i think
i can see what i did here i did two instead of typing three here there should be three columns
save this rerun that and now it does match so i made a mistake here this should be column
zero not column one so let's clear this let's try to rerun this one more time so zero index
we're gonna run this there it is we went to the next line we add this new line now if i
run it again it should automatically now find the next line after this
and place this information in our next line and every time i run this the line
is gonna be the next line after that because we're dynamically figuring out
the last row and then go one row down over here and we basically add some information to this