In this post, I demonstrate how to create a Time Log Google Web App on Google Sheets.
How to Video:
Video Notes:
- Jquery, Bootstrap, and DateTimePicker Libraries. Click Here
- Bootstrap Documentation
- Check out Employee Time Clock Post. Click Here
- Check out Time Tracker Software Post. Click Here
Code in Video:
function doGet(e) {
const userEmail = Session.getActiveUser().getEmail();
var htmlOutput = HtmlService.createTemplateFromFile('Index');
htmlOutput.email = userEmail;
return htmlOutput.evaluate();
}
function getJobs() {
var ss= SpreadsheetApp.getActiveSpreadsheet();
var jobSheet = ss.getSheetByName("JOBS");
var [headers, ...data] = jobSheet.getDataRange().getValues();
return data;
}
function AddRecord(job, note, startDate, endDate) {
const userEmail = Session.getActiveUser().getEmail();
var ss= SpreadsheetApp.getActiveSpreadsheet();
var employeeSheet = ss.getSheetByName("EMPLOYEES");
var [headers, ...data] = employeeSheet.getDataRange().getValues();
for(var i = 0; i < data.length; i++)
{
if(data[i][2] == userEmail)
{
var firstname = data[i][0];
var lastname = data[i][1];
break;
}
}
var timeSheet = ss.getSheetByName("TIME SHEET");
var nextRow = timeSheet.getLastRow() + 1;
timeSheet.appendRow([firstname,
lastname,
startDate,
endDate,
'=ROUND((D'+nextRow+'-C'+nextRow+')*24, 2)',
job,
note,
new Date()]);
return 'Record Added';
}
<!DOCTYPE html>
<html>
<head>
<script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/1.12.4/jquery.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/moment.js/2.14.1/moment.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/twitter-bootstrap/3.3.6/js/bootstrap.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/bootstrap-datetimepicker/4.17.37/js/bootstrap-datetimepicker.min.js">
</script>
<link rel="stylesheet"
href="https://cdnjs.cloudflare.com/ajax/libs/bootstrap-datetimepicker/4.17.37/css/bootstrap-datetimepicker.css">
<link rel="stylesheet"
href="https://cdnjs.cloudflare.com/ajax/libs/twitter-bootstrap/3.3.6/css/bootstrap.min.css">
<base target="_top">
<script>
function onFailure(error) {
document.getElementById('display').innerHTML = "ERROR: " + error.message;
}
function onSuccess(message) {
document.getElementById("job").value = '';
document.getElementById("note").value = '';
document.getElementById("startDate").value = '';
document.getElementById("endDate").value = '';
document.getElementById('display').innerHTML = message;
}
function AddRecord()
{
document.getElementById('display').innerHTML = "";
var job = document.getElementById("job").value;
var note = document.getElementById("note").value;
var startDate = document.getElementById("startDate").value;
var endDate = document.getElementById("endDate").value;
if(job != '' && startDate != '' && endDate != '')
{
google.script.run.withSuccessHandler(onSuccess).
withFailureHandler(onFailure).
AddRecord(job, note, startDate, endDate);
}
else
{
document.getElementById('display').innerHTML = "Not All Fields Filled Out";
}
}
function getJobs()
{
google.script.run.withSuccessHandler(function(ar)
{
var job = document.getElementById("job");
console.log(ar);
let option = document.createElement("option");
option.value = "";
option.text = "";
job.appendChild(option);
ar.forEach(function(item, index)
{
let option = document.createElement("option");
option.value = item[0];
option.text = item[0];
job.appendChild(option);
});
}).getJobs();
}
</script>
</head>
<body>
<div style="padding: 10px;" >
<h1>Time Log</h1>
<span>Logged In: <?= email ?></span>
<hr>
<form>
<div class="row">
<div class="form-group col-md-3">
<label for="job">Job</label>
<select class="form-control" id="job">
</select>
</div>
</div>
<div class="row">
<div class="form-group col-md-3">
<label for="note">Note</label>
<textarea id="note" class="form-control" style="width: 500px" ></textarea>
</div>
</div>
<div class="row">
<div class='form-group col-md-3'>
<label for="startDate" >Start Date</label>
<input type='text' class="form-control" id='startDate' />
</div>
</div>
<div class="row">
<div class='form-group col-md-3'>
<label for="endDate" >End Date</label>
<input type='text' class="form-control" id='endDate' />
</div>
</div>
<div class="row">
<div class="form-group col-md-3">
<input type="button" value="Add Time Log" class="btn btn-primary" onclick="AddRecord()" />
<span id="display" ></span>
</div>
</div>
</form>
</div>
<script>
getJobs();
$('#startDate').datetimepicker();
$('#endDate').datetimepicker();
</script>
</body>
</html>Related Posts
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.
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.
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
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
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 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. 