Apps Script – Clocking in & out system

In this post, we’ll look at setting up a simple clocking in and out system using Google Forms and Sheets. This idea originally came from a real life situation where we had to implement a clocking in and out system to meet a change in Spanish law. With our teachers working both on-site and off-site, we had to come up with a flexible solution to be able to do this. The best things about it are that the teacher can do it wherever they are teaching via their mobile phone, it was quick and easy to implement and it’s free!

This post will introduce using Apps Script to change and update forms based on form submissions, plus adding formulas to sheets when a form is submitted.

How’s it work?

The employee fills in a Google Form on their mobile (see below). They enter their individual code and press “next”. Note, there is a reminder at the top, telling them when they last clocked in or out, which helps them do it correctly. This is updated via Apps Script.

They then tap “clock IN” or “clock OUT” and press “Submit” to register the time. There’s also a reminder of whether they need to clock in or out. Again, this is updated via Apps Script.

The form submission is then stored in the linked Sheet. When they clock out, the hours they worked is added to the sheet, via Apps Script.

---

SHEET & LINKED FORM

First, we create a new spreadsheet and create a new form (Tools > create a new form), which automatically links it to the spreadsheet.

Then in the Form, we set up the first page, which will require the employee to enter their individual code.

To get the Form to check the code is correct, click on the question, select “Data validation” from the 3 dots menu.

Then change the default options by clicking on the drop-down menus, to “Number” and “Equal to”. Then type in the number you want the employee to type in. You can also add some help text, in case they enter something wrong.

Make this a Required question to make sure they can’t progress without completing it.

Then we create a second section, with the following information and questions:

Now the form and sheet are ready, we add the two pieces of code.

1. Connected to the Sheet – to work out the hours worked
2. Connected to the Form – to update the form, reminding the employee when they last clocked in/out and whether they need to clock in or out next

---

SHEET CODE

From the Sheet, open the Apps Script Editor from the Extensions menu.

Here, I’ve renamed the script file “Hours Register”.

1. function onFormSubmit (){
2.  //Get spreadsheet, sheet, and form response row
3.  const ss = SpreadsheetApp.getActiveSpreadsheet();
4.  const sheet = ss.getActiveSheet();
5.  const lastRow = sheet.getLastRow();

Line 1: Set up the function. Here I’ve called it onFormSubmit.

Line 3: Get the active spreadsheet.

Line 4: We get the active sheet and it’s important to note, that this will be the sheet that has received the form submission. So, if we have a spreadsheet with multiple sheets connected to different forms, the code will know which sheet to get the latest information from.

Line 5: Then we get the last row, via getLastRow(), which is where the latest submission will be.

7. //Get clocking status (in or out)   
8.   const clockIO = sheet.getRange("D"+lastRow).getValue();

Line 8: Here, we want to get the clocking in or out status the employee submitted. To do this we get the range from column D and specify the row by using the variable lastRow we collected earlier. We combine the two to make a cell reference simply by using the plus sign, e.g. D15. Then we get the value in that cell.

10. //Get latest hours worked cell
11.   const hoursWorkedCell = sheet.getRange("E"+lastRow);

Line 11: Here we want to get the ‘hours worked’ cell in the latest row. Similar to above, but this time we get column E and add the lastRow variable. Here, we just want the cell reference and not the value, as we’re going to need this when we add a formula in that cell.

Here’s a reminder of what the sheet looks like and which columns correspond with what data.

13.  //Calculate hours worked (clocked out time - clocked in time)  
14.  if (clockIO === "clock OUT") {
15.    hoursWorkedCell.setFormulaR1C1('=R[0]C[-3]-R[-1]C[-3]');
16.    hoursWorkedCell.setNumberFormat("H:mm");
17.  }

Now, if the employee has clocked out, we will add a formula in the ‘hours worked’ column, which will get the time clocked out and subtract the time clocked in from it.

Line 14: First, we set up an if statement to check if the employe has clocked out. We do this by checking if the value stored in the variable finish, which we set up in line 9, is equal to “clock OUT”. If it does we run the code in lines 15 and 16.

Line 15: Here we will set a formula in the hours worked cell. We get the cell from the variable hoursWorked and use the setFormulaR1C1() method.

Instead of using normal cell references, we are going to use row and column references and we are referring to how many rows or columns we are away from the ‘hours worked’ cell. The formula that actually gets added to the sheet will be in the normal format, i.e. column letter and row number (e.g. B15-B14).

The timestamp data we want is in column B, which is 3 columns to the left of the ‘hours worked’ column (E). We will need the cell in that column from this row (clock out time) and from the row before (clocked in time). So, we put R[0] (same row) C[-3] (three columns to the left) minus R[-1] (1 row above) C[-3]. Note, the formula is within quote marks.

Line 16: Finally, I want the time format to be hours:minutes, e.g. 2:45. I get the hours worked cell and using the setNumberFormat() method, I state the format I want, i.e. “H:mm”.

19.  //Change code column format to 0000
20.  const latestCode = sheet.getRange("C" + lastRow);
21.  latestCode.setNumberFormat("0000");

Line 20: The codes we use, are connected to employee’s identification numbers, which sometimes can start with zeros. So, to ensure the code is displayed with all 4 digits, we get the last cell in column C.

Line 21: Then, like in Line 16, we use setNumberFormat() to state the format we want. In the brackets we add “0000”.

23.  //Centre columns
24.  const lastRowFormat = sheet.getRange("B" + lastRow + ":" + "E" + lastRow);
25.  lastRowFormat.setHorizontalAlignment("center");
26. }

Just to tidy things up, I also want to centrally align the information, except for the comments column.

Line 24: First, we get the last cells from column B to column E. Here, it’s combining column B with the last row number with a colon (:) and column E with the last row e.g. B15:E15.

Line 25: Then, we set the horizontal alignment to ‘center’ by using the setHorizontalAlignment() method.

Line 26: Close the function.

---

Setting up the trigger

To get the code to run automatically, we need to set up the onFormSubmit trigger. Click the triggers clock symbol on the left-hand side menu.

This will open the triggers dialogue box. Click on “Add Trigger”.

Change the Select event type from “On open” to “On form submit” and click Save.

To set up the trigger the first time, you will need to authorise it. Click on your account.

Click “Advanced”.

Click “Go to Hours Register (unsafe)”. – It is!

Click on “Allow”.

---

FORM CODE

In our context, our teachers have to clock in and out many times during the day, and they sometimes forget whether they last clocked in and out. This little code updates their form to help them remember when they last did it and what they need to do the next time.

It also provides an opportunity to show how forms can be updated automatically from form submissions.

Open the Script editor in the form via the 3 dots menu.

Let’s go through the code step-by-step:

1. function onFormSubmit() {
2.  //Get current form  
3.  const form = FormApp.getActiveForm();

Line 1: Call the function onFormSubmit ().

Line 3: Then we get the active form by using the FormApp class and getActiveForm() method.

5.  //Get all form responses received and the last one (row)
6.  const formResponses = form.getResponses();
7.  const responseLength = formResponses.length;
8.  const formResponse = formResponses[responseLength - 1];

We need to get the form responses that have been submitted.

Line 6: First, we get the form responses using the getResponses() method and store them in the variable formResponses.

Line 7: To get the latest response, we’re going to use the length property, which will tell us how many responses there are.

Line 8: Then, we want the last response in formResponses. We’ll use the responseLength to get the last position. We subtract one as this is an array. So, for example, if there are 10 responses, the last one will be array position [9].

10.  //Get 2nd response on form (clock in or clock out question)  
11.  const itemResponses = formResponse.getItemResponses();
12.  const itemResponse = itemResponses[1];
13.  const startStop = itemResponse.getResponse();

Above, we got the responses from different form submissions, and got the latest one and stored it in the variable formResponse. Now, we need to get the responses to the questions within that last form response. First, we want to get the clock in or clock out response, which is in the second question.

Line 11: Here, we get the responses to the questions in the last form response, using the getItemResponses() method, then store it in itemResponses.

Line 12: Now, let’s get the clock in / out question, which is question 2, i.e. in the itemResponses array at position 1.

Line 13: Then, we get the response in that question, using the getResponse() method and store it in the variable startStop. Now, we have whether the employee submitted clock in or clock out.

15.  //Get last time clocked in or out & change date & time format
16.  const timeDate1 = formResponse.getTimestamp();
17.  const timeZone = Session.getScriptTimeZone()
18.  const timeDate = Utilities.formatDate(timeDate1, timeZone, 'dd/MM/yy HH:mm');

I also want to add a message to state when the last time they clocked in or out was. So, I will need to get the timestamp from the last form response. As the timestamp format will include the full date and time and I only want to display a shortened date and time, we’ll have to format the date.

Line 16: I get the last form response, already stored in formResponse and use the getTimestamp() method to get the time it was submitted.

Line 17: To format the date, we need to know the time zone we’re working in. Here, we use Session.getScriptTimeZone() (more info here).

Line 18: Finally, we use the Utilities class and the formatDate() method to format the date the way we want it. There are 3 parts to it in the brackets: timestamp, timezone, date format we want.

Now, we need to change the text in the form based on whether they clocked in or out.

20.  //Change last time clock in/out message and update reminder what to do next
21.  if (startStop === "clock IN") {
22.    form.setDescription(`Last time you clocked in: ${timeDate}.  You need to clock OUT.`);
23.    const items = form.getItems();
24.    items[items.length - 3].setTitle("You need to clock OUT");
25.  }

Line 21: We set up an IF statement to see if they clocked in. This checks the value stored in startStop is the same as “clock IN”.

The small the under the title of the form is the description and it’s there where I want to add the text, telling the employee when they last clocked in or out.

Line 22: We get the active form using the variable form and change the description to what we want, using the setDescription() method. In between the back ticks, we use a mixture of text and the variable timeDate to get the text we want. We add the variable by using the ${variable} format (template literal).

I also want to add a reminder for what they need to do this time, on the clocking in and out page.

Line 23: So, first I get the number of items there are in the form. Items are questions and titles in the form.To do this, we use the getItems() method.

Line 24: We get the item we want by getting the length of the items variable, i.e. finding out how many items there are in total in the form. Then we subtract 3, as the title we want to change is 3 from the last one, as we can see below in the form. This gives us the position in the items variable we want to change. Then we use the setTitle() method to change the title to the text in the brackets.

27.  if (startStop === "clock OUT") {
28.    form.setDescription(`Last time you clocked out: ${timeDate}.  You need to clock IN.`);
29.    const items = form.getItems();
30.    items[items.length - 3].setTitle("You need to clock IN");
31.  }
32. }

Lines 27-32: We repeat the same to check if they have clocked out. Obviously, we need to change the wording in the text to correspond with this. Then we close the function in line 32.

As with the spreadsheet code, we need to add an onFormSubmit trigger, so that it works automatically when a form is submitted. Click on the clock symbol as before and set up the on Form Submit trigger.

---

Let’s just see what the employee sees on the form. At the top of the first page, they will see the message telling them the last time they clocked in or out and a little reminder as to what to do this time.

After entering their code, they go to the second page. Under their name, they see a reminder message telling them what to do. This is the title we set up above.

Let’s assume they clocked out. Then the form updates itself and the next time they open it, the text in the description has been updated.

---

This system is likely to be used with multiple users, so you will need to make copies of the form and link them to the same spreadsheet.

Here’s the link to make a copy of the Sheet.  You’ll then have a copy of the Sheet and the linked Form. You will find the code already in the sheet and form. To run it automatically, you will need to authorise both triggers.

---

Further reading

Apps Script: Form reference guide – Google’s reference on using Apps Script for Forms

Apps Script: Script services – Google’s reference on the script services in Apps Script, e.g. Utilities.formatDate

---