Magical Checklists That Archive Done Items

Intermediate

About this Tutorial

I'm sharing a magical checklist that will make your sheets so darn amazing. We'll start with a to-do list on a Monday and check off tasks to see them move to a "done" list. I'll guide you through the process of creating an "on edit" function in Google Sheets using app scripts. By the end, you'll know how to archive completed tasks from your to-do list. Get ready to boost your productivity and stay motivated! 🚀

Sheet Resources

Access Sheet

Copy Sheet

Preview Sheet

Video Transcript

<div>00:01 So I've been posting a lot on tiktok lately and apparently checklists are what people want to see so here on better shades I'm dropping one of these videos that I think is gonna like make your sheets so darn magical What we are gonna do is in this video we're gonna go from we're gonna have a to-do list 00:19 say it's a Monday and we're gonna check something off we want this thing this whatever task we do to end up on a done list because honestly whenever I get stuff stuff done it motivates me to do more things when I see what has been done and we don't necessarily maybe we don't want to save it on this  00:40 particular Thank you.

Page we want to say okay when we write this blog check here and then what's going to happen what we'll do in this video is we're going to take this text move it well actually not move it we'll copy it to the done list then we will come back here and delete or clear the content 00:58 of the B4 cell here whatever Herself. All is checked off and then we're going to also uncheck this so let's do that what's we have some other app scripts here written. 01:09 We're going to edit those and move on with our lives.

We have the time stamp we did it on the TikTok video we we made a time stamp video we're using on the on edit so there's all already something here. 01:23 I'm going to actually start from scratch for this video so I'm going to change this to on edits. That just gets it out of the on edit. 01:33 There can only be one on edit in a sheet in an app script . Let's just create a new script here and call this move and our function will be called on edit again. 01:47 It's just a built in pre prebuilt function for Google sheets app script . We're going to have an event here.

This will help us because just like in the timestamp video if you hadn't seen that before we're going to be able to get the range which includes the row which includes the column and then 02:04 we'll make sure that the row and the column and the sheet name are all the correct things we want to do moving on. 02:13 If there's edits on other parts of the pages we don't want to do anything so we want to make sure we got the correct range which is here on edit the event. range row is our variable row is going to be range .getrow our column is going to be range .getcolumn our sheet is going to be event.source.getactivesheet 02:31 and then we're going to be able to get the sheet name just by going sheet.getname so now if sheet name and we'll go back to our document here and see to do all caps one word is our sheet name sheet name is equal to to do we only want to make make this edit whenever the edit action the event is on the 02:56 to page and also we'll use two ampersands and row is greater than what is the top row here greater than three cuz this is fourth row starts so if we edit that if we edit this header we don't want to do that we don't want to do anything in this app script so we need rows greater than three and two more 03:21 crampersands column is equal to we only care if we're editing the a column so column is equal to one now what do we want to do if all of these are true so let's think about this a little bit we are going to take whatever row so we need to get the b column whatever and the row we're going to move 03:44 it or copy it actually I think it's going to be so we're going to take variable let's call it range row we don't need that range to move we'll call it is equal to we're going to do no we don't need two signs there equal spreadsheet app dot get sheet not get active sheet get sheet nope get active get 04:14 active spreadsheet then dot get sheet by name we're going to make sure we're on the to do dot get range is going to be the row column is going to be two because we're in the B column here and then we want to get value we want to get whatever that value is so let's not call this range to move let's 04:44 call it value or task to move let's call it that then we want to so we got that value we know we want to move it to the done so we need all the information about done so variable done row is equal to just copy all of this and then edit it make it a little easier on us we'll call this done.getlastrow 05:17 now we want to do actually let's call this done so done variable done equals that so we can replace all of that with done we want to get the last row actually we want to let's just not us visually have we don't care about the visuals on this so we're going to delete and try to delete this third and  05:51 first row delete tickle so now the last row is going to be one and we want to go here maybe insert a row we don't really care maybe we don't care about inserting a row right now we just want to put that last row put whatever value is task to move one task to move dot copy So I don't think we need copy 06:47 right now, because to copy we need the range, but we have the value, so actually we can set our value. 06:53 We can say done dot get range, row will be done row plus one.

The column will be one. We only need one cell, so one by one. 07:11 Dot set value is going to be task to move. And then we want to take our active row and clear it. 07:23 It can call this variable what is it? You need the to do so we need this. So let's add another variable to do. 07:39 Equals this. We can delete all of this. To do. Now that looks a little bit better. Easier, might be easier to read. 07:48 All of this is to do. All of this is done. And then whatever we want to do on it. Get range , get value. 07:55 Get the last row. Move it.

Or not move it, we're just adding the value. Now we need a clear. All we need to clear the existing row. 08:10 So we go to do.get range . Row is whatever row we're on here. Row. Column is going to be two. It's only a one by one cell. 08:23 Clear content . That's what we want to do. Not clear, but clear content , because we don't want to clear the formatting or anything. 08:30 And to do.get range , we want to also uncheck the check mark. Uncheck. Just reset it so we can add another thing there. 08:41 So row. Column is going to be one. One. I think it's uncheck. There.

So let's see if this works. If we are now eight minutes into this and it works, this will be amazing. 08:56 Let's see. We write a blog. Let's see what happens if we click write a blog. Nothing is happening. But we are not here. 09:05 We might get an error . So we'll check over here in executioner. We have failure. Great. Let's see. Let's look at the failure done dot get last row is not a function . 09:17 Perfect. I think I know why. Nope. I don't know why. Sorry. Done row. Done dot get last row. Get sheet by name. 09:30 Can't do get last row. Yeah, get last row.

Or is this the correct? That is it. Did I spell it wrong, maybe? 09:48 Let's try again. Let's see. Oh, there it goes. Now it's going. I don't know what I did wrong. Moving! Great! 10:01 It's working now. It's slowly working now. Let's say, okay, another task. And now adding this edit is in the wrong section from this if, so it's not doing this if. 10:14 That's great. Another, we can even edit these, another task. Now we click done, and it moves over here. Perfect!

So I hope this was helpful to you to know how to archive done tasks from our to-do list. 10:32 Again, going through this, using the function on edit, we can only have one in a sheet. So we did have to edit this. 10:38 If we wanted to, we could actually just take basically all of this. And move it into here, because if it's if it doesn't fulfill the if thing, it'll just skip it. 10:50 So if it's not the first one, it'll go to the second one. If it's not that, it'll go to the third one. 10:54 If it's not that, it'll just not do anything.

So we could do that, but for our purposes, I wanted to focus on just writing for the first time an unedit function here. 11:03 And hopefully your to-dos are to-dons. Bye. Thank you. Thank you.</div>