Posted on by

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 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.
  • Check out Web App Time Tracker. Click Here

Code in Video:

function clockIn() {
  
  //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();

  for (var j = 5; j <= lastRow; j++)
  {
    // CHECK CLOCK IN
    if(mainSheet.getRange('B1:B1').getValue() ==  mainSheet.getRange(j, 1).getValue() && mainSheet.getRange(j,3).getValue() == '')
    {
      Browser.msgBox('Need to Clock Out before Clocking IN');
      return;
    }
    
  }
  
  // ADD CLOCK IN RECORD
  mainSheet.getRange(lastRow+1,1).setValue(mainSheet.getRange('B1:B1').getValue()).setFontSize(12);
  mainSheet.getRange(lastRow+1,2).setValue(new Date()).setNumberFormat("MM/dd/yyyy hh:mm:ss A/P").setHorizontalAlignment("left").setFontSize(12);
  
}


function clockOut() {
  
  //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;

  for (var j = 5; j <= lastRow; j++)
  {
    // FIND CLOCK IN RECORD
    if(mainSheet.getRange('B1:B1').getValue() ==  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)
  {
    Browser.msgBox('Need to Clock In First');
    return;
  }
  
  // CALL TOTAL HOURS
  TotalHours();
  
}

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 = 5; 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(5+i,6).setValue(totals[i][0]).setFontSize(12);
    mainSheet.getRange(5+i,7).setValue(totals[i][1]).setFontSize(12);  
  } 
}

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.
Employee Time Clock Web App 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 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
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.

Published by codewithcurt