In this tutorial, I'll show you how to create an efficient Employee Attendance System using Google Sheets and Apps Script. This system allows you to track employee login and logout times along with their IP addresses. Follow along as I walk you through the entire process, including:
- Setting up the Google Sheet to store employee data
- Writing Apps Script code to handle user authentication and attendance logging
- Capturing login attempts and IP addresses for security
- Displaying real-time attendance records
You'll learn how to use the following key functions in Apps Script:
doGet(e): To serve the HTML interfacegetEmployees(): To fetch employee data from the sheetvalidateUser(username, password): To authenticate userslogLoginAttempt(username, status, ip): To log login attempts with IP addressesclockIn(username): To clock in users and record their IP addressclockOut(username): To clock out users and calculate their total time worked
| Step | Instructions |
|---|---|
| Step 1 | Open the web app link provided. |
| Step 2 |
If you see a Google Sheet in view-only mode:
|
| Step 3 |
If you see a page with two options (Make a Copy and AppScript File):
|
| Step 4 | Follow the on-screen instructions to complete the setup. |
| Step 5 | Click on the Extensions menu at the top of the Google Sheet. |
| Step 6 | Select Apps Script from the dropdown menu. |
| Step 7 | In the Apps Script editor, click on the Deploy button. |
| Step 8 | Select New deployment from the dropdown menu. |
| Step 9 | In the deployment configuration, click on Select type and choose Web app. |
| Step 10 | Fill in the required fields and click on Deploy. |
| Step 11 | Grant the necessary permissions when prompted. |
| Step 12 | Once deployed, copy the web app URL provided. |