0:00 So someone asked how to move blank rows, so we have like a column here with info we have yes, or no, or blank, and we only want to move the blank ones.
0:09 We want to move the entire row over to the sheet blank. Now, how do we do this? I'm going to show you actually two ways to sort of do this in Apps Script, and one might be better than the other, which is, here I'll show you the way that I started it.
0:24 We create a function moveIfBlank, we're just naming this moveIfBlank. We get the active spreadsheet, the column to move is going to be two, but we're not going to use it yet.
0:34 We get a column array, we're just setting the column B2 to B here, setting the range, getting all the values.
0:42 So this gets us the entire column of values, including the blank ones. The blank ones are just going to have nothing in them, it's just going to be a blank value.
0:51 Uh we're going to get the first sheet info, the second sheet is blank, and that's just the names here. So if you're using this, you probably want to capitalize this whole thing, blank.
1:02 So we two sheets. Now this is the most important part, uh it's a for loop, we're starting at zero, we're going until the entire length of the column array, which we got up here, the column B, and we just put the range here as we usually use in spreadsheets.
1:20 B two to B. We're starting at zero, that's going to come in and be important later, but at the first we just want to know if the actual value there is blaming, there's just two quotes here so there's nothing.
1:36 inside these quotes. And if that's true, then it's going to, then the next two things are going to happen. But if it's not true, we're just going to skip it.
1:44 It's just going to check for each one, basically for each item in this array, uh start at zero, go to the to the zeroth, the first item, check if it's blank.
1:56 If it is, here's what we're going to do. We're going to go and we're going to get the last row of sheet two, which is in case blank.
2:04 And then we're going to move the range, which is i plus two. Remember I said this i zero comes in later.
2:10 It's because we want to get the row here, but we have to add two because we're using, we're starting at b two.
2:17 So that's the first row. So i plus two here. We're only getting two columns out of this. We're going from the first to from the first column to the second column here, and we're going to use the move two.
2:31 So we're taking sheet one, getting the range, getting that specific row, then we're going to move two. We're going to use this function called move two.
2:41 I'm going to show you how this, what it looks like. So we have a blank sheet here, we have info, and the only one blank right now is this fourth row.
2:50 So let's run this. I'm going to run. It's going to run fine. I'm going to go back, and have a blank row here, and our blank one now is what was blank on info.
3:01 But we have this pretty horrific looking uhm blank area. What it did is it actually cut and pasted it. So it, say.
3:12 This one was blank. This is what it did. It took this two rows, did command x, or it did uh cut, went to blank, went to the last row, added one, and then paste.
3:27 And so we get. the exact same result here as if we cut and paste. And this is not necessarily saving us time.
3:33 We still, sure it's saving us time of moving each item, especially if there's multiple items here. But funny enough, it's actually keeping a blank one.
3:41 So if we run this again. Let's run it now with a bunch more blanks. We'll hopefully see it executed correctly.
3:50 Now we have more blanks. Here, it moved them. And here we have all the blanks. It actually is moving these, but it's but because it's moving no values, when it goes to the last row, it's just adding the last row here.
4:05 Um, but this is not that great. What we actually want to do usually is we want to copy, uh, let's undo all of this.
4:18 And get back to here. What we actually want to do is usually take this entire row, copy it, paste it here, and then delete this row.
4:30 That's usually what we mean by move. Not just cut and paste the issue. So what are we going to change?
4:36 We're going to change this move to, actually I'm going to change, I'm not going to change I'm actually going to create a whole new function here.
4:44 Duplicate it. I'm going to call it copy to move. If blank, and we're going to use, instead of move to, I'm going to say copy to.
4:56 Now this copy to will just copy, so how do we delete? We're going to now go to sheet one. We're actually going to get this entire range, this range, again.
5:05 Actually, no, we don't need the range. We just need sheet one dot delete row. Which row are we going to delete?
5:12 We're going to delete i plus two. And now, we're going to save it. Now let's see if this works again.
5:27 So let's delete these two. So we now have two that are possibly going to move. We're going to use copy two.
5:32 And again, it's copying the information over. We can also, also copy just formulas if we want. We can add some options to this.
5:42 It allows us to do contents only true. If we just want, that's just a little extra information there. But let's run this, see if we have any errors.
5:56 We have the wrong one. That's so interesting. So what it's doing is it's getting the row, but it's not going through the four path.
6:08 Essentially, it's deleting this row, but then now when we go to another one, we copy it, we're getting the wrong I.
6:14 So what I think we can do here is instead of deleting all of the rows all at once, what we can do is go variable delete array.
6:25 Equals, and we'll create a blank array, and we'll go delete array dot push, and we're just going to push I there, and that's the I actually plus 2.
6:35 So we're not going to delete anything there, we're going to wait until after the for loop is completely done. And we're going to go to sheet 1, actually 4, I equals 0.
6:49 We're going to create another, actually we can do J here, J equals 0, J is less than, deleteArray.length, so the length of that array, if there's anything in there.
7:04 We're going to each time do J++. And now for each one, we're going to sheet1.deleteRow. So we have a couple problems here.
7:18 What we need to do is actually delete the rows bottom up, so that we don't get any problems with deleting rows and then they shifting.
7:27 And also, this array here is being initialized inside of the for loop. We should do it outside of the for loop.
7:34 So we create a delete array here. Ah, it's going to be blank. For each one that we're deleting, we're adding it also here on line 13, pushing it, and then we're actually deleting it.
7:44 We're deleting the, each row backwards so that we don't have any problems. So let's see if this works now. We have, let's say, Judy and Chelsea here are blank, no others are blank, and we should have this fixed.
8:00 Let's run it, see our execution started, it's running through all of them, we don't see any blank ones here, and we see Chelsea and Judy, perfect, so it's absolutely working fine.
8:11 So again, the first way we did this, right, is we just used moveTo. as a function here, and we're just cut- cutting and pasting over, unfortunately that's leaving a row blank, uh, and it's also not necessarily, uh, it's actually cutting and pasting if we have any formulas, so we can add this contents
8:29 only true if we have only- only one. the, uh, values, not the formulas. And I just want to point out again, like moving rows, when we say move, sometimes we don't want to use the fa- the Apps Script, uh, function that's called move, we want to use copy and add delete.
8:46 It's a bit more information, but it- does get the job done that we want to do, right? If you're watching this on BetterSheets, you can get the- this sheet and this Apps Script below.
8:56 If you're not watching this on, uh, BetterSheets, then become a BetterSheets member today at bettersheets.co.