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.

[@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

[@andreaburtondesign]

This is exactly what I needed!

[@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!

[@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!

[@karensmith1457]

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

[@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.

[@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.

[@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

[@luciasun4727]

wow very useful demo for a script beginner. thank you

[@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.)

[@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.

[@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.

[@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

[@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.

[@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?

[@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?!?!

[@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.

[@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.

[@dansunderland9124]

how do I time in Google Sheets, I want to use a start time of 9:00 am and then use a formula to add hours to it, for instance my staff starts at 9:00 and I project they will finish in 6 hours how can I add these 6 hours to the 9:00 am start time to give me an estimated stopping time. 9:00am + 6 hours = 3:00 PM =N6+H8 when I do this it still gives me 9:00am?

[@TimothyJamesPittsburgh]

This isn't too hard, but it's not obvious. This video explains a bit more about how time works in Google Sheets - I think it will answer your question: kzbin.info/www/bejne/g6C3mpymgtmNlbc

[@joshpayne3318]

Hi, What you need to do is in one cell have 09:00:00 and assign that cell to be "time" and then another cell have 06:00:00 and assign that "Duration" then do = A+B and that will give you 15:00:00

[@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.

[@vinayaksudalai9114]

Excellent Concept!

[@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.

[@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.

[@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

[@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.

[@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

[@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

[@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.

[@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");

[@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.

[@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.

[@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

[@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!

[@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.

[@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.

[@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 :)

[@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?

[@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.

[@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.

[@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.

[@mishathecarman4231]

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

[@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.

[@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.

[@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.

[@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.

[@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.

[@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!!!! :)

[@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.

[@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

[@abdalahpr]

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

[@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.

[@Masakazu-cy6vz]

does this work in touch screen sheets app on playstore?

[@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?

[@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

[@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)

[@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

[@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!

[@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.

[@elmedinzejnelovic6007]

This is very helpful! Thank you

[@TimothyJamesPittsburgh]

You're so welcome!

[@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.

[@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.

[@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!

[@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

[@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.

[@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

[@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');

[@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!

[@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.

[@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.

[@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

[@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.

[@meganpowell4484]

Could you please help me do this for two different columns at two different times?

[@TimothyJamesPittsburgh]

I'm not sure what you're looking for - but stay tuned, I've got a whole series of videos on Google Sheets & Apps Script coming.

[@joshpayne3318]

How do you make the time punch time with the user and time on another sheet

[@TimothyJamesPittsburgh]

SpreadsheetApp.getSheetByName (see: developers.google.com/apps-script/reference/spreadsheet/spreadsheet#getsheetbynamename) should help you along.

[@christinalim8268]

After setting my user code i clicked on the button it show 'this app isnt verified'. Pkease advise

[@TimothyJamesPittsburgh]

Hi! That's just the Google security check asking permission for your code to run and modify your spreadsheets. Check out this video: kzbin.info?o=U&video_id=hscc2gKPZ8Y - it should show you how to get through it.

[@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

[@tonykempson4331]

Thanks very much. Great channel and excellent tutorials!

[@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

[@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.

[@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

[@cinderelladimaandal5885]

hi can I lock the column a - c but data can be input?

[@TimothyJamesPittsburgh]

Check out these instructions for locking particular ranges: docs.google.com/document/d/1pK7uY1r6pgleIMcX-C_9t9miErXn61zQ68WkdqnvBz8/edit

[@a14266]

Thanks alot for all your effort.

[@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).

[@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 :)

[@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

[@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.

[@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

[@Lucas-dy4wy]

It helps me a lot! Thank you soo much!

[@ashleyengel2354]

Can i make this so it inputs the data on a second sheet?

[@TimothyJamesPittsburgh]

Which data? I'm not sure what you're trying to do. Can you give me some more detail?

[@ashleyengel2354]

Timothy James instead of putting the times on the same sheet, can they be logged on a separate sheet in the same book?

[@TimothyJamesPittsburgh]

Oh yeah - that's not too hard. Instead of using SpreadsheetApp.getActiveSpreadsheet().getActiveSheet() try this instead: developers.google.com/apps-script/reference/spreadsheet/spreadsheet#getSheetByName(String)

[@millerinc8530]

i did what you did in your video and it is throwing a error could you maybe help

[@TimothyJamesPittsburgh]

hard to tell without seeing your code / the spreadsheet itself. Share it with the email address in the video and I'll take a look.

[@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?

any fashion I could make a clock that automatically updates its count every minute?

[@TimothyJamesPittsburgh]

Potentially yes but it's a bit more complicated - stay tuned, I'll have more programming tutorials for Google Sheets up soon.

[@MrBlackMautz]

Is it possible to play a sound when I press a button?

[@TimothyJamesPittsburgh]

Not in any simple way; you'd have to create a button, then connect to a function, then have that function create some HTML and show it, and **then** you could embed a sound file in that HTML, but it's a lot of work and a bit tricky. Apps Script in Google Sheets isn't really a full-featured development environment - it's really just to enhance what you can do in a Spreadsheet. I think you'd be better off to create something in HTML and JavaScript to play sounds.

[@t.brianbair3154]

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

[@craigmarylarsen3185]

Great vid. I followed step by step however range not found when I click punch in or out

[@gianmichaelvega1850]

Same

[@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.

[@sharayahapplegate2708]

can you create it so that the hours total on their own for each user?

[@TimothyJamesPittsburgh]

Sure! I'd use a different tab for a summary, and use the SUMIF function built-in to Google Sheets to tally the hours for each individual user. See: support.google.com/docs/answer/3093583?hl=en for more information on SUMIF.