Self Destructing Google Sheet

Intermediate

About this Tutorial

Create a sheet that can trash itself. It can delete itself instantly or on a timer. Maybe in 24 hours or a month.

Sheet Resources

Access Sheet

Copy Sheet

Preview Sheet

Video Transcript

<div>0:01 Hi, so this is one of those things where you have sort of like a fever dream or like a 2am sort of nightmare and you think, is that possible? 0:10 So I built a self-destructing Google Sheet now. What are the uses of this? This might be actually be useful but I sort of took a interesting angle on it and I was like, if you're trying to quit and you're like, I want these Google Sheets to go with me, you might be able to sell to self-destruct your  0:30 Google Sheets in 24 hours or a week.

I also I don't condone that kind of behavior but there might be some other actual use cases for a self-destructing Google Sheet. 0:42 Maybe you do have some time sensitive projects and you're like, absolutely know that I need to, you know, quote-unquote destroy or delete these sheets at x point in time. 0:58 Maybe it's a month after some event, 30 days after some event, or where the event is not like you need to save this information. 1:08 Alright, enough of that. Let's see what this is, right? Let's see how it works.

So I made a little menu up here set for 24 hours from now or a week from now. 1:19 But what does it actually do? And does this actually work? So let's go to Extensions, Apps Script and look at what's going on. 1:25 We can self-destruct this if we want. The code for self-destruct is actually pretty much, pretty much one line. It's set trashed. 1:35 It gets the sheet ID here. Of the drive app, but the sheet ID that you're in now, and it sets it to trashed. 1:45 True. Let me, actually let me show you. You can choose this self-destruct file. Function up here.

We're gonna just choose self-destruct here. 1:59 Click on run. And then if we go over, once it actually reviews the permissions, right, we have to do this all the time. 2:07 Whenever we're using Apps Script and we need to use Drive app, spreadsheet app, whatever, we do have to authorize it. 2:13 And now we go over and the file is in the trash. Immediately, right? So that's set to trash, gets trashed. 2:20 So now in 30 days it'll permanently be deleted, but let's actually take it out of the trash.

So, how do we create a trigger that does this, right? 2:33 Maybe you don't wanna come into Apps Script and hit run and self-destruct and say, okay, let someone take it out of trash. 2:40 So what we do is we, create another function called set self-destruct. And again, this is a very simple Apps Script here. 2:49 Script app, new trigger , and we trigger the function self-destruct right here. We just, new trigger , we do that.

We call it it's a time-based trigger . 3:00 We have to do this little calculation which is 24 times 60, times 60 times 1,000 milliseconds to know, okay, in 24 hours this trigger will run. 3:13 So that's what that after. Time-based means it'll run once at its particular time after x number of milliseconds it will run. 3:21 And then just create that trigger . That's all that function is. So we're taking a self-destructing function that sets it to trashed. 3:29 We are now creating a new trigger . That's going to self-destruct it in 24 hours.

That's what this 24 times 60 times 60 times 1000 is. 3:36 That's a 24 hours of milliseconds. But in addition to that I also added an unopened menu or a custom menu here. 3:46 And so we have to use the function on open in order to do that. And we set the variable ui, spreadsheet , dot get ui. 3:53 We create a menu called self-destruct. We add two items which are the self-set self-destruct that we already created. And then one week. 4:02 I create another one where I just multiply that by seven.

So, I explained a little bit of why you might want to create this, again, it's a bit silly to create a self-destructing Google Sheet, but you might actually have a real use case if you're a BetterSheets member, you're watching this right now on 4:17 BetterSheets.co. Feel free if you know you're like, hey, I needed this in this particular instance, email me. Let me know when you actually needed a self-destructing Google Sheet and now you have one. 4:29 But let me talk about a little bit of things you can do to improve this, right?

You can set this trigger to sort of any amount of time. 4:39 You could also set this function to run not based on a, not necessarily user interaction, but rather different user actions, right? 4:50 There's a button. You can create an image on here, assign the script as a button instead of having to say self destruct. 4:57 And maybe it's not called self destruct. Maybe it's called destroying 24 hours or destroying one week. And again, this is not necessarily actually destroying it in 24 hours. 5:07 It is setting it to trash, which then will be permanently deleted 30 days after.

So a little bit different. You might run into issues with that if you're trying to destroy the sheet right away. 5:21 Some other things you like to do do in order to sort of similarly create a self-destructing sheet. If you're like, hey, yeah, in 24 hours, I want to do something, then yeah, do this trigger . 5:32 And that trigger could trigger any function . The function could be, hey, destroy one sheet in the sheet file.

Or it could mean clear content . 5:42 So one of the questions I had when I did release this, I sort of released this on Twitter, the code, but there is a clear content function here you can do where you don't necessarily have to delete the entire file and you don't have to delete the sheet you can just clear the content so maybe you have 6:05 a template that you're like hey I fill out this template I fill out this information and then a month later I literally have to delete all of this content all of the text and repopulate it and so this clear content might be much better for a quote-unquote self-destructing cheat that you might want to 6:23 use and that you can just slip right into here instead of set trashed you say hey set this file to clear content of this particular sheet now if you're watching this and you're like hey this app script is pretty crazy I want to do more stuff like this check out spreadsheet automation 101 here on BetterSheets.co 6:40 and get coding you will be able to read this app script really really easily thanks bye Thank you.https://docs.google.com/spreadsheets/d/16p15fU2BwgIWlCf5MTysgUOn-tWthO_679xNZtDX_vc/edit?usp=sharing</div>