00:00 Alright by the end of this video you should be knowledgeable about how to automate spreadsheets. I'm assuming you have no knowledge of how to automate or how to code and so we're gonna start with a very blank page just like you would if you knew nothing hadn't you can go to sheet.new and create a blank
00:20 sheet just like this im gonna try to walk you step by step through the process of your first automation and im gonna do that by sharing with you the very first automation that i made myself in google sheet so prior to learning google sheets and app script about 10 years ago i was already using excel 
00:38 vba and i switched to a company where they were using google sheets they had been using google sheets for 6 months there was one person who created their google sheet and they had no automation they just designed it behind a spreadsheet but 8 people needed to use that spreadsheet from now on and they
00:53 were doing this one thing over and over and over again and screwing it up every single time so what they had is some uhh let's call this data 1 data 2 and they had a status column lets call this the c column and they had some more data and they just had a bunch of data here but they had this status and
01:13 it could be active, send, inactive, lost, one whatever it could be whatever it was they had a lot of like different versions of this now first thing you might want to do is make like a drop down menu so we'll do that lets create a drop down menu and we're going to create act same things we have over 
01:32 here send inactive lost one and what were going to do in our automation is anytime we set this to lost this drop down to lost or one were going to move it to another tab were going to move the entire row of data so were going to click done here and instead of see e2 were going to write c2 colon c done
01:56 so now every single one of these is one of these options so lets write some data data first lets call this data second so its a header umm data a third data fourth and im going to not delete any extra lines or rows or columns but usually i would umm in a good spreadsheet you know what i am going to do
02:21 im going to use my free extension sheet styles to make this a little nicer experience for us this is just if you are using google sheets for a long time everyday im going to change this to papyrus style it decreases the contrast a little bit really helps your eyes not get headaches and not get strained
02:39 over a long period of time using spreadsheets alright thats about it its a free google sheet add-on literally go up to add-ons get add-ons and look for sheet styles absolutely free go grab it now in one click you saw how it worked alright lets do this data one across the board data one data one data 
02:57 one and then we're going to drag this down there we go to some just so we have something to look at because we're going to be moving tabs in automation here's sort of the simplest way i can explain automation in google sheet sheets to help you come up with the ways in which you might want to automate
03:19 or think about automating one thing is having structured data make sure you know exactly where data is and ultimately where you want it to go or how you want to transform it or what you want to transform it into what is the input and what is the output you desire those two things are incredibly important
03:39 we'll deal with the middle from the input to the output in a second make sure you have structured data that is rule number one so in this particular case we have a spreadsheet one single tab that has exactly the same data in every call every row that is labeled here and we're just changing the status
04:01 and we all ultimately want when a user clicks on one of these either lost or one we want to move it to another tab called lost or one this is the second rule make sure you actually have an event or some thing some action that a user takes or a particular time so there could be either event driven or 
04:23 time driven automations one or those two in this case we are using an event that a user is taking this can if you've never done automation before you might start thinking I want to do something automatically but what you really need to think about is doing that something automatically when something 
04:49 happens it cant be more clear about that and actually I can say that in a bunch of different ways and one of the clearest ways you can probably think about this is knowing all of the ways to automate what events in this particular case I happen to know that this is an on edit event we can capture this
05:09 event with an on edit trigger and so what we're gonna do is gonna go up to extensions app script and again there's no coding whatsoever yet I'll be coding this along with you and you dont have to know anything outside of this video to do this what were gonna do is call our function on edit this is whats
05:32 called a simple trigger and every single time a cell is edited in a sheet or in your complete spreadsheet file this will trigger it will trigger an event and this event we will use to know what is happening so we only want to trigger the this functioning when certain events happen and we'll do that together
05:58 here so this on edit you just type you don't have to install anything you don't have to do anything else other than type it it is lowercase o and the capital e make sure you have those i use event here in this particular case but sometimes when i want to go fast and i know what it is this variable is
06:15 called e and this is e also in a lot of the google sheets documentation lets look at that simple trigger and we can see it here on open e uninstall e these are simple triggers as well but here's the on edit e this e they use is exactly the same as what i'm going to use event except i'm just using five
06:42 characters and they're using one character this may be complicated for those who are new to app script and variables but for instance for example im only using event here now we wanna know when someone clicks somewhere they enter any data like we're filling out this sort of sheet right with some sales
07:04 information or licensing information that's what I did this is the very first script I ever wrote I wrote it took me about two weeks to write this so this is not gonna take two weeks to make this video we're gonna walk through the whole process here in a few minutes but it did take me about two weeks
07:20 to learn this when I first learned it without any other information and so what were gonna do is upon editing this column C column to lost or one we're going to move it to that particular sheet so we need structure data to begin with and end with and so to end with we need a lost tab so let's call this
07:48 sales were going to actually duplicate this for now but im going to delete everything there and im going to call this lost then im going to duplicate it again and call it one with one now this is where we want to get to so again our input is we have our structured data here on sales and upon someone 
08:15 clicking on the C column and selecting one of these which is an editing we are editing it to one we then want the structured output is move this entire row over to one but the thing is here's the one key about automation and Google Sheets you might want to know is that sometimes what you want to do like
08:40 the language you use is not going to be the exact language used in Apps Script or JavaScript or coding and sometimes you're going to have to sort of break apart even simple ideas that you may think are simple youre going to have to break them down to their very much more elemental statuses so for example
09:02 there is no move there is no move in Apps Script however there are two things that add up to move I'm going to write some suit what's called pseudo code here we're going to write this out and I'll get to the what elements we need but essentially we want to move upon the row sorry column being edited 
09:30 get the row upon the on the column in the sheet so we need to make sure were in the right sheet on the right column were editing we want to get the row and then were gonna move the row to either lost or one based on what the column was edited to ok and again we don't have a move there's no move so what
10:01 were gonna have to do is first copy the row to either lost or one and then were gonna delete the row that was edited ok in our original sheet this copy and delete ends up being moved so sometimes we will this happens a lot this happened a lot of time I wasted a lot a lot of time early on in my Google
10:31 Sheets experience searching for things that didn't exist because the words that I used just weren't the same words as the JavaScript or the Apps Script at the time so I did make some tools to help you.
10:48 Theres like a formula generator on BetterSheets.co if youre a BetterSheets member you can use it for free. BetterSheets.co slash GoogleSheets formula generator.
10:57 Thats for formulas. I dont yet have one for Apps Script but I may be working on that. Soon BetterSheets members are more than welcome to email me your convoluted ideas what you want to do and I may be able to help change your words around or ask you particular questions initially when you're doing app
11:19 script and automating things for the first time I'm gonna ask you what is the triggering event you want to do what's your structured data input what's your structured data output those are the three things if you know those three things oh my god it's gonna be so much easier so this is a pseudo code 
11:35 these are just comments this is two slashes this is not code its just me writing but lets start coding and automating this right so first off the thing about on edit is that it will trigger on every single edit across the entire sheet we only care if this event is on sales tab and in this c column so
12:02 we can get that from the event so we can get variables which variables are like information that we can name ourselves so we're just naming the information we're figuring out the information and then we name it so what is this name of this information it's going to be column variable column is equal 
12:19 to event dot range dot dot get column you can always check this in a second but lets look at sheet as well variable sheet equals event dot i think it's source get name logar dot log so logar helps us know if we're getting the correct information so sometimes we might just have to try this stuff out get
12:50 some kind of error so we saved it you can click on the save project first time you save it might take a moment but lets look at this I'm going to just select this switch it to lost and over we should actually call this something how to make google sheets call this rename it over here we look at executions
13:12 it is completed and this is perfect we got three which is the C column so C in our structure data is number three the third column the sheet name like oh sorry not the sheet name the spreadsheet name here so let's fix this and again I'm doing this without any experience you might run into this so this
13:40 logger helps us know what is actually happening lets look at the sheet when these events happen i think we need to get actually lets google this get sheet name from on edit event use on edit trigger it's purple so i must have been here before i google these things all the time i've been coding for 10
14:08 years and i still google all the time okay it says get sheet name but lets look at the Thank you.
14:15 We'll you next We'll see you next uhh get active sheet dot get name that looks like it should work so lets change this to instead of e event dot source dot get active sheet dot get sheet name we're gonna save that I'm looking at this e dot source dot get active sheet and then here active sheet dot get
14:42 name ah that's what we did wrong just copy that ok save project lets edit this back to one and now we will have another execution lets look at our log perfect we have the column number and the sheet okay this is going to get a little more complicated but we are going to use an if if column two equal 
15:17 signs so this is sort of the same is equal I know equals means equals but like in our coding case we need two equal signs because equals is actually assigning this variable column to whatever the output of this is and then the two equal sign is asking is it equal to that's why that is column equal to
15:39 three and we want two things to be true we want the column to be three and sheet equals sales sorry is equal to two two equal signs so both of these things must be true in order to execute in these curly brackets what we're going to do so we want this so essentially we want to get the row so that's going
16:09 to be variable row equals let's get this column and instead of get column we do get row couldn't be easier to get the row so now we have the row that is happening we can also look at this logger dot log row and lets just save it and double check that we are correct so we are changing one to lost in row
16:34 five or lost one in row five lets look at our execution maybe it takes a moment to get there lets refresh it does take like moments five perfect row five we are rocking and rolling so now we want to copy the row over from based on one or loss so lets look at how do we get the umm uhh event we do on edit
17:08 value app script lets look at that lets see if we can lets look at event value I think its just .value I think thats what it is so we can always check it logger.log edit value up here we can do variable edit value equals e.value and we're going to check that this is correct by switching this to lost 
17:54 looking at our not our checkers but our executions failed e is not defined oh I think I might have written e instead of e event right okay perfect don't worry about failures failures we'll usually tell you where you've gone wrong and actually literally tell you what you need to fix so in our executions
18:20 and failure it literally read reference error e is not defined on at on edit code 619 this is row 6 so we go to our code edit this code here 6 and that's exactly where our our error was let's change this over to one in row five let's go back to our executions refresh completed perfect and we have one
18:52 now we know exactly what value is being edited and we can say inside of this if if edit value is equal to one now do something so now we want to copy it to the one column okay let's do variable row to move equals we need spreadsheet app dot get active spreadsheet dot get sheet by name its going to be
19:31 on the sales page because we checked that here sheet equals sales so we know its on the sales page we can actually call this sales variable row equals sales dot get range the range is going to be the row and we want the entire row so we are going to use column we need a max row so variable rows count
20:02 there's no rows column count is equal to sales dot get max columns thats however many columns there are on our uhm spreadsheet so we are going to put that column count right there actually we want so this range is 4 items you can actually see it when we do row its 4 items columns row column column is
20:31 starting 1 now number of rows 1 the columns is going to be column count so that's four things that we need here but we're going to get the range and we're going to get values the entire range actually we just need the range there i think and we do row dot copy to dot get let's get the values row to move
21:09 lets call it that there you go, copy 2 and where do we want to copy it to? So this gives us an example that we need to know on the one sheet date.
21:31 Right so let's look at that let's add a variable 1 call this 1 we want to get the range which we want to know uhh variable last row on 1 is equal to 1 dot get last row so thats the last row with data on it but were going to actually do last row on 1 plus 1 and column 1 number of rows 1 number of columns
22:17 is is Probably our same column count. We probably want to have exactly the same number columns on both sheets Why is this ah so we need one more parenthesis here at the end There we go And so now this is the line that's really doing all of the work here It's taking the row to move and it's copying it
22:45 to our one sheet and it's saying put it on the last row that has data plus one and put it on the entire column take that and put it there right doo doo doo doo doo doo and now we need to go back and essentially we need to delete the row that we had before so we wanna take this row to move so we don't
23:16 need that we want sales dot Delighted. delete row and the row position is the row that we already have ok so now hopefully if we change something to 1 lets change data 6 to 1 it disappeared and on our 1 column, our 1 sheet, it now has appeared.
23:47 Isn't that cool? So the only thing we need to do now is add a lost so we want to do the exact same thing but the edit value is lost and we change the sheet name to lost it's going to do exactly the same thing but if it's lost we want to move it to lost okay so save that let's make sure it saves first
24:08 go back to our sales and change this one to lost that's data 5 look at lost now it is data 5 so now we have automated completed our first automation right we have copy the row either lost or one based on only doing it when its in column 3 and its sales and the edit value is 1 or lost we can change it
24:36 to inactive and nothing is happening inactive nothing is happening active if we change it to lost thats data 4 we can see here it is data 4 and whats saving us is essentially this last row oh we have to change last row on 1 to lost this should be just for readability we want to change all of this to 
25:10 lost down here this is just so its easier to read hmm hmmm perfect so lets just keep making sure that this works and we've done our first automation and hopefully you've been able to get through this without having any experience whatsoever but now you can create a really cool automation knowing we have
25:39 some the exact trigger we want which is a edit change to something specific we have our structured data which is all of our rows on our sale page and our structured output which is we want to put it on lost or one so let's change this to one data eight and look at data eight there it is data eight isn't
26:02 this pretty cool I hope you had a good time watching this if you are a better sheets member you can get the spreadsheet down below if you're not hopefully you'll watch again and get all of this app script yourself copy it try it on your sheet if you have any difficulties let me know happy to help you
26:19 along the way also I have if you have not yet checked it out on better sheets I have spreadsheet automation 101 on udemy I have master spreadsheet automation on udemy master spreadsheet automation is a bunch of courses all combined it includes automating what I've shown you here plus a lot more of automations
26:41 and app script shows you how to learn how to choose you app script completely then it shows you how to integrate apis it shows you how to integrate ai how to literally use chat GPT inside of your spreadsheet uhm some really really cool stuff across there udemy again master spreadsheet automation on bettersheets.go
27:02 go check out spreadsheet automation 101 to get further deeper into this im excited that you are able to