Someone wanted to find out how to delete a row when writing a certain word. The word is cancel. We're going to write cancel here. in all caps, and we're going to delete this row. Now you're going to get some problems with this and I'm going to show you how to do this, what problems you're going to run into and how to fix those problems or work around them to make this safer and better to use. So first thing we're going to do is go up to extensions app script . This will open up Apps Script . Don't worry, I'm going to write every single piece of code for you step by step in this video. Just watch and type as I type. One function we're going to use is called the onEdit function . This is a built in function that uses an event here. We're going to make that just E, but that's fine. Just think of this E as the event, the actual editing that's happening.

Now, editing means we're going to go to the sheet, not the structure, the only thing that triggers when onEdit happens is when a cell is changed. When you're editing the cell, even this January to February, you select this drop down menu, you edit text inside, or you add text like cancel. If we do that, that's going to trigger this on edit function . We can only have one of these in a spreadsheet at a time. We need to know the row that we're on. So this is going to be the event dot range dot get row.

What is the editing value? What is the text that someone is going to be entering? And this is e. value. Simply that. We want to make sure that we just say value is equal to and we're going to write all caps cancel. That's the only thing we're going to do right now. If it's cancel, we're going to delete the whole row . So, what we do is spreadsheet app dot get active. Spreadsheet Actually, getActiveSheet is going to work here. We just need deleteRow.

There, it is gone. Done, deleted, never to be seen again. Here are some issues with this code. One, we're, we're not checking where is this value being entered. We're only getting the word cancel. Even if you put cancel in a different Column, it's going to cancel it. So even if you have, for instance, it's going to sound weird, but if you have the project called cancel, let's, if this just happens to be something the project name, it will also delete that whole row .

Another issue is that once we put in the word cancel, it is deleting it never to be seen again. I prefer that it goes to another page, another tab. As an archive, or a delete tab, and then you delete it from there.

There it goes. But also again, we don't want to delete this row. That's my main beef with this is we want to move this row. So let's do that. Let's get the row first. Get range row one and we need max columns. We just want the one row. The number of columns is going to be however many columns there are in this page.

We're going to delete everything in here except the header . Make sure we have a new row so we can go ss dot get sheet by name. Actually, we need something else. Variable sheet. Cancel sheet equals spreadsheet . App dot get active spreadsheet , get sheet by name, canceled.

We'll save it. And we're just copying it over. So we'll write cancel. And there it is. It's in the very top one. So actually we need to do last row plus one to ensure that that header stays there.

And this whole thing will move it, meaning what it'll do is actually copy the row and then delete the row. So let's see if we do it. Website design, redesign, cancelled. It's deleted and there it is. Website redesign. So that saves you a lot of headaches of, Hey where is it? We have to go and look in the version history and see it.

If we're on the sheet called projects variable, we want to make sure a variable active sheet name is equal to spreadsheet app dot get active sheet dot get name.

word if you're looking for more out of your Google Sheets, subscribe here on Better Sheets on YouTube. Feel free to ask any questions you want.