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

The workflow JSON

Copy or download the full n8n JSON below. Paste it into a new n8n workflow, add your credentials, activate. Full import guide →

{
  "id": "XFSW9RpYrzrEHBhi",
  "meta": {
    "templateCredsSetupCompleted": true
  },
  "name": "Employee Monthly Attendance Analytics",
  "tags": [],
  "nodes": [
    {
      "id": "cba69b6d-ab70-4748-8b80-16e677b42fb2",
      "name": "Schedule Trigger",
      "type": "n8n-nodes-base.scheduleTrigger",
      "position": [
        -1552,
        80
      ],
      "parameters": {
        "rule": {
          "interval": [
            {
              "field": "months"
            }
          ]
        }
      },
      "typeVersion": 1.2
    },
    {
      "id": "27ecb735-771d-4b52-a190-aa42fbe2cfd1",
      "name": "Calculate hours per day",
      "type": "n8n-nodes-base.code",
      "position": [
        -1104,
        80
      ],
      "parameters": {
        "jsCode": "const totalCalculated= [];\nfor (const item of items){\n  const row = item.json;\n  if(row.HoursWorked === 0){\n    totalCalculated.push({\n      \n      EmpId: row.EmpId,\n      Name: row.Name,\n      Date: row.Date,\n      Hours: 0,\n      Status: \"Absent\",\n      MissingHours: 8\n    });\n    continue;\n  }\n  const start = new Date(`${row.Date} ${row.InTime}`);\nconst end = new Date(`${row.Date} ${row.OutTime}`);\nconst hours = (end.getTime() - start.getTime()) / (1000 * 60 * 60 );\n\nconst rounded = Number(hours.toFixed(2));\nconst missing = rounded < 8 ? Number((8 - rounded).toFixed(2)) : 0;\n\ntotalCalculated.push({\n  EmpId: row.EmpId,\n  Name: row.Name,\n  Date: row.Date,\n  Hours: rounded,\n  MissingHours: missing,\n  Status: rounded < 8 ? \"LowHours\" : \"Present\"\n  \n});  \n}\n\nconst grouped = {};\nfor(const row of totalCalculated){\n  const empId = row.EmpId;\n  if(!grouped[empId]){\n    grouped[empId] = {\n      EmpId: row.EmpId,\n      Name: row.Name,\n      Hours: 0,\n      TotalMissingHours: 0,\n      TotalAbsentDays: 0,\n      TotalLowHoursDays: 0,\n      TotalPresentDays: 0\n    };\n  }\n   grouped[empId].Hours += row.Hours;\n  grouped[empId].TotalMissingHours += row.MissingHours;\n\n  if (row.Status === \"Absent\") grouped[empId].TotalAbsentDays++;\n  if (row.Status === \"LowHours\") grouped[empId].TotalLowHoursDays++;\n  if (row.Status === \"Present\") grouped[empId].TotalPresentDays++;\n  \n}\nreturn Object.values(grouped).map(emp => ({ json: emp }));\n\n"
      },
      "typeVersion": 2
    },
    {
      "id": "880dd2be-6ff5-439a-bde0-df7c2da165c5",
      "name": "Calculate Salary Deduction",
      "type": "n8n-nodes-base.code",
      "position": [
        -496,
        80
      ],
      "parameters": {
        "jsCode": "return items.map(item =>{\n  const row = item.json;\n  const salary = Number(row.Salary);\n  const missingHours = Number(row.TotalMissingHours || 0);\n  const absentDays = Number(row.TotalAbsentDays || 0);\n\n  const hourlyRate = salary / (22 * 8);\n\n  const deduction = (missingHours * hourlyRate) + (absentDays * 8 * hourlyRate);\n  const finalSalary = salary - deduction;\n\n  return{\n    json: {\n      EmpId: row.EmpId,\n      Name: row.Name,\n      OriginalSalary: salary,\n      TotalMissingHours: missingHours,\n      TotalAbsentDays: absentDays,\n      Deduction: Number(deduction.toFixed(2)),\n      FinalSalary: Number(finalSalary.toFixed(2)),\n      EmpEmail: row.col_4\n    }\n  };\n\n});"
      },
      "typeVersion": 2
    },
    {
      "id": "2d70459c-4974-4b69-b3ab-5e2da47f3b86",
      "name": "Employees Attendant Sheet",
      "type": "n8n-nodes-base.googleSheets",
      "position": [
        -1328,
        80
      ],
      "parameters": {
        "options": {},
        "filtersUI": {
          "values": [
            {
              "lookupValue": "Present",
              "lookupColumn": "Status"
            },
            {
              "lookupValue": "Absent",
              "lookupColumn": "Status"
            }
          ]
        },
        "sheetName": {
          "__rl": true,
          "mode": "list",
          "value": "gid=0",
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/1xZHi0bC7i4OkJeQB8BOJ4XWO8GM7ZB0FQKWI-6UC7a8/edit#gid=0",
          "cachedResultName": "Emp list"
        },
        "documentId": {
          "__rl": true,
          "mode": "url",
          "value": "https://docs.google.com/spreadsheets/d/1xZHi0bC7i4OkJeQB8BOJ4XWO8GM7ZB0FQKWI-6UC7a8"
        },
        "authentication": "serviceAccount",
        "combineFilters": "OR"
      },
      "credentials": {
        "googleApi": {
          "name": "<your credential>"
        }
      },
      "typeVersion": 4.7
    },
    {
      "id": "bd787217-6494-4e74-bb0e-37c35cde381a",
      "name": "Employees Salary Sheet",
      "type": "n8n-nodes-base.googleSheets",
      "position": [
        -864,
        80
      ],
      "parameters": {
        "options": {},
        "sheetName": {
          "__rl": true,
          "mode": "list",
          "value": 1839371816,
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/1xZHi0bC7i4OkJeQB8BOJ4XWO8GM7ZB0FQKWI-6UC7a8/edit#gid=1839371816",
          "cachedResultName": "Emp Salary"
        },
        "documentId": {
          "__rl": true,
          "mode": "url",
          "value": "https://docs.google.com/spreadsheets/d/1xZHi0bC7i4OkJeQB8BOJ4XWO8GM7ZB0FQKWI-6UC7a8"
        },
        "authentication": "serviceAccount"
      },
      "credentials": {
        "googleApi": {
          "name": "<your credential>"
        }
      },
      "typeVersion": 4.7
    },
    {
      "id": "a6c78e53-7220-4f7b-b3a4-d7c7047b2203",
      "name": "Merge with Salary",
      "type": "n8n-nodes-base.merge",
      "position": [
        -688,
        -80
      ],
      "parameters": {
        "mode": "combine",
        "options": {
          "multipleMatches": "first"
        },
        "fieldsToMatchString": "EmpId"
      },
      "typeVersion": 3.2
    },
    {
      "id": "055e954f-50c0-4d72-83bb-335d268e62a7",
      "name": "OpenAI Chat Model",
      "type": "@n8n/n8n-nodes-langchain.lmChatOpenAi",
      "position": [
        -80,
        320
      ],
      "parameters": {
        "model": {
          "__rl": true,
          "mode": "list",
          "value": "gpt-4.1-mini"
        },
        "options": {}
      },
      "credentials": {
        "openAiApi": {
          "name": "<your credential>"
        }
      },
      "typeVersion": 1.2
    },
    {
      "id": "fdfa2b7f-df56-430b-8075-c7f4b1952275",
      "name": "Adding the report to new sheet",
      "type": "n8n-nodes-base.googleSheets",
      "position": [
        -288,
        80
      ],
      "parameters": {
        "columns": {
          "value": {},
          "schema": [
            {
              "id": "EmpId",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "EmpId",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "Name",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "Name",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "OriginalSalary",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "OriginalSalary",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "TotalMissingHours",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "TotalMissingHours",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "TotalAbsentDays",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "TotalAbsentDays",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "Deduction",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "Deduction",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "FinalSalary",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "FinalSalary",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "EmpEmail",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "EmpEmail",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            }
          ],
          "mappingMode": "autoMapInputData",
          "matchingColumns": [],
          "attemptToConvertTypes": false,
          "convertFieldsToString": false
        },
        "options": {
          "useAppend": true
        },
        "operation": "append",
        "sheetName": {
          "__rl": true,
          "mode": "list",
          "value": 735600063,
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/1xZHi0bC7i4OkJeQB8BOJ4XWO8GM7ZB0FQKWI-6UC7a8/edit#gid=735600063",
          "cachedResultName": "Employee Monthly Report"
        },
        "documentId": {
          "__rl": true,
          "mode": "url",
          "value": "https://docs.google.com/spreadsheets/d/1xZHi0bC7i4OkJeQB8BOJ4XWO8GM7ZB0FQKWI-6UC7a8"
        },
        "authentication": "serviceAccount"
      },
      "credentials": {
        "googleApi": {
          "name": "<your credential>"
        }
      },
      "typeVersion": 4.7
    },
    {
      "id": "bb0d8a5f-8ad3-4bbc-bae3-e2efb4d20513",
      "name": "Convert the result to JSON format",
      "type": "n8n-nodes-base.code",
      "position": [
        224,
        80
      ],
      "parameters": {
        "jsCode": "let allEmployees = [];\n\nfor (let item of items) {\n    let arr = JSON.parse(item.json.output.replace(/\\\\n/g, \" \")); \n    allEmployees.push(...arr);\n}\n\nreturn allEmployees.map(emp => ({ json: emp }));"
      },
      "typeVersion": 2,
      "alwaysOutputData": false
    },
    {
      "id": "9e5bdd96-c9b1-4dd0-8cf7-c317e5719746",
      "name": "Save Email Log to Sheet",
      "type": "n8n-nodes-base.googleSheets",
      "position": [
        16,
        480
      ],
      "parameters": {
        "columns": {
          "value": {},
          "schema": [
            {
              "id": "accepted",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "accepted",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "rejected",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "rejected",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "ehlo",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "ehlo",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "envelopeTime",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "envelopeTime",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "messageTime",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "messageTime",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "messageSize",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "messageSize",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "response",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "response",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "envelope",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "envelope",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "messageId",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "messageId",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            }
          ],
          "mappingMode": "autoMapInputData",
          "matchingColumns": [],
          "attemptToConvertTypes": false,
          "convertFieldsToString": false
        },
        "options": {},
        "operation": "append",
        "sheetName": {
          "__rl": true,
          "mode": "list",
          "value": 1224577084,
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/1xZHi0bC7i4OkJeQB8BOJ4XWO8GM7ZB0FQKWI-6UC7a8/edit#gid=1224577084",
          "cachedResultName": "Email Send Log"
        },
        "documentId": {
          "__rl": true,
          "mode": "url",
          "value": "https://docs.google.com/spreadsheets/d/1xZHi0bC7i4OkJeQB8BOJ4XWO8GM7ZB0FQKWI-6UC7a8"
        },
        "authentication": "serviceAccount"
      },
      "credentials": {
        "googleApi": {
          "name": "<your credential>"
        }
      },
      "typeVersion": 4.7
    },
    {
      "id": "0ddeb883-f5e9-4cf3-8e62-d96bd8ffd51a",
      "name": "Sticky Note",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -1632,
        -896
      ],
      "parameters": {
        "width": 544,
        "height": 576,
        "content": "## How it works\nThis workflow automates the generation and distribution of **monthly attendance and salary summarize** for all employees. it integrates Google Sheets, AI processing, JSON normalization and automated email delivery via google SMTP, ensuring accurate, consistent and personalized reporting to each employee.\n\n## Setup steps\n1. Connect your credentials\n    a. Google Service account or OAuth\n    b. Google Sheet\n    c. OpenAi\n    d. Google SMTP.\n2. Replace google sheet link, OpenAi API, SMTP Credentials with yours.\n3. Adjust email template if desired.\n4. Adjust trigger based on your need.\n\n## Process Steps\n1. Fetch all employees attendance data from google sheet.\n2. Calculate daily work hours for each employee.\n3. Fetch additional information about each employees (salary, email, name).\n4. Prepare final dataset.\n5. AI Agent: Generate HR Summary.\n6. Send mail to each employees with final report of salary deduction details."
      },
      "typeVersion": 1
    },
    {
      "id": "cae9b537-3240-4320-9e7c-5a69d4c43fd0",
      "name": "Sticky Note1",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -1600,
        -208
      ],
      "parameters": {
        "color": 7,
        "width": 432,
        "height": 480,
        "content": "## Trigger node and Employee List\nRuns monthly once before salary and adjust the time if needed.\nFetch the attendance report of all employees from the google sheet."
      },
      "typeVersion": 1
    },
    {
      "id": "c005ffa4-c28f-448d-92a4-171266b92e14",
      "name": "Sticky Note2",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -1136,
        -208
      ],
      "parameters": {
        "color": 7,
        "width": 400,
        "height": 480,
        "content": "## Calculate Daily Hours and Fetch Employees Details\nThis function calculates employee's daily working hours and determines total monthly absences.\nIt will computes the total worked hours by subtracting the clock-out time from clock-in time.\nFetch the information about all employees like (salary, email, name and etc)\n"
      },
      "typeVersion": 1
    },
    {
      "id": "63a8de0a-b99e-4afa-a447-a8f532b83e57",
      "name": "Sticky Note3",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -544,
        -208
      ],
      "parameters": {
        "color": 7,
        "width": 432,
        "height": 480,
        "content": "## Salary Deduction & Final Calculation\nOnce received monthly attendance summary for each employees. it applies the deduction formula by calculation the hours salary rate and absent days.\nOnce all calculations are complete it formats the final report including salary breakdown, missing hours, absences, deductions and final salary\nCreates new google sheet for record-keeping and payroll processing."
      },
      "typeVersion": 1
    },
    {
      "id": "323e471f-4541-4bd2-a422-54304adec2af",
      "name": "Sticky Note4",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -80,
        -208
      ],
      "parameters": {
        "color": 7,
        "width": 432,
        "height": 480,
        "content": "## AI Summary & JSON Formatter\nAI Agent used for calculate the payroll report for each employee's to generate a clean and readable summary for email communication.\nAfter response formula node applies formatting rules to convert response summary to JSON objects for each employees."
      },
      "typeVersion": 1
    },
    {
      "id": "92a19d43-e8e4-4310-b7a2-ada3cde59578",
      "name": "Sticky Note5",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -288,
        416
      ],
      "parameters": {
        "color": 7,
        "width": 432,
        "height": 480,
        "content": "\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n## Email Dispatch & Logging\nThis stage sends each employee's finalized summary report to their respective email address using google SMTP.\nOnce email delivered this workflow automatically logs the sent-mail details."
      },
      "typeVersion": 1
    },
    {
      "id": "9f65084e-15ba-47f3-bf70-652eee59e116",
      "name": "Generate AI Summary Report",
      "type": "@n8n/n8n-nodes-langchain.agent",
      "position": [
        -80,
        80
      ],
      "parameters": {
        "text": "=Employee ID : {{ $json.EmpId }}\nName : {{ $json.Name }}\nOriginal Salary :{{ $json.OriginalSalary }}\nTotal Missing Hours:{{ $json.TotalMissingHours }}\nTotal Absent Days {{ $json.TotalAbsentDays }}\nDeduction: {{ $json.Deduction }}\nFinal Salary: {{ $json.FinalSalary }}\nEmpEmail: {{ $json.EmpEmail }}",
        "options": {
          "systemMessage": "=You are an HR assistant AI. Your task is to generate a professional, clear, and concise monthly attendance and salary summary for each employee based on the JSON data provided. \n\nThe JSON structure is as follows:\n{\n  \"EmpId\": number,\n  \"Name\": string,\n  \"EmpEmail\": string,\n  \"OriginalSalary\": number,\n  \"TotalMissingHours\": number,\n  \"TotalAbsentDays\": number,\n  \"Deduction\": number,\n  \"FinalSalary\": number,\n  \"SummaryText\": string\n}\n\nPlease return an array of objects for each employee report"
        },
        "promptType": "define"
      },
      "typeVersion": 3
    },
    {
      "id": "2eefb6f0-12e1-478c-925f-61b4b998b4da",
      "name": "Send Salary Deduction Report Email",
      "type": "n8n-nodes-base.emailSend",
      "position": [
        -256,
        480
      ],
      "parameters": {
        "text": "={{ $json.SummaryText }}",
        "options": {
          "appendAttribution": false
        },
        "subject": "={{ $json.EmpEmail }}",
        "toEmail": "=Monthly Salary Summary \u2013 {{ $json.Name }} (Emp ID: {{ $json.EmpId }})",
        "fromEmail": "user@example.com",
        "emailFormat": "text"
      },
      "credentials": {
        "smtp": {
          "name": "<your credential>"
        }
      },
      "typeVersion": 2.1
    }
  ],
  "active": false,
  "settings": {
    "executionOrder": "v1"
  },
  "versionId": "0674458e-a1df-4fe9-8c71-2d363f9de604",
  "connections": {
    "Schedule Trigger": {
      "main": [
        [
          {
            "node": "Employees Attendant Sheet",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Merge with Salary": {
      "main": [
        [
          {
            "node": "Calculate Salary Deduction",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "OpenAI Chat Model": {
      "ai_languageModel": [
        [
          {
            "node": "Generate AI Summary Report",
            "type": "ai_languageModel",
            "index": 0
          }
        ]
      ]
    },
    "Employees Salary Sheet": {
      "main": [
        [
          {
            "node": "Merge with Salary",
            "type": "main",
            "index": 1
          }
        ]
      ]
    },
    "Calculate hours per day": {
      "main": [
        [
          {
            "node": "Employees Salary Sheet",
            "type": "main",
            "index": 0
          },
          {
            "node": "Merge with Salary",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Employees Attendant Sheet": {
      "main": [
        [
          {
            "node": "Calculate hours per day",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Calculate Salary Deduction": {
      "main": [
        [
          {
            "node": "Adding the report to new sheet",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Generate AI Summary Report": {
      "main": [
        [
          {
            "node": "Convert the result to JSON format",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Adding the report to new sheet": {
      "main": [
        [
          {
            "node": "Generate AI Summary Report",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Convert the result to JSON format": {
      "main": [
        [
          {
            "node": "Send Salary Deduction Report Email",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Send Salary Deduction Report Email": {
      "main": [
        [
          {
            "node": "Save Email Log to Sheet",
            "type": "main",
            "index": 0
          }
        ]
      ]
    }
  }
}

Credentials you'll need

Each integration node will prompt for credentials when you import. We strip credential IDs before publishing — you'll add your own.

• googleApi
• openAiApi
• smtp

About this workflow

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…

Source: https://n8n.io/workflows/11625/ — original creator credit. Request a take-down →

More AI & RAG workflows → · Browse all categories →

Related workflows

Workflows that share integrations, category, or trigger type with this one. All free to copy and import.