👥 Automated employee attendance & salary deduction with Google Sheets and GPT-4
⚡ 392 views · 👥 HR & Recruitment
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 Status2. Ouput Sheet (Generated by Workflow)
EmpId Name OriginalSalary TotalMissingHours TotalAbsentDays Deduction FinalSalary EmpEmailHow 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
🔗 Nodes Used
Send Email, Google Sheets, Schedule Trigger, AI Agent, OpenAI Chat Model
📥 Import
Download workflow.json and import into n8n:
Workflow menu → Import from File