Creating an Attendance System with Google Sheets and Apps Script
Maintaining accurate attendance records is essential for any educational institution or workplace. The manual process of collecting attendance data can be time-consuming and prone to errors. To address this, we can use technology to streamline the process of taking attendance.
In this blog post, we will discuss how to build an attendance system using Google Sheets and Apps Script. With this system, teachers or supervisors can use a mobile device to clock-in and clock-out, and the system will record the time, date, and location of each entry. The system will also calculate the total hours worked by each employee and generate a report.
To begin, we will create a Google Sheet to store the attendance data. We will name this sheet "MAIN". In this sheet, we will have the following columns: "Employee Name", "Clock-In Time", "Clock-Out Time", "Location", "Hours Worked", and "Date". We will also create another sheet called "Teachers" to store the names of all the employees.
Next, we will create a Google Apps Script file and write the following code:
function doGet() { return HtmlService.createTemplateFromFile('index').evaluate() .setTitle('Attendance System') .addMetaTag('viewport', 'width=device-width , initial-scale=1') .setXFrameOptionsMode(HtmlService.XFrameOptionsMode.ALLOWALL) }
This code creates a web app and sets the title to "Attendance System". It also sets the viewport and allows the app to be embedded in an iframe. Next, we will write a function to get all the employees' names from the "Teachers" sheet:
function getEmployees() { var ss= SpreadsheetApp.getActiveSpreadsheet(); var employeeSheet = ss.getSheetByName("Teachers"); var getLastRow = employeeSheet.getLastRow(); var data = employeeSheet.getRange(2, 1, getLastRow - 1, 1).getValues(); return data; }
This function gets the active spreadsheet, gets the "Teachers" sheet, and gets the range of employee names. It returns an array of names.
Now we will write two more functions to clock-in and clock-out:
function clockOut(employee,gps) {
var response = Maps.newGeocoder().setRegion('IND').setLanguage('en-IN').reverseGeocode(gps[0],gps[1]);
var location = response.results[0].formatted_address;
var ss = SpreadsheetApp.getActiveSpreadsheet();
var mainSheet = ss.getSheetByName("MAIN");
var lastRow = mainSheet.getLastRow();
var foundRecord = false;
var new_date = new Date();
var return_date = getDate(new_date);
var msg = 'SUCCESS';
var return_array = [];
for (var j = 2; j <= lastRow; j++){
if(employee == mainSheet.getRange(j,1).getValue() && mainSheet.getRange(j,3).getValue() == ''){
mainSheet.getRange(j,3)
.setValue(new_date)
.setNumberFormat("dd/MM/yyyy - HH:mm:ss")
.setHorizontalAlignment("left")
.setFontSize(10);
mainSheet.getRange(j,5).setValue(location)
.setFontSize(10);
var totalTime = (mainSheet.getRange(j,3).getValue() - mainSheet.getRange(j,2).getValue()) /(60*60*1000);
mainSheet.getRange(j,6).setValue(totalTime.toFixed(2))
.setNumberFormat("#0.00")
.setHorizontalAlignment("left")
.setFontSize(12);
foundRecord = true;
}}
if(foundRecord == false){
return_array.push(['<br>Sorry, you have not ClockIn yet.', '', employee]);
return return_array;}
TotalHours();
return_array.push([msg, return_date, employee]);
return return_array;}
function TotalHours(){
var ss = SpreadsheetApp.getActiveSpreadsheet();
var mainSheet = ss.getSheetByName("MAIN");
var lastRow = mainSheet.getLastRow();
var totals = [];
for (var j = 2; j <= lastRow; j++){
var rate = mainSheet.getRange(j, 6).getValue();
var name = mainSheet.getRange(j, 1).getValue();
var foundRecord = false;
for(var i = 0; i < totals.length; i++){
if(name == totals[i][0] && rate != ''){
totals[i][1] = totals[i][1] + rate;
foundRecord = true;}}
if(foundRecord == false && rate != ''){
totals.push([name, rate]);
}}
mainSheet.getRange("H2:I").clear();
for(var i = 0; i < totals.length; i++){
mainSheet.getRange(2+i,7).setValue(totals[i][0]).setFontSize(12);
mainSheet.getRange(2+i,8).setValue(totals[i][1]).setFontSize(12);
}
}
function addZero(i){
if (i < 10) {
i = "0" + i;
}
return i;
}
The clockIn() and clockOut() functions are designed to allow teachers to clock in and clock out, and the TotalHours() function calculates the total hours worked by each teacher.
Let's go over the code in more detail:
The getEmployees() function retrieves a list of teachers from the "Teachers" sheet in the Google Sheets spreadsheet. It uses the getSheetByName() method to get a reference to the sheet, and then uses the getLastRow() method to get the last row in the sheet. It then uses the getRange() method to get a range that excludes the header row (which is assumed to be in row 1) and includes all rows up to the last row. Finally, it uses the getValues() method to get the values in the range and returns them as an array.
The clockIn() function is called when a teacher wants to clock in. It takes two parameters: employee (the name of the teacher) and gps (an array containing the latitude and longitude of the teacher's location). It uses the Maps.newGeocoder() method to get a formatted address based on the latitude and longitude of the teacher's location. It then gets a reference to the "MAIN" sheet in the spreadsheet using SpreadsheetApp.getActiveSpreadsheet().getSheetByName("MAIN"), and finds the last row in the sheet using getLastRow(). It then creates a new Date object to get the current date and time, and formats it using the getDate() function. If the teacher has not clocked out from their previous shift, the function returns an error message. Otherwise, it adds a new row to the "MAIN" sheet with the teacher's name, clock-in time, and location, and returns a success message along with the clock-in time, formatted using getDate(), and the teacher's name.
The clockOut() function is called when a teacher wants to clock out. It takes two parameters: employee (the name of the teacher) and gps (an array containing the latitude and longitude of the teacher's location). It uses the Maps.newGeocoder() method to get a formatted address based on the latitude and longitude of the teacher's location. It then gets a reference to the "MAIN" sheet in the spreadsheet using SpreadsheetApp.getActiveSpreadsheet().getSheetByName("MAIN"), and finds the last row in the sheet using getLastRow(). It then loops through each row in the sheet to find the row that corresponds to the current teacher's clock-in time. If the teacher has not clocked in yet, the function returns an error message. Otherwise, it updates the row with the teacher's clock-out time, calculates the total hours worked by the teacher, and updates the "TOTALS" sheet with the total hours worked by each teacher. Finally, it returns a success message along with the clock-out time, formatted using getDate(), and the teacher's name.
The TotalHours() function calculates the total hours worked by each teacher and updates the "TOTALS" sheet with the results. It starts by getting a reference to the "MAIN" sheet in the spreadsheet using SpreadsheetApp.getActiveSpreadsheet().getSheetByName("MAIN"), and finding the last row in the sheet using getLastRow(). It then loops through each row in the sheet and calculates the total hours worked by each teacher. It stores the results in an array called totals. Finally, it updates the "TOTALS" sheet with the results.
The addZero() function is a helper function that adds a leading zero to a number if it is less than 10. It is used to format the time components.
Next, we will write a function to get time:
function getDate(return_array){
var currentDate = return_array;
var currentMonth = currentDate.getMonth()+1;
var currentYear = currentDate.getFullYear()+0;
var currentHours = currentDate.getHours();
var currentMinutes = addZero(currentDate.getMinutes());
var currentSeconds = addZero(currentDate.getSeconds());
var dateOutput = 'date ' + currentDate.getDate()+ '/' + currentMonth.toString().toString() + '/' +
currentYear.toString() + ' ' + currentHours.toString() + ':' +
currentMinutes.toString() + ':' + currentSeconds.toString() + ' .';
return dateOutput;
}
The getDate function is a helper function used to format the current date and time to a specific string format that can be used in the attendance sheet. It takes the current date as input, and then formats it to the desired string format using the addZero function to ensure that minutes and seconds are always two digits long.
Finally, the TotalHours function is called at the end of the clockOut function to update the total number of hours worked by each employee on the MAIN sheet. It retrieves the name and total hours worked for each employee from the MAIN sheet, and then summarizes this information in the H and I columns. It first clears the contents of the H and I columns, and then iterates over the data in the MAIN sheet to update the totals for each employee. If the name of an employee is already in the totals array, it adds the total hours worked to the existing value for that employee. If the name of an employee is not in the totals array, it adds a new entry for that employee. Finally, it writes the totals for each employee to the H and I columns in the MAIN sheet.
In conclusion, the code provided is a simple attendance system that allows employees to clock in and out using a web interface, and automatically calculates the total hours worked by each employee. It makes use of several built-in Google Apps Script services, including the SpreadsheetApp service for accessing and modifying spreadsheets, the HtmlService service for creating and serving web pages, and the Maps service for geocoding GPS coordinates. The code demonstrates how to use these services together to create a functional web application that can be used to track employee attendance.
index.html
<!DOCTYPE html>
<html>
<head>
<base target="_top">
<link href="https://cdn.jsdelivr.net/npm/bootstrap@5.1.3/dist/css/bootstrap.min.css" rel="stylesheet">
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.5.1/jquery.min.js"></script>
<link href="https://cdnjs.cloudflare.com/ajax/libs/select2/4.0.7/css/select2.min.css" rel="stylesheet" />
<script src="https://code.jquery.com/jquery-3.4.1.js" integrity="sha256-WpOohJOqMqqyKL9FccASB9O0KwACQJpFTUBLTYOVvVU=" crossorigin="anonymous"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/select2/4.0.7/js/select2.min.js"></script>
<style>
@font-face {
font-family: 'Digital dream Fat';
src: url('https://semicon.github.io/fonts/DigitaldreamFat.woff2') format('woff2'),
url('https://semicon.github.io/fonts/DigitaldreamFat.woff') format('woff');
font-weight: normal;
font-style: normal;
font-display: swap;}
@import url('https://fonts.googleapis.com/css2?family=K2D&family=Kanit&family=Sriracha&display=swap');
*{
margin: 0px;
padding: 0px;
box-sizing: border-box;
font-family: 'K2D', sans-serif;}
body{
font-size: 1.05rem;
min-height:100vh;
display: grid;
place-items: center;
background-color: #92a8d1;
background: url("https://semicon.github.io/img/bgTimeTracker.jpg") no-repeat center center fixed;
-webkit-background-size: cover;
-moz-background-size: cover;
-o-background-size: cover;
background-size: cover;}
.msgBg {
background-color: transparent;
}
.wrapper{
background: #dff;
width: 350px;
padding: 20px;
border: 1px solid #999;
border-radius: 5px;
box-shadow: 0px 15px 20px rgba(0,0,0,0.1);
}
/** #### digital clock #### **/
.clock {
font-family: Digital dream Fat;
font-size: 28px;
color: cyan;
letter-spacing: 3px;
text-shadow: 1px 1px 2px teal, 0 0 25px green, 0 0 5px darkturquoise;
min-width:98%;
background: #444;
padding:5px 0px 5px 10px;
display: inline-block;
border: 3px solid #ccc;
border-radius: 2px;
outline-style: solid;
outline-color: #999;
}
</style>
</head>
<!-- ******* Time Tracker HTML Body *********** -->
<body>
<div class="wrapper text-center" style="max-width:350px">
<div class="site-logo text-center"></div>
<h3>Time Clock:</h3>
<span id="MyClockDisplay" class="clock mt-3" onload="showTime()"></span>
<form id="myForm">
<div class="row mt-4">
<div class="form-group col">
<label for="employee">Select Teacher</label>
<select class="form-control my-auto" id="employee">
</select>
</div>
</div>
<div class="row mt-4">
<div class="form-group col">
<input type="button" value="ClockIn" id="clockin" class="btn btn-success" onclick="ClockIn()" />
<input type="button" value="ClockOut" id="clockout" class="btn btn-warning" onclick="ClockOut()" />
</div>
</div>
<div class="row mt-4">
<div class="form-group col">
<div class="alert" role="alert" id="message"></div>
</div>
</div>
</form>
</div>
<script >
$(document ).ready(function() {
document.getElementById('message').className = 'alert msgBg';
getEmployees()
});
function getEmployees(){
google.script.run.withSuccessHandler(function(ar){
var employeeSelect = document.getElementById("employee");
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();
}
/** ******* Clock In *********** **/
async function ClockIn(){
var gps = await getlocation();
var employee = document.getElementById("employee").value;
if(employee != ''){
$('#message').html("<span class='spinner-border spinner-border-sm text-primary'></span> Please wait...!");
google.script.run.withSuccessHandler(function(ar){
ar.forEach(function(item, index){
if(item[0] == 'SUCCESS'){
var message = item[2] + '<br> Entry ' + item[1];
$('#message').html(message);
document.getElementById("message").className = "alert alert-primary";
clearForm();
}else{
var message = item[2] + ' ' + item[0];
$('#message').html(message);
document.getElementById("message").className = "alert alert-warning";
clearForm();}});
}).clockIn(employee,gps);
}else{
$('#message').html('Please select a teacher...!');
document.getElementById('message').className = 'alert alert-warning text-danger';
clearForm();}}
async function ClockOut(){
var gps = await getlocation();
var employee = document.getElementById("employee").value;
if(employee != ''){
$('#message').html("<span class='spinner-border spinner-border-sm text-warning'></span> Please wait...!");
google.script.run.withSuccessHandler(function(ar){
ar.forEach(function(item, index){
if(item[0] == 'SUCCESS'){
var message = item[2] + '<br> Leavig ' + item[1];
$('#message').html(message);
document.getElementById("message").className = "alert alert-primary";
clearForm();
}else{
var message = item[2] + ' ' + item[0];
$('#message').html(message);
document.getElementById("message").className = "alert alert-warning";
clearForm();}});
}).clockOut(employee,gps);
}else{
$('#message').html("Please select a teacher...!");
document.getElementById("message").className = "alert alert-warning text-danger";
clearForm();}}
function getPosition() {
return new Promise((res, rej) => {
navigator.geolocation.getCurrentPosition(res, rej)
});}
async function getlocation() {
var data = await getPosition()
var lat=data.coords.latitude
var lng=data.coords.longitude
return [lat,lng];}
function clearForm(){
setTimeout(function(){
document.getElementById('message').innerText= owner;
document.getElementById("message").className = "alert msgBg";
document.getElementById("myForm").reset();
}, 3000);
}
function showTime(){
var date = new Date();
var h = date.getHours(); // 0 - 23
var m = date.getMinutes(); // 0 - 59
var s = date.getSeconds(); // 0 - 59
var dot =document.textContent = '.';
if(s%2 == 1){
dot = document.textContent = '.';
}else{
dot = document.textContent = '\xa0';
}
h = h < 10 ? "0" + h : h;
m = m < 10 ? "0" + m : m;
s = s < 10 ? "0" + s : s;
var time = h + ":" + m + ":" + s + '' +dot;
document.getElementById("MyClockDisplay").innerText = time;
document.getElementById("MyClockDisplay").textContent = time;
setTimeout(showTime, 1000);}
showTime();
</script>
<script type="text/javascript">
$(document).ready(function() {
$('#employee').select2();
});
</script>
</body>
</html>
To use make a copy of sheet - Copy now
For pro version whatsapp : +91 9801740685