I searched and searched and SEARCHED to find the code for this EXACT thing! I simply cannot tell you HOW MUCH I appreciate you doing this VIDEO!!! I have spent endless hours to try to get this to work and could not get it! AMAZING and THANK YOU SO MUCH FOR HELPING MY SANITY! :)
I had an issue as many have with the ranged error. Two things - an error will be returned if you run the code from the editor OR debugger. The reason for this is the conditions of the fields are not being met on the spreadsheet side and this is normal. The other thing is recheck ALL OF YOUR SYNTAX. I chased this problem for far longer than I should have and found I had typed a "v" where a "u" should have gone in value - was too easy to miss but it looked right at a glance. Once this correction was made the code started to function correctly. Another great video by this youtuber!
I remember following this 2 years ago took me easily 3 hours or so and I got a headache. It felt like a huge achievement to complete it. Now I made this code for a new sheet and it took me 30 min, felt easy, but most importantly,; this video has proven valueable over and over. I use this code in a sheet I use weekly and it has saved me lots of time. Thanks a bunch!
I love the way you explain everything by stages, without delving into too much technicalities and even makes the improvements on it easy to grasp, so that even a code-ignorant like me can follow it easily. Subscribing to your channel in 3, 2, 1...!
There have been a few questions on how to run the same script on more than one sheet, but not all of them. Through simple copy and paste, I quickly created multiple scripts, one for each sheet I wanted the script to run on, giving each a unique name (e.g., Script1, Script2, Script3. - I gave mine the name of the sheet they corresponded to). Note, you can only have one script named "onEdit." You will want to reserve the onEdit name for the short "master script" that will run the rest. After creating each new script, copy and paste the same code into each. Then, change the script name on the first line of code (right after "function" and before "(e)" to match the name of the new script. Then, change the sheet reference in the code located right after "getName() ===" to match the name of the sheet that script should run on. Finally, go back to your original onEdit script and delete all of the code and paste the following new code, making the onEdit script the "master script" to run the rest of them (of course, modify the script names to match your script names, adding however many scripts you have): function onEdit(e) { script1(e); script2(e); script3(e); } Remember to save the scripts. While on the onEdit master script, click "Run." The sheet-specific scripts will execute in the order they appear in your onEdit master script. I have no coding skills. But after some research, this approach worked for me.
You might want to check this guy's vid at 3:30 th-cam.com/video/N4V0FZASK60/w-d-xo.html He talks about the "OR" statement and how you can use it This will make your code a lot shorter and quicker to execute. I also got no coding knowledge but trust me it's easy EDIT: It's not very easy tho For it to work properly my if statement had to be: if(col === targetColumn && row >= startRow && e.source.getActiveSheet().getName() === ws ||col === targetColumn && row >= startRow && e.source.getActiveSheet().getName() === bs){ bs stands for bullsh!t as well as var bs = Sheet 2 I had to repeat almost the entire statement inside IF() So in theory you could expand this row infinitely for as many sheets as you want In the end it works, even though it's not as compact as I'd like
Great instructions! Thanks. Is there a way to make the timestamp trigger when you paste several rows of data into the target column? Currently it only adds the timestamp to the top line where the start of the list was pasted.
Great tutorial, I have a very little experience with programming language, but you really made it for me. I do still have one question related to this timestamp method. I realized that this method only works with cell edited manually, so for example I paste 3 rows of data, it would only update timestamp for the first cell only, the second and third one wouldn't be updated. Could you please help me with this one ? Really appreciate your woks man!
I am so glad I stumbled on your channel this is perfect. now I have to figure out that when our receptionist receives a package, based on the tracking number it will fill in the carrier in cell 3 Thanks so much for this tutorial
Hello! this is a great tutorial, but how to get the timestamp from when any cell from a range is modified? cant find out how to set the range beside the number of column
I freaking love you, man! Thanks for the lesson, it works wonders! S2 Thank you, thank you a lot! I can't express how grateful I am to you right now! Best regards!
Thank you! I didn't know anything about coding, and after this video I still don't know anything about coding but I do have a working timestamp solution.
would you please elaborate how to lock cells automatically after entry is made in sheets so that cells can't be edited after making an entry. Thanks in advance
Are you able to advise on how to run script for multiple timestamps on one sheet? Also, for the same timestamp to only work on selected sheets...i am struggling to make either scenario to work ? Do you have videos to cover these?
I have had the exact same problem, but in line 15. I checked the code time and again, but I could not find an error or any reason, why this was happening. Strangely the code worked fine, until the housekeepng part with adding all these variables started. After I wrote the code a second time, it finally worked. I compared both versions, but I can not find any differences except there might be some different spaces and paragraphs. Something like this seems to be the problem. *The code works fine now! Thanks very much to this channel for all those outstanding tutorials!*
@@ianpropst-campbell6028 on the script editor go to edit and then current project triggers so you can enable on edit which is being called by the script.
@@onewiththefreaks3664 on the script editor go to edit and then current project triggers so you can enable on edit which is being called by the script.
This is a great tutorial and it used to work perfectly but as of 2021-11-05 it fails. The debugger says that several of the variables are "undefined". ("e", "ws", "row", "col", "currentDate") Has something changed in the scripting syntax to break this script?
Just edit the code and it should be live on your Sheet. Only way to test the code is by actually editing a cell in other words, since you're running the code from inside the script editor.
@Octave Laurentin I figured it out; it works when you don't try to run the code in script editor but save it and go to your sheet and it'll work. Because no range is given to it until you open the active sheet in question, then the value becomes defined.
if that doesnt work, try this code: function onEdit(e) { var ss = SpreadsheetApp.getActiveSheet(); var r = ss.getActiveCell(); //1.Change 'Ark 1' to be matching your sheet name if (r.getColumn() == 4 && ss.getName()=='Ark 1') { // 2. If Edit is done in column 4 (D) And sheet name is 'Ark 1' then: var celladdress ='L'+ r.getRowIndex() ss.getRange(celladdress).setValue(new Date()).setNumberFormat("dd/MM/yyyy hh:mm");} }; Replace the 4 with the column you edit, and 'L' with the column you want your timestamp in.
Great video! Really thorough. Is there a way to do this same type of thing but instead of a timestamp its a user-stamp based on the email of who edited the document?
When doing this on my Google Sheet for example the first time is fine so lets say A29 comes up with 17:00 when i type in cell A32. However when I type in B32 for Cell B29 to be updated A29 then changes to 12:00. Anyone know of a workaround?
Hey there, this is an incredible instruction, very detailed. Could you explain, how to apply this function to additional worksheets (but not all) in the workbook?
While testing this, I input some test string info into a few cells. As expected, the date/time data appeared from this script. Great! (Thank you so much!) However, now that I'm done testing things, I cleared out the test cells, and the date/time info is persistent. Any quick solutions or addendums to get this to update such that when info in the input cell is removed, the output date/time info disappears too? (you can see this in the video, for example, at timestamp 11:50 where both rows 10 and 11 have no text entered in any cells, but column D is still showing the previous entry's date/time)
Hi I appreciate your work very much, but may I know if Is there anyway I can put a time stamp in a single cell whenever changes happen to multiple range?
Thank you 4 that, a further implementation would be, to avoid users to make changes on modified columns...do you think would be an option to check if the "Session.getActiveUser().getEmail();" is not the spreadsheet owner? thank you again
Thank you so much, brother! This is exactly the solution I was looking for! I was experimenting with circular reference formulas, but the date would update the following day after inputting content into adjacent cells. So I needed a different solution. I thought of using some sort of script and just searched TH-cam. Thank goodness I found your video! You saved me so much time! Well done, great work! - Greeting from Toronto, Canada!
@@ExcelGoogleSheets This only works when the owner of the sheet makes any change but if anyone enter any value then the timestamp doesnot appear. I want to create the same when anyone can enter the value
@@subhamdey1922 Code.gs function onEdit(e) { addTimestamp (e); } function addTimestamp (e){ //variables I 7 R 7/16:38 Fix Date Delay > var startRow 2; = var targetColumn = 1; var ws = "My Data"; //get modified row and column var row = e. .range.getRow(); var col = e.range.getColumn(); if (col === targetColumn && row >= startRow && e.source.getActiveSheet().getName() WS var currentDate = new Date(); e.source.getActiveSheet().getRange (row, 4).setValue(currentDate); if (e.source.getActiveSheet().getRange (row, 3).getValue() ""){ e.source.getActiveSheet().getRange (row, 3).setValue(currentDate); } // END IF check if date created exists } // END IF check column, row, worksheet
Hi, just one doubt, I am copy pasting a data with multiple entries in rows, but script put a timestamp just in 1st row not the below copy pasted ones. As per my understanding this works only when onEdit triggers the cell which is edited. Can we do some changes in the script or trigger to work with the copy pasted values? Please help, in urgent need as I am working on large set of data.
I really appreciate the video which you have made, it is very easier for the non-technical person to understand, thank you so much. I am the new person who is going to use this script in our master sheet and also I would like to see more videos to learn the same from scratch... :)
Thank you so much for creating this tutorial, so helpful! One question I have is, is there a way to have the script display a particular date format? I know that I can format it in sheets using but that only changes what you see, not the actual data in the cell. Thanks again!
Would it be possible to show how to update the "Last Modified" column timestamp when making edits to ANY of the cells in the same row without changing the original "Date Entered" column? e.g. entering a value in the Package ID, the same Date is filled to both the "Date Entered" and "Last Modified" columns. IF I enter text in the Comments column I want the "Last Modified" date to update.
Has the Google code changed in the last 3 years? I keep getting errors now when I use it: 6:35:04 PM Notice Execution started 6:35:05 PM Error TypeError: Cannot read properties of undefined (reading 'range') addTimestamp @ Code:gs:15 onEdit @ Code.gs:3
Thanks a lot, i have a question if I add a data to the cell will get the date and last modified but if I drag the data from the first column only get the date of the first row, for the other the code won´t work. If there anything to add in the code to work when dragging down the data?
I have set up for check boxes and the timestamp and the row to the right of each. Users are clicking..... check check check in quick succession. sometimes the script fails to put in the timestamp. Seems like a problem of overlapping the running of the script before it has completed the previous. Any suggestions?
I have the exact same problem. I checked the code time and again, but I can not find an error or any reason, why this is happening. There is no other google account logged in like User "Cedrick Lacuarin" mentioned. Strangely the code worked fine, until the housekeepng part with adding all these variables started. Did you find a solution? EDIT: After I wrote the code a second time, it finally worked. I compared both versions, but I can not find any differences except there might be some different spaces and paragraphs. Something like this seems to be the problem. The code works fine now!
Hi! I am trying to combine your tutorial of combining multiple sheets into a masterlist with this timestamp. In column B of Masterlist, a timestamp should appear when data from another sheet is inputted in Column C by updating another sheet. Is that possible by using another code? I’ve tried it using the code, but when the masterlist gets updated from another sheet, no timestamp appear. However if I try to manipulate the data from the masterlist itself, a timestamp appears. I would really prefer if the timestamp will appear if the masterlist gets updated through another sheet.
Is there a way to make this work when you paste data into the sheet? Currently if I copy and paste data in the sheet it does not trigger the time stamps
Thanks for this great class , I have a problem when I try to bring new data into my spread sheet from Formulario , it doesnt work because of there's any editable change thats why it doesnt run the macro, could you give me some advice please
what would the code look like if you wanted the date and time stamp to be blank if the data in the column was deleted and empty now. Example A2 had data and D2 had last modified. But now A2 is empty so I want D2 to be empty now too.
Hi! First of all thank you for all the learnings I get in this channel! This may be a dumb question but Im very new in spreadsheet, so was thinking if you could guide me through on how to automatically add a time stamp based on the last edit made in the spreadsheet?
If you manually run the function you'll get an error and that's normal. The function needs to run automatically when changes are made in the spreadsheet.
Congratulations ! i not speak well english, but you explain very easy and simple , thanks for your work , definettly best tutorial ever ! hugs from Perú
What if we also want to see which user (with whom sheet was shared assuming more than 5 users are working on the sheet) has made the changes? can you please suggest on it as well
i wanna ask, if u has write something, the timestamp will be appear automaticly, but if u delete some text in the Package ID cloumn, is it the timestamp will be disapear or we must delete the timestamp manually
What is the formula for the timestamp to be entered when data is either copied or dragged? My timestamp script only works when I manually add data in each cell and does not work when I drag or copy data, it seems the onEdit trigger doesn't fire for every cell or row, it fires for every edit.
Hi. I tested the first, easiest, shortest script and it does not work. I saved the script and tested in a worksheet. I use google chrome and mac. What am I doing wrong. The script is in 4:22 minut of the movie. Somebody can help?
@@ExcelGoogleSheets I am very sorry. Here is the code from the movie: function onEdit(e) { var row = e.range.getRow(); e.source.getActiveSheet().getRange(row,4).set.Value(new Data()); }
Very helpful! Thank you. I have a question: What if I want to add timestamp in "Other" worksheet too? So when I'm in "My Data", the timestamp is still written in the first and fourth column of My Data, but, when I'm in "Other", the timestamp will be written in the first column of "Other" worksheet only. Thank you
Great video. Looks like this method of onEdit doesn't trigger when combined with pulling data into a worksheet via a Query. Is that what you are finding? Any thoughts on how to overcome this? I have a scenario where I am pulling data into a sheet with a query and when a new record is added to the query I want this onEdit to be triggered.
i really appreciate and do love everything you teach. It's so simple and easy to use in real work. I practice and really use it in my restaurant. I would like to thank you for all these knowledge. If you come to Thailand, let me know, I will guide you around enjoy Bangkok for 1 day....Thankssssssssssss my real teacher
thanks for the videos, it's amazing! I just have one question, i dont know if its possible, but I'd like to know how to protect the range so that no one can edit it later?. Because the way it is in the video, someone can change the date and put the date they want manually, changing it to days after ou before what it really was, i would love to prevent this from happening
This is very helpful thank you for posting! One thing I noticed is that if you would copy and paste Package ID down 2 rows, the timestamp code wont pick up the last two rows. Seems the code only works when something is hardcoded. Any advise here?
Impressive. But I was actually looking for inverted operation . What if I want to auto fill data in certain time interval? The data is retrieved from a cell data(pulled out from website) which is dynamic.
Thank you for this video, but for some reason Im having this error. TypeError: Cannot read property "range" from undefined. (line 3, file "Code") I really appreciated if you or somebody else can help me out.
Si es que ese código se detecta cuando escribes , porque es al momento de la edición de la celda que se dispara no es necesario darle al play, escribe el código y luego dale al botón guardar no al play, luego edita la hoja y "bum", funciona, por lo menos a mí me funcionó, logré configurarlo a modo de que se disparé al momento de hacer click en un checkbox, fabuloso. Suerte
Could you make the OnEdit check a whole range of clumns being eddited insted of just a single one? i.e. make a modified time stamp if someone changed a cell betwen Column E and Z if that was my range?
How do you call two similar functions, for example if I need to timestamp quote date and then invoice date? I just duplicated what you showed here with different row and column reference. Would I pass (e) in both functions or I need to change that?
Great Tuto, one question, what happen if you have more than one spreadsheet? , each spreadsheet need their own variables if you duplicate but change the name or it will work automatically?
QUESTION: If I wanted it to ONLY update when the cell in the column is an exact TEXT (from a dropdown text value in the cell), how would I write that? I've been trying to add it to the first IF statement, something like if(col== 1 && "COMPLETE") but nothing is working/I feel like I'm missing something simple. Any ideas?
I searched and searched and SEARCHED to find the code for this EXACT thing! I simply cannot tell you HOW MUCH I appreciate you doing this VIDEO!!! I have spent endless hours to try to get this to work and could not get it! AMAZING and THANK YOU SO MUCH FOR HELPING MY SANITY! :)
I had an issue as many have with the ranged error. Two things - an error will be returned if you run the code from the editor OR debugger. The reason for this is the conditions of the fields are not being met on the spreadsheet side and this is normal.
The other thing is recheck ALL OF YOUR SYNTAX. I chased this problem for far longer than I should have and found I had typed a "v" where a "u" should have gone in value - was too easy to miss but it looked right at a glance. Once this correction was made the code started to function correctly.
Another great video by this youtuber!
Hello, I had a problem with the range. What could possibly be the error? Thank you so much!
I remember following this 2 years ago took me easily 3 hours or so and I got a headache.
It felt like a huge achievement to complete it.
Now I made this code for a new sheet and it took me 30 min, felt easy, but most importantly,; this video has proven valueable over and over.
I use this code in a sheet I use weekly and it has saved me lots of time.
Thanks a bunch!
I think anyone could learn how to code with a professor like you. Congratulations and thank you for the knowledge shared!
I love the way you explain everything by stages, without delving into too much technicalities and even makes the improvements on it easy to grasp, so that even a code-ignorant like me can follow it easily. Subscribing to your channel in 3, 2, 1...!
There have been a few questions on how to run the same script on more than one sheet, but not all of them. Through simple copy and paste, I quickly created multiple scripts, one for each sheet I wanted the script to run on, giving each a unique name (e.g., Script1, Script2, Script3. - I gave mine the name of the sheet they corresponded to). Note, you can only have one script named "onEdit." You will want to reserve the onEdit name for the short "master script" that will run the rest.
After creating each new script, copy and paste the same code into each. Then, change the script name on the first line of code (right after "function" and before "(e)" to match the name of the new script. Then, change the sheet reference in the code located right after "getName() ===" to match the name of the sheet that script should run on.
Finally, go back to your original onEdit script and delete all of the code and paste the following new code, making the onEdit script the "master script" to run the rest of them (of course, modify the script names to match your script names, adding however many scripts you have):
function onEdit(e) {
script1(e);
script2(e);
script3(e);
}
Remember to save the scripts. While on the onEdit master script, click "Run." The sheet-specific scripts will execute in the order they appear in your onEdit master script.
I have no coding skills. But after some research, this approach worked for me.
You might want to check this guy's vid at 3:30
th-cam.com/video/N4V0FZASK60/w-d-xo.html
He talks about the "OR" statement and how you can use it
This will make your code a lot shorter and quicker to execute.
I also got no coding knowledge but trust me it's easy
EDIT: It's not very easy tho
For it to work properly my if statement had to be:
if(col === targetColumn && row >= startRow && e.source.getActiveSheet().getName() === ws ||col === targetColumn && row >= startRow && e.source.getActiveSheet().getName() === bs){
bs stands for bullsh!t as well as var bs = Sheet 2
I had to repeat almost the entire statement inside IF()
So in theory you could expand this row infinitely for as many sheets as you want
In the end it works, even though it's not as compact as I'd like
I searched for hours for this solution! Your teaching methods were clear and easy to follow. Thank you.
Great tut! Could you drop the script in the description below of your video? then you just can copy paste it. That would be easy!
Great instructions! Thanks. Is there a way to make the timestamp trigger when you paste several rows of data into the target column? Currently it only adds the timestamp to the top line where the start of the list was pasted.
Great tutorial, I have a very little experience with programming language, but you really made it for me.
I do still have one question related to this timestamp method.
I realized that this method only works with cell edited manually, so for example I paste 3 rows of data, it would only update timestamp for the first cell only, the second and third one wouldn't be updated. Could you please help me with this one ?
Really appreciate your woks man!
Man, I have this problem, do you have any idea how to solve this?
You just taught EXACTLY what I'm searching for! Thank you!!! You explain everything clearly!
Awesome tutorial! Is there a way to define a range of columns?
I'm chasing same answer.
Thanks for that! Could you please add the code into the video description?
Thanks, this is just what I was looking for.
Can you please tell me how to add condition that date erases when col 1 has no data?
UPP
@@gregoriusalvin118 ???
I am so glad I stumbled on your channel this is perfect.
now I have to figure out that when our receptionist receives a package, based on the tracking number it will fill in the carrier in cell 3
Thanks so much for this tutorial
Hello! this is a great tutorial, but how to get the timestamp from when any cell from a range is modified? cant find out how to set the range beside the number of column
I freaking love you, man!
Thanks for the lesson, it works wonders! S2
Thank you, thank you a lot! I can't express how grateful I am to you right now!
Best regards!
ME TOO!! I AM just so THANKFUL for this video ! lol I LOVE THIS MAN FOR DOING THIS!
Thank you! I didn't know anything about coding, and after this video I still don't know anything about coding but I do have a working timestamp solution.
would you please elaborate how to lock cells automatically after entry is made in sheets so that cells can't be edited after making an entry.
Thanks in advance
Holy Smokes, I think I found my new teacher. Wow!!
Thank you very much. After watching a lot of videos regarding this, i finally found yours and understood what i did. Not just copy and paste code.
Are you able to advise on how to run script for multiple timestamps on one sheet? Also, for the same timestamp to only work on selected sheets...i am struggling to make either scenario to work ? Do you have videos to cover these?
this script gives an error on the e.range.getRow(); TypeError: Cannot read property 'range' of undefined (line 14, file "Code")
I have had the exact same problem, but in line 15. I checked the code time and again, but I could not find an error or any reason, why this was happening. Strangely the code worked fine, until the housekeepng part with adding all these variables started.
After I wrote the code a second time, it finally worked. I compared both versions, but I can not find any differences except there might be some different spaces and paragraphs. Something like this seems to be the problem.
*The code works fine now! Thanks very much to this channel for all those outstanding tutorials!*
I had the same problem.
@@ianpropst-campbell6028 on the script editor go to edit and then current project triggers so you can enable on edit which is being called by the script.
@@onewiththefreaks3664 on the script editor go to edit and then current project triggers so you can enable on edit which is being called by the script.
@@MauricioHernandez87 Okay thank you!
This is a great tutorial and it used to work perfectly but as of 2021-11-05 it fails. The debugger says that several of the variables are "undefined". ("e", "ws", "row", "col", "currentDate") Has something changed in the scripting syntax to break this script?
Just edit the code and it should be live on your Sheet. Only way to test the code is by actually editing a cell in other words, since you're running the code from inside the script editor.
I’ve learned so much from this channel it’s insane. Thanks for all your help.
TypeError: Cannot read property 'range' of undefined (line 15, file "Code")
Can you help me on this ?
Anyone has a solution to this? I have the same problem. Thank you!
@Octave Laurentin I figured it out; it works when you don't try to run the code in script editor but save it and go to your sheet and it'll work. Because no range is given to it until you open the active sheet in question, then the value becomes defined.
if that doesnt work, try this code:
function onEdit(e) {
var ss = SpreadsheetApp.getActiveSheet();
var r = ss.getActiveCell();
//1.Change 'Ark 1' to be matching your sheet name
if (r.getColumn() == 4 && ss.getName()=='Ark 1') { // 2. If Edit is done in column 4 (D) And sheet name is 'Ark 1' then:
var celladdress ='L'+ r.getRowIndex()
ss.getRange(celladdress).setValue(new Date()).setNumberFormat("dd/MM/yyyy hh:mm");}
};
Replace the 4 with the column you edit, and 'L' with the column you want your timestamp in.
@@lukasodern thnx Jan, this script worked for me..
the way of writing codes and explanations is easily understandable to everyone thanks for this video.
Great video! Really thorough.
Is there a way to do this same type of thing but instead of a timestamp its a user-stamp based on the email of who edited the document?
When doing this on my Google Sheet for example the first time is fine so lets say A29 comes up with 17:00 when i type in cell A32. However when I type in B32 for Cell B29 to be updated A29 then changes to 12:00. Anyone know of a workaround?
Hey there, this is an incredible instruction, very detailed. Could you explain, how to apply this function to additional worksheets (but not all) in the workbook?
did you ever find a way to do this?
While testing this, I input some test string info into a few cells. As expected, the date/time data appeared from this script. Great! (Thank you so much!) However, now that I'm done testing things, I cleared out the test cells, and the date/time info is persistent. Any quick solutions or addendums to get this to update such that when info in the input cell is removed, the output date/time info disappears too? (you can see this in the video, for example, at timestamp 11:50 where both rows 10 and 11 have no text entered in any cells, but column D is still showing the previous entry's date/time)
Hi I appreciate your work very much, but may I know if Is there anyway I can put a time stamp in a single cell whenever changes happen to multiple range?
Thank you very much.You are a very good teacher.
Thank you 4 that, a further implementation would be, to avoid users to make changes on modified columns...do you think would be an option to check if the "Session.getActiveUser().getEmail();" is not the spreadsheet owner? thank you again
Can you not just lock the modified columns?
Thank you so much, brother! This is exactly the solution I was looking for!
I was experimenting with circular reference formulas, but the date would update the following day after inputting content into adjacent cells. So I needed a different solution.
I thought of using some sort of script and just searched TH-cam. Thank goodness I found your video! You saved me so much time! Well done, great work!
- Greeting from Toronto, Canada!
Glad it helped!
@@ExcelGoogleSheets This only works when the owner of the sheet makes any change but if anyone enter any value then the timestamp doesnot appear.
I want to create the same when anyone can enter the value
@@ExcelGoogleSheets Can you help me with the script code?
@@subhamdey1922 Code.gs
function onEdit(e) {
addTimestamp (e);
}
function addTimestamp (e){
//variables
I
7
R
7/16:38 Fix Date Delay >
var startRow 2; =
var targetColumn = 1;
var ws = "My Data";
//get modified row and column
var row = e. .range.getRow();
var col = e.range.getColumn();
if (col === targetColumn && row >= startRow && e.source.getActiveSheet().getName() WS
var currentDate = new Date();
e.source.getActiveSheet().getRange (row, 4).setValue(currentDate);
if (e.source.getActiveSheet().getRange (row, 3).getValue() ""){ e.source.getActiveSheet().getRange (row, 3).setValue(currentDate);
} // END IF check if date created exists } // END IF check column, row, worksheet
Can i today function with this formula?
Hi, just one doubt,
I am copy pasting a data with multiple entries in rows, but script put a timestamp just in 1st row not the below copy pasted ones. As per my understanding this works only when onEdit triggers the cell which is edited. Can we do some changes in the script or trigger to work with the copy pasted values?
Please help, in urgent need as I am working on large set of data.
I really appreciate the video which you have made, it is very easier for the non-technical person to understand, thank you so much. I am the new person who is going to use this script in our master sheet and also I would like to see more videos to learn the same from scratch... :)
I did the same as you said...in edit script screen. But when I modified the rows in my spreadsheet it's not reflecting. Am I missing something?
SAME
That was amazing tutorial, thank you for making this.
Dude, you're the best, I wish you get everything that you want my friend
Thank you so much for creating this tutorial, so helpful!
One question I have is, is there a way to have the script display a particular date format?
I know that I can format it in sheets using but that only changes what you see, not the actual data in the cell.
Thanks again!
Would it be possible to show how to update the "Last Modified" column timestamp when making edits to ANY of the cells in the same row without changing the original "Date Entered" column? e.g. entering a value in the Package ID, the same Date is filled to both the "Date Entered" and "Last Modified" columns. IF I enter text in the Comments column I want the "Last Modified" date to update.
Awesome very helpful. Just wanted to know if there was a way to change it so the timestamp would only change when specific test was added to the cell?
thank you for sharing, all your tutorial very very very very helpful. God Bless you and Family always
This is perfect! But is there a script wherein whenever there is a change, it'll have an automatic copy of the previous data?
This is great. But is there any way that the column 3 and 4 will only be updated for a certain text in column1?
Has the Google code changed in the last 3 years? I keep getting errors now when I use it:
6:35:04 PM Notice Execution started
6:35:05 PM Error TypeError: Cannot read properties of undefined (reading 'range')
addTimestamp @ Code:gs:15
onEdit @ Code.gs:3
Thanks a lot, i have a question if I add a data to the cell will get the date and last modified but if I drag the data from the first column only get the date of the first row, for the other the code won´t work. If there anything to add in the code to work when dragging down the data?
Awesome. Not only learned to timestamp but scripting in Google Sheets. Thanks!
Wow!! Is there a way just to add the time so that can be subtracted against another set time?
I have set up for check boxes and the timestamp and the row to the right of each. Users are clicking..... check check check in quick succession. sometimes the script fails to put in the timestamp. Seems like a problem of overlapping the running of the script before it has completed the previous. Any suggestions?
How would the script look different if I wanted to have an auto time stamp on two separate tabs?
Kindly help! TypeError: Cannot read property "range" from undefined. (line 15, file "Code")
heve same problem
Seems you have other google accounts logged in at the same time. Do check if you are running the script in the same Google account.
I have the exact same problem. I checked the code time and again, but I can not find an error or any reason, why this is happening. There is no other google account logged in like User "Cedrick Lacuarin" mentioned. Strangely the code worked fine, until the housekeepng part with adding all these variables started. Did you find a solution?
EDIT: After I wrote the code a second time, it finally worked. I compared both versions, but I can not find any differences except there might be some different spaces and paragraphs. Something like this seems to be the problem. The code works fine now!
Quick question, what if there is a second argument: e.g. IF column 4 modified, timestamp in column 5 for example. Thanks!
You are the best. You just helped me. Beautiful explanation
Hi! I am trying to combine your tutorial of combining multiple sheets into a masterlist with this timestamp. In column B of Masterlist, a timestamp should appear when data from another sheet is inputted in Column C by updating another sheet. Is that possible by using another code?
I’ve tried it using the code, but when the masterlist gets updated from another sheet, no timestamp appear. However if I try to manipulate the data from the masterlist itself, a timestamp appears. I would really prefer if the timestamp will appear if the masterlist gets updated through another sheet.
are you get the code?
Can it be applied on sheet that the data is filled by mirroring from other sheet? Thanks
Thank you! How do you target multiple columns?
add "||"
Is there a way to make this work when you paste data into the sheet? Currently if I copy and paste data in the sheet it does not trigger the time stamps
Thanks for this great class , I have a problem when I try to bring new data into my spread sheet from Formulario , it doesnt work because of there's any editable change thats why it doesnt run the macro, could you give me some advice please
love the way you explain the script , not just typing it. for begginers like me, looking for some code for my project, thanks
👍
what would the code look like if you wanted the date and time stamp to be blank if the data in the column was deleted and empty now. Example A2 had data and D2 had last modified. But now A2 is empty so I want D2 to be empty now too.
Great vid, much appreciated! Is there any way to use this several times in one specific tab? Thanks
Speaking from Brasil! Simply perfect! Thank you!
Hi! First of all thank you for all the learnings I get in this channel! This may be a dumb question but Im very new in spreadsheet, so was thinking if you could guide me through on how to automatically add a time stamp based on the last edit made in the spreadsheet?
received this error " TypeError: Cannot read property 'range' of undefined (line 3, file "Code") " send help
I received the same error. Did you figure it out?
@@Eduardo12783 did you?
If you manually run the function you'll get an error and that's normal. The function needs to run automatically when changes are made in the spreadsheet.
Congratulations ! i not speak well english, but you explain very easy and simple , thanks for your work , definettly best tutorial ever ! hugs from Perú
What if we also want to see which user (with whom sheet was shared assuming more than 5 users are working on the sheet) has made the changes? can you please suggest on it as well
Can't get it to work after typing it all in verbatim. Can't even get the initial function to work @ timestamp 4:15. Guess I'm not a programmer.
i wanna ask, if u has write something, the timestamp will be appear automaticly, but if u delete some text in the Package ID cloumn, is it the timestamp will be disapear or we must delete the timestamp manually
What is the formula for the timestamp to be entered when data is either copied or dragged? My timestamp script only works when I manually add data in each cell and does not work when I drag or copy data, it seems the onEdit trigger doesn't fire for every cell or row, it fires for every edit.
Hi. I tested the first, easiest, shortest script and it does not work. I saved the script and tested in a worksheet. I use google chrome and mac. What am I doing wrong. The script is in 4:22 minut of the movie. Somebody can help?
No one can help you reading you comment. Try to at least share you code that doesn't work.
@@ExcelGoogleSheets I am very sorry. Here is the code from the movie:
function onEdit(e) {
var row = e.range.getRow();
e.source.getActiveSheet().getRange(row,4).set.Value(new Data());
}
Very helpful! Thank you.
I have a question: What if I want to add timestamp in "Other" worksheet too? So when I'm in "My Data", the timestamp is still written in the first and fourth column of My Data, but, when I'm in "Other", the timestamp will be written in the first column of "Other" worksheet only.
Thank you
TypeError: Cannot read property "range" from undefined. (line 3, file "Code")Dismiss
Make sure everything is on the correct line as the tutorial, fixing this fixed the same issue I had when running the script.
dont run it.. just save it and then after saving test it on your sheet.
Seems you have other google accounts logged in at the same time. Do check if you are running the script in the same Google account.
same problem. anyone has found a solution or figure out what the problem is?
The same...TypeError: Cannot read property 'range' of undefined (line 8, file "Code")
will it work if we copy paste the data as well?
Very nice...I've been watching many others and you are by far the easiest to follow and understand. Thank you very much.
Great video. Looks like this method of onEdit doesn't trigger when combined with pulling data into a worksheet via a Query. Is that what you are finding? Any thoughts on how to overcome this? I have a scenario where I am pulling data into a sheet with a query and when a new record is added to the query I want this onEdit to be triggered.
i really appreciate and do love everything you teach. It's so simple and easy to use in real work. I practice and really use it in my restaurant. I would like to thank you for all these knowledge. If you come to Thailand, let me know, I will guide you around enjoy Bangkok for 1 day....Thankssssssssssss my real teacher
Is it possible to use this script in the same sheet, but in different ws?
Totally just used this. Thank you. I feel like I kinda jumped into the advanced area. but you made it easy to follow your instructions. Thank you!
thanks for the videos, it's amazing! I just have one question, i dont know if its possible, but I'd like to know how to protect the range so that no one can edit it later?. Because the way it is in the video, someone can change the date and put the date they want manually, changing it to days after ou before what it really was, i would love to prevent this from happening
just protect the column from all editor except owner
This is very helpful thank you for posting! One thing I noticed is that if you would copy and paste Package ID down 2 rows, the timestamp code wont pick up the last two rows. Seems the code only works when something is hardcoded. Any advise here?
Thank you!! No coding experience whatsoever and I was able to do it!
Excellent!
This tutorial is so good and informative in anyways. May i ask how can i specify a column range like A1 to E1 for example.
Hi there did you find a solution for this?
Impressive. But I was actually looking for inverted operation . What if I want to auto fill data in certain time interval? The data is retrieved from a cell data(pulled out from website) which is dynamic.
Hi how do I set the range for multiple target columns instead of only one column?
Is there a way to make this work, so when I share the document, all the other folks working in the doc will also force the time stamp?
Thank you for this video, but for some reason Im having this error.
TypeError: Cannot read property "range" from undefined. (line 3, file "Code")
I really appreciated if you or somebody else can help me out.
I'm also facing same error in google excell sheet... Pls help me too.
@@sanjeev82932 I'm also facing same error in google excell sheet...
hola, lo pudiste resolver???
Si es que ese código se detecta cuando escribes , porque es al momento de la edición de la celda que se dispara no es necesario darle al play, escribe el código y luego dale al botón guardar no al play, luego edita la hoja y "bum", funciona, por lo menos a mí me funcionó, logré configurarlo a modo de que se disparé al momento de hacer click en un checkbox, fabuloso. Suerte
Luis Miguel Corado Umaña muchas gracias, voy a probar nuevamente.
Hi What would the code look like if there as just one cell with date being updated in it if any cell in the table is changed?
Thanks for this. How would I modify so that it triggers on modification of multiple columns. Say for example columns 1-14?
Specifically looking to trigger if 4 columns are modified in my case
Wow I finally got this to work thanks to your video! Super helpful and well explained.
Could you make the OnEdit check a whole range of clumns being eddited insted of just a single one? i.e. make a modified time stamp if someone changed a cell betwen Column E and Z if that was my range?
Hey great tutorial; how would I add a function to delete the timestamps when I delete the info in the cell?
I owe my new job to this youtube channel
How do you call two similar functions, for example if I need to timestamp quote date and then invoice date? I just duplicated what you showed here with different row and column reference. Would I pass (e) in both functions or I need to change that?
Yes, create another function that accepts e as an argument and call that function in onEdit as well.
Great Tuto, one question, what happen if you have more than one spreadsheet? , each spreadsheet need their own variables if you duplicate but change the name or it will work automatically?
QUESTION: If I wanted it to ONLY update when the cell in the column is an exact TEXT (from a dropdown text value in the cell), how would I write that? I've been trying to add it to the first IF statement, something like if(col== 1 && "COMPLETE") but nothing is working/I feel like I'm missing something simple. Any ideas?
Thanks for this. How do you add a target col? Say edit to col 1 & 2 will change date?
It doesnot work if you protect the column from people who overwrite the time stamp. Is there any solution for this?