Automate the apps script we just created that moves blank rows from one sheet to the other sheet. Original tutorial to write the apps script
Academy ↓
Hey there stranger!
Automate Move Blank Rows
About this Tutorial
Sheet Resources
Video Transcript
0:00 So in a previous video we created this app script that moved blank rows, we can show you again and I want to show you how we can automate that so in this video I'm going to show you how to automate it but we're just going to run this copy to if blank what this is doing is it's cutting it's finding each
0:17 of the blank items that is blank in the B column only. Uh we do have to authorize this and once it's authorized and run it has now moved things to the blank column uh to the blank sheet.
0:29 Okay but how do we do this automatic What does automatic mean or automate mean here? Uh one thing we can do is we can add a menu item so we don't have to come into the app script.
0:42 So right now we go to extensions app script and the app script is there. We can run this as many times as we want but we have to click run.
0:48 We have to select this. What if we want to make it a little bit easier and we can do that function on open.
0:55 I have the app script here at better sheets. I'll just snip it's slash custom menu. Uh two functions. We have this already written.
1:07 So we can actually delete that and paste here and instead of custom menu call it automation menu. We'll take the function name, put it as the menu item here as the second item here.
1:19 First item. And this first text here we're going to call uh move blank, move if info blank. So we can write anything we want here.
1:32 We're going to save this. Make sure it's saved. And then refresh. We have to refresh the sheet or close it and reopen the sheet.
1:40 And right next to help comes this automation menu. So if there's any of that are blank, say these three here.
1:45 And we want to move them over to the blank page. We can click in the automation menu. Go up to move.
1:52 If info blank and run it, it'll ask us to authorize. It'll only do that once the first time we run it.
1:58 So now we can run it and it'll run the script and it will move those items over to the blank.
2:04 Here they are. Wait, but is that really automation? That sort of- some of my automation. Sure we've now made it a couple clicks but there's something else we can do to make this truly automatic and we can run this sort of every night.
2:16 And so if you have an automation that you want it to be done say at the end of the workday or before the workday starts or you want to have it run it some portion of the day.
2:27 We're going to go to our app script. I'm going to go over to the left side and call triggers. Click that and on the bottom right we're going to add trigger.
2:35 We're going to choose which function to run in this case it's copy to if blank. From the last video we have this move if blank that's creating sort of a blank space that leaves a blank space because it's just cutting and pacing.
2:46 So the one we want to copy and delete the row that we had before these copy to. So we're going to choose that.
2:53 Keep uh head Thank you. Here because there's no other option. If we have a failure at some point at any time we can have this email us and this is what the notification means.
3:05 It'll email us and say hey this failed. And we may want that maybe it's failing for some reason. sometimes and probably for this reason it might fail if there's no uhh page to move to if we've renamed the pages or something.
3:21 And I want to know as soon as possible. If you want to know as soon as possible choose notify me immediately.
3:26 If you want to just know each day. Hey, what's happening? You can just leave it at daily. The events source is going to be time driven.
3:33 Now here once we select time driven we can select some options. We have a daytime where we can run this every day let's say at the end of the work day.
3:42 After everyone's done working seven p.m. to 8 p.m. it will run. We can also do it in the morning like if we want to do like 2 3 a.m.
3:50 or 4 to 5 a.m. before the work day starts can save. Once we save that that trigger has been created.
3:58 We're choosing an hour in the day it's not going to happen at a specific time but it will happen within that hour and it will be a random time within that hour for the first time it runs and then it will run 24 hours each after each time.
4:10 We can always come to this 3 dots over in the corner and delete trigger if we want to delete this forever and create a new one.
4:16 Or if we're like hey we don't need to clean this up anymore all so often. We can also let's do another one copy of blank.
4:25 Do time-driven. We can also do every week let's say. We want to do it every Saturday. Umm. Or every Monday before the day starts we can do you know three to four a.m.
4:39 on Monday anytime you want to run it again. If you have any problems or if it fails to run it will notify you.
4:48 This is important to know like maybe you want to run it early in the day so that during the day you know okay there's a failure or you want to run it overnight so that in the morning when you're checking your email oh it didn't run we can run it manually.
5:02 Right you can always come in here. And uh run this automation menu or if you don't want this automation menu created you just want the trigger automatic every day happened great you can add that you don't need this on open in order to use the triggers you could just keep the function here.
5:20 If it for some reason it's having problem you can always come in here click on it click run and then if it runs and it has a problem here it will show you and you will be able to debug it and figure out the problem and fix it pretty quickly.
0:17 of the blank items that is blank in the B column only. Uh we do have to authorize this and once it's authorized and run it has now moved things to the blank column uh to the blank sheet.
0:29 Okay but how do we do this automatic What does automatic mean or automate mean here? Uh one thing we can do is we can add a menu item so we don't have to come into the app script.
0:42 So right now we go to extensions app script and the app script is there. We can run this as many times as we want but we have to click run.
0:48 We have to select this. What if we want to make it a little bit easier and we can do that function on open.
0:55 I have the app script here at better sheets. I'll just snip it's slash custom menu. Uh two functions. We have this already written.
1:07 So we can actually delete that and paste here and instead of custom menu call it automation menu. We'll take the function name, put it as the menu item here as the second item here.
1:19 First item. And this first text here we're going to call uh move blank, move if info blank. So we can write anything we want here.
1:32 We're going to save this. Make sure it's saved. And then refresh. We have to refresh the sheet or close it and reopen the sheet.
1:40 And right next to help comes this automation menu. So if there's any of that are blank, say these three here.
1:45 And we want to move them over to the blank page. We can click in the automation menu. Go up to move.
1:52 If info blank and run it, it'll ask us to authorize. It'll only do that once the first time we run it.
1:58 So now we can run it and it'll run the script and it will move those items over to the blank.
2:04 Here they are. Wait, but is that really automation? That sort of- some of my automation. Sure we've now made it a couple clicks but there's something else we can do to make this truly automatic and we can run this sort of every night.
2:16 And so if you have an automation that you want it to be done say at the end of the workday or before the workday starts or you want to have it run it some portion of the day.
2:27 We're going to go to our app script. I'm going to go over to the left side and call triggers. Click that and on the bottom right we're going to add trigger.
2:35 We're going to choose which function to run in this case it's copy to if blank. From the last video we have this move if blank that's creating sort of a blank space that leaves a blank space because it's just cutting and pacing.
2:46 So the one we want to copy and delete the row that we had before these copy to. So we're going to choose that.
2:53 Keep uh head Thank you. Here because there's no other option. If we have a failure at some point at any time we can have this email us and this is what the notification means.
3:05 It'll email us and say hey this failed. And we may want that maybe it's failing for some reason. sometimes and probably for this reason it might fail if there's no uhh page to move to if we've renamed the pages or something.
3:21 And I want to know as soon as possible. If you want to know as soon as possible choose notify me immediately.
3:26 If you want to just know each day. Hey, what's happening? You can just leave it at daily. The events source is going to be time driven.
3:33 Now here once we select time driven we can select some options. We have a daytime where we can run this every day let's say at the end of the work day.
3:42 After everyone's done working seven p.m. to 8 p.m. it will run. We can also do it in the morning like if we want to do like 2 3 a.m.
3:50 or 4 to 5 a.m. before the work day starts can save. Once we save that that trigger has been created.
3:58 We're choosing an hour in the day it's not going to happen at a specific time but it will happen within that hour and it will be a random time within that hour for the first time it runs and then it will run 24 hours each after each time.
4:10 We can always come to this 3 dots over in the corner and delete trigger if we want to delete this forever and create a new one.
4:16 Or if we're like hey we don't need to clean this up anymore all so often. We can also let's do another one copy of blank.
4:25 Do time-driven. We can also do every week let's say. We want to do it every Saturday. Umm. Or every Monday before the day starts we can do you know three to four a.m.
4:39 on Monday anytime you want to run it again. If you have any problems or if it fails to run it will notify you.
4:48 This is important to know like maybe you want to run it early in the day so that during the day you know okay there's a failure or you want to run it overnight so that in the morning when you're checking your email oh it didn't run we can run it manually.
5:02 Right you can always come in here. And uh run this automation menu or if you don't want this automation menu created you just want the trigger automatic every day happened great you can add that you don't need this on open in order to use the triggers you could just keep the function here.
5:20 If it for some reason it's having problem you can always come in here click on it click run and then if it runs and it has a problem here it will show you and you will be able to debug it and figure out the problem and fix it pretty quickly.