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
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!
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();
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
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?
@@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?
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.
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
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?
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.
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?
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..
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.
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.
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?
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 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.
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* *};*
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.
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?
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?
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.
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
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"))
You're the best teacher i have ever seen about googlesheet. So clever!
Thank you
I literally fall on my knee each time I get to see one of your new videos. Each time you make my day!
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 channel is the most informative I have found.
It's so cool to see how you make Google Sheets do whatever you want!
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
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!
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!
Very details. just what i want to learn. thank you very much
thanks for the tutorial, how do you move to another worksheet? did try with oneedit(e) function but failed
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?
This video is awesome, you answered all my questions, thank you very much, sir.
can you please explain the difference by create custom formula for specify code and for any spreadsheet?
It totally work w me. I appreciate it
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.
This is amazing. Thanks for this tutorial!
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?
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?
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.
Great tutorial. Pls make a video on how to use index and match in script editior.
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?
is it possible to use post requests with the onedith trigger?
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..
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.
What if you want it to run everytime the value inside the formula changes?
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.
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?
Is there any way to make login form google form and if login condition is correct then jump to another google form?
amazing video, but how we can do this with Vlookups?
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!
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 ?
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.
Love this channel
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))*
Thank you! Its beautiful!
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
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.
Great teacher
Thanx aloooot for these vids .
My scrip editor do not have the debug - menu bar ? why
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.
bro how can i solve the maximum execution time exceeded error?
Superb!!!
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.
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
Thank you very much
thanks man
👍
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.
Thanks
please show me onedit on a new version of app script.
It's the same.
great.
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