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! :)
@RadoHudranАй бұрын
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!
@Brakken993 жыл бұрын
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!
@gladysgalat4992 Жыл бұрын
Hello, I had a problem with the range. What could possibly be the error? Thank you so much!
@lucazarts25 Жыл бұрын
I think anyone could learn how to code with a professor like you. Congratulations and thank you for the knowledge shared!
@scott-ish4043 жыл бұрын
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...!
@GoTouchSomeGrassBuddy4 ай бұрын
You just taught EXACTLY what I'm searching for! Thank you!!! You explain everything clearly!
@guyw68492 жыл бұрын
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.
@RadoHudran2 жыл бұрын
You might want to check this guy's vid at 3:30 kzbin.info/www/bejne/hGW5YXmQdrh-bJI 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
@kickinitwithk7463 жыл бұрын
I searched for hours for this solution! Your teaching methods were clear and easy to follow. Thank you.
@mailtossundar Жыл бұрын
the way of writing codes and explanations is easily understandable to everyone thanks for this video.
@gebruikerarjan4 жыл бұрын
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!
@philippschumacher46594 жыл бұрын
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.
@LucasFurioFranco4 жыл бұрын
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!
@teresalgeorge3 жыл бұрын
ME TOO!! I AM just so THANKFUL for this video ! lol I LOVE THIS MAN FOR DOING THIS!
@davem453 жыл бұрын
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
@user-zw6bw3kt7j Жыл бұрын
thank you for sharing, all your tutorial very very very very helpful. God Bless you and Family always
@benedictchua-theredreamer Жыл бұрын
love the way you explain the script , not just typing it. for begginers like me, looking for some code for my project, thanks
@ExcelGoogleSheets Жыл бұрын
👍
@muraly35232 жыл бұрын
Thank you very much.You are a very good teacher.
@madhumohan38432 жыл бұрын
It is really impressive the way he explain and can grab the attention of code ignorant people
@sangeetarora2 ай бұрын
Very helpful and informative
@BustaCap4 жыл бұрын
I’ve learned so much from this channel it’s insane. Thanks for all your help.
@nasirshaikh27154 жыл бұрын
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... :)
@hoiyinwan82334 жыл бұрын
That was amazing tutorial, thank you for making this.
@wagnerbarbosa18943 жыл бұрын
Speaking from Brasil! Simply perfect! Thank you!
@lovelyktott3 жыл бұрын
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
@fauzanwidianto13654 жыл бұрын
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!
@cmteclaudios Жыл бұрын
Man, I have this problem, do you have any idea how to solve this?
@audiomemento2 жыл бұрын
Wow I finally got this to work thanks to your video! Super helpful and well explained.
@dannybans234 жыл бұрын
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 KZbin. Thank goodness I found your video! You saved me so much time! Well done, great work! - Greeting from Toronto, Canada!
@ExcelGoogleSheets4 жыл бұрын
Glad it helped!
@soamyaashishgetengaged78814 жыл бұрын
@@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
@subhamdey19222 жыл бұрын
@@ExcelGoogleSheets Can you help me with the script code?
@ali399 Жыл бұрын
@@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
@emmanuelmgbemena3 жыл бұрын
You are the best. You just helped me. Beautiful explanation
@travel247peru84 жыл бұрын
Congratulations ! i not speak well english, but you explain very easy and simple , thanks for your work , definettly best tutorial ever ! hugs from Perú
@saoudiabdealbaseete42393 жыл бұрын
Dude, you're the best, I wish you get everything that you want my friend
@JanRossi3 жыл бұрын
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!
@andrewjawney13 жыл бұрын
Thanks for that! Could you please add the code into the video description?
@omarjimz4 жыл бұрын
Awesome. Not only learned to timestamp but scripting in Google Sheets. Thanks!
@DavidMacKayE4 жыл бұрын
Holy Smokes, I think I found my new teacher. Wow!!
@Nibarger Жыл бұрын
Very nice...I've been watching many others and you are by far the easiest to follow and understand. Thank you very much.
@adeep5983 жыл бұрын
I owe my new job to this youtube channel
@cristinapajarillo69564 ай бұрын
Thanks so much you helped improve my project significantly! All the best!
@manasgarg144 жыл бұрын
This is exactly what I'm looking for, thanks for the tutorial. Great job man! THANKS A TON !!! :)
@MehdiKamali119 күн бұрын
works perfectly, thank you so much for your time
@ohwe0w2263 жыл бұрын
Thank you!! No coding experience whatsoever and I was able to do it!
@ExcelGoogleSheets3 жыл бұрын
Excellent!
@PencilBoxID4 жыл бұрын
This exactly what im looking for, thanks for the tutorial,
@manishdashsharma73814 жыл бұрын
Hello you know app scripting?
@samprotestchanel22524 жыл бұрын
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?
@gregoriusalvin1183 жыл бұрын
UPP
@pearldme74563 жыл бұрын
@@gregoriusalvin118 ???
@bhawanishekhawat31634 жыл бұрын
Very nice video and it is very useful in companies where Google sheets are used too much, our company is completely on Gmail
@decenmueblesinfantiles36473 жыл бұрын
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
@dennyandriansyah25603 жыл бұрын
exactly what I looking for.. thanks for tutorial.. very helpful..
@edisonpalima4 жыл бұрын
Thank you very much. This is the exact tutorial that I'm looking for. I'm now subscribed :)
@ExcelGoogleSheets4 жыл бұрын
Thanks for the sub!
@norma-bh4voАй бұрын
SO HELPFUL THANK YOU!
@whinapomana97154 жыл бұрын
This is such a great tutorial, thank you so much!
@AhmedIbrahim-ct5fs4 жыл бұрын
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
@Corigador4 жыл бұрын
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.
@subratadutta45624 жыл бұрын
Nicely explained with minute details. Thanks for uploading this video. Looking forward to watch more.
@Trackdaygenius7 ай бұрын
Awesome tutorial! Is there a way to define a range of columns?
@fjlugo6 ай бұрын
I'm chasing same answer.
@affiqfadzil4 жыл бұрын
Great job! Clear tutorial, exactly what i'm looking for.. Thank you so much
@sigvardbjorkman2 ай бұрын
For someone that doesn't code but just want to have time stamps, I am just so confused why this functionality isn't already inside Google Spreadsheets to begin with?
@tamayonoelle884 жыл бұрын
Very informative and superb knowledge! Thank you for sharing it!
@unityindualities4 жыл бұрын
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.
@bangdoeltips74924 жыл бұрын
Very useful and helpful tutorial, thank you.
@scottcardais35602 жыл бұрын
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?
@ingridrnilsen Жыл бұрын
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.
@hellboy14882 жыл бұрын
this was very helpful and my very first exposure to any sort of code! worked perfectly thank you!
@agamgumelar66052 жыл бұрын
Works like a charm, thank you so much bro!!
@ExcelGoogleSheets2 жыл бұрын
👍
@jayrodathome8 ай бұрын
FYI you can also easily add a timestamp by using Lamda instead of Appscript. For example. Assuming A1 is a Checkbox (true/false) then you could use this formula =LAMBDA(now,cell,if(cell,now,))(now(),A1) which will print the time whenever that checkbox is checked off and it is static. It will not change onEdit or refresh. It will only update when you click the checkbox. Now you can change the logic to whatever I'm just using the checkbox as an example. I believe this is a newer feature in google sheets.
@sinisamoravac84823 жыл бұрын
Works perfectly for me! Very well explained!! Thank you
@CompSciSimplified3 жыл бұрын
Thanks man,respect for your hard work.
@RonMorlan1516 Жыл бұрын
Just did a mod of this code and it worked great! Thanks for a great tutorial!
@sophie81993 жыл бұрын
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?
@felixtsoy31383 жыл бұрын
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?
@christran86432 жыл бұрын
did you ever find a way to do this?
@152M13 жыл бұрын
Holy camoly best tutorial and explaination ever!! Thank you so much for the knowledge!!
@ExcelGoogleSheets3 жыл бұрын
Glad you enjoyed it!
@mirandusfarmers72503 жыл бұрын
for those who want the code plus a few tweaks function onEdit(e) { addTimestamp(e); } function addTimestamp(e) { var startRow = 2; var targetColumn = 10; var ws = "testSheetOne" ; var row = e.range.getRow(); var col = e.range.getColumn(); var insertColumn1 = 12 var insertColumn2 = 13 if(col === targetColumn && row >= startRow && e.source.getActiveSheet().getName() === ws ) { var currentDate = new Date (); e.source.getActiveSheet().getRange(row,insertColumn2).setValue(currentDate); if(e.source.getActiveSheet().getRange(row,insertColumn1).getValue() == ""){ e.source.getActiveSheet().getRange(row,insertColumn1).setValue(currentDate); } } } //kzbin.info/www/bejne/a2WblXdpnr2Yqsk
@itbrian392 жыл бұрын
Can't get it to work, but thanks anyway.
@PPPRAJWALLL3 жыл бұрын
It doesnot work if you protect the column from people who overwrite the time stamp. Is there any solution for this?
@garricko4 жыл бұрын
Thank you for making this! This helped a lot with my simple spreadsheet.
@administradordrycleanservi5321 Жыл бұрын
Apreciated the good explanation, keep it up!
@jo-annebernardo7979 Жыл бұрын
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?
@apljaaay3 жыл бұрын
THANK YOU FOR SHARING YOUR KNOWLEDGE MAN!!
@kamleshpatel82443 жыл бұрын
Amazingly helpful video, very easy to follow and give confidence adapt too ... thank you so much
@15weouthere103 жыл бұрын
received this error " TypeError: Cannot read property 'range' of undefined (line 3, file "Code") " send help
@Eduardo127833 жыл бұрын
I received the same error. Did you figure it out?
@tanmaykaushik37323 жыл бұрын
@@Eduardo12783 did you?
@karyaadhikari80453 жыл бұрын
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.
@kunal_chand3 жыл бұрын
Helpful as always. Brilliant!
@drylunch84623 жыл бұрын
Really useful info, thank you very much for posting, have now subscribed
@lumberdesign44014 жыл бұрын
Very Helpful Tutorial!
@RadoHudran2 жыл бұрын
Awesome, I enjoyed coding with you because it's interesting and I can't code on my own at all. Thx!
@najmuddinazik4 жыл бұрын
Thank you so much. I follow step by step and make it! God bless you for helping🙏🏼☺️
@eptrade23234 жыл бұрын
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.
@prony51453 жыл бұрын
Great!! I have just been looking for this everywhere. Thanks a lot man.
@jogifrh2 жыл бұрын
Simple and useful tutorial! Thanks.
@jamessteffen47054 жыл бұрын
Thank you, super helpful. Great Job!!!
@StarsNStripesForever2 жыл бұрын
Super helpful. Thanks for the run-through!
@mentality-monster Жыл бұрын
This is very useful. Thank you sir.
@carloumali34173 жыл бұрын
great tutorial, easy to understand! I tried it myself and it works!
@k2s3532 жыл бұрын
Thanks for that great explanation!
@Xansos2 жыл бұрын
Thanks a lot for this man, exactly what i was looking for
@mostafizurrahman41103 жыл бұрын
Thank you so much brother for your helpful video.
@aleksandarnakevski80414 жыл бұрын
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?
@MauricioHernandez873 жыл бұрын
this script gives an error on the e.range.getRow(); TypeError: Cannot read property 'range' of undefined (line 14, file "Code")
@onewiththefreaks36643 жыл бұрын
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-campbell60283 жыл бұрын
I had the same problem.
@MauricioHernandez873 жыл бұрын
@@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.
@MauricioHernandez873 жыл бұрын
@@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.
@ianpropst-campbell60283 жыл бұрын
@@MauricioHernandez87 Okay thank you!
@kevinbelmonte5242 жыл бұрын
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.
@Trackdaygenius7 ай бұрын
Hi there did you find a solution for this?
@vivienm57193 жыл бұрын
Such great explanation and helpful information. Thanks!
@ExcelGoogleSheets3 жыл бұрын
Glad it was helpful!
@prasantamaji99983 жыл бұрын
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??
@kyledinwiddie99622 жыл бұрын
Fabulous tutorial. Thank you
@ConsulthinkProgrammer4 жыл бұрын
Thank's Sir. That's "formatting issue" solution in 4:40 helping me a lot, simple but efficacious :)
@raihannewaz47284 жыл бұрын
Hello Google sheet Expert please help me 🙏🙏🙏🙏. I need a script , when i put data Col "D" it'll automatic create a date stamp on Col "B", Ata the same time Same Sheet when i put data Col "N" it'll automatic create a date stamp on Col "M" Please Help me. My office boss say;s its very urgent. i have no idea about script so please help me 🙏🙏🙏🙏. Sheet Link docs.google.com/spreadsheets/d/1BrZPYUUldQt1n_NZ0MZYLfmU1B_KkrAnvanprE_KE8E/edit?usp=sharing
@joshuageraldinotantrysenak76923 жыл бұрын
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
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