Google Sheets onEdit Trigger - Automatically Drag Down Formulas - Apps Script
HTML-код
- Опубликовано: 28 июл 2019
- Learn how to get Google Sheets to automatically drag down formulas using Apps Script and onEdit Trigger. We'll create auto AutoFill similar to how table objects work in Excel.
![XO (Only If You Say Yes) - ENHYPEN エンハイプン 엔하이픈 [Music Bank] | KBS WORLD TV 240712](http://i.ytimg.com/vi/wBT1bKKwPTo/mqdefault.jpg)
I literally fall on my knee each time I get to see one of your new videos. Each time you make my day!
You're the best teacher i have ever seen about googlesheet. So clever!
Thank you
This is the script I didn’t know I needed, amazingly helpful and very well explained! Thank you so much
Thank you for sharing such useful content. I appreciate how you explain each step very clearly. I don't have a lot of experience with sheets, but I have some practical experience with javascript. This all made sense very well. Thanks!
This channel is the most informative I have found.
It's so cool to see how you make Google Sheets do whatever you want!
First of all thank you so much for ALL your videos, they have been a constant source of learning and all i know about google sheet is thanks to you.
3 things
1. We want this to work only on the formula mentioned in row 2
2. Only update the last row on edit
3. Copy and paste as value
Basically what copy down addon does
Would greatly appreciate your support
Thanks again
I Think ill explain a bit more, For example we have data in 4 Columns A,B,C,D and formula in E2
So First row of all is Title
And data from form will come in ABCD row,
So once the data comes
1. the formula should copy from E2 to the last cell
2. Paste the result as value
Something like
IF 4 is current last data filled row and new form is submitted
A B C D = Data Row , E2 = formula cell
If A5 is not null copy formula from E2 and paste on E5
Then copy the result of E5 to E5 as values
Hope this makes sense
This is amazing. Thanks for this tutorial!
This video is awesome, you answered all my questions, thank you very much, sir.
Love this channel
Thank you! Its beautiful!
Very details. just what i want to learn. thank you very much
Thanx aloooot for these vids .
I was seeing your web app series ,its very useful but i am not able to do it myself without learning < html> .I hope you will make video for it
I really enjoy your videos, awesome work. I have a question what if I only want the formula to be only copied down so that it doesn't affect any data above that is not formula dependent?
It totally work w me. I appreciate it
Great teacher
Hey just started watchingsome of your videos, there are very helpful when i ever hit a wall in google sheets but i was if you can make a video on creating a script that calculates the sum of a set of cell/columns but ignore all fields that have had a strikethrough applied to the vaules in the rows/columns?
Thanks, you save my time again
Glad to hear that!
Thank you very much
Great tutorial. Pls make a video on how to use index and match in script editior.
Superb!!!
Very nice. This could be an add-on! When activated it creates an options tab where the user may just add tab names and columns.
thanks man
👍
great.
Thanks
I created a timestamp in my sheet everytime when there is a "Yes" in that particular column. But what if, if the value in a column in a particular is a lookup from other folder. I tried it manually writing "Yes" and the time stamp works. I tried the lookup version, then "Yes" will appear if the condition is met but the timestamp didn't work at all. I tried using oneEdit() function but it did work manually but if it is a lookup then it will not work.
Thanks a lot for great videos. Learning along with your videos is fun and easy.
Have one query, If I paste multiple lines (rows) onedit() function seems to be triggered only once. Can I detect all the rows that have changed?
e.range should give you the range when you alter multiple rows. I'm not exactly sure what happens with paste, but I'd imagine it should still give you the range that was altered.
I am also facing same issue I am copying a multiple rows of data and onedit function is triggered only for first cell rest cell data remains unchanged
Can you please make video for this as well ?
Hi A perfect video to understand OnEdit trigger..Can you similary explain OnFormSubmit trigger. I am trying generate a propertyID automatically for every form submission in google sheet.
I am a big fan of your vidoe. I have a question what I could do move to bottom row in sheet once it opened since it has too many rows and takes a while to drage it all the down.
ctrl+down or cmd+down
love it, one quick question, if I have a cell currently does not use a formula, how do I prevent that from being overwritten by this?
Not easy. You would have to use get formulas method and compare with get values method and then create the new array from both.
is it possible to use post requests with the onedith trigger?
thanks for the tutorial, how do you move to another worksheet? did try with oneedit(e) function but failed
What if you want it to run everytime the value inside the formula changes?
If we incert a new row in between will it work that's the task I have or at the botttom new records update by fetching vlookup..
This is somehow my requirment thank U Man God Bless You Bro..
Hi, i tried this - onEdit() function part, I am receiving an error TypeError: Cannot read property 'range' of undefined (line 3, file "CLEAR")Dismiss. Care to explain why?
function onEdit(e)
{
var cell = e.range;
var activeSheet = e.source.getActiveSheet();
Had the same problem
I have the same error - Cannot read property 'source' of undefined - in fact I get Undefined for all e, activesheets, tab etc. - any help is appreciated
@@ali5t4ir I think it's because onEdit only runs when you make a change to the active spreadsheet. Don't press the play button in the script editor.
@@clharri23 thanks, now it worked!
Is there any way to make login form google form and if login condition is correct then jump to another google form?
can you please explain the difference by create custom formula for specify code and for any spreadsheet?
Thanks for sharing this content with us.
Is there a way to get the formula before it was changed inside the cell?
Can you advise us about this I will appreciate that?
e.oldValue
@@ExcelGoogleSheets Actually, I wasn't asking that. Think this way. There is a formula in a cell, and it is trying to protect from changing by the user. However, we can not use cell protection because the formula should change by script. I thought I could get the formula in the cell with the onedit function so I could paste the formula back into the cell. e.oldValue gave us cell value. Is there a possibility of getting old formula in the cell?
Thanks your lesson. I have a small question: when i try to input add data into row 16; but calculation for column 6, 7, 8 don't auto display formula as well as Results. may you help me?
Are you in Excel or Google Sheets?
bro how can i solve the maximum execution time exceeded error?
Helly Teacher, do you have a video on how to create an free app mobile from google spreadsheet? As i donn't know if is posible or do i have to pay if i need this app to share with 4 users? also i need appSheet or there is a way from appScript to fit in the mobile screen?
how do you get the autocomplete/suggestions in the script editor
Just have a browser that supports it and nothing is blocking it. Firefox or Google Chrome should work unless you have some sort of addon that prevents it from working.
amazing video, but how we can do this with Vlookups?
1) you can write this in row 2 for autocalculate all rows:
*=ArrayFormula(IF(A2:A="";"";B2:B+C2:C))*
i'ts work perfectly
2) the best solution is
*if (tabs.indexOf(activeSheet.getName())===-1){return;};*
instead of
*if (tabs.indexOf(activeSheet.getName())!=-1){*
*//your*
*//long*
*//code*
*};*
Thanks for your input.
There's some situations that we can't use arrayfórmula. Like when we have a index iteration inside de formula, for example (as far as I tested). The video is a solution for this indeed.
@@felipetesta for index iteranion use *=arrayformula(ROW(A:A))* , example *=arrayformula("index="&ROW(A:A))*
what does the 'e' refer to, I've tried copying the code exactly as you have, but the function does not work and generates error: TypeError: Cannot read property "source" from undefined. (line 2, file "Code"). Would really appreciate some help on this?
e refers to event object. If you're not a programmer, I'd suggest follow the tutorial step by step instead of trying to copy the final code.
@@ExcelGoogleSheets ...been through the Video step-by-step 3 times and I get the Same Error.
My scrip editor do not have the debug - menu bar ? why
I am starting with action script and google sheet. I have created functions to use, when I passed an argument to a function and it returned the value according to the input all fine, but if I use
function name(x,y){
Return x*y
}
When I run it on the sheet I get Error , I have watched videos and I can't figure out where the error is. Could you help me?
javascript is case sensitive. Return is not the same as return
just caused myself a huge headache by not adding the below code - before this script runs
spreadsheet.setActiveSheet(spreadsheet.getSheetByName('C&C Customer Collection Form'), true);
spreadsheet.getRange('M3').activate();
because it ran on the activesheet and i wasn't on the sheet i wanted, it ran the code on the wrong sheet !!
lesson learned
CB/01/2023
CB/02/2023
CB/03/2023 When I highlight and drag down the column, it only increases from the end but not from the centre. I want to increase from the center. Please help me.
how to get trigger from cell value change by from formula/importrange/query ; change by calculate value not change user edit
No direct way of doing it.
Its possible to copy the formula and change some text for another?
example:
=SUM(FILTER(Principal!$D$2:$D;MONTH(Principal!$A$2:$A)=1;YEAR(Principal!$A$2:$A)=2018;Principal!$C$2:$C="Text to change"))
for
=SUM(FILTER(Principal!$D$2:$D;MONTH(Principal!$A$2:$A)=1;YEAR(Principal!$A$2:$A)=2018;Principal!$C$2:$C="Other Text"))
the short answer is yes.
please show me onedit on a new version of app script.
It's the same.
This doesn't work anymore. Constantly getting "[20-03-04 13:42:07:058 SAST] TypeError: Cannot read property 'oldValue' of undefined
at onEdit(Code:2:44)" error
You shouldn't be running the function from the script side. If you do you will get undefined.
@@ExcelGoogleSheets The issue was that I was viewing the "Logs" and not the "Stackdriver Logging". With V8, the "Logs" don't show anything anymore.
Good to know. Thanks for the update.
@@emptygaming8738 and how did you change the viewing from "Logs" to Stackdriver?
@@ExcelGoogleSheets so how else are you supposed to run it please? if it doesnt work how else do you debug?
OnEdit not showing logs.. just says please wait
Any help?
@@dewaynewest7756 Logs may not always work with events like this. Just ignore and move forward.
first you go watch some others video then teach other ppl