Attendance Logger via Google Sheet
Luigi Gabriel Espiritu
HR Technology Leader | Driving People Productivity | Championing Process Excellence | Innovating with Automation | Agile HR Advocate
We leveraged Google Sheets and App Script to build an interactive attendance logger. Check out the steps below to see how we made it happen!
2. Inside your spreadsheet, go to Extensions and click Apps Script.
3. In Apps Script, rename the project to anything you prefer. For this guide, we will name it AttLogger.
4. In the code editor, input the following code. (Special thanks to Stack Overflow for the structure and ChatGPT for breaking down the explanation!)
For this example, HCM Code refers to the Employee Number. You can adjust the code to suit your organization's specific requirements.
function markAttendance() {
const html = HtmlService.createHtmlOutputFromFile('Popup')
.setWidth(400)
.setHeight(300);
SpreadsheetApp.getUi().showModalDialog(html, 'Mark Attendance');
}
function processHCMCode(hcmCode) {
const ss = SpreadsheetApp.getActiveSpreadsheet();
const attendeesSheet = ss.getSheetByName("LIST OF ATTENDEES");
if (!attendeesSheet) {
SpreadsheetApp.getUi().alert("The 'Attendees' sheet does not exist.");
return;
}
const data = attendeesSheet.getDataRange().getValues();
const hcmIndex = data[0].indexOf("HCM Code");
const tableIndex = data[0].indexOf("Table Number");
const attendanceIndex = data[0].indexOf("Attendance");
if (hcmIndex === -1 || tableIndex === -1 || attendanceIndex === -1) {
SpreadsheetApp.getUi().alert("The 'Attendees' sheet must have columns: HCM Code, Table Number, and Attendance.");
return;
}
let found = false;
for (let i = 1; i < data.length; i++) {
if (data[i][hcmIndex] == hcmCode) {
const tableNumber = data[i][tableIndex];
attendeesSheet.getRange(i + 1, attendanceIndex + 1).setValue("YES");
// Show the Confirmation Dialog
const html = HtmlService.createHtmlOutputFromFile('Confirmation')
.setWidth(400)
.setHeight(300);
html.append(`<script>setTableNumber("${tableNumber}");</script>`);
SpreadsheetApp.getUi().showModalDialog(html, 'Attendance Accepted');
found = true;
break;
}
}
if (!found) {
SpreadsheetApp.getUi().alert(`HCM Code '${hcmCode}' not found in the 'Attendees' sheet.`);
}
}
5. Once the code has been pasted into AppScript, go back to the Attendance Logger spreadsheet and add a new tab named LIST OF ATTENDEES. This tab will act as the database where the code will verify the user input. In this sheet, set up the headers HCM Code, Name, Table Number, and Attendance. You can populate it with sample data to test whether the code triggers properly.
6. After creating the template, return to your AppScript environment to make the application interactive by adding pop-up fields. In AppScript, create a new HTML file and paste the code provided below. Be sure to name this HTML file Popup.html, as it is designed to integrate seamlessly with your main code.
<!DOCTYPE html>
<html>
<head>
<base target="_top">
<style>
body {
font-family: Arial, sans-serif;
margin: 0;
padding: 20px;
background-color: #f4f4f4;
box-sizing: border-box;
}
h2 {
text-align: center;
color: #333;
margin-top: 0;
}
p {
text-align: center;
color: #555;
}
input[type="text"] {
width: calc(100% - 20px);
padding: 10px;
margin: 20px auto;
font-size: 16px;
display: block;
border: 1px solid #ccc;
border-radius: 5px;
}
button {
display: block;
width: 100%;
padding: 15px;
font-size: 16px;
font-weight: bold;
color: white;
background-color: #4CAF50;
border: none;
border-radius: 5px;
cursor: pointer;
margin-top: 10px;
}
button:hover {
background-color: #45a049;
}
</style>
</head>
<body>
<h2>Mark Attendance</h2>
<p>Please enter the HCM Code below:</p>
<input type="text" id="hcmCode" placeholder="Enter HCM Code">
<button onclick="submitHCMCode()">Submit</button>
<script>
function submitHCMCode() {
const hcmCode = document.getElementById('hcmCode').value.trim();
if (!hcmCode) {
alert("Please enter a valid HCM Code.");
return;
}
google.script.run.withSuccessHandler(closeDialog).processHCMCode(hcmCode);
}
function closeDialog() {
google.script.host.close();
}
</script>
</body>
</html>
7. After creating the Popup.html file, proceed to add another HTML file named Confirmation.html. Once created, paste the code provided below into this new file. This file will handle the confirmation step of the process.
<!DOCTYPE html>
<html>
<head>
<base target="_top">
<style>
body {
font-family: Arial, sans-serif;
margin: 0;
padding: 20px;
background-color: #f9f9f9;
box-sizing: border-box;
text-align: center;
}
h2 {
color: #4CAF50;
margin-top: 0;
}
p {
font-size: 18px;
color: #333;
margin: 10px 0;
}
.table-number {
font-weight: bold;
font-size: 20px;
color: #4CAF50;
margin: 10px 0;
}
button {
padding: 10px 20px;
font-size: 16px;
font-weight: bold;
background-color: #4CAF50;
color: white;
border: none;
border-radius: 5px;
cursor: pointer;
margin-top: 20px;
}
button:hover {
background-color: #45a049;
}
</style>
</head>
<body>
<h2>Your Attendance is Accepted</h2>
<p>Your table number is:</p>
<div class="table-number" id="tableNumber"></div>
<button onclick="google.script.host.close()">Close</button>
<script>
function setTableNumber(tableNumber) {
document.getElementById('tableNumber').textContent = tableNumber;
}
</script>
</body>
</html>
8. After completing the creation of the three files, you can now run your program to verify if it executes successfully. Make sure all connections between the files are properly configured and that your setup aligns with the intended functionality.
9. After clicking Run, you will need to authorize the App Script project. To do this, click on Review Permissions, sign in with your Google Account, and then click Allow to grant the necessary access.
10. After running the App Script, you can now return to your Spreadsheet, and you should see the pop-up appear.
11. Enter the Employee Number from your dummy data to check if the system will correctly display the assigned Table Number and update the Attendance status from "NO" to "YES."
领英推荐
12. After successfully testing the functionality, you can now deploy your App Script. To do so, go back to App Script, click on Deploy, and then select New Deployment.
13. Select the type as Web App when prompted during the deployment process. This will allow you to deploy the script as an interactive web application.
14. Fill out the configuration part by setting the following parameters:
A. Project Version: You can leave this as the default or create a new version.
B. Execute as: Select "Me" so the script runs with your privileges.
C. Who has access: Choose "Only myself" or "Anyone with the link" depending on your preference for accessibility.
15. After clicking Deploy, the App Script will display a confirmation message indicating that the deployment has been successfully updated. This means your web app is now live and ready for use!
16. After deployment, create a trigger, as this will be linked later to the button we will create. To do this, click on the left side of the screen to show the Selection menu and then click Triggers.
17. In the triggers section, click Add Trigger. In the trigger configuration, make sure to use the settings below and then click Save.
18. After saving the trigger, you can now create a button in Sheet1 and link it to your App Script. To do this, go to Insert, click Drawing, and draw your button. After drawing the button, right-click on it and click the three dots (options menu) on the button. Then, select Assign Script.
19. After clicking Assign Script, a pop-up will appear. In the field, input "markAttendance" and then click OK.
20. After clicking OK, you can now click the button to check if the script runs successfully.
21. Done.
With this simple automation, you'll have a more user-friendly interface for the attendance logger, which you can use not only for parties but also for formal events.
Just a note that we're not experts; we simply enjoy learning, which is why we're documenting our progress. However, if you have any questions, please feel free to reach out.