Employee Attendance System in Google Sheets with Apps Script (Login / Logout + Free Code)

Employee Attendance System in Google Sheets + Apps Script

Create a smart Login / Logout attendance tracker using Google Form, Google Sheets and Apps Script. Perfect for office, shop, warehouse, startup & HR teams.

⚡ Smart Login

Employees submit only Employee ID + Login / Logout.

📊 Auto Attendance Log

Name, department, hours, late mark and status updated automatically.

🚀 Dashboard Ready

Use Looker Studio for premium visual dashboard.

💯 Free Setup

Build complete system using free Google tools.

🔥 Features

Login / Logout Tracking Working Hours Late Mark Half Day Active / Inactive Check Looker Dashboard

📋 Required Sheets

Sheet Name Purpose
Form Responses 1 Google Form submissions
Emp Master Employee master data + settings
Attendance_Log Processed attendance records

👨‍💼 Emp Master Structure

Setting Value
Standard Login9:00 AM
Standard Logout6:00 PM
Working Hours9
Half Day Min4.5

Employee Data from Row 7

Emp ID Name Email Manager Department Status
EMP001 Rajat Sharma abc@gmail.com mgr@gmail.com HR Active
EMP002 Rushikesh Godbole xyz@gmail.com mgr@gmail.com SCM Active

📝 Google Form Setup

Only 2 Questions required:
  • Employee ID (Dropdown)
  • Login / Logout (Dropdown)

⚙️ Apps Script Code

Go to Extensions → Apps Script and paste your code.

function onFormSubmit(e) {

  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const masterSheet = ss.getSheetByName("Emp Master");
  const logSheet = ss.getSheetByName("Attendance_Log");

  const row = e.values;

  const timestamp = new Date(row[0]);
  const empId = row[2].toString().trim();
  const action = row[3].toString().replace("-", "").trim(); // Login / Logout

  const tz = Session.getScriptTimeZone();

  const dateOnly = Utilities.formatDate(timestamp, tz, "dd-MMM-yyyy");
  const timeOnly = Utilities.formatDate(timestamp, tz, "HH:mm");

  // SETTINGS FROM EMP MASTER
  const stdLogin = masterSheet.getRange("B1").getDisplayValue();
  const halfDayMin = Number(masterSheet.getRange("B4").getValue());

  // EMP MASTER DATA
  const lastRow = masterSheet.getLastRow();
  const masterData = masterSheet.getRange(7, 1, lastRow - 6, 6).getValues();

  let empName = "";
  let dept = "";
  let empStatus = "";

  for (let i = 0; i < masterData.length; i++) {

    if (masterData[i][0] == empId) {

      empName = masterData[i][1];
      dept = masterData[i][4];
      empStatus = masterData[i][5];
      break;

    }

  }

  // INVALID OR INACTIVE EMPLOYEE
  if (empName == "") return;
  if (empStatus != "Active") return;

  // CHECK EXISTING ENTRY
  const logData = logSheet.getDataRange().getValues();

  let foundRow = -1;

  for (let i = 1; i < logData.length; i++) {

    let sheetDate = logSheet.getRange(i + 1, 1).getDisplayValue();
    let sheetEmp = logData[i][1];

    if (sheetDate == dateOnly && sheetEmp == empId) {
      foundRow = i + 1;
      break;
    }

  }

  // ==========================
  // NEW ENTRY
  // ==========================
  if (foundRow == -1) {

    // LOGIN FIRST
    if (action == "Login") {

      let lateMark = checkLate(timeOnly, stdLogin);

      logSheet.appendRow([
        dateOnly,        // A Date
        empId,           // B Emp ID
        empName,         // C Name
        dept,            // D Dept
        timeOnly,        // E Login Time
        "",              // F Logout Time
        "",              // G Total Hours
        lateMark,        // H Late
        "",              // I Half Day
        "Working"        // J Status
      ]);

    }

    // LOGOUT FIRST
    if (action == "Logout") {

      logSheet.appendRow([
        dateOnly,
        empId,
        empName,
        dept,
        "",
        timeOnly,
        "",
        "",
        "",
        "Login Missing"
      ]);

    }

  }

  // ==========================
  // UPDATE EXISTING ENTRY
  // ==========================
  else {

    const loginTime = logSheet.getRange(foundRow, 5).getDisplayValue();
    const logoutTime = logSheet.getRange(foundRow, 6).getDisplayValue();

    // LOGIN UPDATE
    if (action == "Login" && loginTime == "") {

      let lateMark = checkLate(timeOnly, stdLogin);

      logSheet.getRange(foundRow, 5).setValue(timeOnly);
      logSheet.getRange(foundRow, 8).setValue(lateMark);
      logSheet.getRange(foundRow, 10).setValue("Working");

    }

    // LOGOUT UPDATE
    if (action == "Logout" && logoutTime == "") {

      logSheet.getRange(foundRow, 6).setValue(timeOnly);

      if (loginTime != "") {

        let total = calculateHours(loginTime, timeOnly);

        logSheet.getRange(foundRow, 7).setValue(total);

        let halfDay = total < halfDayMin ? "Yes" : "No";

        logSheet.getRange(foundRow, 9).setValue(halfDay);
        logSheet.getRange(foundRow, 10).setValue("Complete");

      } else {

        logSheet.getRange(foundRow, 10).setValue("Login Missing");

      }

    }

  }

}



// ==========================
// CHECK LATE MARK
// ==========================
function checkLate(actual, standard) {

  let a = new Date("1/1/2000 " + actual);
  let b = new Date("1/1/2000 " + standard);

  return a > b ? "Yes" : "No";

}



// ==========================
// CALCULATE HOURS
// ==========================
function calculateHours(login, logout) {

  let inTime = new Date("1/1/2000 " + login);
  let outTime = new Date("1/1/2000 " + logout);

  let diff = (outTime - inTime) / (1000 * 60 * 60);

  return diff.toFixed(2);

}

⏰ Trigger Setup

  • Open Apps Script
  • Click Triggers
  • Add Trigger
  • Select onFormSubmit
  • Event Type = On Form Submit

📊 Dashboard Upgrade

Connect Attendance_Log sheet to Looker Studio for a premium live dashboard.

Watch Full Video Tutorial

🎯 SEO Keywords

google sheets attendance system, apps script attendance tracker, employee login logout tracker, google form attendance system, attendance dashboard in looker studio, office attendance system free

💬 Final Words

If you want advanced version with auto absent marking, email alerts, biometric style punch logic or manager approval system, comment below.

Subscribe Scripted Success Master

Comments

Post a Comment

Popular posts from this blog

Image
Google Form Leave Approval System with Auto Email, Attachments & Manager Action Buttons Google Form Leave Approval System with Auto Email, Attachments & Manager Action Buttons This tutorial shows how to automate leave request approvals using Google Forms, Google Sheets, and Apps Script. Trigger auto emails on form submit CC manager with in-email action buttons Update status in Google Sheet 🧾 Google Form Structure Create a Google Form with the following fields: Full Name Email ID Manager Email ID Leave Type Start Date End Date Duration Reason 📊 Google Sheet Columns After linking your form to Google Sheets, add 3 columns: Status – Manager’s decision Approve Link Reject Link ⚙️ Apps Script Setup Open your linked Sheet → Extensions > Apps Script and paste the following code: ...
Read more
Image
Automate Google Sheets Task Tracker with Apps Script ✅ Automate Task Completion in Google Sheets Using Apps Script Welcome to Scripted Success Master ! In this step-by-step guide, you’ll learn how to: Create a task management system in Google Sheets Automatically move completed tasks to a separate sheet Track completion date and calculate TAT (Turnaround Time) Use Google Apps Script to power it all 🧱 Step 1: Create the Google Sheet Structure Sheet 1: Task List Rename the first sheet to Task List and create the following columns: ℹ️ Use Data Validation (Dropdown) in the Status column for values like: Not yet Started , WIP , Completed Sheet 2: Completed Tasks Create a new sheet and rename it to Completed Tasks . Add these headers: Column Header Note A Sr No From Task List B Task Description From Task List C Start Date From Task List D Priority From Task List E Status...
Read more
Image
Send Birthday Email Reminders using Google Sheets & Apps Script Send Automatic Birthday Reminders using Google Sheets & Google Apps Script This tutorial shows you how to use Google Sheets and Apps Script to send automatic email reminders for birthdays — be it for friends, family, or office colleagues. A scheduled trigger will run daily and send beautiful HTML emails when a birthday matches today's date! 📋 Step 1: Setup Google Sheet Create a new Google Sheet and name it Birthday Reminder Tracker . Use the following structure: Column Details Sr No 1 Name Jane Doe Birthday 1995-06-28 Email janedoe@email.com Reminder Time (HH:MM) 09:00 Last Sent (empty) Reminder Time: This lets you define when to send the mail (in 24-hour format like 08:30 or 17:45). Last Sent: This column will be updated automatically to avoid duplicate mails. 💻 Step 2: Add Google Apps Script Click Extensions → Apps Script Clear any default code Pas...
Read more