Step 2: Connect Google Sheets

These nodes retrieve attendance and salary data, append payroll results, and log email sends.

1. Open Retrieve Attendance Sheet and set Document to [YOUR_ID] and Sheet to Emp list (gid=0).
2. Keep Authentication set to serviceAccount and Combine Filters set to OR with Status filters for Present and Absent.
3. Open Retrieve Salary Sheet and set Document to [YOUR_ID] and Sheet to Emp Salary (gid=1839371816).
4. Configure Append Payroll Report with Operation set to append, Sheet to Employee Monthly Report (gid=735600063), and keep Use Append enabled.
5. Configure Log Email Send with Operation set to append and Sheet to Email Send Log (gid=1224577084).
6. Credential Required: Connect your googleApi credentials in Retrieve Attendance Sheet, Retrieve Salary Sheet, Append Payroll Report, and Log Email Send.

Step 3: Set Up Attendance Processing and Salary Merge

This stage calculates hours, runs a parallel branch to retrieve salaries, and combines datasets before calculating final salary deductions.

1. Open Compute Daily Hours and keep the JavaScript as provided to calculate totals, missing hours, and attendance status.
2. Ensure Compute Daily Hours outputs to both Retrieve Salary Sheet and Combine Salary Data in parallel.
3. Open Combine Salary Data and set Mode to combine and Fields to Match to EmpId.
4. Open Compute Salary Adjustment and keep the provided JavaScript that calculates Deduction and FinalSalary.

Step 4: Configure AI Summary Generation

This step uses an AI model to create a professional summary per employee and then formats it for email sending.

1. In Produce AI Summary, set Text to =Employee ID : {{ $json.EmpId }} Name : {{ $json.Name }} Original Salary :{{ $json.OriginalSalary }} Total Missing Hours:{{ $json.TotalMissingHours }} Total Absent Days {{ $json.TotalAbsentDays }} Deduction: {{ $json.Deduction }} Final Salary: {{ $json.FinalSalary }} EmpEmail: {{ $json.EmpEmail }}.
2. In Produce AI Summary, set System Message to the provided HR assistant prompt so it returns an array of objects with SummaryText.
3. Open OpenAI Chat Engine and set Model to gpt-4.1-mini.
4. Credential Required: Connect your openAiApi credentials in OpenAI Chat Engine.
5. Open Format Summary JSON and keep the provided JavaScript to parse the model output into individual employee objects.

Step 5: Configure Email Output and Logging

Send the AI-generated summary email and record the result in a Google Sheet.

1. Open Dispatch Salary Email and set Text to ={{ $json.SummaryText }}.
2. Set Subject to ={{ $json.EmpEmail }} and To Email to =Monthly Salary Summary – {{ $json.Name }} (Emp ID: {{ $json.EmpId }}).
3. Set From Email to [YOUR_EMAIL] and keep Email Format as text.
4. Credential Required: Connect your smtp credentials in Dispatch Salary Email.
5. Connect Dispatch Salary Email to Log Email Send to append delivery results.

Step 6: Test and Activate Your Workflow

Run a manual test to confirm data flow, AI summaries, and email delivery before activating the monthly schedule.

1. Click Execute Workflow to run a manual test from Scheduled Automation Trigger.
2. Confirm Retrieve Attendance Sheet and Retrieve Salary Sheet return data, and that Combine Salary Data matches on EmpId.
3. Verify Append Payroll Report added rows to the Employee Monthly Report sheet.
4. Check that Dispatch Salary Email sends messages and Log Email Send writes results to Email Send Log.
5. Once validated, toggle the workflow to Active so it runs monthly.