Elevate Attendance Tracking with Apps Script – Your Ultimate Tool for Seamless Management! 2024

Are you looking for an efficient way to track attendance, whether it’s for your school, business, or any other group? Well, you’re in luck because we have a solution for you! In this article, we’ll guide you through building an attendance tracker using Google Sheets and Apps Script, and we’ll even share the source code with you.

Isahayata.com – Harness the Power of Ultimate Tools for PDF, Image, Text, and Calculators at Your Fingertips

Introduction

Attendance tracking made smarter and more efficient – that’s the goal of this guide. We’ll walk you through the  step-by-step process of developing a dynamic Attendance Tracking Web App with a Geo-Fencing feature, all powered by Google Apps Script. With this solution, you’ll not only streamline attendance management  but also ensure location-based accuracy for your organization’s needs.

Looking to simplify attendance tracking? Explore our Apps Script Google Sheets project, an efficient attendance tracker app. Access the source code for this Google Apps Script, empowering you to create a web-based attendance app effortlessly. Our Google Sheets-based attendance tracker is perfect for various projects, allowing you to automate attendance management, generate insightful reports, and enhance productivity. Streamline your organization’s tracking process with our Google Script solution today!

• Dangerous AI Tools
• Telegram Likely to Banned In India?
• Artificial Intelligence vs Human Intelligence
• Can I use ChatGPT to write articles for my website?
• How to Delete Row Based on Cell Value in Google Apps Script

Let’s build- below are following steps to build the above snapshot web-app using apps script !!

Step 1: Set Up Your Google Sheet

Create a New Google Sheet: Start by opening Google Drive and clicking on “+ New” > “Google Sheets” to create a new spreadsheet.

Design Your Sheet: Organize columns for attendance-related data, such as “Employee list” and Employee attendance record.

Step 2: Access Google Apps Script

Open Script Editor: Inside your Google Sheet, go to “Extensions” > “Apps Script” to open the Google Apps Script editor.

Clear Default Code: Clean the editor by removing any default code present.

Step 3 : Adding Code.gs and HTML code in apps script section

Just copy and paste the below code and save it. or do the changes as per your requirement and save

//Code.gs

function attendance_web_app() {
  var htmlOutput = HtmlService.createHtmlOutputFromFile('index')
      .setWidth(700)
      .setHeight(750);
  return htmlOutput;
}


function getEmployeeNames(searchTerm) {
  var sheet = SpreadsheetApp.openById('sheet Id').getSheetByName('sheet name');
  var data = sheet.getRange(2, 3, sheet.getLastRow() - 1, 3).getValues().flat();
  var filteredData = data.filter(function(name) {
    return name.toLowerCase().includes(searchTerm.toLowerCase());
  });
  
  if (filteredData.length === 0) {
    return ['Employee not found'];
  }
  
  return filteredData;
}

function markAttendance(employeeName, punchType, comment) {
  var sheet = SpreadsheetApp.openById('sheet id').getSheetByName('sheet name');
  var lastRow = sheet.getLastRow();
  
  var currentTime = new Date(); // Use the current local date and time
  var formattedTime = currentTime.toLocaleString(); // Format as a string
  
  if (punchType === 'in') {
    sheet.getRange(lastRow + 1, 1).setValue('Punch In');
    sheet.getRange(lastRow + 1, 2).setValue(employeeName);
    sheet.getRange(lastRow + 1, 3).setValue(formattedTime);
    sheet.getRange(lastRow + 1, 4).setValue(comment);
    sheet.getRange(lastRow + 1, 5).setValue('');
    return 'Punch In successful at ' + formattedTime;
  } else if (punchType === 'out') {
    var lastPunchType = sheet.getRange(lastRow, 1).getValue();
    
    if (lastPunchType !== 'Punch In') {
      return 'Cannot Punch Out. Please Punch In first.';
    }
    
    var punchInTime = sheet.getRange(lastRow, 3).getValue();
    var punchInDate = new Date(punchInTime);
    var punchOutDate = currentTime; // Use the current local date and time
    var timeDiff = (punchOutDate - punchInDate) / (1000 * 60 * 60); // hours
    
    sheet.getRange(lastRow, 5).setValue(punchOutDate.toLocaleString());
    sheet.getRange(lastRow, 6).setValue(comment);
    
    if (timeDiff < 9) {
      return 'Punch Out successful at ' + formattedTime + '. You punched out before 9 hours. Comment recorded.';
    } else {
      return 'Punch Out successful at ' + formattedTime + '. Comment recorded.';
    }
  }
}

In HTML Section :  Just copy and paste the below code and save it. or do the changes as per your requirement and save

//index.html

<!DOCTYPE html>
<html>
<head>
    <base target="_top">
    <style>
        .attendance-box {
            width: 550px;
            margin: 0 auto;
            padding: 20px;
            background-color: #f8f8f8;
            border: 1px solid #ccc;
            border-radius: 5px;
        }
        
        .logo {
            text-align: center;
            margin-bottom: 20px;
        }
        
        .logo img {
            max-width: 100px;
            max-height: 100px;
        }
        
        .company-name {
            text-align: center;
            font-weight: bold;
            margin-bottom: 10px;
            font-size: 30px;
        }
        
        .description {
            margin-top: 20px;
            text-align: center;
            color: #666;
        }
        
        label {
            font-weight: bold;
        }
        
        select, input, button {
            padding: 8px;
            margin: 5px 0;
            width: 100%;
        }
        
        button {
            background-color: #333;
            color: #fff;
            border: none;
            cursor: pointer;
        }
        
        .comment-box {
            display: none;
        }
        
        #status {
            margin-top: 10px;
            color: green;
        }
        
        .error-msg {
            color: red;
        }
    </style>
</head>
<body>
    <div class="attendance-box">
        <div class="logo">
            <img src="https://isahayata.com/wp-content/uploads/2023/07/PNG-logo.png" alt="Company Logo">
        </div>
        
        <div class="company-name">
            ISAHAYATA.COM
        </div>
        
        <h2>Attendance System</h2>
        
        <label for="searchEmployee">Search Employee:</label>
        <input type="text" id="searchEmployee">
        <button id="searchButton" onclick="populateEmployeeNames()">Search</button>
        <p class="error-msg" id="searchError"></p>
        
        <label for="employeeName">Select Employee:</label>
        <select id="employeeName"></select>
        
        <button id="punchInButton" onclick="showCommentBox('in')">Punch In</button>
        <button id="punchOutButton" onclick="showCommentBox('out')">Punch Out</button>
        
        <div class="comment-box">
            <label for="comment">Comment:</label>
            <input type="text" id="comment">
            <button onclick="markAttendance()">Submit</button>
        </div>
        
        <p id="status"></p>
        
        <div class="description">
            We kindly request your punctual attendance; lateness is to be avoided. Thank you for your understanding.
        </div>
    </div>
    
    <script>
        var officeCoordinates = {
            latitude: 28.5208666, // enter your yours latitude here
            longitude: 77.16866600  //enter your yours  longitude here
        };
        var currentLocation = null;

        function populateEmployeeNames() {
            var searchTerm = document.getElementById("searchEmployee").value;
            google.script.run.withSuccessHandler(updateEmployeeNames).getEmployeeNames(searchTerm);
        }
        
        function updateEmployeeNames(names) {
            var employeeNameSelect = document.getElementById("employeeName");
            employeeNameSelect.innerHTML = "";
            
            if (names[0] !== 'Employee not found') {
                document.getElementById('searchError').textContent = '';
                
                for (var i = 0; i < names.length; i++) {
                    var option = document.createElement("option");
                    option.text = names[i];
                    employeeNameSelect.appendChild(option);
                }
            } else {
                document.getElementById('searchError').textContent = 'Employee not found';
            }
        }
        
        function showCommentBox(punchType) {
            var commentBox = document.querySelector('.comment-box');
            var punchInButton = document.getElementById('punchInButton');
            var punchOutButton = document.getElementById('punchOutButton');
            
            if (punchType === 'in') {
                punchInButton.disabled = true;
                punchOutButton.disabled = false;
            } else {
                punchOutButton.disabled = true;
                punchInButton.disabled = false;
            }
            
            commentBox.style.display = 'block';

            if (navigator.geolocation) {
                navigator.geolocation.getCurrentPosition(
                    function(position) {
                        currentLocation = {
                            latitude: position.coords.latitude,
                            longitude: position.coords.longitude
                        };
                    },
                    function(error) {
                        console.error('Error getting location:', error);
                    }
                );
            } else {
                console.error('Geolocation is not supported.');
            }
        }
        
        function markAttendance() {
            if (!currentLocation) {
                console.error('Current location is not available.');
                return;
            }

            var employeeName = document.getElementById("employeeName").value;
            var comment = document.getElementById("comment").value;
            var punchInButton = document.getElementById('punchInButton');
            var punchOutButton = document.getElementById('punchOutButton');
            
            if (comment === '') {
                document.getElementById("status").textContent = "Comment is mandatory";
                return;
            }
            
            var punchType = punchInButton.disabled ? 'in' : 'out';
            var isWithinRadius = calculateDistance(currentLocation.latitude, currentLocation.longitude, officeCoordinates.latitude, officeCoordinates.longitude);

            if (!isWithinRadius) {
                document.getElementById("status").textContent = "You are not within the allowed range for punching.";
                return;
            }

            google.script.run.withSuccessHandler(updateStatus).markAttendance(employeeName, punchType, comment);
        }

        function calculateDistance(lat1, lon1, lat2, lon2) {
            var earthRadius = 6371000; // Radius of the Earth in meters
            var dLat = degToRad(lat2 - lat1);
            var dLon = degToRad(lon2 - lon1);
            var a = Math.sin(dLat / 2) * Math.sin(dLat / 2) +
                    Math.cos(degToRad(lat1)) * Math.cos(degToRad(lat2)) *
                    Math.sin(dLon / 2) * Math.sin(dLon / 2);
            var c = 2 * Math.atan2(Math.sqrt(a), Math.sqrt(1 - a));
            var distance = earthRadius * c;

            return distance <= 100; // Check if the distance is within 100 meters
        }

        function degToRad(deg) {
            return deg * (Math.PI / 180);
        }

        function updateStatus(message) {
            document.getElementById("status").textContent = message;
            document.querySelector('.comment-box').style.display = 'none';
            document.getElementById('comment').value = ''; // Clear the comment box after punch
            document.getElementById('punchInButton').disabled = false;
            document.getElementById('punchOutButton').disabled = false;
        }
    </script>
</body>
</html>

Step 4: Deploy the Web App

1. Publish the Web App: In the Apps Script editor, go to “Deploy” > “New Deployment.”
2. Select type : In the select type setting button select “web app”
3. Configure Deployment: Choose the access level (e.g., “Anyone, even anonymous”) and set up other options as needed. Click “Deploy.”
4. Authorization: If prompted, grant necessary permissions for the script to access your Google Sheets data.
5. Copy Web App URL: After deployment, copy the provided web app URL for accessing your Attendance Tracking Web App.

Step 5: Interact with the Geo-Fenced Web App

1. Access the Web App: Open a web browser and paste the copied web app URL. The user interface will prompt for name search, once you search list will appear in “select employee”
2. Input Data: Users input their name.
3. Geo-Fencing Validation: The app will auto detect the employee location it will allow to punch in or punch out if user is in the 200 meter radius.
4. Real-time Updates: once the user clicked on punch in or out date, time and comment will be recorded in the google sheet.

Type employee name or code list will appear or can be search by the name only

When employee in 200 meter range attendance will be punch in or out as shown in screenshot

Result : Punch in and punch out time is recorded in sheet with comments

If user is not in the 200 Meter range message will be appear that “You are not within the allowed range for punching.”

Enjoy !!

https://developers.google.com/apps-script/overview