Create Employee Time Clock Web App on Google Sheets

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

Time Log Web App Google Sheets 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.
Inventory Tracker 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.
Purchase Order Generator Google Sheets 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
Cycle Count Google Sheets 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
Time Tracker Google Sheets 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.
Inventory Tracking Software 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.