00:00 I'm building an automated to-do list template that is going to be pretty awesome. Here are a few things we're going to do.
00:10 We're going to make sure it looks nice. We're going to make sure it has the exact items that we want to be writing.
00:19 We want to insert a checkbox and this is what you normally do, right? You have a task. We're gonna write a to-do list and then if it's done, we check it off, right?
00:33 But we want to automate this. We want to absolutely make this the coolest, most automated thing ever. So one of the first things we're going to do is open our Apps Script and we're going to start automating.
00:44 We are going to need some on-edit automations. These are automations that happen when we edit something. So if we click say this checkbox, we change it from false to true, we will automate writing in a task and adding it to a checkbox.
01:02 So first off, let's name this to-do list code. Our function that we're going to use is going to be called on-edit and we're going to use an event here that we're going to know what that event is.
01:13 We can get things like the row, variable event dot range, not range, range dot get row. We can also get the column, we'll call it call, just a short version event dot range dot.
01:30 Get column. And so now we know every time there's an edit what is, where it is. We also can find out what the edit value is.
01:39 Edit value meaning what is it editing to. Go event dot value, I think it is. Not edit value, just value.
01:50 Then ah let's say we want to gate keep this on-edit and we only want to take some action once we know certain things are happening, like we want to automate the time stamp.
02:01 So the time stamp is actually just time stamp equals new date. We can format this if we want to, but for right now we're just going to get the original, whatever time is on our computer right now.
02:15 And we're going to need to know when should we be editing this or adding this time stamp. Well, we want to know that the row is greater than 1, which which means it's not the header row that we're editing.
02:29 We also, using the double dot. Double ampersand. We also want to make sure the column is equal to 1. That is because over here on our list the A column, or first column, has the checkboxes.
02:41 The last thing we want to make sure is that the actual edit value is true. So we can do true.
02:51 And now, once that is all true, meaning if the row is above 1, greater than 1, the column that we're editing is one and we're changing it.
03:01 Changing from false to true only, not true to false. Uh, sort of undone. But these are marking done. We're, we're marking done.
03:08 So we want to insert a timestamp. Well, where do we want to insert the timestamp? We'll go to spreadsheet, spreadsheet app dot get, get active sheet, whatever sheet we're on.
03:20 We will then, uh, get the range. The range is going to be the row we're on. The column will be actually 3 because we want to insert with the timestamp.
03:31 In the C column. We don't want anything else. We'll actually delete all the other stuff here. And we also, we can put this extra 1 and 1 which just means it's one row, one column size.
03:44 And then we're going to set the value. Set value of timestamp. I'm going to hit save, command s, or we can run, hit this button up here next to run which is save project.
03:56 Okay, now this is already checked. Let's check off this one. And we may have some errors, so let's go view those and see if it's just not saved.
04:10 Hmm hmm hmm. It is completing it, so definitely something's wrong here.
04:26 In order to see what we're, let's look at logger.log. Row and it might be this true actually. I think we need.
04:39 True. So let's uncheck those and we can check it again and see we get a date. We get time. Actually the whole timestamp function there just because it's this in quotes true.
04:56 Now, the next thing we want to automate is we don't want to be adding our checkboxes. We just want to add a task and have a checkbox.
05:01 added there. So, I'm gonna use this exact same syntax. If row is greater than one and column is actually two and we want to make sure that the old value is blank.
05:13 So we don't want to be adding a checkbox if we're editing something. So we want the old value which is going to be variable old equals event dot old.
05:26 I think it's It's going to be dot. old value. Yeah. So, we want old is equal to blank. Uh but actually, we want null here.
05:44 Not blank. Null. Cuz we want it to be actually nothing. So, if the column is the second column and if it's not the header row and we want the a checkbox here.
05:55 So, instead of column three, column one, insert checkboxes. So, let's see if this, we save it, make sure that orange goes away and add a checkbox.
06:11 There we go. We've added a checkbox and now we can hit that's done and we have timestamp when it's done.
06:17 One more thing we wanna automate is that when we delete a task, we want the date and the checkbox to be undone.
06:24 So, let's see what we can do. Uh we know we have this syntax here. So, instead of old, being null, we actually want our edit value to be null, meaning we're deleting everything in there.
06:37 So, we don't wanna insert checkboxes. We want to set, actually delete, uh clear data validations of the first one and we wanna set the value to nothing.
06:50 Basically, we're getting rid of that uh set value, basically getting rid of that checkbox. But we also want to do this for the third column, right.
07:00 So, Instead of row and one, we have row and three, and we set that to nothing as well. Actually, we can set that value, I think, to null.
07:09 Let's try null. So, anytime we, ah, delete an item, there we go. So, we want to add a task. So, we'll add the checkbox.
07:27 We'll click it done. And then if we want to delete a task. We add this task, that sounds weird. We click it done.
07:36 But then we delete it, and everything goes away. So, that's pretty cool. Automatic, automating these to-do list items to go away as well.
07:45 So, we want to also add some cool automations, like, let's send a daily email that ends up telling us what is the next item, or the first item on this list.
07:56 So, first item, second item. So we have two items, one of them both of them are unchecked, and we just want like a daily email that, whatever is our very top thing on the list, let us know what that is.
08:10 We have to do a few things. We can write the function, so we need to email every day. Actually, we just want to write email top task. We want to write this function.
08:22 Then we also want to create a function that creates a trigger. So we'll say email every day and we'll write this down.
08:31 We'll do as well after we test this email. So mail, we'll use gmail app. We will send email and we're going to send our email to, to someone.
08:46 We're going to have a subject and we're going to have a body of the text. Ah, we need these as variables, so variable two is equal to, let's just get the owner of the sheet for now, session, ah actually spreadsheet app dot get active spreadsheet dot get owner dot get email.
09:11 Again, this is something we can actually put a text in here if we want, but this gives us the ability to give this app script to someone else, ah so that they can set it up and it does, they don't have to change their email address, it's whoever is the owner of the spreadsheet is going to get these emails
09:30 . We're going to give a subject a name. Subject should be ah top task. We also want to make sure that we know what day it is, so we'll add plus new date.
09:46 So we'll add that timestamp there. We can format this as well, um actually we'll go utilities dot format date, get a new date.
09:56 We will say it's GMT and we will set it as just day day, month month, year year year. So that's it.
10:08 Ah what are we going to send in our body? So our body is going to be just text. We're not going to use HTML.
10:14 Actually we could use a little bit of HTML here if we wanted to to make it look nice, but we just want to add some text.
10:19 Your top task today is and then add top task. So how do we get top task? Let's write another function get top task and let's do that here.
10:32 So the result of this function variable top task equals get top task and we want to return top task. So what do we do?
10:46 Oh we want to go to spreadsheet app dot get active spreadsheet dot get sheet. We can get the sheet by name.
10:56 We could also, yeah we can call this tasks. Let's do that. Or items, to-do list items. Get sheet by name tasks dot get range.
11:11 We're going to get B, no A2 colon A. Get values. And this will be tasks. Actually it'll be check boxes, sorry.
11:25 And we were going to look for the first uh false one. So there's three options. That we have. We have true, false, and then null.
11:35 So if we look at this, we can do logger dot log uh check boxes. It's just going to be an array.
11:49 So let's run this get top task. We will have to authorize this. And see it is just an array. We're just looking for the top false one.
12:09 Uh so we go for check boxes for i. We'll do a little for loop i equals zero. I is less than check boxes i is equal to false, then return, not check boxes, but actually we want to do this for tasks.
12:45 We want to get the exact same row in tasks. Uhm b2. So we'll just get the name of it. So we'll get return tasks i 0.
13:00 So let's run this and see if we get any errors. But we probably need, want to log this. Logger.log. And we don't need to log the check boxes now.
13:23 So we're just going to get the top task. Oops. Ah, here we go. So we just added this. Bracket notation zero to make sure we actually get the item in there.
13:35 And we did. We got the first item. We actually got everything. So we don't want everything. We just want the first one.
13:41 So we'll break here. Um, actually I don't even think we have to do the break. We just return it. So we'll just return and that will be the break because it will execute something and be dumb.
13:54 So if we do something like get top task here, it will just return the top one. Let's Double check that, though.
14:03 We'll actually double check it when we run this. So we have our email. We're getting an email from the owner.
14:10 We're going to send a top task with the date and our top task is this. So let's run this email.
14:17 Email top task. Check it that it's running. And here we go. Your top task today is first item. Fantastic. Well, actually is it?
14:27 Yeah, it is. So let's run it again. Uh, I've checked off first item. There it is. Our top task is second item.
14:44 Perfect. So we are now able to send at any moment the top task. Uh, if there is no task, it will probably just not send.
14:54 Let's, it'll probably just have something. Let's see what that does. Actually, it says undefined. So let's make sure in our code we have a fallback here.
15:03 If it's, uh, else, uh, we will write return, uh, no tasks left undone. So we get to email top task.
15:19 Let's run it again. And now we get an email that says no tasks left undone. Perfect. We want to add something else to this body.
15:28 Uh, this body says your top task is this. Let's add a few more. A more pieces of information. We'll do plus and then we'll do slash n a couple times for a new line.
15:41 We'll also add the URL of the sheet that we're using. Variable URL equals spreadsheet app dot get active spreadsheet dot get URL.
15:53 We're gonna make sure that we have a link in this email, uh, to this URL of the spreadsheet so we know where we're going to go, can we actually, fix it, can we actually do the task and then check it.
16:05 So, we need to run an email every day but we don't want to have the user go over here to triggers, add trigger.
16:12 We want to be able to trigger it from somewhere on this sheet. So we're gonna create a little menu up here.
16:18 It's as, it's much easier than you ever think. We'll just go to bettersheets.co slash snippets, grab the top one, it'll be on open.
16:27 You'll say custom menu. We'll literally just copy this code here, function on open, we'll come back to our code. And above our function, all the way at the top, we'll have on open.
16:37 We have, we'll call this automation, automation menu. Our first item is going to be called, actually to the user we'll say, set up an email every day for top undone for top task.
16:54 We'll call this email every day. This function here will create, will run this function. You may want to send a test email, so we want to say, send test email, which will run email top task.
17:10 It'll just run this one right here. And this menu is going to be basically right here next to help, to the right of help.
17:19 But it will only happen when it's opened. So right now it doesn't show it, even if we saved it, we'll have to refresh it.
17:26 But we want to write this email every day function. This is going to be spreadsheet app. Actually, it's going to be script app dot new trigger.
17:49 The trigger that we're going to put into the text here is the function we want to run. So it'll be email top task.
17:57 It'll be time based. Uh, we want to do every day. One day. Uh, at hour. And this is going to be a number.
18:09 It's going to be a number from zero to twenty three. If it's zero, it will run between midnight and one.
18:19 it'll be one a.m. to two a.m. If it's two, it'll be run from two a.m. to three a.m. So this task that we want to email, we want to do it like around five, six, seven a.m.
18:29 So it's in our inbox like before we start work. So let's just do a five minute drive. And then we want to create it.
18:37 Now, let's test this. I'm going to run this email every day function. I'm going to run it from here. I'm going to make sure it's saved.
18:45 I'm going to click run. See if we have any issues. We're going to make sure we have the correct authorizations before we do it and see if we get any errors.
18:57 Worked. Okay. Looks like We go over to triggers and do we have a trigger? Yes, we do Let's edit this and see what has happened.
19:07 So we've chosen which function to run. Email top task. It's time driven. Day timer 5 a.m. to 6 a.m. Perfect.
19:15 That's it. That's all we need to do to automate this email. We can do a couple more things like we may want to delete this trigger at some point.
19:25 So let's automate that as well or at least allow someone to delete this trigger. So we want to say function delete email trigger.
19:35 Uh here we're going to add it to our open menu. We're going to add item. Just copy it. And then delete daily email.
19:47 Or not delete. Stop. Stop daily email. And we're going to call this delete email trigger. So how do we find this?
19:56 Well we do script app dot, um, it's not find triggers, get project triggers. And here we're, this is going to return a list of all of the triggers.
20:11 So we want to iterate through it. So we're going to go triggers, variable triggers equals. And we're going to say for i equals zero, i is less than triggers dot length.
20:27 We are going to add another i plus plus. And here, for each one, if, triggers zero, or not zero, i dot get event hand, get, not event handler, handler function is equal to the email top task.
20:48 So if we create an email top task variable, then we want to delete that. So we go to trigger, triggers i dot, Actually, not delete, sorry.
21:04 We do script app dot delete trigger, and inside of here we call the triggers i. There we go. So in making sure that we're getting the right function, and once we find that function, delete that trigger.
21:21 No matter whatever other triggers there are, uh we can now email top No, email every day, we're going to create that trigger.
21:33 So we can, again look over here at triggers and see is there an event. Yep, there is a trigger. Go back to our editor.
21:40 Now we're going to choose to run the delete email trigger. Run. See if we have any errors. We don't have any errors, but do we have any triggers?
21:50 We have no triggers, so we have successfully deleted it. So now let's close our app script. Let's go back and refresh our sheet.
21:57 So now we have up at the top automation menu. We have three things. We can set up an email. That every day is going to send us the top task we have yet to do.
22:08 We can also send that test email and we can also stop those daily emails without having to touch the app script.
22:13 So this in and of itself is a totally automated checklist. We can start adding. Add another task. One more. There it is.
22:26 Our checkboxes are added. Checkbox added. Question mark. Yes, the checkbox is added. Check it off. We are done. Now we want to send an email and make sure that we get an email of our top task every day.
22:40 We can do that. Isn't that awesome? We are now automating our to-do list. If you want to add any more automations to this to-do list template, let me know.
22:49 I'm more than happy to continue improving it. Some interesting things that could be added that I'm not going to do in this video are like adding priority drop-down menus to the right here, then sorting those automatically every single night, so that even if you change those items, uh different priorities
23:05 , that every day you see the top priority. Or even sending like a random uh list, a random task. Instead of seeing the entire list and instead of only prioritizing the very top one, maybe you want to send a random undone task.
23:21 You can do that with Apps Script with a little bit of math and a little bit of randomness. Those are some ideas we may add to this to-do list template, automated to-do list template.