in this video let's add functionality to our data to be automatically sorted when we enter new data
so let's say we want to have our data always sorted by sales amount from highest to lowest
or really any other column you might want to sort this by for me i'm going to go with the
sales amounts so first of all let's just go ahead and sort this data so i'm going to take this
go under data sort range check the box choose sales column and do z2a to have that sorted
from the highest amount on top lowest amount all the way down so basically what i'm trying
to do if i enter another sale here i want that to automatically be sorted and go where it needs to
go in this data now if you do something like this i suggest to have the column you're sorting by to
always be the last column even though it doesn't really matter but for best user experience that's
probably what you want to do and you'll see why when i go through this in a second so again the
idea is gonna be i'm gonna come in here and enter some new date or whatever this is going to be and
then some more information and then at some point we're going to enter some sort of sales amount
and when we do that let's actually make this currency and when we do this we want this to be
automatically going up here where it belongs so let's first of all get rid of this and
i'm gonna go under tools script editor and we're going to write a script for this so
the first thing i'm going to do i'm going to rename this i'm going to call this auto sort
and first let's just create a function that will sort this data so what i'm gonna do i'm gonna
just go grab this data set so we're gonna grab it starting from the second row not the headers
and then all of this going until the end of this data set there are a few different ways you can go
about this depending on your spreadsheet setup i have a video covering how to find the last row in
your spreadsheet in many different ways depending on your situation you can watch that to modify how
i'm getting the last row here but for this one since this is going to be just this data set i'm
just going to keep it simple just get the last row so what i'll do i'll just get my spreadsheet
so i'm going to get the active spreadsheet
let's just save this in a variable and then let's also make sure we get the worksheet in a variable
so the worksheet is going to be this worksheet let's change the name of this
and i'm going to use that name
so i'll take my spreadsheet and get the sheet by name and then
pass the name of the worksheet in there so at this point i would like to get the range of my data
so the way i'm going to do that i'm going to take the worksheet and do get range and then start from
row 2 column 2 so row 2 column 2 right here then i need to set the number of rows and the number of
columns the number of rows is going to be total number of rows in our spreadsheet minus one so
if we have 14 it's going to be 13 here and to do that we'll take our worksheet and get last row
and this is where i was saying if you have a different situation you can watch my video about
getting last rows to see how else you can get this last row in a different way
but for me that's gonna be that and finally the number of columns
i'm just gonna hard code that one two three four five six seven
so seven columns so that's gonna be my range of data so now i should be able
to take that range and on this we should have a method sort and that will see accept a sort
object let's just scroll down to see if there's a good example here there it is here's a good
example column two ascending false i'm just gonna copy that paste it in here
so we're trying to sort by column number seven because seven would be 1 2 3 4 5 6 7 sales amounts
and then ascending no true would be lowest number on top false would be the highest number on top
so that's that so that should sort my data now let's first of all test this to make sure it works
so i'm going to go in here let's just change some of these numbers
something like this so i'm just going to go ahead and run this function auto sort right in here
and for me i did run some scripts in the spreadsheet so i'm not getting
any review screens here if you do you have to just go through it and just give permissions
so let's go back and take a look so it's sorted but not the right way it seems like
it's sorted by this column so let's just count this again one two three four five six seven
so this column seven apparently that refers to the actual column in our spreadsheet so we
need to do eight to get h okay we'll do that let's change this to eight run this again
okay good now it's sorting our data using this column now what's left to do is to make sure that
this automatically happens when we enter some data and we're going to sort when there is a new sales
amount entered right in here and the way i'm going to do that
i'm going to use on edit function and this is a special function
that runs every time something changes on your spreadsheet so i'm going to go ahead and accept
this event object here and inside of that event object there is going to be this
range that basically will tell us what cell or range was changed so we're trying to make
sure that we only run our sort function when the change is happening in this area
so for now what i'm gonna do i'm just gonna check for the column
so i'm gonna say if this is column eight and then it's starting from row two or down let's
actually do this thing so the way we can do that let's actually save a couple of variables here
so we'll save the row and the column
from that range and then we'll do an if statement and we'll say if rho is greater than or equal to 2
and at the same time let's actually do the column first so we'll say if the column is equal to
eight and at the same time the row is greater than 2 then we want to be able to do these things or
we can just do the opposite here and then say if it's not this so the way i can do that i'm
just going to do not equal to and put this whole thing in parentheses so if it's not in that area
let's just return and not do anything else so now we can move on to say let's actually do things
in case it's in that range and what i want to happen i want to actually auto sort
which is this function over here i'm going to change this a little bit but for now let's just
save this so i'm going to save this go back and let's just try to enter something here
so as you can see after i enter the amount
immediately that went to the top now what i'm gonna do i'm gonna go back and move all of this
inside of this auto sort function because i want to have this on added just called a function and
that's it so the way i can do that i can just accept this e inside of this auto sort and then
also provide that e when i call the function and then just move all of this logic right here
right here on top and this way it's going to be cleaner for on edit function in case
you want to do something else which means now i should be able to use the same thing
and as you can see as soon as i enter that sort now if you remember when i started i said it's
best to have the column you're sorting by to be last and the reason for that
is that if i start now entering this sales amounts first and i enter some amount then
it's immediately gonna sort the data and then i have to go back in here and enter the rest
of the data which might not be the best experience for the user and this is why you probably want to
have that as the last column so as you enter your data you get to that column you enter the data and
it sorts now the last thing i want to do here i want to make sure that i limit this only to happen
when we're on this particular worksheet i don't want this to try to sort our data when we're on a
different worksheet in the same file so for that i'm going to go back and add something to this
condition and what we'll do we'll get our worksheet so we can do that by taking that e
and in this one we're gonna have the source which will refer to our source spreadsheet
which is gonna be this get active sheet like this ss right in here so uh for that let's actually
move this variable over here like that now i'm gonna have to take this line out in a second
but before i do that let's actually comment this line and go back and grab the code we're
going to need because this is going to give us autocomplete so i'm going to take the active
sheet what worksheet is currently active and from that we're going to get the name of the
worksheet like this so let's grab this and move this up in here and we'll just set a variable
and i'm gonna
remove the comment here and get rid of that and then what i'm gonna do i'm gonna take my
current sheet which will be the current sheet name actually let's actually separate this
let's do this one to be our current sheet and this one will be our
current sheet name which we can get like this so now we can check
right in here let's check it in the beginning if the current sheet name equals to
this then in this case we'll actually do the rest in all the other cases we don't want to
run this so now i can just also get rid of this part the one that sets the worksheet
because now we're gonna have the current sheet being the right worksheet so we can just simply
say current sheet dot get range and then current sheet get last row which would
refer to our current worksheet which should be this one and then we'll do our sorting like we
used to do before so i'm just going to save all of this let's test this and make sure it works
and it does thanks for watching and i'll see in the next video