Build a simple function that deletes undone items in our checklist.
Academy ↓
Hey there stranger!
Sign up to get access.
Idea Graveyard - Create a Decaying Checklist
About this Tutorial
Sheet Resources
Video Transcript
0:00 So in this video we're going to go step-by-step through coding this kind of decaying checklist idea graveyard I don't know if you have the same problem as me But I will have just tons of ideas and I'll write them all down and then I will either forget about them or find that there is some reason not
0:20 to do the idea. So what I wanted to do is increase the jeopardy as I check off these things and I add more to the list I want over time to be deleting the ones that I have not yet done.
0:34 Why? That is because I want to add a little jeopardy to it I want to add a little bit of a stakes well if you will some stakes if you will of if I don't do this then it will never be done and not just never be done and be an item in my checklist but it will go away the idea will be deleted and it should
0:56 force me then to be very productive right it should force me to oh go through and check off the thing that I thought of the idea right so if I have like an idea for a youtube video or I have an idea for a tiktok video or an idea for a new tutorial at better sheets I want to write it down and then have
1:11 the have the jeopardy have the death defying need to make it or it will go away will be deleted it will decay from this checklist alright so here's how I think we should do it let's go up to extensions app script we're going to open our app script here and start coding.
1:32 And so what we have here is we have a sheet named ID. ID is we have a checklist in column A and the ID is in column B.
1:41 Okay so we're going to create a trigger eventually we will create a trigger to trigger this function and what the function is going to do is decay.
1:52 The list and we need to know we need to know first off which ones are done or not done right and we know that in column A a checkbox is just a visual representation of true or false so let's just grab all of of the items that are ah let's just grab all of column A.
2:14 How do we do that? We can do variable ah let's call this true false and it's going to be an array.
2:24 But we're going to do spreadsheet app dot get active spreadsheet. First let's actually call this SA and do variable ideas equals SA dot get active get sheet by name and we're going to call it ideas, right?
2:43 And then we have a the true falseArray is equal to ideas.getRange, a colon. A colon a getValues. Okay, so now we have a true false which will be an array and it'll be all of the true, all of the check boxes.
3:07 Alright, let's look at that. Let's first log it, logger.log. True false. Let's save it. We have to get rid of that orange button.
3:16 Now we're gonna run it. We might need to do authorization first. Okay, so once we did that authorization, now we have this output.
3:25 And it says true, true, true. It's an array. We, it's an array with these brackets, true, true, true, true, true, false.
3:31 So now we know what's true and what's false, right? Now the thing is we can choose, do we want to delete the ones that are false at the top or at the bottom?
3:40 Do we want to delete the ones at the very bottom or at the very top that are not yet done?
3:46 Now I think I'm going to go with the bottom one just because. So what we might want to do here is actually delete these checkboxes so that they're not all false so that now we get 50 for instance.
4:04 We're going to get 50 here in our true false log. Let's run it again and see. Perfect, we got blank.
4:14 So now we're looking for the last false. Ooh, this is going to be a tall order. Okay, for loop, so for i equals zero, semicolon, i is less than true false dot length, i plus plus to iterate.
4:32 Through each one, and each time, what do we want to do? Actually, I think there's an even easier than for loop, I think we can just count.
4:40 True, false. So we are going to use a for loop to count for i equals zero, semicolon, i is less than true, false, dot length, i is plus plus.
5:04 And now for each i, we need a curly bracket here actually, and for each i, we're going to get the value of true, false, i.
5:18 And if it is equal, if. It is equal to true, so I just realized we probably want to delete the false, the first false.
5:34 So if it is equal to. To false. Then. What should we do? So what we're gonna do is we're gonna write delete me in log.
5:56 We need one more bracket down here. We're gonna write delete me in the log and just see what happens. We're not gonna actually delete anything just yet.
6:04 We have a bunch of delete me's. Let's look at the first one. Scroll back up. It's going to be 50 which is wrong.
6:20 Okay we got it correct. We have the first one at 9 here. So that is. Actually it is the 10th item.
6:27 So it is ah number 9 but it's the 10th item. And we have it with this true, false, I in brackets and 0.
6:35 So what we'll do is we're going to delete a row. We're going to take. Ideas dot delete row. And which row are we going to do?
6:49 We just get a position. We're going to do I plus 1. And we're going to just delete the row. So every time we run this function we're going to delete a row.
6:57 And it's going to be the very first false. So here we have in the B column it says sheet storm.
7:02 Quick data import techniques. This is just crazy ideas I had. Ah we're going to run this and see if that gets deleted.
7:08 And it does. And it's deleting everything else too. So what we need to do is we only want to run this once.
7:14 So we're going to add a break here. And we now have very few. So it's good we're watching this and adding this break.
7:24 So let's run this again. Let's actually see. Stop this and run it. Actually, let's make sure we know which one it is.
7:32 So formulate a comedy. So let's run it again. And make sure that it is deleting the right one. And it's only deleting one.
7:39 It's not deleting all of them as it goes. That's good. It's only deleting one because we have this break. And now we can run this as a trigger.
7:48 Alright, let's do that. Go over here to the left side, add a trigger. And on the bottom right, on the bottom right, click add trigger.
8:02 We're going to decay the list, choose a function to run, that's decay the list. We're going to choose time driven, not from spreadsheet.
8:10 We're going to choose a, uhm, let's do a month. Let's do a, let's do a week timer every Monday, actually every Friday at 5pm.
8:27 If we don't have the idea done, it's going to delete one every single week. Click save, and now we can see this function here is time based, decay the list.
8:45 If we want to delete anything, we can go over to the three buttons over here and click delete trigger. We can also edit this by clicking on the pencil icon.
8:59 And that's how you create a decaying list or have an idea graveyard.
0:20 to do the idea. So what I wanted to do is increase the jeopardy as I check off these things and I add more to the list I want over time to be deleting the ones that I have not yet done.
0:34 Why? That is because I want to add a little jeopardy to it I want to add a little bit of a stakes well if you will some stakes if you will of if I don't do this then it will never be done and not just never be done and be an item in my checklist but it will go away the idea will be deleted and it should
0:56 force me then to be very productive right it should force me to oh go through and check off the thing that I thought of the idea right so if I have like an idea for a youtube video or I have an idea for a tiktok video or an idea for a new tutorial at better sheets I want to write it down and then have
1:11 the have the jeopardy have the death defying need to make it or it will go away will be deleted it will decay from this checklist alright so here's how I think we should do it let's go up to extensions app script we're going to open our app script here and start coding.
1:32 And so what we have here is we have a sheet named ID. ID is we have a checklist in column A and the ID is in column B.
1:41 Okay so we're going to create a trigger eventually we will create a trigger to trigger this function and what the function is going to do is decay.
1:52 The list and we need to know we need to know first off which ones are done or not done right and we know that in column A a checkbox is just a visual representation of true or false so let's just grab all of of the items that are ah let's just grab all of column A.
2:14 How do we do that? We can do variable ah let's call this true false and it's going to be an array.
2:24 But we're going to do spreadsheet app dot get active spreadsheet. First let's actually call this SA and do variable ideas equals SA dot get active get sheet by name and we're going to call it ideas, right?
2:43 And then we have a the true falseArray is equal to ideas.getRange, a colon. A colon a getValues. Okay, so now we have a true false which will be an array and it'll be all of the true, all of the check boxes.
3:07 Alright, let's look at that. Let's first log it, logger.log. True false. Let's save it. We have to get rid of that orange button.
3:16 Now we're gonna run it. We might need to do authorization first. Okay, so once we did that authorization, now we have this output.
3:25 And it says true, true, true. It's an array. We, it's an array with these brackets, true, true, true, true, true, false.
3:31 So now we know what's true and what's false, right? Now the thing is we can choose, do we want to delete the ones that are false at the top or at the bottom?
3:40 Do we want to delete the ones at the very bottom or at the very top that are not yet done?
3:46 Now I think I'm going to go with the bottom one just because. So what we might want to do here is actually delete these checkboxes so that they're not all false so that now we get 50 for instance.
4:04 We're going to get 50 here in our true false log. Let's run it again and see. Perfect, we got blank.
4:14 So now we're looking for the last false. Ooh, this is going to be a tall order. Okay, for loop, so for i equals zero, semicolon, i is less than true false dot length, i plus plus to iterate.
4:32 Through each one, and each time, what do we want to do? Actually, I think there's an even easier than for loop, I think we can just count.
4:40 True, false. So we are going to use a for loop to count for i equals zero, semicolon, i is less than true, false, dot length, i is plus plus.
5:04 And now for each i, we need a curly bracket here actually, and for each i, we're going to get the value of true, false, i.
5:18 And if it is equal, if. It is equal to true, so I just realized we probably want to delete the false, the first false.
5:34 So if it is equal to. To false. Then. What should we do? So what we're gonna do is we're gonna write delete me in log.
5:56 We need one more bracket down here. We're gonna write delete me in the log and just see what happens. We're not gonna actually delete anything just yet.
6:04 We have a bunch of delete me's. Let's look at the first one. Scroll back up. It's going to be 50 which is wrong.
6:20 Okay we got it correct. We have the first one at 9 here. So that is. Actually it is the 10th item.
6:27 So it is ah number 9 but it's the 10th item. And we have it with this true, false, I in brackets and 0.
6:35 So what we'll do is we're going to delete a row. We're going to take. Ideas dot delete row. And which row are we going to do?
6:49 We just get a position. We're going to do I plus 1. And we're going to just delete the row. So every time we run this function we're going to delete a row.
6:57 And it's going to be the very first false. So here we have in the B column it says sheet storm.
7:02 Quick data import techniques. This is just crazy ideas I had. Ah we're going to run this and see if that gets deleted.
7:08 And it does. And it's deleting everything else too. So what we need to do is we only want to run this once.
7:14 So we're going to add a break here. And we now have very few. So it's good we're watching this and adding this break.
7:24 So let's run this again. Let's actually see. Stop this and run it. Actually, let's make sure we know which one it is.
7:32 So formulate a comedy. So let's run it again. And make sure that it is deleting the right one. And it's only deleting one.
7:39 It's not deleting all of them as it goes. That's good. It's only deleting one because we have this break. And now we can run this as a trigger.
7:48 Alright, let's do that. Go over here to the left side, add a trigger. And on the bottom right, on the bottom right, click add trigger.
8:02 We're going to decay the list, choose a function to run, that's decay the list. We're going to choose time driven, not from spreadsheet.
8:10 We're going to choose a, uhm, let's do a month. Let's do a, let's do a week timer every Monday, actually every Friday at 5pm.
8:27 If we don't have the idea done, it's going to delete one every single week. Click save, and now we can see this function here is time based, decay the list.
8:45 If we want to delete anything, we can go over to the three buttons over here and click delete trigger. We can also edit this by clicking on the pencil icon.
8:59 And that's how you create a decaying list or have an idea graveyard.