👥 Track employee attendance with analytics, email reports & Slack alerts using Google Sheets

⚡ 303 views · 👥 HR & Recruitment

Description

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

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

How to Use

Basic Setup

1. Import the Workflow: Copy JSON → n8n → Import from File
2. Configure Credentials: Add Google Sheets, SMTP, and Slack credentials
3. Update Spreadsheet IDs: Replace all placeholder sheet IDs with your actual Google Sheet IDs
4. Set Email Addresses: Update sender and recipient email addresses
5. Configure Slack Channel: Replace channel ID with your team’s attendance channel
6. Test: Execute workflow manually to verify connections
7. 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

Sheet 2: Employees (Master Data)

Sheet 3: DailySummary (Auto-populated by workflow)

Credentials Needed

1. Google Sheets OAuth2 API

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

2. SMTP Email Account

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

3. 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

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: <img src="https://yourcompany.com/logo.png" />
• Adjust grid columns: grid-template-columns: repeat(5, 1fr);

Add SMS Notifications

1. Add Twilio or Vonage node after “Critical Alerts”
2. Send to on-call manager for high-severity alerts
3. 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

🔗 Nodes Used

Send Email, Google Sheets, Slack, Schedule Trigger

📥 Import

Download workflow.json and import into n8n: Workflow menu → Import from File

📖 Importing guide · 🔑 Credential setup