Hi. In this video we're going to be doing something very interesting, which is we're gonna take our sheet stories and actually clear out any videos that are more than 24 hours old. Why would you do this? Why would you delete data? Oh my God, it's so crazy. It's because you might want to only have videos that are within the last 24 hours.

You might want to make sure the people watch them, or you might have just You might not wanna keep all your data like you might actually want to delete. Tasks that have been happened a week ago, a month ago. We can back up and there's some other videos perhaps that I think I've made of a couple videos.

You can make copies of sheets and you can keep backups, and we have more and more. But the idea of stories, okay, so the idea of stories on Instagram, on Snapchat in now there's fleets on Twitter. The idea is that they disappear. That, that there is some that the basic function of.

Makes you want to watch them in real time. Like you want to make sure you watch it and keep track. Certain announcements or something you might wanna say, like if somebody doesn't get to it within 24 hours, maybe you want to delete it so that they don't think, oh, do I have to do that today?

It's oh, only on this day they have to do that. You might have some instructions for someone that you want to. Once someone sees it, but you don't know exactly when they're gonna see it. So you give them 24 hours to see it and then delete it. Yeah, there's a few other reasons why you might want to delete videos based on some action.

If somebody's watching marked is watched. So I'm gonna share with you in this video again, how did, how to delete it within 20, like once 24 hours has passed. And I think once you see this, you're going to be able to morph this and. To whatever you need your specific needs. Maybe it's a click, maybe it's an action, maybe it's a non-action you want to do.

Like maybe you wanna have a column that says save. And anything that's not saved, delete. Lots of different reasons. But I'm gonna show you how to delete a row here in these videos. And again, you can go back through the other two videos. You should go back through the other two videos that I've made on this sort of sheet stories.

Because in the first video, we created this video notes and we can add a video based on this form inside of sheets. We don't have to have an external form. We can watch the latest video. And then also in the last video I made. We did email notifications, so that link and the sheet link is in an email already, so perhaps you want to only keep that in an email and delete it when you need to and not keep adding to this.

And only have the latest videos here. That's another reason why you might want to delete videos. This is only save the latest ones or unwatched ones, whatever reason you have. So in this video, we're gonna clear this out, and this is gets a little complicated because we need to do two things.

One, we're gonna go to our script. We're going to create a function that says Clear old rose. Okay? And we're gonna create or gonna go through and array. We're gonna get all the values. So here's what we're gonna do. I'll put them in the comments here. So first we're going to get all values and I think it's.

It's going an array of get values. We're going to we're gonna do a loop through through them and find the ones that are old and we're gonna use an if statement and we're gonna say, if you know this X date is greater than. Today or today, plus one kind of thing. We'll figure it out when we get there.

And then within that loop, we're gonna within the loop. If if they are old, we'll delete it and we're going to use, I'll show you here in the. Google dot, Google developer docs. We're going to use this delete row function. And literally all this does is you say, what number? And we delete it. So let's say, let's just copy this and I'll show you what it does.

And that's it, that's what we're gonna do. Oh, sorry. And then we can run this and it can run every. Like when we want it, right? When a function is in a sheet, we can say, select this function and run it. And we can put it in and we probably will also put it in this menu just to be able to manually do it and to check that it's working.

But what's the coolest thing too, let me dismiss this, is we can go to edit current projects triggers, and what we can do is we will create a trigger that says Every hour. Run through this or every day run through this clear old. So I'm gonna save this and right now it's not gonna do anything. But I'll show you this trigger and we can do this again at the end.

We're gonna add a trigger. We're gonna add clear old rose is what we want to do. Sure. From the spreadsheet is not correct. We want to do time. And now we can select hour. We can select day a week, and let's just do day. We'll just run this once a day. We'll run it let's say between 2:00 AM and 3:00 AM our local time.

And then if we have a failure, we do want to be notified. So we're gonna save that. And now this function, no matter what, will run every single day, 24 hours a day. So we just need to choose the account that's running it. Let's make sure we. We do. We're okay with it. It's safe. We're not doing anything yet.

We'll have to, we might have to save this again and do, we'll do this again at the end, I think just to make sure it works. And what's cool about this trigger is yeah you can see there's some options there. We can check it. But yeah, that's, this is how we get it to run every single day without our interac.

And what really is really cool. Okay, so first off, let me show you how this delete works so that we can then figure out how, what do we delete, how do we delete it? It's going to end up inside of our loop, but, so I'll put it here. Get active sheet. Let's get, actually get the sheet by name.

Cause we know the sheet. I do this a lot where I'll get the sheet. It's name sometimes. Okay. We're just gonna go here, get sheet by name.

Video notes. I think it was that. I hope that is the.

Did you notice? Yeah,

there we go. So we got there sheet by name on the sheet. We wanna delete row one. Actually we want to delete row two. Let's see. Okay. And now whatever number we put there, I'm gonna save this just so that we can the, this part of it. So just so that we can run it from our inside of our sheet without coming back here.

I'm gonna add it as a item here. You don't really need to do this but I like to do this just so that can run it clear old rose and just double check that we get the correct

name. I is gonna copy paste it so I don't type it wrong.

Oops, select the name. There we go. So save. And now I'm gonna restart this. So anytime you change something about the ui, it doesn't automatically do it. So I need to refresh this. I'm gonna pause the video so you won't see the refresh, but it will close the script and I'll just. It. I'm gonna refresh it just so it goes faster.

I'm gonna pause it. So we're coming back here. Now. We have refreshed our video our entire sheet in our video notes menu. Now let's click on that. Now we have clear old rows what it will literally do right now. Let's go back and just double check what it we think it's gonna do, and then we're going to run it.

Let's dismiss.

Let it load. Scroll down to the bottom. So what we think it's gonna do is it's gonna go to video notes tab and it's gonna delete the second row. Okay? We think it's gonna do that. Let's see what it does. So if this 6 11 16 with this thumbnail, it heard Google Sheets is not there anymore, we'll know it worked.

We're running the script and it deletes it there. Piece of cake, right? But really what we need to do, we can delete any row we want, but what we really need to do is make sure that this is deleting the correct row when they are old, when the timestamp is old. So we need to get a couple things. We need to know what is today.

We need to compare it right from between today and this timestamp. We need to know how to compare timestamps. We'll need this value. Let's do this. Let's get the values. Let's first get all the values. We're going to use something called Get Values and it'll give us an array, I think Double check.

Cool. So we're gonna use GI values. You might be familiar with GI value that I use that a lot to grab just one cell and it gives us one thing. But what's interesting about GIT values is that it returns a rectangular grid of values. It returns a two-dimensional array of values, index by ROE and then by column.

Why this is important is because we are going to be using. JavaScript array here. Zero. Zero is one. One, okay. The values are not just this single integer here. It is a row in a column. And that will be very important as we loop through the values. Okay. Let's grab, and we're gonna log this and see what happens. Okay?

Okay. So I'm gonna redo some of this so that we can reuse some of this code. So it's not messy and we're not repeating ourselves all the time.

This move this up.

Okay, so we go let's do all dates equals ss. So we just use this variable and what we wanna do is get, the range is gonna be two, so we want row two column. Two, which is, this is easy cuz we don't know, we don't have to know which one is which. We want this to start here. And then we want to number of rows is going to be last row.

So remember we got this last row. We don't need every, we don't need the entire column. There's gonna be a lot of empty stuff there. So we use this get last row. I can actually copy this and change it for what we need here. Get last row. So they just do ss Oh, and actually we need to put here sorry, we need to move this as well.

So let me move this. Up to here. And now this is not Ss it is sheet. We wanna get on the correct sheet. We got get last row. Okay, so I always forget this get range thing. So we're gonna look at the range and we're gonna go get range. We're gonna go look at it again,

want to go the other. Can always check the docs to remind you what what the actual numbers are. Should be get, should be up here. Where is it? Get range. Get rich. That's what we're trying to do. Oh my God, it's not even here. It is the range thing. We want get range. Let's see. Why isn't this showing up?

The sidebar isn't showing up here.

One second. That'll be back. Cool. So this get range, if we have two items, it's just row and column. If we have three, it's row column and number of rows. If it's four items, it's row column, number of rows and number of columns. What that means is we want to get B2 until the end of the B column, right? So B2 is two.

Two, it's row two, column two. The number of rows is gonna be based on the last. Minus one because we're starting it at two, not one. How we show that is we will do number of, we'll just do last row minus one. So this will be, should be something like 10, but we're starting at two. Two, so we really want 1, 2, 3, 4, 5, 6, 7, 8, 9 rows.

We don't want 10 rows, we want nine rows. So that's gonna be last row minus. And we're gonna get all the values and we're gonna log the values of zero zero. So we'll do all dates and we're gonna see what is the very first thing. So we're gonna save this. We don't want to delete anything right now, so we're gonna comment that out and we'll come back to that in a second.

But we do wanna log this and see, and actually it's really fun to see the. This lets you see if you are doing things correctly if you're getting the correct information. So right now we will run our function video notes, clear all rose, and it is done. And if we go to our logs, we can see what did it log?

We logged Tuesday, November 17th. Is that what we want? That, is that the first? Yes, that is 7 11 0 8. That is perfect. This is a date Tuesday, November 17th. It's, this is a timestamp, right? This is the timestamp we want. And let's do this, let's log, let's go back here. Let's just log what we get, right? We're gonna get an array.

We're gonna log this entire array and see what happens. We can also, oh, select this function. You can run it right here and click and let's go to our log. And now we've grant it from the editor. We have no logs available. Might take a second. Let's refresh. Sometimes it takes longer from there. Let's go here.

Let's try again. Let's go back to our.

There's our array. See? So we have my face. So we have these brackets around everything, and we have this comma in between these. So this is all a row or one column. And one row. So we'll figure out how do we work through this in a hot second. So we need, so we know all dates, zero. Zero is the first one, but what's the second one?

Let's see if it's this one. So we know it's going to be

1118. Okay. So if we get that in our log, then we did it, correct?

No. So by doing the second column, we got nothing. So let's do zero. And one there save and we're gonna run it again. This seems to be faster from in here.

And now we have completed one No logs for this session. Fresh refresh. There we go. So we got November 18th, seven. Just have a name. Say, yeah. Seven. There we go. So we now know that we have to iterate over this first one. So zero. Zero is the first thing, one zero is the second thing. Third is, as you keep on adding.

So great. Now we know what we need to loop through. How we build a loop is a loop. I'm gonna go copy a loop. I always forget these how to do loop. So I'll just go copy it and then I. Okay, so I just grabbed the four loop. It's it's variable. I, and then as I is under five we iterate. I, and then we'll do something in here each time.

This five we need to change to last row. We don't want to go past the last row, so we want to know, that's the number of things we need. We want to. Delete, so we can put this inside of our loop, but we're just gonna keep it co commented out right now. But what I wanna do is I wanna recreate this, log this I wanna make sure we're getting each one.

So we're gonna log nothing there, but we're gonna log something here. And instead of a one, we're gonna put an I, because as this I loops through, we want to iterate over this one. So let me delete this. And now let's just log it, logger dot log all dates. And now we're going to get each log, we're gonna get up a big list of logs of all of these, right?

If we did this correctly let's run. Let's go back to our log, which our log, the second one here from the editor. We know that it's the one we did here and there we go. We have all of our items here, all of our timestamps, all each of them logged, and that is exactly what we need. Now, if we had done something differently, right?

If we had not checked before and we did something like this, zero I. I guarantee you we're gonna get a bunch of nulls. And this will show you in a font. Second, there's our log. We're gonna refresh. Refresh. And there's a bunch of nulls, right? Because the only, the first one got something. Zero. Zero got something, zero one got.

So we want to iterate over the first one, and now we have to create. We're gonna create an if statement here is greater than blah, blah, blah. So we're two things. Oops. One second. Okay. So our, if se, our if is going to be, if all day I zero is greater than let's say today. Greater than, no, less than, sorry, less than today.

I don't know. Minus one. Meaning more than 24 hours in then we're going to delete, and in this integer we want I plus one, I think. No, we wanted plus. Because the very first item here, zero I is zero here and it's gonna look okay. Is this greater than, or is this date less than this? Add two because our, if we add one, we're just gonna hit that first column or first row, right?

If we add this is zero plus two is two. This zero. This is actually gonna be one plus two is three. So we want to get the correct, we wanna make sure we're correct, deleting the correct row, and only if this is this date, this timestamp is less than today. Okay. Let me dismiss this. Wanna say this, but I think we're gonna, we're gonna need a little bit of more work.

We need to get this. We need to get today. We got the date, we got the timestamp. We just have to double check the timestamps can be done in this way. One sec. Okay. So to go do the next thing we need to do a little bit of complicated math. What we've done so far is we are doing, we're looping, we're gonna loop, we've looped through all of our val values, all of our date times.

And we're trying to compare them. We're trying to compare them to a day yesterday. We wanna say anything 24 hours ago or more. Or more than 24 hours ago, delete. Okay. And we need to know if it's more than 24 hours. So we do this comparison. We compare each of these dates, right? And it's, our eye is looping through and then we do today.

And I had today minus one for a while, and it just was not. But I realized by doing some Google searching that timestamps are milliseconds and you can compare them by milliseconds. So we need to find out how many milliseconds are there in a day. It's 24 times, 60 times, 60 times 1000 perhaps for milliseconds, not seconds.

And when we do today, This milliseconds per day, meaning, okay, as many milliseconds there are in a day. Go back 24 that time. Now, anything before that we're going to delete. So we have our log here. So one, we're logging our today, we're logging what is today. And then we're gonna log yes. If this date. And our val, our value once when flipping through, we're gonna log Yes.

If not, we put, I put an else statement here and I said log. No. Okay. So let me delete this and put one and I'll show you what happened. So I'm gonna click save. I'm gonna run this function and it's going through all of these. Just as a reminder, it's going through all of these here and saying, is this Less than the timestamp.

Now minus one, which is actually one millisecond wheeler. It's one millisecond. And so when I go to these logs, it's gonna say yes. And this is, it's supposed to be no. So we have to change this one to this variable of milliseconds per day. And we can name this anything we want. We named that cuz that's what it was in the in the help that I Googled.

So we say today minus milliseconds. Now if we see what it should say, right? This is the only date that should say yes cuz it's a day ago. Today is the 18th, so it should say no. For all of these except for the very first one, which is a day ago. Okay, let's save. And run it again. We're gonna go to our log here.

We're gonna wait for it to refresh. There it is. That's the newest one. And we have Yes. And then no. Great. This is perfect. Okay. So now as we're looping through, I, we now can differentiate, put them into two groups. We're gonna be like, Hey, this is a yes and this is a no. This is 24 hours ago.

This is within the last 24 hours. We have that going now and now the only action we wanna take is we wanna delete it when it is a yes. This is old bags. We want to delete these old bags. So now that we know that it's correct, We can take away this logger.today. I'm gonna keep the log cuz it's not really adding anything.

And if you want to copy this script, you can do it and see the log. What you need to do is, what I would recommend is just adding to commenting this out, adding those two slashes in front of here and looking at your logs and trying out for yourself. But we're gonna give this a go. This is really exciting.

If this is correct, if we have everything correct. We have gotten all of our values from our video notes, we have gotten the correct we've looked through, we now say, okay, yes, this is old bags. We wanna delete those old bags and all the other ones. We're gonna not. If this is correct and we run this, that means that it will delete this row and this row only.

I'm gonna copy this so we can add it back in just in case it runs. Let's see. Clear old. And it only deletes it. And see it's finished script. It only deletes that. All right, let's add it back in kind of video note. I'm gonna add this. Hope it clicks. There we go. And add it back in and see it show up. I wish it shows up faster.

It'll show up eventually. Oh. There it is. Now let's go. I just looked at my email. I got an email notification that still works. Alright, let's do this, let's go into this and change this to 16. Let's change this to 17. Let's what else do we want to delete? We wanna delete to delete, let's say this one as well.

17.

And we want delete this 16. This is change that, this one, I wanna change it actually to the future. Okay or not the fu sorry, within 23 hours. So we wanna change the time to the future, but the date to the past so that it is within 24 hours. So this row should not be deleted if if all goes well. But all of these 16 seventeens, all this 16 and 17, all of those should get deleted.

So let's say clear old rose. Ooh, we can see it happening. Whoa. What happened here? This 16 didn't work. That didn't work very nicely. Okay. So I think I know why this didn't work very nicely because as it's leading them the, it, it's changing, right? Once it deletes a row, Then the next time it loops through, when we try to delete that row, it's gonna delete a different row.

Cuz this I is changing, but you've deleted the row so it's deleting the same ones. Okay, one second. Okay. So depending on how many videos you have and how many videos you want to clean up at any given time there's gonna be a variety of different solutions for this issue. So what I did is I know in my particular.

I'm making this for we're not gonna have hundreds of videos, not even dozens. Like we're gonna have some videos. We just wanna delete them. We only wanna have the videos that are made that are posted in here within the last 24 hours. We just wanna save those. And we're gonna have like maybe one or two videos here.

So what I did to my script and what you might not want to do is I added a break. And what that means is that it's only going to delete one video at a time ever. When it runs once it hits, once it goes through this loop and it finds, oh, here's a de road to delete, it will stop and then won't happen again until it needs to.

Why? Fixes this particular problem is that we can only delete one row at one position, and then we need to like, create, figure out all the positions again. There are more elegant solutions to this. I think you can pause, you can restart the loop if needed to like double check. Is there anything else to delete?

But for our case, like if we delete one, we wanna stop and then we, what we actually can do is go to our triggers and run it every hour. Okay. So instead of running it once a day and deleting all of the ones that are old, what we can do is just run. So let me double check. Let's run it and make sure it only deletes one thing.

Good. So there are some other old ones and say, I run it. Should delete one more. Great. So it's breaking and it's stopping right when it should. Okay. So let me add this back in. Video notes, add a video note so we just double make sure we have one that's not going to delete, or I think it's these two that are not gonna delete, but it will delete this one.

But let me go to my triggers and remember we're gonna come back to our triggers. So we're now back to our triggers. I'm gonna actually Edit this trigger and what we have now is we're running this trigger the function. Clear old roads, we're running it, the head, it's time driven and it's on a daytimer, but we really wanna make it on our timer and we wanna run it every hour.

What this is really good for is we know we're never gonna add more than 12 videos a day. Then it's 1224 videos a day. Then we can be like, okay, it will delete within. And within a day it'll last 12 because every single hour it'll run and we'll only delete one. And we're gonna click save and now every hour, and we can come back here in an hour and it's gonna run it and it'll delete it.

But I'm not gonna, I'm gonna wait. I'm not gonna wait here. I'm just gonna run this again. Clear Old rose. And now If you copy the sheet and you wanna use this, understand sort of those limitations, right? We have already a sheet that you can add a, in your note, you can watch the last video made.

It'll be whatever is at the bottom. And now you can actually go here and physically clear old row and it'll be one row. You can run your trigger. You can create a trigger on your which is over here. Edit current projects triggers. It'll bring you a new we'll see it again. You can add trigger.

I already added a trigger. You can add a trigger. You can run whichever function you want, which will be clear. Old row. You can run it on an hour on a day if you know you're only gonna be making one video a day. You can use this. And also you might want to have a d. If function here, you may wanna say if the one next to it is a checkbox, is it true?

Is it saved? Don't run, don't delete it. Maybe you want to add a save checkbox here and be like, save forever. And so if that's checked. Don't delete if it's unchecked deleted, certain things like that or it's like, Hey, it's not colored red. If it's colored red, then delete it. If you want might want to do.

If some action is taken, then delete. Or if some action is not taken, then delete. Depends on your situation and how you want to run your. But hopefully this is really helpful for you. Or really fun too, because you can delete an old clear old rose, delete these old rows. And we really have stories. We have some self-destructing mechanism here for these videos and these notes that we have, which is really cool on really fun way to think about Google Sheets.

That it's not just put all your stuff here. It's oh, put this stuff here for the last 24 hours. Thanks so much for watching and enjoy your sheet stories. Videos. Hopefully you'll have fun with it and comment below give me a thumbs up if you like it in loom. Thanks.