Hi Curt, thanks for the tutorial but when i run the script, i receive the error "TypeError: Cannot read property 'range' of undefined", any idea how to resolve this error? thanks
That is the error you will get if you try to run it in Script Editor. You need to run it by changing the cell on google sheets. The cell in my example to change is A1.
Thanks for Sharing! Question: is it possible to trigger a script that read a csv via url and populate a sheet with data from the csv and use a cell value as the trigger?
Nice concise video. Thank you for putting the code in the comments; was easy to adapt to my project and works like a charm. I have a question, and I wonder if you might know the answer: It appears that once you create such a trigger, even though it may be only triggered by changes in one cell on one Tab, your sheet is now alerted to observe changes anywhere in all Tabs of the spreadsheet (just as a simple Trigger would), and for each change evaluate whether it passes the condition in the clause, here written as if(sheetName == 'ONEDIT' && column == 1 && row == 1) Doesn't that slow down your sheet's performance, especially if your sheet has many Tabs or the Tabs have many cells? To be on the safe side, I'm now going to split what started as a single Sheet into two, keep the Sheet with the onEdit(e) trigger minimal, and put the other Tabs, which will get deep and change often, in the other Sheet. Do you think this a wise move, or unnecessary?
I am getting this message: "TypeError: Cannot read property 'range' of undefined (line 4, file "Code")". Can't figure out why. Any possible solution sir? Thank you.
Is there an edit I can make to this that would place the data in the next available column in the row? Say, if C1 was used already, on the next edit it goes to D1?
I hope this answers your question. You can do a while loop to check the next available 'Blank' column in a row. Then from there you can populate it. var sheet1 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('SHEETNAME'); var column = 1; while( sheet1.getRange(ENTERROW,column).isBlank() == false) { column = column + 1; } Logger.log(column);
How would I be able to copy the information to a different cell based on a checkbox? Or better yet, how to input data in prededermined cells when the checkbox is UNCHECKED vs. a different set of predetermined cells when the checkbox is CHECKED?
I copied the exact code, but its not working. I do have one more onEdit function in my program. What might be the possible reason and how to solve this problem?
Hi Curt, Thanks for your video. I get the error “SyntaxError: Invalid or unexpected token (line 10, file “Code.gs). In my code line 10 is if(sheetName == ‘ONEDIT’ && column == 1 && row == 1) Do you have any suggestions? Many thanks! Tim
Hello Curt! Thanks for the tutorial. I have run the code by changing the A1 cell value but i am getting the error TypeError: Cannot read property 'range' of undefined at onEdit. how to resolve this error? thanks in advance.
Sir, how can I call an onEdit() function in a google sheet's app script ...if onEdit is already defined in an app script library.....? When I call any other function using library it is working but onEdit is not working....
Hi Curt! this is very interesting! I don't know why it doesn't work on my google spreadsheet even without giving me any error. Also, what I'm trying to do is to replace a cell on a Google Sheet anytime I edit any cell on a different Google Sheet, do you think that's possible? thank you so much, I hope you can help me with this :)
Hi Curt, I am getting an error. Can someone help me? I just copied and paste it. TypeError: Cannot read property 'range' of undefined (line 3, file "Code") Dismiss
Nice Tutorial. I am trying to create a pop up with a message giving instructions to the user that information must be entered in another cell to avoid an error. Example if I am referencing a number from another cell to a specific cell to do a division, but a number is not put into the referenced cell, I get a #DIV/0 error. I would like when that error is detected, a message pops up and reminds the user that they need to put a value in the referred cell. Thanks.
'e' is the object returning from the triggered event. One of the classes of the object is 'range'. Which in a google sheet could be like 'A1:B2' as an example. In the video, I am using the range to get row and column in which the trigger event happened.
Hello, Here is the code that I have made to run this onEdit. I am trying to populate one cell, not the entire column. What would I change in the code in order to have only one cell say the date the whole sheet was modified? I tried to switch 4 with the specific cell 'D4', but that didn't work. Thank you! function onEdit(e) { var row = e.range.getRow(); var col = e.range.getColumn(); if(col === 3 && row > 3 &&e.source.getActiveSheet().getName() ==="Smith" ){ e.source.getActiveSheet().getRange(row,4).setValue(new Date()); } }
I'd like to combine what you created for the ClockIn/ClockOut (ua-cam.com/video/OBxnzb3_yM4/v-deo.html) and this OnEdit....so that those using a mobile device can have the same experience that those using a browser get using the buttons. I'm a beginner, so I'm trying to do all of this by copying and pasting in script. Any suggestions?
Exactly was I needed. Thank you, Curt.
Hi Curt, thanks for the tutorial but when i run the script, i receive the error "TypeError: Cannot read property 'range' of undefined", any idea how to resolve this error? thanks
That is the error you will get if you try to run it in Script Editor. You need to run it by changing the cell on google sheets. The cell in my example to change is A1.
YOU SAVE MY AFTERNOON, THANK YOU SO MUCH !
Excellent example,Thank you ......from Blitar Indonesia
Thanks Curt. This was a great help!
Hi Curt, excellent video, what would be the script if I want to trigger a filter in a ranking spreadsheet
thanks for the tutorial, how do you move to another worksheet?
Hi, Curt. Thanks for the video. How to do it with the column below?For A:1 to A:10 with output C1:C10?
Hi, I am just wondering why do I getting error "TypeError: Cannot read property 'range' of undefined (line 3, file "Code")"?
was it due to V8 runtime?
Did you get this error after updating the cell?
@@CodeWithCurt me too, nothing happened after updating the cell. This error happen while running in Script
is there a way to replace the if(sheetName == ) with something that could work for any sheet?
Thanks for Sharing! Question: is it possible to trigger a script that read a csv via url and populate a sheet with data from the csv and use a cell value as the trigger?
Nice concise video. Thank you for putting the code in the comments; was easy to adapt to my project and works like a charm. I have a question, and I wonder if you might know the answer:
It appears that once you create such a trigger, even though it may be only triggered by changes in one cell on one Tab, your sheet is now alerted to observe changes anywhere in all Tabs of the spreadsheet (just as a simple Trigger would), and for each change evaluate whether it passes the condition in the clause, here written as
if(sheetName == 'ONEDIT' && column == 1 && row == 1)
Doesn't that slow down your sheet's performance, especially if your sheet has many Tabs or the Tabs have many cells?
To be on the safe side, I'm now going to split what started as a single Sheet into two, keep the Sheet with the onEdit(e) trigger minimal, and put the other Tabs, which will get deep and change often, in the other Sheet. Do you think this a wise move, or unnecessary?
Thanks men! Simple and effective explenation :)
Excellent tutorial. Thanks.
Is there a way to send the latest changes in the sheet to the email using scripts along with the data which is newly added to the sheet?
I am getting this message: "TypeError: Cannot read property 'range' of undefined (line 4, file "Code")". Can't figure out why. Any possible solution sir? Thank you.
Do you get this error when updating cell A1?
@@CodeWithCurt Yes sir. I have done exactly what you have shown.
Is there an edit I can make to this that would place the data in the next available column in the row? Say, if C1 was used already, on the next edit it goes to D1?
I hope this answers your question. You can do a while loop to check the next available 'Blank' column in a row. Then from there you can populate it.
var sheet1 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('SHEETNAME');
var column = 1;
while( sheet1.getRange(ENTERROW,column).isBlank() == false)
{
column = column + 1;
}
Logger.log(column);
Hi Curt, will this work to setvalue to another spreadsheet?
I believe it will. I you watch my Copy Multiple Spreadsheets video I have a segment of code that sets the spreadsheet then I can set the value.
How would I be able to copy the information to a different cell based on a checkbox? Or better yet, how to input data in prededermined cells when the checkbox is UNCHECKED vs. a different set of predetermined cells when the checkbox is CHECKED?
I copied the exact code, but its not working. I do have one more onEdit function in my program. What might be the possible reason and how to solve this problem?
Is there any error messages? I haven't ran a script with 2 onEdit's function in it so I am not sure if that is causing the issue.
Hi Curt, Thanks for your video. I get the error “SyntaxError: Invalid or unexpected token (line 10, file “Code.gs). In my code line 10 is
if(sheetName == ‘ONEDIT’ && column == 1 && row == 1)
Do you have any suggestions? Many thanks!
Tim
}
Try manually typing the single quotes around ONEDIT. Sometimes when you copy the code in it copies in the wrong single quote format.
Thanks for your help Curt!
Hello Curt! Thanks for the tutorial. I have run the code by changing the A1 cell value but i am getting the error TypeError: Cannot read property 'range' of undefined
at onEdit. how to resolve this error? thanks in advance.
Is that error showing up on the google sheet after you change cell A1? In a red Box on top?
@@CodeWithCurt thank you reply. Yes after changing my the text in cell A1.
Sir, how can I call an onEdit() function in a google sheet's app script ...if onEdit is already defined in an app script library.....? When I call any other function using library it is working but onEdit is not working....
Hi Curt! this is very interesting! I don't know why it doesn't work on my google spreadsheet even without giving me any error.
Also, what I'm trying to do is to replace a cell on a Google Sheet anytime I edit any cell on a different Google Sheet, do you think that's possible? thank you so much, I hope you can help me with this :)
I got the same did you fix it? do we need any other configration?
appscript does not open in mobile or tab view. no solution found till now...
Could help me a apps scrip to copy content of a row from many sheets when changing cell C to value "invalid" to sheet "Target". Thank you so much.
Hello! How to column width auto on edit?
How hard is it to change to onSelectionChange(e) // instead of onEdit(e)
Hi Curt, I am getting an error. Can someone help me? I just copied and paste it.
TypeError: Cannot read property 'range' of undefined (line 3, file "Code")
Dismiss
Do you get this error when changing Cell A1?
It's GENIUS. Thanks
Is there a way to omit one of the tabs?
Hi Curt, I would like to know how to code to get user email when edit a cell. Thanks so much.
Hi. You may have found out how already, but wth? var user = session.getEffectiveuser().getEmail() :)
Nice example 💯👍
`e.source` returns “Spreadsheet”. Could be a change in the API. `e.source.getName()` *does* return the name of the spreadsheet
Nice Tutorial. I am trying to create a pop up with a message giving instructions to the user that information must be entered in another cell to avoid an error. Example if I am referencing a number from another cell to a specific cell to do a division, but a number is not put into the referenced cell, I get a #DIV/0 error. I would like when that error is detected, a message pops up and reminds the user that they need to put a value in the referred cell. Thanks.
How to cal python script from gscript on cell edit to pull data from different source and update ni sheet ?
want to auto lock cell after data or value had been entered the whole work book or the sheet
Hello. Can you please explain what the e.range property exactly does? Thank you.
'e' is the object returning from the triggered event. One of the classes of the object is 'range'. Which in a google sheet could be like 'A1:B2' as an example. In the video, I am using the range to get row and column in which the trigger event happened.
@@CodeWithCurt I see! Thank you so much!
Hi where can i contact you?
Great ♥️♥️
Hi Curt. Thanks but this doesn't work for me. I even copied the code, as I thought I didn't quite get the logic.
Hello how to put more column if i change it column == 6 with column == 6, 5, 7 its not take just on that columns its take effect on all cells
column >= 5 && column
Thanks!
Cam this function runs on iphone ios... I did once its runs good on android but not iphone..
Hello,
Here is the code that I have made to run this onEdit. I am trying to populate one cell, not the entire column. What would I change in the code in order to have only one cell say the date the whole sheet was modified? I tried to switch 4 with the specific cell 'D4', but that didn't work. Thank you!
function onEdit(e) {
var row = e.range.getRow();
var col = e.range.getColumn();
if(col === 3 && row > 3 &&e.source.getActiveSheet().getName() ==="Smith" ){
e.source.getActiveSheet().getRange(row,4).setValue(new Date());
}
}
This populates 1 cell. It would just populate D4.
e.source.getActiveSheet().getRange('D4').setValue(new Date());
Thank you
thanks
How to lock the cell using functions
I'd like to combine what you created for the ClockIn/ClockOut (ua-cam.com/video/OBxnzb3_yM4/v-deo.html) and this OnEdit....so that those using a mobile device can have the same experience that those using a browser get using the buttons. I'm a beginner, so I'm trying to do all of this by copying and pasting in script. Any suggestions?
give the code
There a way to get cell value automatic to a specific cell ?