Automated Employee Attendance & Salary Deduction with Google Sheets and GPT-4
Go to Workflow
Mohamed Abubakkar
Description
Overview
This workflow is designed for HR teams and payroll department to automate the monthly process of calculating employee attendance, salary deductionsm and personalized email notification. It seamlessly integrates Google Sheets, internal attendance data and GPT-4 (via AI Agent) to generate complete monthly payroll reports with professional summaries for each employees.
Prerequisites
Before using this workflow, ensure you have:
A Google Sheet with the monthly attendance logs
A secong Google Sheet containing salary, employee names and email addresses.
Gmail or SMTP credentials for sending email
OpenAI API key (for summarization)
Crendentials for Google Sheet and Gmail Integration
Google Sheet Structure
1. Attendance Sheet (Daily Log)
EmpId Name Date InTime OutTime HoursWorked Status
2. Ouput Sheet (Generated by Workflow)
EmpId Name OriginalSalary TotalMissingHours TotalAbsentDays Deduction FinalSalary EmpEmail
How It Works
1. Retrieve Attendance & Employee Master Data
The workflow fetches all employee attendace logs and thier corresponding salary, name and email details from Google Sheets.
2. Calculate Work Hours & Absences
The workflow computes total worked hours per day, missing hours and identifies absent days, It then calculates the total missing hours and absences for the entire month.
3. Salary Deduction Calculation
Each employee's hourly and daily rate is determined from thier salary, and deductions are calculated based on missing hours and absences.
4. Prepare Final Dataset
A structured JSON dataset is generated combining attendace summary, deductions and final salary amounts.
5. Generate AI Summary Report (GPT-4)
Using AI Agent GPT-4 creates a professional, HR-friendly summary paragraph for each employee's email report.
6. Send Salary Deduction Report Email
Each employee receives a personalized email with thier final salary summary and monthly attendance report.
7. Log Email Status
Email delivery details and summary text are stored in new Google Sheet for audit and HR tracking.
User Cases
Monthly attendance and payroll automation
Automated HR communication
Salary deduction reporting
Attendace compliance monitoring
Reducing manual calculation errors