00:00 Hello. So Member Di here asks about deleting rows. So what he's asking, I'll just repeat his work. He has a big export software as many leads that are coming out.
00:16 And in those lead lines, those rows, there's some that are test test leads. So he knows those are test leads.
00:26 Whenever there's a value of 10 or above, it looks like they're 10 plus or maybe the literally 10 plus. But for what I'm gonna show you, I don't think that necessarily matters.
00:34 He wants to locate those and delete the entire row. So I think this is, I think the best way to do this is gonna be an app script.
00:43 So we're gonna go to sheet.new and I'm gonna show you a little bit of an app script in, depending on what the actual thing is.
00:51 I'll show you how to edit. So let's say there's a bunch of rows got some data here, right? And there's either the, we can do this a couple ways.
01:05 I'm gonna look for the, the string, the text 10 plus, but maybe we'll do it both ways. And also if any row is above 10 in the value is above 10.
01:18 So let's say this is some rows we wanna delete the rows that are above 10 or 10 plus. So we'll say these two, right?
01:33 We have some leads. Now if we import this data and we have these, this data here, we have to like physically, manually go through and delete this row, right?
01:43 But app script is gonna help us here. So let's delete rows 10 plus. We're gonna call it that. Let's go to extensions app script.
01:54 We're gonna write a little bit of code here to do this. So what we need to do, one is there's app script for delete row.
02:06 And let's look at that app script first, cuz that's ultimately what we wanna do. Deleting row is an app script.
02:13 We need delete row right there. So let's go back here. Delete row. There it is. And so in this class sheet, delete row row position, this is what we need.
02:32 We just need to know the row position. So as you see here, sheet dot delete row with the number one deletes the first row and it looks pretty simple here.
02:42 We just need some spreadsheet app, get active spreadsheet, we need to know what sheet name it's on. So let's do that.
02:48 Let's delete row. We're gonna get the row number here. All we need to do is know the sheet name could do that.
03:01 Well sheet is equal to spreadsheet app dot get active sheet. Maybe we can do it just with that. Mm. Let's do active spreadsheet dot get sheet by name.
03:20 The name's gonna be sheet one and now we can do sheet dot delete row. There we go. And right here, this is the script that we just insert a row here, delete row and it will delete that row.
03:36 But how do we call this function? How do we get this right? So there's a couple ways, but let's just save this.
03:46 First we gotta get this organ button to go away, saving the project. Okay? Now let's use this in a, in another function.
03:57 So we have some options here that we can do. We could have a little custom menu here that we just say, Oh, delete these.
04:05 We could have it loop through and look for the rows. I guess we could do that both. So we want custom menu, custom menu, google script, always come here and copy this.
04:27 So we have on open, we're gonna get this custom menu and we want to delete one row and we're gonna delete one row.
04:38 Okay? So what we could do here essentially is, hmm, we could prompt for function first, we need to write the function.
04:48 We could prompt for the the, the row number. And we could do one at a time, right? We could delete one row, we could say spreadsheet, app, dot get, ui dot, prompt.
05:14 Okay, so we need to enter row number. And then we have this, if we click okay, we want response, Response text.
05:31 So this is the variable row equals, And then we wanna delete row Row. So now if we call this function, delete one row, it's gonna ask us enter a row number.
05:48 We're gonna enter that row number and it's gonna go and delete that row. And that's from a custom menu. So we're gonna hit command s.
05:56 Once we save this, we need to create a custom menu up here. So we're just gonna refresh the sheet and when we open that again, it'll show up here.
06:08 There it is. Custom menu, delete one row. If we wanna delete a row, it'll ask us first, okay? We need to authorize share.
06:16 The first time we run the script, we have to allow all of this. Great. Okay, delete one row. We wanna roll re delete row one.
06:26 Okay, Response is not defined. Response is not defined. I understand. Probably might be because it thinks it's a string. So we need to turn a string into a nu, okay?
06:41 I think it was actually not that it was a weird number. Let's take this out. It was not that, it was not a number, it was that this response was not coming correctly.
06:50 So I added yes, no buttons. So let's see what happens. Uhm m it was because I didn't have the variable response here.
07:04 High wire flying act, doing this live. All right, we got the details, we got, we figured it out. Let's keep deleting the row one.
07:15 There we go. So this code works. So what we're doing is we are deleting one over, we're getting the prompt.
07:22 We're asking, Hey, what number do you want? What number row? If that, if we say yes, then we get that number and we delete the row, we call our function and we wrote earlier, function, delete, row.
07:33 And we say just delete that one row. Okay, we, we got this working and we know it works, right? We've used this custom menu to give it some action.
07:43 Great. So now we still have the idea that, okay, once we hit that button, we know we just wanna hard code that every row with 10 plus or that's over 10, right?
07:55 We're gonna create those two situations. We want both of those to be able to just go in and delete all those rows.
08:01 All right, let's think about this. Let's say we have another item here. Let's delete all this or not all this.
08:14 Yeah, Add item. I'm gonna add another item here. And we want delete 10 plus rows. Okay? So first we need to create this function, function, delete 10 plus rows.
08:41 Okay? Now we need to get the values of the B column. Alright, so we go variable. Let's say B column equals spreadsheet.
08:57 App dot get active, spreadsheet dot get sheet by name. Sheet one dot get range. We only want the B column.
09:15 So we want row one, column two. Number of rows is gonna be all the way to the last row. L row you can say last row actually for a variable.
09:31 And the number of columns we want one column get values. Okay, where do we get that? Last row variable, last row equals.
09:43 We can take all of this, cut it and call it sheet one variable sheet one equals this. Now we want sheet one dot get last drill.
09:57 There we go. So now we're getting going to sheet one, getting the last row, we're getting all of the values.
10:04 We can also log this if we want to just look at it. This variable B column should be an array.
10:12 Should be everything in the B column. So let's look at this. Let's run our delete 10 rows. Gotta review the permissions.
10:24 We gotta give it the permissions again, allow, there we go. We have an array of 10 pluses. Now the only ones that are 10 plus are these first two.
10:36 So what are we gonna do? We're gonna get a four loop. Let's do this. We're gonna get a four loop and we want it four I equals zero semicolon.
10:50 I is less than this. B column dot length semicolon, i plus plus. So every time this runs, if you wanna learn more about this, take my class spreadsheet automation, we go into the for loop and we teach this very in depth.
11:11 So this for loop for each one, what we're gonna do, we're gonna look at if B column I is equal to 10 plus, right?
11:33 The string 10 plus. Then what do we do? We want to delete that row, right? So we go delete row.
11:46 But we wanna run this actually we wanna get all these first and then delete the row. Cause what's gonna happen is if we delete the row, then find the next one and delete the row, it'll be wrong.
11:58 It'll be deleting the wrong ones. So we need to create a new array. Variable variable, delete array. It's equal to new array and we go delete array dot push.
12:23 I I, right for each one, if it's the right one, we wanna just log it. So essentially, right, if this is gonna be correct, we're gonna get an array of two items, zero and one.
12:37 So let's log this and see, Let's make this a little easier to read this for loop here. And so we're just gonna log delete array.
12:55 We're not gonna delete the rows yet. We just want to see it. So perfect, we got zero and one. So now if we move these, let's say we move these down to here and run this again, we will get two and three.
13:11 Perfect. So now once we do that, how do we delete the rows in the proper order, right? So I guess what we gotta do is figure out how to delete each row in order.
13:25 Okay, I think I figured something out here. Basically we can delete one row, then we need to subtract one from everything in the array and then we can delete again.
13:37 Okay, Hope this makes sense. Here's the idea. If we delete row two, the first time we go through this, we delete row two, then row three is now row two.
13:48 So we need to delete row two again to delete the third one here, I'm gonna just double check that this is correct.
13:56 So this should be an a wrong thing. We're gonna delete each of the items, delete the rows. And what'll happen, my theory is that once it deletes this third row, the fourth row, it also needs delete.
14:13 But it will also be, it'll be the fourth row now. So let's try this. Let's look at delete 10 plus rows And let's try to run this delete plus 10 rows.
14:26 Run it got two and three and see we still have a 10 plus there. So we are correct that it's wrong.
14:36 Okay, I think I have a weird way to solve this. So before we get to this one, we need to go through and subtract one from each of the numbers for its position.
14:51 That means if it's in the zero spot, we minus zero. If it's in the second spot, which is the one we minus one.
14:58 So we can get the delete array here, delete array minus one and then create a new array variable. Final delete array equals new array.
15:33 And then final delete array, push This. So for each one we're gonna subtract one. No, we're gonna subtract its position.
15:46 So the zero position, we minus one, one position, we minus one, two position, we minus two. And then we get a final delete array in which then we have delete rows.
15:58 So we do final delete, then that is the final one might be a little strange. So just to review, we are finding where it is, 10 plus the string, 10 plus we are then finding, going through each fi, getting that array creating a new array that we're gonna minus its position.
16:24 So then we'll delete those rows in that particular order. So this should go, should find 2, 3, 4, then go through the first one minus zero minus one from three, which will be two and minus three from this position.
16:46 And we should then it should be fine. So let's see, let's test this, run it and all of those should be deleted and they're not because row one, okay, this is gonna be sound weird.
17:00 Row one, this is zero. Oh, we need to add one again. I think this is gonna work. Let's see. We can have some extra.
17:16 So we wanna delete row one and three. Let's see if that actually does it. One and three are done for perfect.
17:27 Let's go two, four, and five. Let's look back here. Perfect. They are done for, All right, we have correctly done this few four loops.
17:39 Again, if you wanna learn the for loop, get spreadsheet automation, come check out spreadsheet automation class. Now what we can do is this deletes 10 plus words with the string 10 plus, but I'm gonna take this entire function, copy it and say 10 over 10, I'm gonna call it over 10.
18:08 And instead of this, if it's equal to 10 plus I'm actually gonna say is column play, oh, greater than 10.
18:18 So this is the other situation that might happen. See search for all values, 10 plus. So that could either mean the string 10 plus, which is a crazy idea.
18:28 I don't know why I thought that, but really it just means, I think he means every value that is 10 or higher or higher than 10.
18:37 So actually you can make this over 10 or greater than or equal to 10. We'll do greater than or equal to 10.
18:44 So let's see, we have 10, 11, 1 and one. So we wanna delete the first two rows. This is gonna be delete not 10 plus, but 10 over 10 rows.
18:59 So let's change this and let's see if this works. Perfect, this works absolutely perfectly. Let's add some more here. Let's do 88.
19:13 We wanna delete these two rows out of all this. And if we write, run, delete over 10 rows, we got those two.
19:22 Perfect. So this code works. Hopefully it's useful for you Dylan. In either of those situations, if you have some string or if you have the number I'm gonna make this sheet available.
19:38 What we can do alls well is add these, delete over 10, delete 10 plus. So we can create this menu.
19:54 I'm gonna save this. I'm going to, once this is saved, it's saved. Refresh this page. Now in this custom help menu, we will have the option to run that script without having to open app script.
20:10 So custom menu, delete plus 10 plus. So if we have 10 plus, we only wanna delete row eight. Let's see if it works.
20:18 Perfect. We wanna do the same Dylan and then have this like 100 let's over 10 perfect deletes only those rows that we wanted to delete.
20:35 Awesome. Hopefully this helps you Dylan. And if you're out watching this video and trying to figure out what I just did you can get the sheet as well and look at the extensions app script and look at those four loops and check it out.
20:48 Hopefully this was helpful to someone trying to delete rows with app script by.