Google Sheets: onEdit Basics

unlike most of my videos where we look at a script that does something today i really just wanted to talk about on edit triggers generally so we're going to be using a few different resources my sheet my script file with a few different on-edit triggers and then the documentation about simple triggers and the documentation about event objects first and perhaps most importantly on edit scripts are not meant to be manually run that is just using the run button here if you do it's going to fail on some error that a property cannot be read in this case cannot read property range of undefined frequently on edit trigger we pass a value in this case i usually just use e and that value is created by the edit itself that's we're going to talk about in event objects but basically without that value being passed there's nothing for the script to do this entire script is based on e dot range e dot range you can also use e dot value again we're going to look at that in the event objects but without that e variable which is passed by the edit nothing can happen so rather than running it on edit triggers run automatically whenever an edit is made in this case an edit is whenever a user changes a value in a spreadsheet that's an important definition so unedited triggers run when i a user manually change a value they do not change when a script changes a value they do not change when a formula changes a value so for instance if i have set up here a random number generator and i have a few of them if i make an edit here it will run once or the edit on here it will not run for all of these values changing because a user did not make an a change to a value okay it also won't run when i do other types of changes so it won't run on highlighting it won't run when we add or delete sheets or when we set up conditional formatting all of these things while we may consider them a change or an edit they are not considered an edit by the script standard so an on edit trigger if you name your script on edit like this that script will run every time that you change a value okay secondly let's talk about event objects so as it says here triggers let apps script on function automatically if a certain event occurs in this case we make a change to a value when that fires apps script pass the function event object as an argument typically called e that's what we just talked about that contains information about the context that caused the trigger to fire okay so let's come down here and just look at here's all the objects available in an edit so if we pass this e value then in there we have e dot auth mode e dot old value so you can see what was there before e dot range which we use a lot e dot source tells us the spreadsheet e dot trigger u id rarely does this one come up one it has to be installed it cannot be a simple trigger and then there's very specific reasons you would use that at all e dot user if that value is available based on some security restrictions that one often is not basically don't rely on that one and then e dot value the ones i most commonly use e dot old value if i need to track what was there before e dot range i use almost every time e dot source i use almost every time and then e dot value is very frequent if we want to do something with that value okay so there are some basics about on edit triggers run whenever a user specifically changes a value specifically in the spreadsheet and these are the types of objects and values available so do not run on edit triggers manually they fail they will always fail if you have passed them some value secondly let's talk about building on edit triggers to do exactly what we want them to do so for this i've created a very simple i just have name and assigned date so on this if the column is one and the row is not one and the value next to or the cell next to the range i edit is blank so i want to say that it's in this column it is not in this row and if i edit ba2 i want b2 to be blank basically this is supposed to put the day the time that it was assigned and that should not be updating so if i just say spencer and it puts it actually puts the entire date timestamp so 1223 at 10 38 57 we have all the way to uh the second marker in here if i come here and put spencer again and we're getting another one they are obviously a different time value right and importantly if i come back here and let's update this to george it doesn't change right because of this last thing e dot range dot offset so offset let's find that range one one dot offset so here it wants a row offset and a column offset so how many rows down should it go and how many columns over should it go in this case i don't want any right i want the same row so zero row offset and one column offset okay now let's suppose we want something more to that not only do we want to assign date or to when it was named but we also want to track the status and update the status date so that this one will change every time the status updates this one will only go once i wrote another script for that if the column start is three and the row start is not one then e.range.offset set value new date same action but different different if statement to run it notice i did not include e.range.offset equals blank because i do want this one to update so let's try this new great it updates pending right it updates then we have 10 40 56. that's my fat pending and it did update from 10 40 56 to 10 41.08 okay now let's go back here and assign another one to fred oh nothing ran here's an important part about google sheets and google apps script these have the same name right i have two function on edits when this runs or when it compiles it will only consider the last function of a given name and given um header parameter so in this case it sees two on edit scripts but it's only actually considering this one so another important thing if you have multiple events multiple actions that need to occur within an on edit you need to write them as a single block so rather than these two functions i want this function in this case i have two if statements if the column is one and the row is not one and the cell next to the edit is blank then do the action if column is three and the range is not one then do the action so if i've tied those together i can come down here let's assign another one to fred wonderful that worked and let's uh say the status is new wonderful that did too and just to show that it's still working 10 42 52 mark that pending updates to 1043 but here i have 10 42 47 if i change that to spencer it's still 10 42 47. so here's the basis we're looking at on edit triggers are not to be run they will fail rather they run automatically whenever a user changes a value in a spreadsheet again changing a value in a spreadsheet is only counting when a user specifically not in the automatic sort of update but when a user specifically changes a value in the spreadsheet and we have access to all of these objects including the range of the edit the source of the edit the value of the edit and you cannot have two on edit functions working in the same sheet rather you need to consolidate them into a single function in order to run properly

Channel: Spencer Farris

Views: 6,025

Keywords:

Id: VBzjyQYICNw

Length: 10min 56sec (656 seconds)

Published: Wed Dec 23 2020