Add New Task when Task Completed

0:00 We have a task list here or a to-do list, and we may want to not give ourselves every single task we ever have. We may wanna have some in the reservoir or another table where we just see a few things, okay? We can get those done and then once we complete them or check them off, we'll add another one here. So how do we do that? We'll do that with a little bit of app script . Let's go to Extensions app script . And the very first thing I want to do is I want to rename this function on edit with a capital E. Now, this is a simple trigger . We don't have to do anything else. We don't have to install it. We don't have to literally do anything else. Just name this on edit. We can only have one in our project, but it will execute. Whatever is inside of these curly brackets, every single

0:49 time any cell is edited. Okay, so that means we need to figure out how to look at column A or the first column on the to-do tab. So let's do that. Let's go put an E here, or event. It could be any word, any variable. Here I like to do E 'cause it's simpler to type, but for clarity, maybe we say event. And so what we need to do is say variable. Row equals event range . Now we're just saying hey dot range , meaning inside this event, let's look at the range and then we're saying do get row and we get a column two event. Do range . Do get column, we know it. Do we know what sheet we're on? Well, we'll go active sheet as a variable equals spreadsheet .

1:36 App, duck. Get active sheet , duck get name. So we know what tab are we on? If we're editing on tasks, we don't wanna do anything. We only wanna do it on to do. So how are we gonna say, okay, we know the row, we know the column, we know what sheet we're on. Now if we're on the right sheet, what are we gonna do? We're gonna use a little if function here. If, and in parentheses we're gonna say active sheet , double equal sign is equal to to do. Now that only means we're going to be able to tell what sheet we're on, but we also need to know what column we're on. So we need to do double ampersand. This is an and basically it's saying, we wanna know is the active sheet to do

2:23 and is call or column and we can edit this any phrase we want here, we just have to use it exactly the same column. Is it equal to one? Now, this might be a huge surprise to you that this is not a, it's okay. Basically in Google Sheets, like the sheet itself, in the spreadsheet , we have a column A, but if we do equals column if, and it's A one. So A is one, B is two, and so on and so forth. So in our app script , we need to be using. Numbers, not a or a colon A, we can't do that. Just a little fun tip there. So we're making sure that the active sheet is to do, we're saying is it a column and bro is greater than one.

3:10 Why is that? That's because on row one we have a header , so we don't really care about that. We don't wanna know. We just wanna know, is it a two down? This is going to tell us it is. The other thing we have to do is if we uncheck something. Meaning, oh, we, we sort of screwed up. Maybe we didn't actually complete that. We don't want to actually add something new. So every single edit we're going to have true or false. So we actually can get that with variable value equals event value. And now we can say andand value is equal to true. We need one more thing. We need to add these curly brackets so that if all of these things are true, let's do something. So what is that thing we're gonna do? Let's call this project new task.

3:56 I keep looking at that and I'm like, ah, I don't wanna call it untitled. All right, what are we gonna do? Well, we need to go over to tasks. We need to take maybe whatever the first line is and we need to move it over to the last line of to do. Now, that probably sounds like a lot of stuff and it's just a few lines of code. Let's do variable tasks equals. Spreadsheet app dot get active spreadsheet , get sheet by name. All caps tasks, in quotes. Now that's gonna allow us to look at that sheet called tasks, and we have a tasks list which is equal to tasks dot get range , a colon a. Get, oh, actually, we might not need this task list 'cause we actually don't really care what's in the entire list. We just care about a.

4:41 And we care about getting it to, to-do list whatever is the last row plus one, because this is gonna get us the last row. Let's get the last row for to-do. So, we're gonna do exactly this, but we're gonna call this to-do. And change the sheet name to do do so we do not have to keep typing all of this stuff. So we're gonna say to do or variable last row equals to do do get last row simple there. So we know where we're gonna go. We're gonna go todo dot get range . The row we're gonna go on to is last row plus one. Lemme put some spaces there so you see it a little better. The column is gonna be two number of rows is only gonna be one number of columns, gonna be one dot set value is going to be task item.

5:31 So what is that task item? Variable to task item equals tasks that get range . Here. We can do a one. It's possible, but I also like to keep the uniformity of this where you go to row one, column one, and we're gonna get value. So we're getting the value of whatever is in a one or one, one comma one, and we're setting it whatever is the last row plus one. We have one more thing to do here. Actually, two more things to do is. In this first column, just in that first uh, cell, we want to insert check boxes, and now what's gonna happen? All of this code is going to get us whatever the task is.

6:17 Move it to to do, add a checkbox here, but we want to delete that row. So that's the last thing we need to do is to do delete row . And this is only gonna be a single number and so it's gonna be one. So we're just gonna delete that top row. So let's save this all and let's see if it's working. Again, we do not need to. Add any installable triggers or anything. This on edit is going to work. Maybe, let's see, we're gonna say rename sheet one to something like masterpiece. If that's the case, we'll see it there. And there we go. We see it there and I think we deleted the wrong row. Yes, this should be tasks. What a funny error there. But yeah, we should be deleting the tasks.

7:04 Okay, let's save that. Let's uncheck it now, unchecking again will not add something, but let's check, fix the formula . And we have the same exact task, but now it's deleted. That's perfect. So we can delete this row and now deleting that row or editing something in here, automate something totally necessary. Even editing that is not moving anything over. It is only moving over if we made coffee before debugging. There it is. And it is not at the top of our task list. Perfect. Right. We've added a new task when a task is complete and done a bunch of other stuff. Right. And add a checkbox . We've deleted it from the tasks list here. Perfect. Thanks for watching. And if you're interested more in this kind of.

7:51 Automations and coding. I have a course over on Udemy. I'll put the link in the description below. It is a three hour course that gets you through the main stuff. It explains in detail all of these things like dots, parentheses, square brackets, curly brackets, but it also just shows you really cool things like pulling data from other things into Google Sheets. Putting stuff from Google Sheets into other things on the internet. It's really, really cool. Check it out. Master spreadsheet Automation over on Udemy. You are watching better sheets here on YouTube. Make sure you check out this video or this video and subscribe right now to get more tips, tricks, how tos, get more out of your Google sheets than you ever have before. I'm excited to be making a ton more videos here.

8:38 Ask me questions down in the comments and I will answer them in future videos. But for right now, right here, one of these videos is gonna be your next Google sheet.