Google Sheets onEdit Trigger - Automatically Drag Down Formulas - Apps Script

alright so in this video I'm gonna create a script that will automatically drag down our formulas in our spreadsheet what I basically wanted to happen I want to be able to add some columns here and I want to be able to go here and create a formula like this - this does actually do - this and when I hit enter see I want this to automatically just drag the formula down so I don't have to go here and do this undo this I also want to be able to update this so if I drag this down if I decide to go change this formula and change this to something else it should automatically again update all the rest and if possible I want it also to work if I go and update the formula halfway through so if I update this formula I want all the formulas above and below to change as well and I want this to work for other columns as well so if I wanted to add another formula with a column I want this to happen again the same way so let's start with a basic thing here let's try to just create a script that will just take this formula and drag it down right so I'm just gonna click in the cell where the formula is and I'm gonna try to grab the information in that cell and drag it down until the last row here so I'm gonna go under tools script editor that should get us started I'm gonna rename this project call this drop down now let's try to create a script so the first thing I want to be able to do I want to be able to get the current active cell that I'm in so I'm gonna do that by first going to our spreadsheet app then you know spreadsheet app let's just spell it right we're gonna get our active spreadsheet in our active spreadsheet we'll get our active sheet and in our active sheet we will well just let's just stop it that and save this in a variable I'm gonna save our active sheet equals to this go to the next row here take this active sheet and in that active sheet we're gonna get our active cell which should be the current cell that we're in I'm gonna save our cell equals to that cell so that's gonna be the cell first I want to figure out which column that cell is in so I'm gonna say the column equals to cell dot get column that should get us the number of the column that we're currently in and basically we need to make sure that we drag our formulas starting from the second row because the first row is gonna be the labels in this case and until the last row down here so that means that I'm gonna do a range so I'm gonna take my cell and I'm gonna copy that to see we need to copy to a range destination so that destination we're gonna create on top of this so I'm going to copy that I'm gonna save our destination equals two and that should be a range so that should be a range from our current worksheet so from that we're gonna get range let's do this so it shows up again and we need to define the range size so the range is gonna start from the second row so that's starting here it's gonna be in this column that we're currently in so that column is gonna be this Co L variable and the number of rows we need to go down well it's gonna be this last row right minus one because we have a label on top so it's gonna be basically like this many rows down so to get to that we're gonna do active sheet again and in that we'll get the last row now if your spreadsheet is laid out in a way that you have extra things below here and the last row is not really the last row of your data there are many different ways to get to your last row and I have a video just about how to get less rows using different methods so watch that to see which won't work for you for this I'm just gonna go with straight a simple last row method that should get me that now I need to do minus one because I have label on top here so that's why I'm gonna do that minus 1 and the number of columns well that's just one column for my formula it's gonna be one so that's gonna be my destination range and that's where I'm copying this to see that cell I'm gonna copy to that destination and that should copy the formula down now let's try this and see what happens right so I'm gonna first of all try to run this it will ask me for some permission stuff and I'm gonna go ahead and do that so let's just quickly do all of this allow running so that ran let's go back and check what's going on so apparently I'm here on the bottom blank cell that should not work so I'm gonna try to undo that and go here so this is where I should have been so this is the formula I want to drag down I'm gonna click on this go back and try to run this again go back and there it is so see if I look here there's our formula we dragged it down using a script now what I also want to check will this work if that wasn't the first one on top so I'm gonna just remove some of this formulas bellowing above just keep this one formula somewhere in the middle I'm gonna click on this one and go back and rerun the script to just see what happens so it seems like it worked just fine so that created a script that will basically just drag the formula up and down based on the current active cell but the problem with this is that every time we have to just go and run this so we don't want to do this instead we want this to happen automatically so to do that we need to use on added trigger so let's just go and take a look at our on added trigger to see how it works that's our event objects let's go back to open change at it that's the one we'll want to look at so now this is going to show us what are the available event objects here so first of all let me just go ahead and create that function so that will be function on at it so this function will run every time we make a change in our spreadsheet so this function will accept an argument here e and that argument is basically going to be an object and that object is going to have these properties that are showing here on top so some of the properties that I'm interested in here are first of all the old value and the new value and the reason I'm interested in those is because I only want to run this thing if the formula actually changes because if we already have a formula here that works and somebody opens this and hits enter I don't want to run this all over again I only want to run if there are any differences in our previous formula in the new formula there's the only time I want to run this drag down all over again so I need to compare my old value to the new value to see what happened before and after the change so first of all let me try to log those things out so you can see what's going on here so I'm gonna create some logs here so logger.log what we're gonna log is that e dot so let's go see what those things are cold see there's value and there's old value so let's do old value first that's gonna be that one and then let's logout the new one as well and the new one is value apparently with lower case so that it's case sensitive so be careful so I'm gonna save this let's just go back here and change one of these things to just see what happens see this is the formula I'm gonna go in and hit enter see it says equals D 2 minus e 2 and that's the value let's go back and try to check what happened with our log so I go here check the log there's nothing in here so it may have not triggered on at it or maybe you would just have to refresh this but just to make sure let's just go and change one of these formulas I'm gonna change this formula to be this minus that I'm gonna hit enter let's go back and take a look the log and yes so that triggered it so it gives us the old value and it gives us the new value so apparently that doesn't even trigger if the value stays the same as it was so if you just open this and hit enter without changing anything it doesn't seem like it triggers that at all so we didn't get any logs which means we don't even probably have to compare to sieve the old value and the new value are different which is good so now let's get to the other part so this will also have this range object in it and range is basically the cell that you were currently editing the same way that we have this active cell right here so that range so I'm gonna get rid of this too we don't need those anymore so that range is gonna be our current active cell and one thing that I want to do I want to make sure that we only drag this down if this thing actually is a formula and if somebody were to just go in here and just type something like this I don't want to drag that down so let's go and make that change first of all right here to this function so we can verify that it works so I'm gonna first clear all of these so I can test that I'm gonna go back here go right there that gets us the column so let's get the cell and in that cell we want to check if that's a formula right so what I'm gonna do I'm gonna run this get formula method to see if there is a formula in the cell and what I'm gonna do I'm gonna do an if statement I'm gonna say if that cell get formula is not equal to blank then we're gonna run this copy down script all of that let's check if this actually works so I need to just tab this thing just to keep this clean I'm gonna go here just type some random text click on this let me go and run this function go check see nothing is dragging up or down that's good news now let me go ahead and create a formula here click on this cell go back and rerun that and now because that was a formula that dragging down worked so that's good so now I'm gonna clear this again let's go back and apply all of these to this so every time we change the cell we want something to happen so what do we need to do first we need to get the cell so see this cell that we got here we get the active sheet and get the active cell well we don't need to do that here there is this e object which is gonna have this range in it which is really the cell so instead of doing this I'm gonna do a dot range that should give us the cell that was just edited so most of these is gonna stay the same so I'm just gonna copy pasted all of that right here so then we do cell we get the column we check if the thing is a formula and then we drag it down there's gonna be one problem this active sheet well it's not gonna exist because we didn't get it like we do here so one thing we can do inside of this event thing see there's also source which is the spreadsheet so if we go here we can do that e dot source that's gonna be that so this active sheet it's gonna be equal to similar to here now the spreadsheet app get active sheep it's pretty much the same thing as this a dot source at this point and then inside of that we can get the active sheet in this case so that's the tab that we're currently on so that should get me this so I'm gonna save this so now hopefully this should work out so let's try this and see how it works so I'm gonna go here and create a formula I'm gonna say let's take this minus this hit enter and nice so that's dragging down nicely good let's go here and create another formula new one let's take this minus this good that works let me try to go and update this formula some way halfway through to see if this works I'm gonna change this minus to multiplication as you can see it work for all of them so this basically just drags down our formulas so anything that's not a formula however shouldn't do that and as you can see it does not I don't need that anymore this pretty much is all I need this is my automatic drag down thing now one thing we might want to do instead of dragging everything down so this is now gonna make sure that every formula you do wherever you do it's gonna drag that formula down you may or may not want that to happen now you may want to limit this to just certain columns instead of just dragging every formula everywhere so this is gonna even work if we just make a duplicate of this tab and do this on a different tab right so if I make it duplicate see now we have two different tabs I go on this tab and let's say I make a different formula here there it is it's just gonna drag it down automatically so now this is gonna work across this whole spreadsheet now let's try to limit this to only specific things let's say we want to just limit this to this tab let's also limit that to maybe certain columns from this tab from that point on so let's copy the name of tab I'm gonna hit escape go back here and I'm gonna make some changes so this is gonna get us the active worksheet that we're on I want to make sure that that current worksheet that we're on is this one before we run what we run so we can just add an if statement we could do it right here we could do it before so I get so let's do it maybe here so I'm gonna do a nice statement right there and I'm gonna put that whole block all the way until here in my if statement to first make sure we're on the right tab so I'm gonna say if that active sheet let's put that in quotations so I'm gonna say if this active sheet dot so that is gonna have that active sheet should have a property in that let's go spreadsheet and then let's do the sheet object so that should give us what's available here let's see so get something like get name or get spreadsheet name let's seek it let's just search there this get name so that's gonna return the name of the tab that's good I'm gonna go here and say if that get name equals to this then we want to run all of this let's just comment this so this is if tab name matches so that should work if we want to be able to do this for multiple tabs maybe we'll also just do an array instead of doing just this one thing so we can just do VAR tabs and I'm gonna create an array here and that's gonna be well currently she's gonna be just this one tab and what I'm gonna do I'm just gonna do this so I'm gonna say if tabs and because it's an array we can do index off and index off this so I have to make sure by the way that this is spelled correctly because this index of is case-sensitive so basically if the index of this is not equal to negative one because it's gonna be negative one when it's not able to find that name in this list of tabs that you provide if it's not negative one it's one of the tabs we want to target and then this should allow you to do and type different names of tabs well they will go in here if you wanted to add more tabs to be ordered dragging down but I'm just gonna leave it at this and go check if this works and do a formula that's divided by two so that worked as you can see well now I want to make sure that also doesn't work for this one so I'm gonna go this divided by 2 that doesn't do anything because this is not one of the tabs that are listed in that list but if I go back and add I'm gonna just call it other and let's just add other to the list of tabs we want to target here so if I go here to comma and do other in codes I should be able to go here and now do the same thing and as you can see now it works I'm gonna go ahead and remove this so now that shouldn't work in this tab anymore and as you can see that did an update and it should still work on this one awesome so that works and that limits to this now we could also limit it to particular columns instead of just running it on all columns so we may want to say if that's the column number 4 or called number 5 or something like that we want to run this otherwise just don't run the function so to do that I'm gonna go back here and after we check that the name of the tab matches here and we got the range or we got which column we're in we're gonna say if this is in a particular column so now I'm thinking maybe I'll do another array here of columns and do an array of which calls will want to target so let's say we want to target column this one so this is which one 1 2 3 4 5 6 & 8 & 9 maybe 6 8 & 9 so I'm gonna do 6 comma 8 comma 9 those are the columns we need to target and what I'm gonna do I'm gonna go here and check after I get the column if it's in the right column so I'm gonna go ahead and do an if statement here I'm gonna copy this and paste that here and close it right below indent this and comment this so this is if column match that's good so here instead of doing tabs I'm gonna check columns and we're gonna do index off that this Co L if the column is not again it's not equal to negative 1 then we know it's one of the columns we want to target that we want to do this whole thing all over again so I'm gonna save this now this hopefully should be working for column 6 8 & 9 let's try this I'm gonna go here this is column 6 let's try to change this formula that worked for this column now let's try 2 6 8 so this this shouldn't do anything so let's divide this and see what happens see nothing happened for this formula because it was not one of the columns now this one this should do something because that's one of the columns let's take this and multiply it by 2 and this new column should work too so if I do this to the power of 2 it should do that too right so that should just drag down then formula and this column shouldn't do anything if we do something like this see it doesn't drag it down by the way this should also not do anything because that's the name of the tab so yeah so that drags down now you could take this to the next level I guess and work with an object where you provide these names of tabs and columns in a way that you can do an object of the name of the tab and the columns inside of the tab so that way you can make it more fancy you can just target certain columns in this tab in certain columns in this other tab but for more or less simple project this should work just fine thanks for watching please subscribe and I'll see in the next video

Channel: Learn Google Spreadsheets

Views: 59,446

Keywords: Google Sheets, onEdit, Trigger, Automatically, Drag, Down, Formulas, Script

Id: eWn_JxPSbds

Length: 25min 3sec (1503 seconds)

Published: Mon Jul 29 2019