In this post, I demonstrate how to Create an Employee Time Clock Web App on Google Sheets. This is a basic tool for employees to clock in and out during a workday.
How to Video:
Video Notes:
- Legacy Apps Script Editor Used in Video
- Apps Script (Script Editor) is now located under tab ‘Extensions’ instead of ‘Tools’ on Google Sheets
- For Further Details on Deploying a Web App Click Here
- Google Hosted Libraries
- Bootstrap Documentation
- Check out Time Tracker just on Google Sheets. Click Here
Code in Video:
function doGet(e) {
return HtmlService.createHtmlOutputFromFile('TimeTracker');
}
function getEmployees() {
var ss= SpreadsheetApp.getActiveSpreadsheet();
var employeeSheet = ss.getSheetByName("EMPLOYEES");
var getLastRow = employeeSheet.getLastRow();
return employeeSheet.getRange(2, 1, getLastRow - 1, 1).getValues();
}
function clockIn(employee) {
//DEFINE ALL ACTIVE SHEETS
var ss = SpreadsheetApp.getActiveSpreadsheet();
//DEFINE MAIN SHEET
var mainSheet = ss.getSheetByName("MAIN");
//LAST ROW ON MAIN SHEET
var lastRow = mainSheet.getLastRow();
//Define Return Variables
var return_date = '';
var error = 'SUCCESS';
var return_array = [];
for (var j = 2; j <= lastRow; j++)
{
// CHECK CLOCK IN
if(employee == mainSheet.getRange(j, 1).getValue() && mainSheet.getRange(j,3).getValue() == '')
{
error = 'Need to Clock Out before Clocking In';
return_array.push([error, return_date, employee]);
return return_array;
}
}
var new_date = new Date();
return_date = getDate(new_date);
// ADD CLOCK IN RECORD
mainSheet.getRange(lastRow+1,1).setValue(employee)
.setFontSize(12);
mainSheet.getRange(lastRow+1,2).setValue(new_date)
.setNumberFormat("MM/dd/yyyy hh:mm:ss A/P")
.setHorizontalAlignment("left")
.setFontSize(12);
return_array.push([error, return_date, employee]);
return return_array;
}
function addZero(i) {
if (i < 10) {
i = "0" + i;
}
return i;
}
function getDate(date_in)
{
var currentDate = date_in;
var currentMonth = currentDate.getMonth()+1;
var currentYear = currentDate.getFullYear();
var currentHours = (addZero(currentDate.getHours()) > 12) ? addZero(currentDate.getHours()) - 12 : addZero(currentDate.getHours());
var currentMinutes = addZero(currentDate.getMinutes());
var currentSeconds = addZero(currentDate.getSeconds());
var suffix = (addZero(currentDate.getHours()) >= 12)? 'PM' : 'AM';
var date = currentMonth.toString() + '/' + currentDate.getDate().toString() + '/' +
currentYear.toString() + ' ' + currentHours.toString() + ':' +
currentMinutes.toString() + ':' + currentSeconds.toString() + ' ' + suffix;
return date;
}
function clockOut(employee) {
//DEFINE ALL ACTIVE SHEETS
var ss = SpreadsheetApp.getActiveSpreadsheet();
//DEFINE MAIN SHEET
var mainSheet = ss.getSheetByName("MAIN");
//LAST ROW ON MAIN SHEET
var lastRow = mainSheet.getLastRow();
var foundRecord = false;
var new_date = new Date();
var return_date = getDate(new_date);
var error = 'SUCCESS';
var return_array = [];
for (var j = 2; j <= lastRow; j++)
{
// FIND CLOCK IN RECORD
if(employee == mainSheet.getRange(j, 1).getValue() && mainSheet.getRange(j,3).getValue() == '')
{
// UPDATE CLOCK IN RECORD
mainSheet.getRange(j,3)
.setValue(new_date)
.setNumberFormat("MM/dd/yyyy hh:mm:ss A/P")
.setHorizontalAlignment("left")
.setFontSize(12);
var totalTime = (mainSheet.getRange(j,3).getValue() - mainSheet.getRange(j,2).getValue()) /(60*60*1000);
mainSheet.getRange(j,4).setValue(totalTime.toFixed(2))
.setNumberFormat("#0.00")
.setHorizontalAlignment("left")
.setFontSize(12);
foundRecord = true;
}
}
// IF NO CLOCK IN RECORD
if(foundRecord == false)
{
return_array.push(['Need to Clock In First', '', employee]);
return return_array;
}
// CALL TOTAL HOURS
TotalHours();
return_array.push([error, return_date, employee]);
return return_array;
}
function TotalHours()
{
//DEFINE ALL ACTIVE SHEETS
var ss = SpreadsheetApp.getActiveSpreadsheet();
//DEFINE MAIN SHEET
var mainSheet = ss.getSheetByName("MAIN");
//LAST ROW ON MAIN SHEET
var lastRow = mainSheet.getLastRow();
//DEFINE ARRAY
var totals = [];
//LOOP THROUGH ALL RATES
for (var j = 2; j <= lastRow; j++)
{
var rate = mainSheet.getRange(j, 4).getValue();
var name = mainSheet.getRange(j, 1).getValue();
var foundRecord = false;
for(var i = 0; i < totals.length; i++)
{
//FOUND RECORD ADD TO TOTAL
if(name == totals[i][0] && rate != '')
{
totals[i][1] = totals[i][1] + rate;
foundRecord = true;
}
}
//ADD NEW RECORD, EXISTING RECORD NOT FOUND
if(foundRecord == false && rate != '')
{
totals.push([name, rate]);
}
}
//CLEAR DATA
mainSheet.getRange("F5:G1000").clear();
//DISPLAY TOTALS
for(var i = 0; i < totals.length; i++)
{
mainSheet.getRange(2+i,6).setValue(totals[i][0]).setFontSize(12);
mainSheet.getRange(2+i,7).setValue(totals[i][1]).setFontSize(12);
}
}<!DOCTYPE html>
<html>
<head>
<base target="_top">
<link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.5.2/css/bootstrap.min.css"
integrity="sha384-JcKb8q3iqJ61gNV9KGb8thSsNjpSL0n8PARn9HuZOnIxN0hoP+VmmDGMN5t9UJ0Z" crossorigin="anonymous">
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.5.1/jquery.min.js"></script>
<script>
$( document ).ready(function() {
getEmployees();
});
function getEmployees()
{
google.script.run.withSuccessHandler(function(ar)
{
var employeeSelect = document.getElementById("employee");
console.log(ar);
let option = document.createElement("option");
option.value = "";
option.text = "";
employeeSelect.appendChild(option);
ar.forEach(function(item, index)
{
let option = document.createElement("option");
var employee = item[0];
option.value = item[0];
option.text = item[0];
employeeSelect.appendChild(option);
});
}).getEmployees();
};
function ClockIn()
{
$('#message').html("");
var employee = document.getElementById("employee").value;
if(employee != '')
{
google.script.run.withSuccessHandler(function(ar)
{
console.log(ar);
ar.forEach(function(item, index)
{
if(item[0] == 'SUCCESS')
{
var message = item[2] + ' Clocked in at ' + item[1];
$('#message').html(message);
document.getElementById("message").className = "alert alert-primary";
}
else
{
var message = item[2] + ' ' + item[0];
$('#message').html(message);
document.getElementById("message").className = "alert alert-warning";
}
});
}).clockIn(employee);
}
}
function ClockOut()
{
$('#message').html("");
var employee = document.getElementById("employee").value;
if(employee != '')
{
google.script.run.withSuccessHandler(function(ar)
{
console.log(ar);
ar.forEach(function(item, index)
{
if(item[0] == 'SUCCESS')
{
var message = item[2] + ' Clocked out at ' + item[1];
$('#message').html(message);
document.getElementById("message").className = "alert alert-primary";
}
else
{
var message = item[2] + ' ' + item[0];
$('#message').html(message);
document.getElementById("message").className = "alert alert-warning";
}
});
}).clockOut(employee);
}
}
</script>
</head>
<body>
<div style="padding: 10px;" >
<h1>Time Tracker</h1><br>
<form>
<div class="form-row">
<div class="form-group col-md-4">
<label for="employee">Employee</label>
<select class="form-control" id="employee">
</select>
</div>
</div>
<div class="form-row">
<div class="form-group col-md-4">
<input type="button" value="Clock In" id="clockin" class="btn btn-primary" onclick="ClockIn()" />
<input type="button" value="Clock Out" id="clockout" class="btn btn-primary" onclick="ClockOut()" /><br><br>
<div class="alert alert-primary" role="alert" id="message">
</div>
</div>
</div>
</form>
</div>
</body>
</html>Related Posts
How to Create Time Log Web App on Google Sheets - In this post, I demonstrate how to create a Time Log Google Web App on Google Sheets.
How to Create an Inventory Tracker on Google Sheets - In this post, I demonstrate how to create an Inventory Tracking system on Googe Sheets that uses transactions of Receipt, Shipment, Transfer, and Adjustment sidebar forms.
How to Create Purchase Order Generator on Google Sheets - In this post, I demonstrate how to create a Purchase Order Generator using the Google Sheets Template and populate the data needed for a Purchase Order using Google Apps Script.
How to Create Invoice Generator on Google Sheets - In this post, I demonstrate how to create an Invoice Generator using the Google Sheets Template. In doing this use Google Apps Script to populate the needed Invoice Information to the Template
How to Create Cycle Count Inventory Software on Google Sheets - In this post, I demonstrate how to create a Cycle Count Inventory Software on Google Sheets using Google Apps Script. This can be used to accurately maintain your inventory
How to Create a Time Tracker Using Google Sheets - In this post, I demonstrate How to Put together a Time Tracker to Clock In and Out Multiple Users using Google Apps Script on Google Sheets.
How to Create Inventory Tracker Software Using Google Sheets - In this post, I demonstrate How to Create an Inventory Tracker Software Using Google Sheets. This uses parts and locations and does transactions like Receipts, Shipments, Transfers, and Inventory Adjustments. 
