Automated Employee Attendance & Salary Deduction with Google Sheets and GPT-4

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