Automatically Clear Content | Refresh Reuse Recycle

<div>00:00 Hello, so in this video, we're going to clear content automatically what that means is that this could be used for templates for signup sheets for anything where you're Using the same sheet over and over again every day every week every month every year whatever it is yearly might not be so cool and  00:19 I'm going to go through a few situations that you might run into uhm and were going to start from scratch so this is with zero code written so far and were going to simply clear content that is it uhm were not going to check for any variety of that content were not going to check if its like the right 00:35 time Ill show you maybe some triggers and stuff but were just going to go up to extensions Apps Script again we have written nothing yet so this is going to be exciting were going to just write some code straight up ok so whats cool about clearing content is that it is essentially sort of technically 00:53 only one line of code were going to get spreadsheet app dot get active spreadsheet so that were always on the spreadsheet file so thats the spreadsheet file were going to get sheet by name and here we can enter any sheet name we want so this is very specifically a specific range on this specific sheet 01:16 so were gonna do actually lets do this sign up sheet first.

Use this sign up we'll do this set up a second because it's a slightly different situation where we have some check boxes but I wanna just do clearing content ok so we have the file which is this we have the sign up sheet the name of the the 01:33 sheet get range and in this particular case let's say we only wanna clear uhm yeah let's clear everything a2 to c7 and so in the range a2 colon c7 and now we're going to clear and it's not just clear so there's clear and there's clear content i think the difference is let me see clear is going to clear 02:03 it like of all content and format but if we do clear content we are only going to take away the content we are not going to change the formatting that is what i believe ok see this is just one line of literally like one line well were going to make this a little more interesting in a second but the really 02:23 cool thing is its one line of it so were just going to save this its going to be called my function but actually lets call it clear sign up clear sign up ok and so now if we ever want to use this function that we have now saved lets save that as again make sure that orange button is away now if we want 02:42 to do this all we have to do is click the run button lets see if there is any errors we will probably have to review permissions this only happens the first time we use it every other time its going to be ok were going to allow this always takes a little bit of a few moments alright lets see project  02:59 requires access to your please try again i think i did a lot didnt i did i not click allow lets run again review permissions did i not click allow i totally thought i could click allow yeah allow ok guess i did not and now we have our nothing is there right we can funny enough we can do command z and 03:27 undo it in this particular case we can do that uhm let's call this automatically clear content ok so that is as simple as i can tell you it is one line of code you name the function and you run it but lets say we have this same line of code but it is another sheet and its like operations sheet sheet  03:56 ok were gonna copy that and now we have another sheet same range actually lets lets not use the same range lets just do a to b a2 to a7 ok so we don't wanna we have none of this on the operations so we just clearing the a column but lets say we dont know the extent of the a column what we could do is 04:23 just do a2 to a7 lets save that now if i choose clear ah i have close.

Set up clear operations lets rename we have to rename it ok now we have two options up here clear operations and run but were gonna make this way easier to run but lets just look at it done a ok we have cleared the content we have 04:46 kept the format the same as it was was just cleared the content and we can reuse that as much as we want ah something that I want to make easier is I don't want to have to come back to this what is this called the the I don't want to come back to the code right I want to execute it it I want to click 05:05 a button and done theres actually two things I can say to make it easier one is make a button that clears it but two theres also you can create a trigger that says ok if I know lets say lets say this setup no matter if we get it done or not it's going to be cleared every Monday lets say that ok but lets 05:24 first actually write the line of code we're going to copy this we're going to get setup we're going to use sheet setup and I'm going to actually see what happens clear I want to show you what might happen if we just use clear content here I think its going to have a problem and youll see why were just 05:49 trying to clear this which quote unquote clearing content you might think oh I just want to uncheck these boxes lets see what happens when we use this clear content on the setup page uhm are we on the a column yes were on the a column were going to use clear setup run its going to run perfectly we have 06:07 no errors great actually what I expected there was actually something far different I expected uhm there to be to actually like take the check box away but it did not which was surprising hopefully you now see that so if that happened there is another thing we might have to do here again we didn't have 06:28 to do it but you could do get range a2 let's just do a2.uncheck so you can use this uncheck and what you'd essentially do is do a for loop for their whole range and just uncheck it i actually show that in some other videos so check out like spreadsheet automation 101 or afan udemy master spreadsheet   06:48 automation check for like 4 loops ill go through that i think its a little too complicated for this video ok so we have a bunch of this function name, write it here, put the parentheses, take this function name, put it here, put parentheses, clear setup, put this function. 07:21 And so now clear all will do all of them at once.

Actually, it'll do it in order in the sign up. 07:26 So, let's look at.

Adding a few of these Do we have data here here here Perfect now lets just check it I'm going to command s just to make sure we save I'm going to use clear all And click run Lets see if we have any errors we don't have any errors And we have clear all Clear content Perfect Okay I got 07:48 it back just because I hit command z great so now we have a plethora of different options right We have individual clears We have clear all We can absolutely add a trigger again this is sort of the sentence that I stopped earlier is add a trigger choose which one let's say clear all actually no let's 08:09 just do clear set up I think that's what we want to do yeah set up ah we want to time driven event source and we're just going to pick a day day ah actually not day timer sorry if you want a day of the week it has to be week timer we'll offer you every Monday there we go every Monday at lets say 2am  08:29 right so before the work week starts we're going to clear that great now all you have to do is go down to the bottom click save and now we have this trigger this will happen on Monday I'm going to delete it for right now because we don't we're not but that's how you created the trigger and that will  08:44 do exactly the same thing almost exactly the same thing as is if you go in here and click this function to run so thats pretty cool but lets do something I think which is even more interesting triggers are great I love them they are the way to quote on quote automatically do things run these scripts  09:04 but lets do something I think which is even cooler which is an on open menu this is I always have to google this I will just google this until the day I die function on open I will walk through this and we will actually edit it a little bit ok we are going to keep this variable UISpreadsheet.getUI we 09:22 are going to keep UI.createMenu we are going to call this the clear menu actually call it clear stuff we are going to delete most of this just add item and the item we are going to add is we are going to name these but not the exact name of the function this is what is the ah shown as text so clear sign 09:46 up but then the second part of this add item is absolutely 100% the exact function we want to run add another item add item and again the same way we are going to call this clear operations put a comma and put exactly the function we want to do trigger do clear setup as well add item clear set set up 10:18 again comma and the exact function name I'm gonna add separator this is a line inside of the menu thats going to just separate these visually and Im gonna add another item and Ill call this clear all again I will write the same exact name of the function that I actually want to run which is clear all 10:40 all okay I saved it command s or go up to here save project now on open function will not happen unless you actually open the sheet you cant force it from this screen so what we have to do is we have to save this app script close the app script and then all we have to do is refresh the sheet to have  11:02 it trigger this on open and once it happens here's a menu up here I hope you can see that clear stuff and we have four functions here clear sign-in up clear operations clear setup clear all lets clear setup again we have to authorize it this just happens the first time we run this uhm lets allow I don't 11:25 know why it has all those uhh lets just try again there it is it cleared all of this stuff so there you go so now you have a few ways to automatically clear this content but I want to walk through a few of the errors you might run into to with this code so let's go back to our extensions app script and 11:48 see if we're having some errors through this the first thing I would say is your range is probably wrong this is spreadsheet app dot get active spreadsheet dot get sheet by name dot get range dot clear content you might I would say the first problem is probably get range second problem might be the function 12:10 name is wrong if your obviously from this place your not going to get the function name wrong but if you write the function name incorrectly in this menu it will just not trigger it so you might not even get an error you might just happen to not get stuff especially if you rewrite the functions down  12:27 here like this in a sort of all function or create separate you can create more variety of functions here you might write them incorrectly what I like to do and in Apps Script they make it very easy to sort of tell if you have it right is this function clear signup the moment that I highlight it it highlights 12:47 here because it's looking for the exact text this clear operations if I click this there it is highlighted and if that highlighting doesn't happen lets look at this see its not the same its the same text but its capitalized wrong so thats actually a huge problem especially in the sheet name range not 13:08 range name but like function names get the capitalization 100% correct so what I would recommend is this camel case where the first letter is lowercase and then any words are separated by uppercase that makes it a little easier to see if there's any problem with this like set up you might have a lowercase 13:28 u and there you see it's blue it's not gray and here it's clear setup as well but it's not the gray that we have here you can also edit all of these this is going to be a weird little trick if you select this clear setup and you're like hey i want this function name to be different but its written three 13:47 other times command d each two more to get each one right so theres three so i select the first one command d to get the second one command d to get the third one and now i can call it clear the set setup and now it edited all of those the same a little trick ah for those who are watching this whole  14:07 video uhm we can make this function a little more complicated but might be actually easier to sort of grok uhm lets do variable ss equals spreadsheet app and we just write ss at the beginning here and we could also then do variable sheet actually I don't even want to do sheet signup lets do signup equals 14:33 we're just gonna use this and now we write signup so were using these variables to sort of shorten this into something thats a little bit more modular so now I dont have to copy the entire uhm within this function I dont have to copy the entire line if I want to add another range I just have to do this 14:55 one signup dot get range this could be a2 and this could be c2 right and maybe we want to not touch column b so we'll do a2 and a column and c column and now our b column will not be cleared. 15:13 But it's all within the same function we don't have to do much else we can and we don't have to copy the entire line over and over and over again but you know truth be told when I was first starting out scripting I could easily copy that and be done with it if it works it works this now makes it for  15:30 me a little bit cleaner because I can grok it a little bit better the modules but totally up to you you might want to just take this entire line copy it paste it and just do a different range b to b and there on our operation sheet maybe we have some dates here that we don't want to delete and or we  15:55 do want to delete but maybe we want to do it in a different way I don't know but hopefully this video has been helpful to you to automatically clear content and understand how to sort of do it piecemeal and how to automatically do it with time driven triggers and this on open menu to allow you to do  16:13 it consistently over time many times alright bye</div>

Automatically Clear Content | Refresh Reuse Recycle Templates