How to Copy To New Tab When Checked

Beginner

About this Tutorial

Copy text from one tab to another based on a checkbox being checked.

Sheet Resources

Access Sheet

Copy Sheet

Preview Sheet

Video Transcript

<div>0:00 So, common in project management and any kind of task management as well, we have a series of rows that have information in them, maybe a little bit of information or a lot of information, and we find ourselves, based on some status or something happening, we want to copy it to another tab. 0:20 We want to just copy it.

We don't want to get rid of it, we don't want to move it, we don't want to do anything. 0:24 Now, one common thing you might think of doing is, let's say we're going to duplicate this and we're going to create a list of all the on-hold projects. 0:33 We'll create a tab called on-hold, we will delete all of everything here, and instead of these stages, we will just have a list of projects, and we'll use filter . 0:44 Now, filter seems like it's going to work, because we can use filter and we can say, hey, this condition , this E, is equal to true. 0:52 Now, we have no, nothing here, no projects.

But if we put a couple on-hold, come back, and we now see we have. 1:00 The information we want. We can actually just get the product name, project name if we want, and that means we can add some more data here. 1:09 But, the thing is, if I have this data , and I'm seeing it, I can't edit it. Watch, if I try to delete Facebook, I'm literally hitting delete. 1:21 Or, if I say Facebook project, I rename it here, for some reason. I get a reference error . This filter is only going to pull data into the to this tab. 1:32 And, it's not allowing me to edit it.

It's not allowing me to do anything to it. I could, perhaps, copy paste the values . 1:42 That's what I just did there, getting rid of the formula . But, now, if something else goes on hold, like this Bucky's project, it's not going to work. 1:49 There's no filter function anymore. So, how do we copy, when we click on hold, how do we copy to the on hold tab? 1:59 And based on. What we want, we maybe just want a little bit of data . We're just going to get the project name for now. 2:05 Well, we're going to need Apps Script for this, and it's actually much simpler than you think.

Apps Script wise, coding wise, I'm going to code the whole thing right here for you. 2:14 Basically, we're looking at projects tab, and we're saying anytime there's an edit, if it's in this column four, and we're changing a check box from false to true, then we want to copy that data . 2:26 So, let's look at our Apps Script and see what we need to do. We're going to call it move on hold. 2:35 On hold. And we're going to write a function here. This is what's called a simple trigger on edit.

And that's all we need to write for the function to work, is on edit with a capital E. 2:48 We need some event object , something that's going to hold all the data of what is actually being edited, what values are being edited. 2:55 We can call this event. Sometimes we call this E, just to make it five. 80% faster to type E. We can call it event right now. 3:06 And what we want to do from this event is gather the information. We want a variable of what is the value, like checkbox value. 3:16 Checkbox . We'll call it checkbox value equals event dot value.

So that's just saying what is the new value that's being edited. 3:26 We're going to log that right now, and I'm going to show you what that looks like. Vlogger. Checkbox value. We're going to log this checkbox value. 3:34 I'm going to save and I'm going to go back and I'm going to just delete Nike, and then I'm going to click go here. 3:42 Um, and let's click on hold, and I'm going to then look at our executions and see what was actually logged here. 3:56 It will refresh. Takes a little bit of time to refresh sometimes. So I just did it again.

Here I'll type in, instead of, I'll type in Nike's project, we'll go back here and we'll see, is this the one? 4:12 Now they're coming in fast and furious. Sometimes it takes a little bit of time. Google's servers have to go get it, save it, get it, and then bring it back and log it. 4:22 So we'll refresh a few times. And again, this may take a little bit of time oh I see what I did this is checkbox value. 4:31 I don't know why that happened. We just want value there. So go back, let's change this. We'll make sure it's saved. 4:40 Let's change this to Nike.

And we will look again at our execution. And there we have the information Nike. So it is saving that value in the database and saying hey, we edited the script, or we edited the sheet, we are going to change this from false to true, and let's go look at it. 5:01 Our edit, and here we see it is all caps true. So we are saving that data . So let's go and actually work with this and say based on that value, based on where it is, we can also get variable row equals event dot range dot get row. 5:18 We can also get the column. Column equals event. range .getColumn, in this case.

And now, based on that information, if I'm going row is equal to, actually row is greater than two, meaning it's not in the header , it's not in the first two column, first two rows. 5:41 And column is equal to the E column here, which is number five. It's the fifth. We need to use a five here. 5:50 Fifth column. If that's the case. And, one more and, double ampersand. Checkbox value is equal to true. Need one more. 6:02 Is equal to true. Then we want to copy this data . Over.

We got the row, so we know we can go spreadsheet app dot get active spreadsheet . 6:14 We also have to make sure it's on projects actually. That's the last thing we need to do. So variable sheet equals spreadsheet app dot get active of sheet.getSheetName. 6:26 And we also double ampersand one, make sure sheet. Is equal to projects. Get the active sheet , which is the project sheet. 6:38 Get range , row, uh column is one, and it is one by one row column. We just want the name. So we go variable project name equals. So we got the project name.

Now we need to put it into on hold so we're going to see spreadsheet app. 7:02 Uh actually we need get sheet by name. Here. Projects. There we go. That's going to work now. Now we say spreadsheet app dot get active spreadsheet . 7:21 Now we want get sheet by name. On hold. Oh move my face. Let's make sure we go on hold. Is that correct spelling, all caps. 7:31 Projects. Get sheet by name. Get range . We want the range to be uhm we need the last row plus one. 7:49 And we only want one one column one and only one cell by one thing.

So let's get the last row. 7:57 Let's get on hold, paste it here. dot get last row. That's all we need to do. Get last row. Now we get the range and we set value. 8:11 And we are setting the value that we got up here which is project name. So let's do that. So every time now if we click here ivy we should paste it here. 8:28 Ah I see an error that I made variable last row. O is equal to the last row. There we go. 8:34 Let's try that again. We're going to not delete it. Oh. Let's get ivy. Nike and ivy.

And we're getting range . 8:47 Ah that's probably because we are getting the project name we have to add get value here. That's very common to forget. 8:54 We're getting the range and we think ok we got that cell. But we actually have to get the value out of that cell. 8:59 I'll actually save that. Let's delete these. Now we want Bose and Facebook. And let's see if that worked. We're getting Facebook. 9:11 Grand. Fantastic. Grand. Let's see maybe we did it a little too fast. Yet to be named Nike. Perfect. Thumbs up. 9:21 It's working now.

We are able to copy any data once we have this checkbox , this specific checkbox checked. And we've done that with Apps Script pretty simply. 9:32 Right, just getting the check, checkbox value, is it true or false, getting the row, the column of this event, getting the sheet that we're on, making sure we're on the correct sheet, getting the right value in the right place, the right row, the right column, getting the project name which is just the 9:47 value from a range , getting the last row where we're supposed to go with it, adding one, and then setting that value. 9:52 There you go.

Hope this was helpful to you on how to copy to a new tab when something is checked here.</div>