Automatically Delete All Sheets If Not Listed Name

Advanced

About this Tutorial

A raw, 1 shot take of figuring out how to automatically delete all sheets not named on a whitelist of sheet names. Using the on Change Installable trigger. This video was tough to make!

Sheet Resources

Access Sheet

Copy Sheet

Preview Sheet

Apps Script APIs used

SpreadsheetApp

Apps Script methods used

.deleteSheet() .getActiveSpreadsheet() .getSheetByName() .getSheets()

Video Transcript

<div>00:01 What are we building? Okay. You probably got it from the title of this video, but it is raw. This is gonna be a raw video because we're gonna try to build something. 00:10 What it is, is a new sort of app script we're gonna try to create inside of a Google sheet. Now, what this ends up being, I don't know, at the beginning of this video, we're only less than 30 seconds in. 00:23 And I don't know what I've <laugh> what I've built because you were watching this video from the outside.

You're watching the video that I've made the title of this video, what should we title it? 00:35 Basically we want to create a list of sheets, like the names of the sheets, like sheet one, sheet two, and limit any other users and ourselves from ever creating any more sheets. 00:49 So if we create, if we add a sheet here and it's sheet three, it should be deleted. Deleted. That is what we want to do. 00:58 This came up member asked this question.

Actually, no not in Udemy, in a Udemy course that I have not spreadsheet automation 1 0 1, but in learn to Code, somebody asks if they could do this. 01:15 And I actually wonder, can we do this? Can we limit the sheets, the tabs to only those that are created or rather on a list? 01:23 And I suggested maybe making a list create a list and then create a script that anytime there's a new sheet added so that it deletes those. 01:36 So delete all sheets other than the ones listed it. Mm.

I don't know if that's gonna be the name of the title forever, but that's the point of this video. 01:48 We're gonna try to create this. We're gonna go to extensions app script first. There is one thing you need to know to unlock. 01:55 Why I think this is a possibility is in app, not in app script at all, actually a trigger . There are things called installable triggers. 02:06 There are simple triggers, which we can just write something like on edit or on open.

The moment that there's an edit, a change of value, something happens. 02:15 Or when the sheet is opened, something happens. There's other triggers which are called installable triggers. And those are things that we can trigger trigger a script to run. 02:27 So let's see what we want to call this is delete all sheets other than this list, I guess. And what's the delete? 02:45 Now this is gonna be a raw video because as coding goes, I cannot remember all functions , but I'll be doing some Google searches. 02:52 I'll be figuring it out along the way.

So the idea is, well, the trigger will be when a sheet is created or any change of the sheet, you'll see why on change, on format change. 03:07 I forgot the name of it. When a sheet is created, check, check if the check if sheet created is on list. 03:21 If not on list, delete it. That's it. That is what we're doing. I'm using these con these slashes to comment to add some notes here. 03:32 Let's see. Over here. There is A button that I need to press that I can't press. There we go. Triggers. 03:45 Okay, add trigger . So right time driven from a spreadsheet on Change save.

Oh, yep, that's the one we want to save. 04:00 That's the trigger we want to do. So anytime it's changed. So let's do this. Let's go look at what this is. 04:06 Oh, I need to authorize it or something outright. Wow.  On change spreadsheet trigger . Oh, here it is. Do, do, do, do Installable triggers. 04:35 That's what we actually want to see. Event on installable, change trigger runs when a user modifies the structure of a spreadsheet itself, for example, by adding a new sheet or removing a column. 04:50 Cool.

I wonder how we can figure out what is going on, or actually, we don't need to figure out what's going on because as long as this change happens, which is adding sheet, let's see if we add a sheet , does the executions run trigger ? 05:14 Yes. This rent, this just, this is it. This is, this is running. Okay, cool. So we need to take like, probably an inventory of so this is gonna run. 05:27 We probably want to do something like, well let, let's just do a toast, toast in Google Sheets, just, just to see if this is go is happening, right? 05:41 Toast. Hello. You did something.

We're gonna save that now. Add another sheet. There we go. There we got a little toast, a little message that says hello. 05:53 You did something. Now if we delete this, yes, it should also come up with a toast. Good. Okay. So that's anytime we add or delete a sheet. 06:07 Insert sheet. Let's make sure. Yep. Perfect. So anytime we add a sheet , it also means when we delete a column, just get run. 06:16 So it's, that's fine. Delete. Okay. Delete a sheet. Delete a sheet in Google Sheets.  In app script . Let's see.

Think we need to just get the name of it and Hmm, hmm. 06:46 You can get the sheets. I think I just want to know how to delete the sheet, which might be something we get later. 06:56 All right, how about let's get the names of the sheets. That's probably what we want to do. So do spreadsheet , app dot get active spreadsheet dot get sheets. 07:20 Let's see if we get this. Variable sheets equals, now I think we can get a list of them. Let's put it in the here, let's put it in a prompt. 07:35 Toast, toast sheets and get the number plus sheets dot length. Let's do that.

Let's just see how many there are. 07:50 If we add one sheets, three. Perfect. Now we need to get their names. Mm-hmm. <affirmative>, Get the names. Ooh, actually I know this get sheet one is equal to sheets. 08:19 Oh, Get active spreadsheet dot get sheets. I think we do zero. I think we can get the sheet name that way. 08:44 And let's put a send a toast. Let's keep moving this down. Plus, Hmm, Plus variable sheet. One name. Let's just see if this works. 09:09 Hmm. Move that in there. Okay. Now we should be able to get sheet one name, perfect. Sheet one.

All right. 09:26 We can get the names of each of them by calling this zero by going through a four loop and saying, you know, every time we, we created a sheet, grab the, all the names of them and then check all the names if they exist on the list. 09:45 So we need a list of names, variable sheet names. This is equal to sheet one, sheet two. That's the two sheets we want. 10:02 And anything else we want deleted? Huh? How are we gonna do this? <laugh> we can give variable new names is an empty array. 10:13 We need to take this do get sheet name and run it through a for loop.

For I equal zero I is less than sheets dot length. 10:47 Oops. I put this in the wrong spot. I plus plus to make it loop. And then this zero is gonna be I. 10:59 And we are just gonna go variable new name equals this. And then new names dot push new name. Let's see. 11:14 So now if we add this to here sheets, every time we create a new tab, we'll get a list of all the names. 11:27 Perfect. There's all the names in a common separated list. Awesome. If we delete as well, we'll get the new list. 11:36 Perfect. So now we have a list of all of the names of all the tabs.

We have our list of sheet names that we want to have. 11:48 Now we need to compare these this, we need to know which sheets should be deleted and which sheets should not. 11:57 Okay. Four. Let's make a new,  Equal zero. Wait. As we're putting them in, we should be able to, we don't really need to create a new one. 12:12 Right? And each one is going to go through and be like, is this on the list? So we really need to just go do this. 12:18 We need new name. Well, we need if new name. Hmm. How Do we do this?

How do we check each one? 12:34 We're gonna get the name of each one. We just need to compare it to the sheet name . Maybe we can, for, for ease. 12:41 Let's just do one. We only want one tab. If anyone else is created, if new name is equal to sheet name , let's do that. 12:51 Then. Do nothing, right? We don't want to delete it else. We wanna delete it. Spreadsheet app, get active. Spreadsheet dot get sheet by name. 13:17 New name do delete. Can we delete how to delete the, we figure that out. Hmm. Dot delete sheet . Ss delete sheet . 13:41 Ah, I don't think we need to do too much.

I don't think we need to get sheet by name. I think we just need to dot delete sheet . 13:52 And then here. I bet. Let's read this. The sheet to delete. We just need the name of the sheet. Oh, we don't need it. 14:02 We need to get the sheet by name. Okay. We just do need to do all that. So all this dot get sheet by name, new name there. 14:16 I think that's it. Hmm. Think everything's fine. Okay. So we're getting the sheet by name. We're deleting that sheet. If it's not the name of this sheet one. 14:32 Okay, let's save it. And let's see what happens.

So we're going to make a new sheet and it deleted it. 14:43 Deleted something else too. Did delete delete sheet two. Oh, it's deleting a lot. All of them. Hmm. Oh, it's going through all of them, right? 14:58 So if nothing, maybe we can put a break here. Oh, it's going through all of them except the new one. 15:07 Because the new one function , it's happening right away. But I thought it would get it. <laugh>. Hmm. Let's delete all this. 15:27 Hmm. So we're not deleting the actual new one. We're deleting the ones. Oh no, didn't happen.

Is there an error ? 15:43 We do get an error here. Hmm. But what is the error ? Let's see. Hmm. Let's see, what did we, oh, I think we have something wrong with this. 16:05 Yeah. Mm-hmm. <affirmative>? Mm-hmm. <affirmative>? Nope. Looks fine to me. But again, we're not really looking too well. Maybe No break there. 16:29 Okay. We're deleting a couple, but not this, not all of them. Hmm. Why aren't we not deleting all of them? 16:36 If the she is we're, we're keeping she on. That's a good, that's a good sign.

There seems to be a little bit of an issue though with something. 16:48 Sheet 12, 14, 12 is being deleted. The new one is not. So when sheet is created, we're getting the sheets. We're getting how many sheets there are. 17:03 We are getting the name of the N. All of them. Maybe not. Hmm hmm. Let's do a little toast here. 17:21 Right? Let's see if we, I think we just want this <laugh>. We need to get the name. Hmm Hmm. What could be the issue? 18:15 Hmm. Well this is funny. We are deleting sheets. We're not deleting the newest one. Created. Hmm.

That's an issue, right? 18:32 Four. New name equals zero. Mm-hmm. <affirmative>, maybe when it's created the trigger will, maybe the trigger triggers too fast. Do we need to like clear the cash or something? 18:57 Clear Cash in Google Sheets. App script . Not sure if this is, oh, it stopped flush, right? Utilities got flush. Something like that. 19:16 Nope. I think it's utilities. There we go. Spreadsheet that flush to apply changes made so far. Maybe that's it. Maybe it triggers it too fast. 19:57 Mm. Let's put it there first. Nope.

It's like, it's not getting the, let's find out if this length is the problem. 20:15 Let's just do con log dot log sheets. Do length sheets is gonna be get sheets. Maybe we need to like flush. 20:27 Let's see what happens. Maybe we can put this sheets dot length in a prompt. Let's see. So there was three. 20:44 Let's look at executions. We have a bunch of failures. Oof. Three. Can I read properties of undefined reading? Can get cheat name? 21:03 Hmm. Hmm. Is it deleting too quickly? No. Undefining. So this typewriter cannot read. I don't know if this is it though. 21:20 Mm.

Oh oh, it's also triggering. Oh, this is interesting. So it's also triggering when this happens. Oh, that might be what the issue is. 21:33 Oh. Okay. Listen. Hear me out. Okay, so we don't have a problem with the length. What it is is that of course it's not a flush issue. 21:45 Okay? So this trigger is triggering again, when it gets deleted, when we're deleting issue, that's the trigger for it. So when it goes to try to do it again, it's getting an error of saying this thing doesn't exist. 22:04 How do we fix that? <laugh>? Oh geez. What if, Hmm.

Okay, if that trigger happens and it's gonna trigger it again, could we just try, try it? 22:21 And then maybe we need to put a flush here. Utilities dot was that It was the flush again. Spreadsheet app dot flush. 22:40 Maybe this is needed because, Hmm. Right after it's deleted. Or maybe let's try both. See if this is gonna happen. 22:59 We're still not getting, so if I go here, we're still getting an error . Probably. Let's go back to executions type error . 23:08 Cannot read properties of undefined reading. Get sheet name . Yeah, because it doesn't exist anymore.

I mean, it's in the wrong spot. 23:16 Maybe get sheet name . Name. Hmm. So for each, what it's, what is it doing? It's going through each one. The first one is gonna be sheet one doesn't do anything. 23:31 It goes blah, we're gonna do nothing. Then it's gonna go through the second one and it deletes it, it causes it to run again. 23:41 No. Cuz then wouldn't it be fine if it just ran again? Or or maybe we have to put a break here because if it what? 23:55 Scene. Oops. What if it's instead of a flush? What if we just do a break?

The thing is break might be the issue or the lack of a break might be the issue. 24:16 Still getting the error . How do we say if it's the first time it runs, don't do anymore. Go again. Or, mm. 24:31 Because we wanna break out of this break. Not breaking out of if in app script . See what it shows up. 24:58 Now here's this flush again. Hmm. So it is maybe the flush. And cuz the third one isn't gonna be the, oh, maybe that's an issue. 25:25 Okay. So the issue is that we're using this get sheet's. I get sheeted name.

When it comes around to the third one, it doesn't exist. 25:40 Hmm. Hmm. Oh, well it, see, it's not deleting them. Hmm. So instead of a break, let's do flush utility sleep. 26:01 Mm. What if we only wanna do this once? No, we don't wanna do it once. Let's look at this error again. 26:20 Executions still running. Ah, it's taking four seconds to run. Because I did <laugh>, that's why cause I did this 4,000 sleep. 26:41 Of course. So each one it's taking four seconds. We can probably, well is it work? Sort of working? Mm. Sort of stopped. 26:55 Let's see. Stay. Same thing.

Cannot read properties of undefined where you can get sheet name . Line nine. Nine, nine. Yeah. 27:08 It won't get the sheet name cuz it's gonna be the third one and it thinks it's a third one. Hmm. 27:15 So maybe this is not a for loop. We need, we need a break out of it. Hmm. Wow. Loop or like for each, but it's always gonna be the same, right? 27:27 We're gonna get to the last one and we're not gonna be able to delete it cuz it's moved. So I bet <laugh> here's what I'm thinking is we don't put this into, yeah. 27:43 I, ah, I think I fi figure this out. Okay.

We can get the name of each one. What we need to do though is create a new array. 27:59 We have this new array. Create that array and then go delete it. Not all in the same loop. I, I sort of got a little lazy here cuz I was like, oh, let's just do it all at once. 28:09 Get the names each one. Compare it. But we don't need to do that. We just need to push the names. 28:15 We, we had it before. Okay. All right. <laugh> new names dot push. This is gonna be great. New name, Right? 28:27 Then take this, put all of this in one. Where , okay, now we have a new array.

These new names. Now we go another one. 28:47 Okay. This is what I was trying to do before, before I equal or j equals zero. J is less than new names. 28:58 Dot length. We're getting the length of it. Length. We then j plus plus. And now here, this is where we go. 29:10 Each one of new names. Oops. Oh shoot. Well we'll figure this out again. Spreadsheet app dot get delete sheet . And inside the sheet we're gonna do here spread variable name, sheet name equals new names. 29:40 J variable and this. Nope, that's not right. Yeah, yeah, it's fine. Yeah.

And then variable delete sheet equals spreadsheet . App dot get active spreadsheet dot get sheet by name. 30:01 Now we're getting the sheet by name. This is great sheet name right there. Delete sheet will be here. Now this will work, right? 30:11 So instead of doing it all in one, we're getting the name, comparing the name and deleting it. When it hap what happened is this, I is going to like three then it's trying to find the name, but that sheet doesn't exist anymore because it's deleting. 30:24 All right, great. So now we've fixed that issue.

We now have two floor loops, one of which is creating all the names of all the sheets that exist. 30:34 Then the second one is going through all the, each of the names in that array and grabbing each one and deleting it. 30:39 That, wait, we don't wanna delete all of them. Wait, wait, wait. If oh, here, let's do this. If we don't want to do sheet name , if sheet name is equal to, Ooh, we have two sheet names here. 31:06 Sheet name whitelist or Yeah. Is equal to whitelist. Do nothing else. Actually, we can just do not equal to instead of else. 31:24 And we're gonna go delete that.

All right? Whew. Now if it's not equal to this one here we can delete it. 31:37 Okay. Let's see if this works. It is deleting everything. Great. Ooh, wow. This took a half an hour. Wow. Okay, so now everything except one sheet. 31:49 Now if someone else comes to this sheet, I give them edit access and they want to add a new tab. 31:56 They cannot, they are for forbidden from adding new tabs <laugh>, other than she want. And the issue is if you change the name of the sheet, I think it's gonna be deleted. 32:11 So that is an issue.

I don't think we're gonna <laugh> I don't think we're gonna solve that now. Maybe, I mean, I think we're gonna get an error . 32:20 We, I don't think we can delete the last sheet. Yeah, we can't even delete the last sheet. So there's going to be at least one sheet existing. 32:27 We don't need to prevent that. Well here actually what happens if we change the name? I think we're just gonna get an air. 32:38 Oh, it didn't delete it. Oh my god. Because yeah, it's gonna be deleted. Okay. Sheet one. That's a bit of an issue. 32:49 I wonder how we could fix it.

I mean, you got the white list, you got the names. I guess the question, next question would be, can I create a white list of sheets with different names? 33:02 Or could you say like the position, then you're gonna have an error if like you move that position and then create a new one. 33:19 <affirmative>. Hmm. I mean it's going to, it's, it's gonna have its problems, right? There are pros and cons to this. 33:28 I don't know if we can go through all the edge cases in this video.

We can at least <laugh> delete things that are not named properly. 33:37 Maybe we can somehow prevent you from changing. I mean, you can protect sheet, right? You can do this, right? You can say protect this sheet. 33:48 Sheet one, set permissions restrict, show a warning when editing this range . Yeah. Done right. So let's, well, <laugh>, I was gonna say add some and see if we can. 34:03 You, you shouldn't be, even if you change, you're trying to edit part of the sheet. Okay. I mean, it gives you an error . 34:12 It's, it, it's gonna delete it. Okay.

This just doesn't work. But it won't delete the last sheet, so. Hmm. I mean what you could probably do is like, if you're like never delete the first sheet you could do an or here or if you could sort of what is that? 34:42 Basically can say sheet variable. First sheet is equal to this and just put it zero instead of an I. Now this first sheet Or sheet name , it's not equal to. 35:22 Do I get sheet name ? I think this is it. So now if if it's not equal to, if it's true. 35:46 Oh my god, this is a bit of a weird f true false thing. Okay.

Or, or so it will happen if, If She name is not aunt. 35:57 This is an aunt. Yeah. It should be an ant, not an R <laugh>. Oops. Yeah, I think this is fine. 36:21 So even if we delete change the name of it, it shouldn't delete 33. There we go. Perfect. So as we add more, they're all gonna be deleted except the first one or sheet one. 36:37 So if you somehow, yeah, you can't really add more. So this is a pretty cool thing. I think this is a really fun, I hope I helped <laugh> figure this out in some way. 36:48 This was a fun thing to do.

Delete all sheets other than this one. No. What's a good name for this? 36:57 Well we've learned an installable trigger this which one <laugh> was it? This change on installable chain trigger white list sheet names to not delete using installable trigger , Something Like that. 37:23 We use no formulas. We just use a bunch of functions in here. Ah, I don't think that really was a good name. 37:33 Well, you'll see the title of it, right? We'll figure it out. Delete all, all other sheets.

If someone creates a sheet, well, you'll figure out the Thanks for watching. 37:51 Thanks for watching this 38 minute video. Wow.</div>