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 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 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 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 ruclips.net/video/N4V0FZASK60/видео.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 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
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!
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.
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... :)
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?
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 RUclips. 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
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?
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
Congratulations ! i not speak well english, but you explain very easy and simple , thanks for your work , definettly best tutorial ever ! hugs from Perú
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!
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?
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?
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
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
Thank you so much sir for sharing this knowledge with us. Sir one thing I need to know if I want to apply this code in multiple sheet on same worksheet then what should to do??
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
This is really helpful!!! thank you so much for this. If its alright, I just have a quick question, what if I want to add another specific sheet with this function? Thank you so much in advance!!!
Thank you so much for doing this, I love this page so much. You are contributing to something that is going to change the world, and I can't thank you enough. Thank you for all these tutorials, they have helped me insurmountably.
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 love it ...explained so simply... I am facing one issue - I have a Script (s1) which takes data from webpage and updates it. Now have added your script(s2) with the same Gsheet. But it doesn't enter if the sheet i updated by s1 but works fine if manually edited.. Your feedback please...
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?
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?
I've been browsing for hours now, finally I Found this thankyou so much. I just have one question, If I want to track the changes on other sheet then timestamp into the current sheet, how should I do that :). Thankyou
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?
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 for this tutorial video. I really learned a lot. But is there a way to know the last day modified from another spreadsheet? Thank you so much.
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
Hi, greeting from Argentina. You're the Best of the best! Thanks to you and your tutorials i'm making a lot ok changes in my business. I konow very little about programming (i just know what i saw in a few videos of yours) so i want to know how can i run this function to give me a different time stamp (in differente columns) depending on the value of the first column. Being precise, i need to calculate the time lapses between events taking place in my business (i have an aesthetic center here in Argentina i want to know how much time the clients wait in the waiting room and how much time their spend in the consulting room). So, in the first column i have a drowpdown list with the differente events. For example, the first event would be "client arrives". When you select that option, the idea is to register that specific dat and time in a column called "arrival". Then, the next event would be "client is being attended in the consulting room". When you select that option, the idea is to register that specific date and time in another column called "in consultating room" (this new event shouldn't change the timestamp in the column "arrival"). This workflow would allow me to take into accountance the average waiting time and the average consulting time (and i would also allow me to have an alert, in real time, if someone has been waiting for too long). After watching your video i managed to make the time stamp exactyly as you did it (it's amazing by the way). I imagined that i would only have to change the if condition (when it sais "getValue() == "")) and that i could just put "getValue() == "client arrives" but sadly that didn't worked. Please, could you help me? Thanks in advance for everything!
@@ExcelGoogleSheets I finally did it. Guess how? With your other video about timestamps. You're the best. Thanks for sharing all your knowledge to the world
hi, Thank you for sharing this video - Add Timestamp when cell is changed. this is working for me. i would like to use two app script i.e onEdit & addTimestamp in same excel sheet. How can i do this which adding two script for same sheet. both script i have learnt from this channel. at present i have added both the script and only one script which addTimestamp is working but onEdit script is not working (i.e: Dynamic Drop Down Based on Another Cell & Multiple Selections Updated Automatically) Please help me in this regards
This is quite amazing! I found a different way of doing it, it is simple, but works for me. I created a "form" with google forms..... as a question you can ask: what is the package id? and let the response be text (the id)... once the answer is submitted you can ask google forms to send the results to a google spreadsheet..... the time stamp is a default column.
First of all thank you very much for this tutorial. I am successfully using it on various sheets. I do have an additional step that I need validated and so far haven't been able to figure it out. I was wondering if I could get some input: The column that I am using as a trigger has a formula that adds a checkmark (CHAR(10004) actually with an IF formula). So I need to know when the checkmark appeared. If I edit the cell manually then it works but the formula doesn't seem to trigger it. function onEdit(e) {
addTimestamp(e); } function addTimestamp(e) { //variables var ws = "Dashboard"; var startRow = 4; var targetColumn1 = 28; var dateColumn1 = 29; var targetColumn2 = 32; var dateColumn2 = 30; var currentDate = new Date();
//get modified row and column var row = e.range.getRow(); var col = e.range.getColumn();
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 think anyone could learn how to code with a professor like you. Congratulations and thank you for the knowledge shared!
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 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 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...!
You just taught EXACTLY what I'm searching for! Thank you!!! You explain everything clearly!
I searched for hours for this solution! Your teaching methods were clear and easy to follow. Thank you.
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
ruclips.net/video/N4V0FZASK60/видео.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 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 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?
Awesome tutorial! Is there a way to define a range of columns?
I'm chasing same answer.
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
I’ve learned so much from this channel it’s insane. Thanks for all your help.
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.
the way of writing codes and explanations is easily understandable to everyone thanks for this video.
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!
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.
Awesome. Not only learned to timestamp but scripting in Google Sheets. Thanks!
love the way you explain the script , not just typing it. for begginers like me, looking for some code for my project, thanks
👍
That was amazing tutorial, thank you for making this.
Holy Smokes, I think I found my new teacher. Wow!!
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!
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... :)
Dude, you're the best, I wish you get everything that you want my friend
Thanks for that! Could you please add the code into the video description?
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?
Wow I finally got this to work thanks to your video! Super helpful and well explained.
Thank you very much.You are a very good teacher.
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 ???
Speaking from Brasil! Simply perfect! Thank you!
Very nice...I've been watching many others and you are by far the easiest to follow and understand. Thank you very much.
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 RUclips. 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
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?
Thank you!! No coding experience whatsoever and I was able to do it!
Excellent!
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
You are the best. You just helped me. Beautiful explanation
This is exactly what I'm looking for, thanks for the tutorial. Great job man!
THANKS A TON !!! :)
Congratulations ! i not speak well english, but you explain very easy and simple , thanks for your work , definettly best tutorial ever ! hugs from Perú
thank you for sharing, all your tutorial very very very very helpful. God Bless you and Family always
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!
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?
It is really impressive the way he explain and can grab the attention of code ignorant people
Thanks so much you helped improve my project significantly! All the best!
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?
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?
Can i today function with this formula?
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
Great job! Clear tutorial, exactly what i'm looking for.. Thank you so much
Great vid, much appreciated! Is there any way to use this several times in one specific tab? Thanks
Very nice video and it is very useful in companies where Google sheets are used too much, our company is completely on Gmail
This is such a great tutorial, thank you so much!
Thank you very much. This is the exact tutorial that I'm looking for. I'm now subscribed :)
Thanks for the sub!
Nicely explained with minute details. Thanks for uploading this video. Looking forward to watch more.
I owe my new job to this youtube channel
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
Thank you so much sir for sharing this knowledge with us.
Sir one thing I need to know if I want to apply this code in multiple sheet on same worksheet then what should to do??
This exactly what im looking for, thanks for the tutorial,
Hello you know app scripting?
this was very helpful and my very first exposure to any sort of code! worked perfectly thank you!
exactly what I looking for.. thanks for tutorial.. very helpful..
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
This is perfect! But is there a script wherein whenever there is a change, it'll have an automatic copy of the previous data?
Works like a charm, thank you so much bro!!
👍
This is really helpful!!! thank you so much for this. If its alright, I just have a quick question, what if I want to add another specific sheet with this function? Thank you so much in advance!!!
ohhhh, i figured it out! no worries. again, this video is great and very educational. really appreciate you and your vids. thank you so much!
👍
Thank you so much for doing this, I love this page so much. You are contributing to something that is going to change the world, and I can't thank you enough. Thank you for all these tutorials, they have helped me insurmountably.
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 love it ...explained so simply...
I am facing one issue -
I have a Script (s1) which takes data from webpage and updates it. Now have added your script(s2) with the same Gsheet. But it doesn't enter if the sheet i updated by s1 but works fine if manually edited.. Your feedback please...
Very informative and superb knowledge! Thank you for sharing it!
Apreciated the good explanation, keep it up!
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?
Works perfectly for me! Very well explained!! Thank you
Very useful and helpful tutorial, thank you.
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?
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?
Great!! I have just been looking for this everywhere. Thanks a lot man.
Just did a mod of this code and it worked great! Thanks for a great tutorial!
Thank you for making this! This helped a lot with my simple spreadsheet.
I've been browsing for hours now, finally I Found this thankyou so much.
I just have one question, If I want to track the changes on other sheet then timestamp into the current sheet, how should I do that :). Thankyou
source.getSheetByName("Time Stamp Sheet Name").setValue(new Date())
Thanks man,respect for your hard work.
SO HELPFUL THANK YOU!
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?
Helpful as always. Brilliant!
Awesome, I enjoyed coding with you because it's interesting and I can't code on my own at all.
Thx!
Super helpful. Thanks for the run-through!
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 for this tutorial video. I really learned a lot. But is there a way to know the last day modified from another spreadsheet? Thank you so much.
Very helpful and informative
Thanks a lot for this man, exactly what i was looking for
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
Really useful info, thank you very much for posting, have now subscribed
Thanks for that great explanation!
Thank you so much. I follow step by step and make it! God bless you for helping🙏🏼☺️
Hi, greeting from Argentina. You're the Best of the best! Thanks to you and your tutorials i'm making a lot ok changes in my business.
I konow very little about programming (i just know what i saw in a few videos of yours) so i want to know how can i run this function to give me a different time stamp (in differente columns) depending on the value of the first column.
Being precise, i need to calculate the time lapses between events taking place in my business (i have an aesthetic center here in Argentina i want to know how much time the clients wait in the waiting room and how much time their spend in the consulting room). So, in the first column i have a drowpdown list with the differente events. For example, the first event would be "client arrives". When you select that option, the idea is to register that specific dat and time in a column called "arrival". Then, the next event would be "client is being attended in the consulting room". When you select that option, the idea is to register that specific date and time in another column called "in consultating room" (this new event shouldn't change the timestamp in the column "arrival").
This workflow would allow me to take into accountance the average waiting time and the average consulting time (and i would also allow me to have an alert, in real time, if someone has been waiting for too long).
After watching your video i managed to make the time stamp exactyly as you did it (it's amazing by the way). I imagined that i would only have to change the if condition (when it sais "getValue() == "")) and that i could just put "getValue() == "client arrives" but sadly that didn't worked.
Please, could you help me?
Thanks in advance for everything!
It should work, just know it's case sensitive. Example "Client Arrives" is not the same as "client arrives"
@@ExcelGoogleSheets I finally did it. Guess how? With your other video about timestamps. You're the best. Thanks for sharing all your knowledge to the world
hi, Thank you for sharing this video - Add Timestamp when cell is changed. this is working for me.
i would like to use two app script i.e onEdit & addTimestamp in same excel sheet. How can i do this which adding two script for same sheet.
both script i have learnt from this channel.
at present i have added both the script and only one script which addTimestamp is working but onEdit script is not working (i.e: Dynamic Drop Down Based on Another Cell & Multiple Selections Updated Automatically)
Please help me in this regards
Hi! I was wondering if there is a way to add the date when the cell contains only specific data klike "done" for example)
Amazing easy to follow video
Holy camoly best tutorial and explaination ever!! Thank you so much for the knowledge!!
Glad you enjoyed it!
Simple and useful tutorial! Thanks.
THANK YOU FOR SHARING YOUR KNOWLEDGE MAN!!
This is quite amazing! I found a different way of doing it, it is simple, but works for me.
I created a "form" with google forms..... as a question you can ask: what is the package id? and let the response be text (the id)... once the answer is submitted you can ask google forms to send the results to a google spreadsheet..... the time stamp is a default column.
First of all thank you very much for this tutorial. I am successfully using it on various sheets. I do have an additional step that I need validated and so far haven't been able to figure it out. I was wondering if I could get some input: The column that I am using as a trigger has a formula that adds a checkmark (CHAR(10004) actually with an IF formula). So I need to know when the checkmark appeared. If I edit the cell manually then it works but the formula doesn't seem to trigger it.
function onEdit(e) {
addTimestamp(e);
}
function addTimestamp(e) {
//variables
var ws = "Dashboard";
var startRow = 4;
var targetColumn1 = 28;
var dateColumn1 = 29;
var targetColumn2 = 32;
var dateColumn2 = 30;
var currentDate = new Date();
//get modified row and column
var row = e.range.getRow();
var col = e.range.getColumn();
if(col === targetColumn1 && row >= startRow && e.source.getActiveSheet().getName() === ws ) {
e.source.getActiveSheet().getRange(row,dateColumn1).setValue(currentDate);
}
if(col === targetColumn2 && row >= startRow && e.source.getActiveSheet().getName() === ws) {
e.source.getActiveSheet().getRange(row,dateColumn2).setValue(currentDate);
} //END IF check column, row, worksheet
} //END function addTimestamp
Good video. It works great for manual cell changes. What if i want to add a timestamp based on a cell change that is in an array? Is this possible?