- [Instructor] All right, so today
we're going to use Google Sheets to create a task list.
And when you check off the task,
it's going to cross off the name of the task,
and it's going to resort your list
and put it down at the bottom.
(gentle music)
All right. In previous videos, we've built task lists.
We put this cool formatting on it,
but then I had people ask
if you can make it automatically sort.
So they want the satisfaction of when you check something,
it goes down to the bottom
and that's something you can't typically do
in a spreadsheet.
So I looked around the web,
I found someone that had written a custom script,
which we're going to use to enable this task list
to sort itself, all right?
So let's go through how to do that.
(gentle music)
First thing that we wanna do
is we wanna get just a cool visual effect on it here
when you click on the status,
it just strikes through the name of the task.
So we're going to do that with conditional formatting.
We will highlight the range that we want to apply it to.
So that would be B2 to B8, we'll go over to the right,
conditional formatting rules are already up here.
You will get those by going to format
and then conditional formatting.
And we'll come over here,
we'll left click on, add another rule.
And the range of B2 to B8 is already in there
because I had highlighted when I came over to add the rule.
And the rule that we're going to use,
it defaults to is not empty, all right?
So that's why these are green already
because they're not empty.
So that rule is being applied, but we're going to change it.
Come down to the bottom, choose custom formula is,
and we get an empty box here to input our formula.
If we'd look at the task list, what we want to do,
we're going to write the custom formula
as if we're in cell B2.
So you always write the custom formula
as if you're in the upper left-hand corner of the range
that you want to format.
So in B2, we want to cross this off
if A2 is equal to true, all right?
So a checkbox is just true or false.
It's what's called a Boolean value.
If it's unchecked, it's equal to false,
if it's checked, it's equal to true.
So let's come back to this box
and we're going to say equal sign to start the formula,
in conditional formatting, there's always an if statement.
So you can say out loud if, but you don't write it,
so if A2 is equal to true.
It applies the rule as you type
and if you come over nothing's green anymore,
let's try it out, A2 is true.
Okay. So we know the rule is firing right now.
We want to customize the rule because green highlighting
isn't really a good way to indicate something's done.
I think a better way is probably for a strike-through
and maybe just make it a little gray in the back.
So let's try that. Check A2's done.
Yeah. That makes it go away a little bit.
But it's even turn the font grayish.
But not gray enough to blend in the background.
All right, we'll try another one.
Yeah, those look good, right?
So now your task list is looking nice,
but we want to add in the custom sorting
and that's where things get a little bit more involved.
Let's click done to get rid of our conditional formatting.
And typically with a spreadsheet,
you can filter it and you can sort it all day long,
but you have to actively do that.
So what I mean is, let's go to data
and create a filter on this,
a little bit more room.
So you can sort on status, right?
If I left click on this filter, sort A to Z,
it's going to put the completed task at the bottom,
but you have to do that every time.
So what users were asking me is that,
can you make it automatically sort?
So when I was first getting those comments, I thought, no,
you can't, but you can do it if you use Custom Apps Script,
is what it's called.
So let's undo this filter with the undo button,
and then we'll go to data and turn the filter off.
And now to load in a script,
we'll go to tools in the script editor,
and that brings up this brave new world of an empty canvas,
well almost empty.
We actually wanna delete this here.
I was not deleting that at first and it wasn't working.
So it has this empty canvas and it's waiting for you
to create custom rules.
Let's title the project, we'll call it Auto Sort Checklist.
No, let's just call it Auto Sort.
So you don't necessarily need a checklist to apply it to it,
which is something that auto sorts.
And we'll go to this page on GitHub.
I'll put the link in the description,
I wanna give credit to the person that wrote this.
I will click the word raw, just so I can do Control A
to select everything and Control C
to put it on my clipboard.
And we'll come over to App Script and I'll just paste it in.
So Control V is how I would do that with a shortcut.
So I've looked over this code.
I mostly understand it,
but there are only three things that it's asking you, okay?
So up top is some preparation, and it's asking you, hey,
what column are we going to be sorting this on?
So we want to sort it on the first column, right,
because we were looking at those check boxes
and this is which direction it should sort.
So think if we leave it on false,
it's going to put the check boxes at the bottom.
And how many header rows are there? There's just one.
So I think we have that all set.
We will click save, the auto sort is the actual function
and it gets triggered when you edit anything,
or when you open the sheet.
Let's first hit the run button
to make sure everything goes through okay.
Oh and it requires, glad I did that,
it requires authorization to run.
So we're going to review the permissions.
My account's going to come up,
I'm going to log into it and say, yep, that's fine.
I gave it permission, it's actually ask me twice
for some reason, so I went back and gave it two permissions.
Again, I reloaded the sheet and now let's click on it.
Right, it gives it a second and then it sorts it.
So actually I probably want it to sort the other way,
so we'll go back into it, the script editor
and it's easy, right?
So it asks me what order is sending is false
will change is sending to true.
It's fancy pop-up box, click save.
And if I check on one again,
it sorts them both down to the bottom.
In this next video,
we're going to look at them a neat application
of App Script, where you can sum or count cells
based on whether or not they have a note.
All right. So I'll see you inside that video.
Thanks for coming by. It's nice to have you along.