Track Employee Attendance with Analytics, Email Reports & Slack Alerts Using Google Sheets

Transform your attendance management with this enterprise-grade automated workflow featuring AI-powered analytics, multi-dimensional insights, and intelligent alerting. Running hourly, it integrates multiple data sources (attendance logs + employee master data), performs sophisticated statistical analysis, detects anomalies, generates department-specific insights, and delivers beautiful HTML reports via email and Slack. Get real-time visibility into attendance patterns, punctuality trends, and actionable alerts for HR, management, and department heads. 📊🎯✨

Good to Know Intelligent Scheduling**: Runs hourly but only sends management alerts when critical thresholds are breached Multi-Source Integration**: Combines attendance logs with employee master data for enriched context Smart Analytics**: Calculates attendance rate, punctuality rate, absenteeism rate, and department-wise metrics Conditional Routing**: Management emails are triggered only for high-priority alerts (no email fatigue!) Production-Ready**: Includes error handling, data validation, and comprehensive logging Scalable Design**: Handles multiple departments, shifts, and employee types efficiently

How It Works

Automated Trigger – Runs hourly to monitor attendance with zero manual effort. Dual Data Ingestion – Fetches attendance and employee master data, then merges them for enriched analytics. Advanced Analytics Engine – Analyzes attendance, calculates key metrics, detects anomalies, and generates alerts. Smart Conditional Routing – Validates data, prioritizes alerts, and routes notifications via email, Slack, and database. Rich Email Reports – Sends visually formatted reports with metrics, alerts, and detailed employee breakdowns. Slack Block Kit Integration – Delivers structured, real-time attendance alerts with visual indicators to team channels. Data Persistence & Logging – Logs daily summaries, maintains audit trails, and prepares data for trend analysis dashboards.

How to Use

Basic Setup Import the Workflow: Copy JSON → n8n → Import from File Configure Credentials: Add Google Sheets, SMTP, and Slack credentials Update Spreadsheet IDs: Replace all placeholder sheet IDs with your actual Google Sheet IDs Set Email Addresses: Update sender and recipient email addresses Configure Slack Channel: Replace channel ID with your team's attendance channel Test: Execute workflow manually to verify connections Activate: Turn on the Schedule Trigger for hourly execution

Advanced Configuration Adjust Alert Thresholds**: Modify the 10% (late) and 15% (absent) thresholds in Analytics Engine Customize Email Design**: Edit HTML in Format Email node for brand alignment Add More Channels**: Extend with Microsoft Teams, Discord, or SMS notifications Enhance Analytics**: Add shift-specific analysis, contractor vs. full-time breakdowns Integrate BI Tools**: Enable Power BI push dataset for real-time dashboards

Requirements

Google Sheets Setup

Sheet 1: AttendanceLogs | Column | Type | Description | Example | |--------|------|-------------|---------| | EmployeeID | Text | Unique employee identifier | EMP001 | | EmployeeName | Text | Full name | John Doe | | Date | Date | Attendance date (YYYY-MM-DD) | 2025-01-15 | | Status | Text | Present/Absent/Late/Leave/WFH/Half-Day | Present | | CheckInTime | Time | Arrival time (HH:MM) | 09:15 | | CheckOutTime | Time | Departure time (HH:MM) | 18:00 | | Notes | Text | Optional comments | Doctor appointment |

Sheet 2: Employees (Master Data) | Column | Type | Description | Example | |--------|------|-------------|---------| | EmployeeID | Text | Unique identifier (matches AttendanceLogs) | EMP001 | | EmployeeName | Text | Full name | John Doe | | Department | Text | Department name | Engineering | | Manager | Text | Reporting manager name | Jane Smith | | Shift | Text | Day/Night/Evening | Day | | Email | Email | Work email address | john.doe@company.com | | ContractType | Text | Full-Time/Part-Time/Contract | Full-Time |

Sheet 3: DailySummary (Auto-populated by workflow) | Column | Type | Description | |--------|------|-------------| | Date | Date | Report date | | Hour | Number | Hour of day (0-23) | | Present | Number | Present count | | Absent | Number | Absent count | | Late | Number | Late count | | AttendanceRate | Number | Attendance percentage |

Credentials Needed

Google Sheets OAuth2 API Enable Google Sheets API in Google Cloud Console Create service account credentials Share all 3 sheets with service account email

SMTP Email Account Gmail: Enable "App Passwords" in Google Account settings Or use company SMTP server details Requires: Host, Port (587), Username, Password

Slack Bot Token Create Slack App at api.slack.com/apps Enable permissions: chat:write, chat:write.public Install app to workspace Copy Bot User OAuth Token (starts with xoxb-) Invite bot to target channel

Placeholder Values to Replace

| Placeholder | Where to Find | Example | |-------------|---------------|---------| | YOUR_ATTENDANCE_SPREADSHEET_ID | Google Sheets URL | 1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms | | YOUR_EMPLOYEE_SPREADSHEET_ID | Google Sheets URL | 1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms | | YOUR_SUMMARY_SPREADSHEET_ID | Google Sheets URL | 1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms | | C12345678 | Slack → Right-click channel → Copy link → Extract ID | C05XXXXXXXX | | hr@company.com | Your HR email | hr@yourcompany.com | | management@company.com | Management distribution list | leadership@yourcompany.com |

Customizing This Workflow

Modify Alert Thresholds In the Analytics Engine node, find these lines: const lateThreshold = metrics.totalEmployees * 0.1; // Change 0.1 to 0.15 for 15% const absentThreshold = metrics.totalEmployees * 0.15; // Change to 0.20 for 20%

Add New Status Types In the Analytics Engine node, add to the switch statement: case 'Remote': statusCount.remote++; break;

Customize Email Template In the Format Email node, modify the emailHtml variable: Change gradient colors in the header div Add company logo: `` Adjust grid columns: grid-template-columns: repeat(5, 1fr);

Add SMS Notifications Add Twilio or Vonage node after "Critical Alerts" Send to on-call manager for high-severity alerts Use message: 🚨 ${data.absent} employees absent today. Review required.

Multi-Location Support Modify Analytics Engine to group by Location field: const locationMetrics = {}; todayRecords.forEach(record => { const location = employee.location; if (!locationMetrics[location]) { locationMetrics[location] = { present: 0, absent: 0, late: 0 }; } // ... aggregate by location });

Shift-Specific Analysis Add logic to compare check-in times against shift schedules: const shiftTimes = { 'Day': '09:00', 'Night': '21:00', 'Evening': '14:00' }; const expectedTime = shiftTimes[employee.shift];

Integration with HR Systems Add HTTP Request node to push data to: Workday**: POST attendance records via REST API BambooHR**: Update employee time-off balances ADP**: Sync payroll-related attendance data SAP SuccessFactors**: Update time management module

Advanced Analytics Add Python or R nodes for: Predictive Analytics**: ML models to predict absence patterns Sentiment Analysis**: Correlate attendance with employee surveys Seasonality Detection**: Identify patterns (Monday blues, pre-holiday trends) Correlation Analysis**: Link attendance with project deadlines, weather, events

Dashboard Integration Export data to visualization platforms: Power BI**: Use HTTP Request node to push to streaming dataset Tableau**: Write to database (PostgreSQL, MySQL) via Execute Query node Grafana**: Store in InfluxDB time-series database Custom Dashboard**: Build React app that reads from DailySummary sheet

Troubleshooting

Common Issues

"Could not find node" Ensure all data sources are fetched before Analytics Engine runs Check node names match exactly in $('Node Name').all() syntax

"No records found" Verify Date column format is YYYY-MM-DD in Google Sheets Check timezone settings in Schedule Trigger vs. Sheet locale Confirm EmployeeID values match between AttendanceLogs and Employees sheets

"Email not sending" Verify SMTP credentials are correct Check if "Critical Alerts?" condition is being met Test email node independently with static data

"Slack message failed" Ensure bot is invited to the target channel Verify channel ID format (starts with C, not # symbol) Check bot has chat:write permission scope

Performance Optimization

Large Datasets (>1000 employees): Add pagination in Google Sheets fetch Multiple Departments: Run parallel branches for each department Historical Analysis**: Schedule separate weekly/monthly summary workflows Reduce API Calls**: Cache employee master data in workflow static data

Security Best Practices

✅ Use service account credentials (not personal OAuth) ✅ Restrict sheet access to service account only ✅ Store credentials in n8n credential store (encrypted) ✅ Enable 2FA on all integrated accounts ✅ Audit logs regularly for unauthorized access ✅ Use HTTPS for all external API calls

Discover more workflows – Get in touch with us