Move Entire Row when a Cell is changed to "Yes"

<div>00:00 Hello, this is a really fun and very commonly asked question in Google sheets.

I get asked this fairly often and sometimes I answer the person directly, but in this video, I'm going to answer everyone who has this question, because I think it's a really useful tool. 00:20 But I will, at the end, tell you all the drawbacks and some of the issues, cause it's a very simple script. 00:26 What we're doing is we have a line of data or a sheet of data , and we want to say, approve this or go through it in some way, add some information and let it get to go over to another sheet. 00:41 In this case, we're saying move to sheet two and column two, we're saying yes.

And when that happens, you see that eight number over on sheet two, it gets moved and everything in that row gets moved. 00:55 All right. I will show you how this works and you're able to go and grab this in this sheet and in this script all you have to do is go obviously to the sheet that I shared with you in the comments here go to tools, script editor , and here is what I have done. 01:16 And this is a code that is using the function on edit.

You can do multiple things on, edit on edit is a function that exists that you fill in what you want it to do. 01:28 And this will check every single edit. I'll go more into what that means later, but let me just walk through this very, very quickly here. 01:36 What it's doing is it's getting the active sheet and it's saying, Hey, is this the sheet one, right? If S dot get name is equal to sheet one. 01:47 And then it's getting the act of cell here where we're trying to figure out what cell are we actually editing on? 01:53 And then over here we say, is that cell column two.

So here's the two in there. And does the value that that cell has now after the edit, is that equal to S now if all of those are true, if, if you're editing on sheet one, if you're in column two and the value that the cell has at the moment is yes, 02:14 then we will take that entire row . And we're going to find out what the last row on sheet two is right here, get last row. 02:23 And with this rate and with these three functions , perform functions , work in a Pender row.

First, we're going to make sure that there's a place on sheet two. 02:36 So we actually create a renewal row with nothing in it. Then we copy, we use this dot copy to going from range , which is she wants to end ranges sheet two in the last row. 02:48 We're getting the raw right row here that we're on. And then we're going back and we're deleting the row.

So now if you want to use this formula and you want to do things like you don't want to move the road, but you just want a copy of the row. 03:02 All you have to do is go to this line 18, tap a couple, a, these are backsplashes here in front of that delete row . 03:10 And you get the exact same thing.

Everything gets moved or not moved, but copied to sheet two, but it doesn't get deleted from sheet one, which is really useful. 03:20 When you want to say, Hey, I want a very basic sheet of data that I append some enrich with some information. 03:30 But maybe I want to send five to six of these items over to an editor , or I need to send it up to approvals to a manager or something and move that data around. 03:41 And I don't want the manager to have to go through all of, all of these this whole sheet.

So we create sort of another view for him on another sheet and say, Hey this is what we did. 03:52 And here's the five items. So he did, you know, that's really helps you manage your data , but I will walk you through some of the issues. 04:00 So let me just show you right now. This is going to show you what it looks like when you just copy it. 04:05 So see this doesn't delete, but on sheet two, we have the data . You can also change this column to say a checkbox . 04:18 Now the tricky part of check boxes is that there are two States true and false and uncheck.

Checkbox is false and a checked checkbox is true. 04:30 So if I want to say change the what is needed to change this to the next sheet, all I have to do is change this column to column four. 04:44 Cause that's D column where the check box is, and then the value it has to be true. Now let's, I'm going to type in true without quotation marks. 04:56 Save that to double-check. And now all I want to do is move this one and let's double check. Oh, long time movies is going to copy it. 05:05 Right? Okay.

It's not doing so let's, I'm going to go over and check out why it failed, probably because I need to put that in quotes. 05:17 Yep. True is not defined. So let's do this. Let's just move that to quotes. And now we're going to see that actually see if two got moved to do, do. 05:37 Yeah, It looks like it did. It thinks it completed it. Okay. Let's double check that it's it's not deleting anything. 05:52 So let me bring that back. Just so we see some, something happened instead of having to check double check here. 06:00 Okay. Right now just fix the issue.

It just had to be lowercase true. So now we move 10 over to sheet two. 06:18 There it is. With the check box checked, go back to our enter all this is Laura caser. So I had just had an uppercase to Altru. 06:26 It didn't work. It worked with lowercase. True. So I'm going to leave the if you're watching the video, you can see that this is true, but if people are not watching the video, I want to make it so that we are editing this on what was originally here, right? 06:47 The, not the checkbox , but the, yes. So we just change this back to yes.

And so you now know how to move a row to another sheet. 07:05 You also know how to move a not move a copy a row to another sheet. And I think this is really exciting because it offers a lot of possibilities and a lot of use cases for managing your data . 07:16 Here's some little drawbacks. If you are at any, a great deal of information let's like, take this I'll show you actually. 07:28 So say, I want to take this. Yes. And apply it to all of these. And I want all of these to move. 07:34 I do that. And when I used to do this, it would only move the top one.

And now it's moving. 07:43 None of them. This is because in this very simple formula , in this very simple script, we're really only looking at one thing like one row at a time. 07:56 We even counted here one row at a time in this sort of range . It says, get the row, get the column. 08:02 Then the number of rows, which is one and the number of columns and even setting a hard count of one here. 08:09 So this is really only useful.

If you want to do this one at a time, again for approval sake or for just enriching some data say you want not just the data to be the correct thing. 08:23 Like, yes, you just want to know, Hey, I made a change in column B. All you have to do there is delete this section that says, and our dot gate value equals. 08:33 Yes.

If you delete that, then any time any edit happens in column two or column B, then those rows will move. 08:44 One other issue with this, if you are using this to with other people, like say you have a large amount of people editing data or adding data the script, as you can see does not move lightning quick. 09:01 It's not going in a moment.

It is happening in less than a second, but when you have a 10, 20, 30 people making edits all at the same time, it gets a little bogged down. 09:15 One way I got around this was just letting everyone know, Hey, sometimes the, the script does not necessarily work exactly. 09:23 If two people hit the same edit button at the same time. But people were very, if it was inside of a business, I knew everyone and everyone was very compassionate about that. 09:32 And, and sort of knew that the script is saving them a lot of time and energy and focus.

And so if it does fail in one, out of a hundred times, it's fine. 09:43 They'll know how to fix it. They sort of just delete, they go delete that and then do it again. That's how you get around it. 09:50 You sort of undoing it and then just do it again. The other issue and the last issue is sort of a data issue and a day, I don't even know what it's called, but essentially if you are not moving the data , if you're not saying, Hey, take this entire row , moving over to this other sheet and make 10:08 sure it's deleted. What you're going to have is a data in all sorts of places.

But the good thing is out of this is that it's the structure of the data . 10:20 Schema is going to stay the same. You're getting the entire row . But you are going to make multiplying your data and where it is. 10:30 You might fall into a rabbit hole like I did when I first discovered this and started doing this kind of thing. 10:37 He was like, if you can be moving data and copying data to certain places, you can create something like a change log. 10:42 You can say, Hey, I don't want to just move this over to sheet two.

I want to keep a record that this was moved from sheet one to sheet two. 10:51 And so you start having like a third she, where it's like every single change, every single edit you keep track of. 11:01 If you do that and you have say 10 to 20 people, or you have a sheet that you have, you know, even 10 to 20 edits or 30 to 50, 60, a hundred edits a day, which doesn't sound like a lot, but it adds up, you might have 360 500 that's like 36,000 rows. 11:23 But by the end of the year, or you might want to just you might want to do things in bulk at them at that point. 11:31 And again, we'd go back into what I referenced earlier.

These bulk edits don't really work on with this on edit or this code. 11:40 Just a few pitfalls that you might run into. I think this is super simple script. So you're more than welcome to use it. 11:50 Say once you understand how to use it, use it on a CRM.  Very good for that.

I one of the most interesting videos I did is use a transpose to, to show data in a different way on a sales CRM, if you have used that, and if you're building a sales CRM, and you have multiple editors, this 12:11 is really awesome to use to get information and enrich information and, and, and get that workflow and the process really moving without you having to copy paste and cut lines and rows when you do these changes and edits. 12:27 Thanks for watching. Have a good one. Bye.</div>

Move Entire Row when a Cell is changed to "Yes" - The $75,000 Google Script