How can I create a time tracker or punch clock using Google Sheets?

[@Ryokishine]

This is honestly one of the coolest tutorials I've ever watched... I've always been mesmerized by how much you can do with coding and have wanted to get into it but chose a different career path. Very inspiring just to watch you work, and obviously very useful as well. Thanks for what you do :)

[@TimothyJamesPittsburgh]

Thanks for watching it! I've got lots of other videos about programming using Apps Script - I'm planning some more tutorials / courses over the summer.

[@M4rt1nX]

Thanks a lot. This works perfectly. I just started a project and this sheet is exactly what I need.

[@andreaburtondesign]

This is exactly what I needed!

[@mrdadecounty3056]

Awesome job by the way , followed basic step by step and it worked , going to try to change box and codes to real people's first name so it's recorded I'm making it for my wife's office they have like 10 employees so I would just add more users to box and script I'm guessing will try hopefully it works

[@luciasun4727]

wow very useful demo for a script beginner. thank you

[@karensmith1457]

Unbelievably helpful! Thank you so much for this video. I know nothing about coding, and was able to do this. Again, thank you.

[@bumpersmith]

This type of situation would be much more usable and informative if you would make a video that would use the NEW Google forms to have the user login then when verified send the user to another form or section on the same form that would carry the user's login info and post that info along with the time in or out to the spreadsheet. The procedure should also check to make sure if the user is checking out that there there was also a check in time before going forward or visa versa. This would make a GREAT video.

[@jimdarelpagtalunan8040]

Any reason why the User won't show up? It's not getting the value where the 'User' was stored. Once I click on the in/out button only the time/date and in/out is showing up, it does not show the user

[@Iknownothing896]

Thank you very much! Can't wait to get back to my laptop to set this up! Is exactly what I am looking for

[@erocketsbiz762]

Is there a way to hide and show the buttons? So if I haven't clocked in the clock out button is hidden and when I hit clock in, the clock in button is hidden and the clock out is now visible? Just to minimize user error. Thanks for the tuts!

[@kayshalandon846]

I loved this and found it to be very helpful. I was wondering how this can be used on a mobile device and not just on a desktop/Laptop?

[@TimothyJamesPittsburgh]

Unfortunately there's not a way (that I'm aware of) that allows you to use buttons / scripts in the Android or iOS app.

[@vinayaksudalai9114]

Excellent Concept!

[@malynearmentano7531]

This is really helpful! I tried it and now it works. However, my problem now is I do have a lot of users. Is it possible to manually input their own IDs then just click on the In and Out button? Thanks!

[@pierrelcane5525]

Hi, this is really helpful, i just want to ask though, i wanted to add a duration column, i tried adding one, however, due to the "getNextRow" function, it will add the data to the next row from where my duration formula is. i.e: the time stamp will populate A3, B3, and C3 after clicking on the button because on d2 (duration column) there is a formula. Hope this is clear, thanks!

[@futuresuperstar4life]

I desperately need to be able to add a second frozen title row that has "tabs" that will filter my table/column one (if it were dates) by weekly, pay period, monthly, yearly and all. Maybe have to dat ranges in a block at the bottom and a way to scroll through them to the next selection of dates. Is this at all possible?!?!

[@youngperspectivestudio7183]

This is really amazing 😍. I wasn't aware that spreadsheet could do this kind of magic. Thank you ♥️♥️♥️♥️♥️♥️♥️♥️♥️♥️

[@TimothyJamesPittsburgh]

You're welcome! Always glad to help.

[@davidandriantoparinussa7024]

Hi Timothy, thank you for sharing this. I found your video just today, March 9, 2022. I have a question. Is it possible to create a QR code that is connected to the in and out, so that employees can just scan the qr code. Thanks once again.

[@TimothyJamesPittsburgh]

If clicking a button generated a parameter or something that you could specify in a QR code, I think it would be possible - but I'm not sure there's a way to trigger a button click through a URL parameter. I guess my answer is, it might be possible but I am not sure how you'd do it.

[@creativegraphicsclients9328]

is there a way to have 2 different columns, one for date, one for time? and if i could even have one for day of the week, that would be great! thank you so much for an awesome tutorial!!!! :)

[@mishathecarman4231]

Hi Timothy, is there a way to call you or email if I have more questions regarding time clock ?

[@pasquotank-camdenemergency1982]

Could you show how to create this same concept except for multiple users that could vary each day. Basically some one could put in their name, email, and department and then hit sign in or sign out rather than having user 1 , 2, etc

[@christinepetit251]

Great video!! Would it also be possible not to use the actual time but the time from a timer that I integrated into my sheet?

[@TimothyJamesPittsburgh]

I imagine so - you should be able to use any value in the spreadsheet, if you can refer to it.

[@fleiteh]

Thank you for a great tutorial. Could you please provide a hint on how to modify the script to have only one in/out button which would alternate the clock in and out function? This way every 'out' click would be constrained to follow every 'in' click.

[@TimothyJamesPittsburgh]

Probably the best way to do this would be to count the number "in" entries and the number of "out" entries. If the number of "in" equals the number of "out" then you would call the punchIn function, and if they are not equal you'd call the punchOut function.

[@vik_g]

Thanks for all the great vids Timothy. Is there a way to create a userform or something else that could create separate login info for User 1 and User 2? That way, one person can't punch in on behalf of the other.

[@TimothyJamesPittsburgh]

Not simply.... there are ways you could do it, but it would be much more involved. You could use prompts to use a password of some sort, but that would have to be written into the Script Editor which wouldn't be very secure.

[@vik_g]

Thanks for the swift reply! This would probably require another database to pull from which stores all the user info and has separate protections probably?

[@TimothyJamesPittsburgh]

That'd probably be a better way to handle it.

[@fast-tranleicesterltd9641]

Hi Great tutorial Just wondering how to use on mobile device at the moment its looks like its not working on mobile phone

[@krewdude]

Great video! Is there a way to make it work on based on last column vs last row? I'm wanting to use a formula in another column but the current set up I can't put the formula in until after the time stamp.

[@bluemd78]

Thank you so much for this tutorial. I am very new to google sheets. I would like to have the in/out function display in column b/c after a new name is added in each row of column A. Is there a way to do this without having to click the active cell and then pressing the button? (I.e. If I'm clicked on A2 and press in it will time stamp in b2 and if I press out it will timestamp c2.)

[@kargil75]

Timothy, Just starting using Google sheet but your tutoral is the most usefull I have found for what I need : a Timer for a race I organize for friends. could you provide me the script for the most simple set up in a page 1 button Start & 1 button Stop or 1 button only START/Stop for a chronograph feature in one celular (I will then duplicate if needed). thanks in advance. My file is at your disposal if needed... But I guess you may have others things to do ... just in case. rgds

[@Nate-ix1mo]

I've been looking for this, thanks Timothy! Question, do you have a video of an efficient way for calculating time? I'm trying to add a formula on 4th column (=if(C3=Out,B3-B2,) but cant get it to auto filldown to next row with adjusted formula. =if(C5=Out,B5-B4,) etc. This method is then helpful to calculate time on a separate sheet using SUMIFS, but cant get it to work properly.

[@TimothyJamesPittsburgh]

Try this? kzbin.info/www/bejne/g6C3mpymgtmNlbc

[@ngokhaihd]

thank you sensei! you SAVED my day :) best wish to you

[@TimothyJamesPittsburgh]

Glad I could help!

[@ngokhaihd]

@@TimothyJamesPittsburgh By the way sensei, can you help me how to "clear content" for the last row by click "button" ? and keep going to top each row

[@laboratoriofotografiapuc6382]

Hi! Thanks very much for this video! Do you know how to calculate the difference between the input and output of each user on a certain day in this spreadsheet? For exemple: User 1, IN 7:00 AM, OUT 14:00 PM = total hours 7h. You did a video calculating time but IN/OUT are columns and I need it to calculate between lines. is that possible? Thanks!

[@TimothyJamesPittsburgh]

You can actually SUM all of the "Out" values and subtract the SUM of all of the "In" values and that will give you what you want (assuming an "In" matches every "Out"). I thought I had a video on this but I'm not able to find it right now :|

[@TimothyJamesPittsburgh]

Nevermind, found it: kzbin.info/www/bejne/g6C3mpymgtmNlbc

[@FeliciaMaureen]

Thanks, super helpful! could you link the 'in' button to a link? so that when clicking the punchIn it takes you to a different page ?

[@TimothyJamesPittsburgh]

I suspect you could do this, but I think it would be complicated. stackoverflow.com/questions/10744760/google-apps-script-to-open-a-url has an example but it's a lot of code for something simple.

[@kristajohnson9180]

How do I modify the code in order to automatically place all the "In" values in the C column and all the "Out" values in the D column so all the recorded times to be next to each other. So In values in C column and Out values in D column next to each other?

[@TimothyJamesPittsburgh]

This video might help with adding them up: kzbin.info/www/bejne/g6C3mpymgtmNlbc - if you want to put the "out" values beside their corresponding "in" values though, you have to find where they go first. The "in" values are pretty easy - you just add them to the first blank space. The "out" values are a bit harder - you'll need to use a for loop to find the first blank "out" space that matches the current user's name (which you can get from Cell I1) and fill in the time there.

[@raisamondal652]

Is there any way to get the names dynamically by clicking the User1 or User2 Button and then entering the In and Out time . Please help

[@greghaybittle_edu]

function punchIn() { var email = Session.getActiveUser().getEmail(); addRecord((email), new Date(), 'In',''); }

[@griffon72]

hi Timothy thanks a lot for that video, I ve made the same sheet as you explain and I ve add buttons in and out for break but I dont know how can I start it at the line 1 of J. cause it goes under the J that's ok but it goes at the last row of a, b, c. (like it start in the middle of the page) Its really new all this for me. Could you help me for that please?

[@ivelissecolondiaz8072]

This is a great video! I have a question, though; I want to use this for a Scout project and we will have probably 20 individuals helping. Would I need to make 20 different "user buttons" so when they come to the project we can clock them in and out, or there is a simpler way to do it?

[@TimothyJamesPittsburgh]

You could have them enter their name, or use a prompt (developers.google.com/apps-script/reference/base/prompt-response) to ask them for it.

[@stayfreekidd]

Hey Timothy, this works great. My only problem now is duration of time between time stamps. How to I get the duration without the date? Also, protection from editing is an issue as well. I set permissions already, but how to I give access to just "punch In / punch Out"?

[@mreighties2860]

Shannasar Mcneil....Select the column with the time-stamps then go to the toolbar and press 123 > Time and you should now only see the time and hopefully can now do your calculations.

[@TimothyJamesPittsburgh]

Take a look at this: developers.google.com/apps-script/reference/spreadsheet/protection - you could protect the sheet with scripts and protect the sheet using a trigger: developers.google.com/apps-script/guides/triggers/ As far as duration, you need 2 dates to keep track of the in / out - the duration is just the difference between two dates.

[@johndavidbogardus]

I need a bit of help. When I add any other text in the sheet it puts the next PunchIn date and time below that point. I have tried messing with the cells and code for about 4 hours now and I can't get it to work. Can you help me? This video and method otherwise has been exactly what I've been looking for. Thank you for putting the time in to help us lesser computer mortals!

[@TimothyJamesPittsburgh]

Ah, the code from this video finds the last row in the data. To adjust this, you'd have to change the getNextRow() function to insert a row and add the values there.

[@elmedinzejnelovic6007]

This is very helpful! Thank you

[@TimothyJamesPittsburgh]

You're so welcome!

[@amolshingne6807]

Sir, I want to transfer data from Sheet A to Sheet B by clicking Submit button. So pls help me to create a script. Pls help me on mail id plsss

[@eliperez2711]

Thanks so much bro! I have a quick question though. Is it possible for the buttons to work on phones, tablets, IPad's or any other hand held device? If it could be done it'd be so helpful to me! Could you help me?

[@TimothyJamesPittsburgh]

I don't think this approach will work on an iOS / Android App, unfortunately.

[@ProcvTube]

Amazing! Do u have more video about Google Scripts with Sheets, Docs etc?? Congrats!!

[@TimothyJamesPittsburgh]

I'll have more coming soon, hold tight.

[@TimothyJamesPittsburgh]

take a look at gr1.me/ipgs

[@mrdadecounty3056]

Question I'm new to coding if I want to set each person's real name I just have to replace user 1, user 2 in the script with there real names same as the buttons?

[@TimothyJamesPittsburgh]

Yes, user 1 / user 2 are arbitrary - these could be real names.

[@adamrideout-arkell7424]

Can I reference the text inside a drawing (a button I've created) with a script? I'm creating a bunch of sign-in buttons (around 100) and I don't want to write a function for each one, rather I'd like to write ONE function that changes the value of a cell to the text written inside the button. I could then assign that one function to every button. Make sense? Is this possible? In this demo you write out a function for each button, which for me will be quite laborious. Thanks in advance for any help or ideas...

[@TimothyJamesPittsburgh]

I think you can access the drawings, but I'm not sure there's any way to determine which button initiated a function call. You're better off having someone enter some text into a cell and then having the button act on that. If you do find a way, please share it here, I'm sure other people would benefit from the approach.

[@tonykempson4331]

Thanks very much. Great channel and excellent tutorials!

[@amctothemoon1139]

Hey Tim- I'm so glad I found your video! Unfortunately for me you are speaking another language and I don't get it. Could I hire you to help me? If not do you have any recommendations on where to find someone?

[@TimothyJamesPittsburgh]

I might be able to find you somebody; can you email me at the email address that's visible in the video?

[@angieziegelmann8658]

Thank you so very much!!!! I feel so empowered by this video! I am trying to make a time and expense tracking tool for our employees. I followed this to the letter, but how do I get the buttons where the assigned user can click them?

[@conniecast9386]

I love this. But I need to do just the log in and log out buttons. Each employee will have there own page. I tried adjusting the script but I could not. Could you help me?

[@TimothyJamesPittsburgh]

You'll need to remove the User buttons, and remove the first parameter from the addRecord function calls. Probably the simplest thing to do is just leave them in, but with a single user name.

[@TimothyJamesPittsburgh]

The other complication is that the last time I tried to share Script Editor files I found it to be not perfectly straightforward.

[@jamiekuntz7179]

Thanks a bunch for this useful tutorial!! Is there a way we could make a button to reset the row values? for example each week I want to wipe the chart and start over, but its inserting the entries farther down the page (not at the top).

[@TimothyJamesPittsburgh]

You'd want to create a new button, and a function that just sets each cell in each row in your sheet to a blank value. You could use a for-loop to do this.

[@rodneywhite315]

How do I update one specific cell with the most recent time stamp? For example: I ONLY want to know when he most recently clocked in, so cell C1 is Bobs name, and cell C2 is the latest time/date he clocked in. How do I get cell C2 to update rather than inserting a new row with values. I just want one row/cell to update the time stamp value.

[@TimothyJamesPittsburgh]

In punchIn / punchOut, just use setValue for that cell instead of addRecord. Otherwise it should be the same.

[@rodneywhite315]

@@TimothyJamesPittsburgh Are you referring to Line 30 and 34 within your code? You're help with this is very much appreciated. Thank yoU!

[@rodneywhite315]

@@TimothyJamesPittsburgh Can I email you a short video clip of my issue with the code. I am very close to figuring it out. I am just struggling with basic statements.

[@rudysoto65]

Would there be a way to add to the punchOut function so that it calculates the difference between in and out and puts the result in Column D, next to "Out"? Or would it be better to do that manually in sheets?

[@TimothyJamesPittsburgh]

I think you could do it either way, though the code for it is maybe a bit more straightforward than having some in code and some in Sheets formulas.

[@TimothyJamesPittsburgh]

To make it work, probably the easiest way would be to get a Date object for the 2 columns, use the getTime() method (see: www.w3schools.com/jsref/jsref_gettime.asp ) to subtract the in time (in milliseconds) from the out time (in milliseconds), then convert the difference to minutes (difference / 60000) or hours (difference / 3600000) depending on your needs.

[@joelyrodriguez958]

Thank for this great tutorial. I am trying to create a spreadsheet where I can include students ID, reason for the visit individual counseling, group ect and most importantly time in and out? Two, can I turn this data in graphs to present the amount of time spent in category using the same format as in this video? TIA

[@TimothyJamesPittsburgh]

Hi! You can tally hours using the instructions in this video: kzbin.info/www/bejne/g6C3mpymgtmNlbc - the other columns, you should be able to just add them.

[@joelyrodriguez958]

Timothy James Thank you!

[@dahl8744]

This is really great! However, instead of using two buttons I am trying to just use one button. But I can't figure out how to change the text of the drawing when it is clicked.So basically, when the START/IN button gets clicked it will change to STOP/OUT. Any suggestions?

[@TimothyJamesPittsburgh]

Unfortunately I haven't found a way to do this; I've looked around but haven't found a solution. As far as I know you can't manipulate these custom buttons.

[@dahl8744]

@@TimothyJamesPittsburgh No worries! Thank you for looking into it. Will also update this thread if I discover a solution.

[@brandywalker7144]

Can you please create a video to show us how to create a time tracker for students to scan their ID card with a barcode scanner and have their ID number show up as the first field and the second field log their time that they scanned their ID? (Without buttons) Please and thank you!

[@creativegraphicsclients9328]

Also, why is my time off by 7 hours? how do i fix that? i'm in Arizona, USA, and it's 11:40pm right now, but the time stamp says 6:40am :)

[@luqmanhakim8366]

I have a little question. .getLastRow is Returns the position of the last row that has content. how to put record results in certain range or column, for example in column "H" or etc and data set start in "7" Row. because I have any tables in sheet please can help me. warm reagrds

[@TimothyJamesPittsburgh]

I'm not sure I understand - the setValue function created in this video will just set values in whatever cell you specify, so you can call that with "H7" if you like.

[@sunsethelix4744]

So I made something similar to this but using data retrieved from a Google Form. Basically I collect someone's name, timestamp, and if they are checking in or out. It is to keep attendance for a club where you need a certain amount of hours in order to attend competition. How would I use my data to find how long they were there for each individual session and also keep track of their total hours over weeks and months?

[@TimothyJamesPittsburgh]

I think you'd have to build a function that retrieves the entire set of in / out for a user, then match in times to out times and calculate the difference. Because the Form will populate this row by row instead of within the same row (i.e., "in" will be 1 row and "out" will be another row instead of both going into the same row for an "in" time and "out" time), you'll need to do the work in a script.

[@curtissteinke9728]

Hi Tim anyway you can contact me for further help on this sheet? I love the idea, however I'd like to add a few things such as job name along with time punch in and out. Please let me know how to get ahold of you thanks!

[@TimothyJamesPittsburgh]

Hi! If you have specific questions, I can try to make a video to answer them, but I'm not taking on any freelance work at this time. Best of luck!

[@shifrafraylach6228]

Hello, I followed your guide and I'm having some issues. I'm hoping you can help me with them please. Firstly, when I am up to authorizing the script my computer doesn't allow me to do so since it says "this app wasn't verified by Google yet". Do you know what I can do to change that? Secondly, when it comes to saving the script it says, "missing formal Parameter". (line 22, file "Code") Is there any way you can help me with this? Thank you.

[@TimothyJamesPittsburgh]

On #1 - you need to authorize it, see: kzbin.info/www/bejne/nqTGlGWdgLWNbrs - there's an "Advanced" link that gets you through. On the second one, it sounds like there's a code error - hard to diagnose without seeing the code. I'd review and make sure you got all the syntax & symbols right.

[@ambergatti]

Thank you for putting this together however I am having an issue after I am assigning a script to the button. I am getting an error saying google has not authenticated the script and will not let me use it. It also says "spreadsheetapp" not defined. Help please!

[@TimothyJamesPittsburgh]

Ah - you probably need to authorize the script. Usually this would come up right away and you can sign in; you might get a notice in a pop-up window that says "This app isn't verified" - you'll need to click "Advanced" and proceed to the app there. That should get you into the authorization and you should be OK to run functions from there.

[@nurulizati4964]

is there any ways to lock users from clicking wrong name? like user1 cannot click on user2

[@TimothyJamesPittsburgh]

You may be able to use Session (see: developers.google.com/apps-script/reference/base/session) to retrieve details about the current user, and use Protection (see: support.google.com/docs/answer/1218656?co=GENIE.Platform%3DDesktop&hl=en) to lock / unlock the sheet each time, but I'm not 100% sure how you'd do this. I bet there's a solution there if you experiment a bit.

[@AJChitwood]

can I make the button reference a specific row? I want to have a list of say employees in column A on and have my buttons on sheet 2. So thay when an employee clicks their button and punches in/out it logs it on their row on sheet 1

[@TimothyJamesPittsburgh]

Not really, unfortunately - I don't know of a way to pass a parameter to a function through a button. As far as I know, you have to create a separate function for each.

[@Mharky87]

This is great! Tried to restrict the editing of cells a to c, but turns out that running the script will also be restricted. Is there any way that I can restrict cells for manual editing but only through the scripts? Thanks!

[@TheOligopeptide]

Hello! I have encountered the same problem and posted on a help forum and group...Did you find any solution to this?

[@TimothyJamesPittsburgh]

I think it can be done but you would need to activate / deactivate the protection using Apps Script. Take a look at this: developers.google.com/apps-script/reference/spreadsheet/protection

[@ammarsdaily8767]

your explanation it's very helpful.. but how to input name on button user 1 & 2.

[@TimothyJamesPittsburgh]

All you have to do is enter the actual name; unfortunately, as far as I know, you can't change it with Apps Script code. If the button already has an action assigned to it, right click it - you should be able to see the little 3-dot snowman menu, then you can select "Edit Text" from that.

[@gregoryheroldmarcelin6868]

The Video is Perfect, But I have a problem, Everytime I Go to the sheet to assign the script, he keep telling Me, > and I did exactly what yo did, cant find the Mistake

[@TimothyJamesPittsburgh]

Sounds like you might have a syntax error - it should be setValue instead of SetValue - capitalization is important.

[@a14266]

Thanks alot for all your effort.

[@stephentackett4064]

Do you have a tutorial to trigger a Slack message from a column? We want to click column (Completed) and trigger a Slack message that uses the first column ( Name Of Project ) then add a message like this..."(Name of Project ) is completed and uploaded."....Is this possible? Thanks.

[@TimothyJamesPittsburgh]

I don't, but it looks like it *can* be done: www.kutil.org/2016/02/integrate-google-apps-and-slack-with.html

[@stephentackett4064]

wow..I don't know where you found that. I'll give it shot. Thanks so much!

[@eugeneagcaoili9888]

what script/code will I use if I want to record the time in/out to another sheet instead of recording in the same sheet? thank you so much

[@TimothyJamesPittsburgh]

Instead of using SpreadsheetApp.getActiveSheet() you'd want to use SpreadsheetApp.getActiveSpreadsheet().getSheetByName("THE NAME OF THE TAB YOU WANT");

[@bobthegreat1781]

what would be a good formula to extract the time clocked in for each session, having multiple entries each day?

[@HenfreyMunene]

By using the REGEXTRACT function

[@TimothyJamesPittsburgh]

Maybe this is what you want? kzbin.info/www/bejne/g6C3mpymgtmNlbc

[@t.brianbair3154]

instead of User 1 when I click the Punch* button, it reads Spreadsheet. Any ideas? Anyone? It seems getValue is not working

[@Meg.EliteMarketing]

I love having a time stamp for our employee to clock in... is there a way to have google sheets do conditional formatting when they clock in after 9:05:00 AM? but before 12:00:00 pm (cause of half days) I thought it would be simple "greater thans" or even "timevalue" formulats but they are not working and after scouring the internet I cant get it to work!!

[@TimothyJamesPittsburgh]

Conditional formatting should do this without any need for code. You can learn a bit about how to apply it here: kzbin.info/www/bejne/fHTCZol6etaUapo - you should be able to use "Is Between" in a conditional formatting rule for that column.

[@garywhitehead1939]

Any chance I could get a copy of this spreadsheet to use as a starting point for the time tracking I want to do?

[@TimothyJamesPittsburgh]

The code & spreadsheet are linked in the description.

[@Lucas-dy4wy]

It helps me a lot! Thank you soo much!

[@ryanbelen2126]

This is so cool, was wondering if i could get the scripts used in this?so that i can just modify :)

[@TimothyJamesPittsburgh]

This should be it (or close): function setValue(cellName, value) { SpreadsheetApp.getActiveSpreadsheet().getRange(cellName).setValue(value); } function getValue(cellName) { return SpreadsheetApp.getActiveSpreadsheet().getRange(cellName).getValue(); } function getNextRow() { return SpreadsheetApp.getActiveSpreadsheet().getLastRow() + 1; } function setUser1() { setValue('I1', 'User 1'); } function setUser2() { setValue('I1', 'User 2'); } function addRecord(a, b, c) { var row = getNextRow(); setValue('A' + row, a); setValue('B' + row, b); setValue('C' + row, c); } function punchIn() { addRecord(getValue('I1'), new Date(), 'In'); } function punchOut() { addRecord(getValue('I1'), new Date(), 'Out'); }

[@Ci2ISerious]

Your video does what I'm trying to do but when I write it exactly as yours I get an error message saying: Missing } after function body. (line 10, file "Code") that I noticed on the script page after trying the buttons which gives error there's a a problem Script function setUser2 could not be found. Any advice?

[@TimothyJamesPittsburgh]

Hard to tell without seeing the code. It's probably a syntax error somewhere, it might not be related to the curly brace. Can you share your code?

[@Ci2ISerious]

Here's a copy function setValue(cellName, value) { SpreadsheetApp.getActiveSpreadsheet().getRange(cellName).setValue(value); } function getValue(cellName) { return SpreadsheetApp.getActiveSpreadsheet().getRange(cellName).getValue(); } function getNextRow() { return SpreadsheetApp.getActiveSpreadsheet().getLastRow() + 1; } function setUser1() { setValue('I1','User 1'); } function setUser2() { setValue('I1','User 2'); }

[@TimothyJamesPittsburgh]

Nothing appears to be wrong - do you have any stray characters / letters / symbols in your file before "function setValue" or after your last function?

[@creativegraphicsclients9328]

When i go to TOOLS - there is NO option to insert SCRIPT... Where did it go? Thank you!!

[@creativegraphicsclients9328]

for some reason i had to start a new document to have that available in the tools... so weird. if anyone has an answer to why it wasn't showing in my first google sheet, please post here, i'd like to know :)

[@jaymarkalvarez-phoenixaral4062]

Wow! Nice tutorial. Do you have tutorial also in coding google spreadsheet script editor?

[@TimothyJamesPittsburgh]

Many. Take a look at other videos on my channel - there is a long series on Apps Script in Google Sheets.

[@TheOligopeptide]

Can I modify the code in order to automatically place all the "In" values in the C column and all the "Out" values in the D column so all the recorded times to be next to each other. So In values in C colum and Out values in D column next to each other?

[@mreighties2860]

Bogdan Taut...I have added a reply to your post on Google+. I wish to say THANKS to Timothy James for an excellent script that has helped me learn a little about coding and therefore I was able to help Bogdan with his problem. I gave credit to Timothy and I take no credit for what I altered and/or added in the script. I'm by no means a script writer but am slowly learning thanks to the tutorials and videos from people like Timothy James.

[@karensmith1457]

James, is there a way that I can get that info as well?

[@aymenissa6053]

hey Mr James can i have a ready sheet for example . thanks ,,,best regards

[@TimothyJamesPittsburgh]

Spreadsheet: docs.google.com/spreadsheets/d/1ixdqHD378UAq944AI-rH6Cr2kZPy_Vk3MO-O51ujf-k/edit?usp=sharing Code: script.google.com/d/1Ws5rrNb6ZHYZnCc0ZQVKhFv95aLXvfYk7vvpf4OJoZFHnUzPc-jqi_bf/edit?usp=sharing

[@jolivercaandoy9552]

Hi I hope I can get the full list of the code you use. It is not really clear in the video.

[@TimothyJamesPittsburgh]

@@jolivercaandoy9552 It's available at the link in the comment above.

[@Masakazu-cy6vz]

does this work in touch screen sheets app on playstore?

[@lemei1095]

Everything worked perfect but it started at row 9 instead of row 2, does anyone know why?

[@TimothyJamesPittsburgh]

Extra data in your spreadsheet, maybe? Hard to say without seeing the spreadsheet or code, but I'd guess that it's due to some stray values in the spreadsheet.

[@redsprites5216]

Where did you find the information for writing that code? Is there a manual of some kind?

[@TimothyJamesPittsburgh]

There are official docs on this (see developers.google.com/apps-script/reference/spreadsheet/ ), or you can try this: docs.google.com/document/d/1opfKDZufX7nib-UU84PTqbf9t9UiHwSCGLHlLoFNRWY/edit?usp=sharing

[@Soldierguy97]

How can I set a formula within the spreadsheet without it skipping that row entirely... When I have getLastRow + 1 it thinks the function in another column is the last row but I want it to be part of it.

[@TimothyJamesPittsburgh]

The simplest would be to try putting it higher in the sheet in another column, or in another sheet? Otherwise you could use getLastRow() - 1 or getLastRow() - 2 and insert a row using insertRowBeforePosition ( developers.google.com/apps-script/reference/spreadsheet/sheet#insertrowbeforebeforeposition ).

[@Soldierguy97]

Alright, trying to subtract time in and time out which are in the same row but in two different columns to get time worked.. I can easily do it with a formula in the spreadsheet but u know what happens with that... and when I try to subtract "D" + row and "C" + row in script i get #NUM! with no flag telling me whats wrong, just the text...

[@neeksmif9288]

I'm trying to figure out a way to get rid of the need to calculate total hours worked. Could I create a formula that would total up the hours from the clock in and clock out times?

[@TimothyJamesPittsburgh]

Hi! Take a look at this video to get a bit more info about how to tally hours in Google Sheets: kzbin.info/www/bejne/g6C3mpymgtmNlbc

[@maurerkl]

Would you happen to know how to get the buttons to work on an iPad version of sheets?

[@TimothyJamesPittsburgh]

Unfortunately I haven't found a way to do this, so I'm not sure it's possible.

[@nicolasbonnet772]

I thanks for this tutorial. I tried to do nearly the same thing but with just a start and stop buttons to record time elapsed on a cell. any ideas ?

[@TimothyJamesPittsburgh]

The first button could record a start time in the spreadsheet, and the second could calculate the time between now and the time that was recorded.

[@shuleewong3281]

Hi Timothy, this tutorial works perfect and really helps a lot! Thank you for sharing this! I've done all these steps but was facing a problem now. Is it possible to create a dynamic Protect Range to protect every new string of data generated? I don't want to let anyone alter the time after punchin. Is this possible?

[@TimothyJamesPittsburgh]

Hi! You should be able to create a dynamic protection script using the Protection class in Apps Script. Take a look at this for reference: developers.google.com/apps-script/reference/spreadsheet/protection

[@ShakirTitleAsha]

Can you create form user and password for see sheet of multiple sheets.

[@TimothyJamesPittsburgh]

You want to check out "Protect Sheet" - support.google.com/docs/answer/1218656?co=GENIE.Platform%3DDesktop&hl=en

[@Grimbr13]

for some reason I get "reference error: spreadsheet not defined when trying to assign and test the "IN" button. not sure how to fix it. ive triple checked that everything matches

[@TimothyJamesPittsburgh]

Could be the code; compare it to the example here? github.com/timothyrjames/appsscript/blob/master/TimeTracker.gs

[@leekyu3628]

Is there any way I could make it where the times are calculated for each User for that week and starts over the next week?

[@TimothyJamesPittsburgh]

I think you'd just want to clear it out week by week, that's probably the simplest way.

[@VoTuuc]

When I debug with punchIN function, I got a error: ReferenceError: "getvalue" is not defined. (line 29, file "Code" This mean that the getValue function is not working. But when I debug with getValue function, I don't receive any message. Could you please tell me why? Thank you so much.

[@TimothyJamesPittsburgh]

Can you share your code? From just what you've said here, it might be a problem with case sensitivity.

[@VoTuuc]

Hi James, Thank you so much for your answer, your code and support. Actually, It was my mistake. It is case sensitivity matter as your mentioned. Your code is working well. Thank you so much. Duc.

[@AJChitwood]

function setValue(cellName, value) { SpreadsheetApp.getActiveSpreadsheet().getRange(cellName).setValue(value); } function getValue(cellName) { return SpreadsheetApp.getActiveSpreadsheet().getRange(cellName).getValue(); } function getNextRow() { return SpreadsheetApp.getActiveSpreadsheet().getLAstRow +1; } function setUser1() { setValue('I1', 'User 1'); } function setUser2() { setValue ('I1', 'User 2') } function addRecord(a, b, c) { var row = getNextRow(); setValue('A' + row, a); setValue('B' + row, b); setValue('C' + row, C); } function punchIn() { addRecord(getValue('I1'), new Date(), 'In'); } function punchOut() { addRecord(getValue('I1'), new Date(), 'Out');

[@shawnie422]

Hi Timothy I get a Script function setUser1 could not be found message, how can I correct what's not working?

[@TimothyJamesPittsburgh]

Hard to say, but it's likely that you don't have a function named *exactly* setUser1 - make sure it's the same name and the uppercase / lowercase letters match.

[@rudysoto65]

I know this is way late, but this was happening to me to. What I realized is that Script Editor does not autosave like Sheets does. Ctrl + S to save and then try it again. Worked like a champ for me.

[@abdalahpr]

هي تعمل علي الكمبيوتر فقط ولا تعمل في الجول لماذا؟

[@NateQDruen]

thank you for this edit: this video was very well explained and i feel like i did everything but i'm still getting this error code - "TypeError: Cannot find function getRange in object function getActiveSpreadsheet() {/* */}." i double checked for typos and saved/refreshed the pages multiple times but i still get that same error for all the buttons. Here's the code, thanks for all the help! Subbed and liked, very much appreciated. function setValue(cellName, value) { SpreadsheetApp.getActiveSpreadsheet.getRange(cellName).setValue(value); } function getValue(cellName) { return SpreadsheetApp.getActiveSpreadsheet.getRange(cellName).getValue(); } function getNextRow() { return SpreadsheetApp.getActiveSpreadsheet.getLastRow() + 1; } function setUser1() { setValue ('I1', 'User 1'); } function setUser2() { setValue ('I1', 'User 2'); } function addRecord (a, b, c) { var row = getNextRow(); setValue ('A' + row, a); setValue ('B' + row, b); setValue ('C' + row, c); } function punchIn() { addRecord(getValue('I1'), newDate(), 'In'); } function punchOut() { addRecord(getValue('I1'), newDate(), 'Out'); }

[@TimothyJamesPittsburgh]

Hi! This can be tricky sometimes. It looks like you're using getActiveSpreadsheet.getRange instead of getActiveSpreadsheet().getRange - the parentheses after getActiveSpreadsheet are important. Change that and I think you'll make some progress.

[@NateQDruen]

it worked! thank you so much, this is really gonna help me out. thanks again!

[@christinalim8268]

Hi may i know is there a function that can avoid the staff edit the clock in column after clock in. Cause it seem eveb after clock in they still able to edit the time.

[@TimothyJamesPittsburgh]

Christina Lim look into data protection. support.google.com/docs/answer/1218656

[@christinalim8268]

Hi, thanks for the reply. I did try the solution given. But when my staff try to lock in yes it work and was unable to edit. However the only issue is, the function ended on show the name of my staff. As for the clock in/out column it not working.

[@pratikghosh8239]

Can you kindly provide me with the coding for how to filter the attendance based on the Users??

[@TimothyJamesPittsburgh]

I would just use the built-in sort and filter functionality in Google Sheets to do this (though I might be misunderstanding your question).

[@jigneshdhadvi2072]

Hello my name is Jignesh. I made the same Time tracker sheet as you made And now I want to know that how can I calculate the total working hours .... Please help me out..

[@TimothyJamesPittsburgh]

I'm not sure what you need - does this help? kzbin.info/www/bejne/g6C3mpymgtmNlbc

[@Acook3]

My sheet works generally okay. However, instead of posting in the 'next row', it keeps placing it in the same row. Erasing the previous submission. Any ideas? See code below: function setValue(cellName, value) { SpreadsheetApp.getActiveSpreadsheet().getRange(cellName).setValue(value); } function getValue(cellName) { return SpreadsheetApp.getActiveSpreadsheet().getRange(cellName).getValue(); } function getNextRow() { return SpreadsheetApp.getActiveSpreadsheet().getLastRow() + 1; } function addTrade(a,b,c,d,e,f,g) { var row = getNextRow(); setValue('Meta4!B' + row, a); setValue('Meta4!C' + row, b); setValue('Meta4!D' + row, c); setValue('Meta4!E' + row, d); setValue('Meta4!F' + row, e); setValue('Meta4!G' + row, f); setValue('Meta4!H' + row, g); } function submitTrade() { addTrade(new Date(), getValue('Dashboard!N3'), getValue('Dashboard!N4'), getValue('Dashboard!N5'), getValue('Dashboard!N6'), getValue ('Dashboard!N7'), getValue('Dashboard!N8')); }

[@TimothyJamesPittsburgh]

Try changing this line: var row = getNextRow(); to var row = getNextRow() + 1; see if that works.

[@maurerkl]

Know any way to send the newest responses to the top row instead of the next row on the bottom???

[@TimothyJamesPittsburgh]

You should be able to use insertRowBefore as outlined here: developers.google.com/apps-script/reference/spreadsheet/sheet#insertRowBefore(Integer)

[@RTimo]

Can you do this in excel?

[@pujaunhale8877]

i want to define Date, Time, In, Out in separate columns. How to do it?

[@TimothyJamesPittsburgh]

If you go this way, you should be able to use basic formulas to calculate the difference between Time In and Time Out to figure out the time; your Apps Script code would be a little more complicated, though - it'd have to find the "in" entry and match it up with the "out" entry. Your "out" button would have to include a for loop to iterate through the rows and find the right place to update the "out" value.