What

A fully automated attendance tracking system where team members simply send “IN” or “OUT” messages to a Telegram group. An n8n workflow processes these messages, validates states, logs data to Google Sheets, and replies with confirmations—all without any manual intervention.

Components:

Telegram bot for message interface
n8n workflow for automation logic
Google Sheets for data storage and calculations
OpenAI GPT-4o-mini (optional) for intelligent message filtering

Real scenario: Your team works remotely or in office. Each person sends “IN” when they start work, “OUT” when they finish. The system automatically tracks hours, prevents errors (like double check-ins), and maintains a permanent record in Google Sheets.

Why

The Problem

Manual attendance tracking is tedious:

Excel sheets need constant updating
Easy to forget or make mistakes
No validation (people can mark IN twice)
Calculating hours is manual work
Hard to track across multiple team members

This Solution

Zero friction: Send a 2-letter message. That’s it.
Smart validation: Can’t check in twice, can’t check out without checking in first
Auto-calculation: Hours spent calculated automatically
AI-powered: Ignores regular chat, catches typos like “ot” instead of “OUT”
Historical data: Everything logged in Google Sheets with timestamps
Multi-org ready: Optional organization mapping for companies with multiple teams

Why These Tools?

Telegram: Everyone already uses it. No new app to install.
n8n: Free, self-hosted workflow automation. No vendor lock-in.
Google Sheets: Free, familiar, easy to export for payroll/reports.
OpenAI: Cheap (~$0.0001 per message) and accurate message classification.

How

Step 1 — Create Telegram Bot

Go to Telegram and find @BotFather:

/newbot

Follow prompts:

Bot name: Attendance Tracker
Bot username: your_company_attendance_bot

BotFather returns:

Done! Your bot token is: 123456789:ABCdefGHIjklMNOpqrsTUVwxyz

Save this token — you’ll need it for n8n credentials.

Add bot to your group:

Create a Telegram group or use existing
Add the bot as member
Make it admin (optional but recommended)

Step 2 — Get Group Chat ID

You need the group’s chat ID for workflow configuration.

Method 1 — Using @RawDataBot:

Add @RawDataBot to your group
Forward any message from your group to this bot
It shows the chat ID: -1002502742130 (negative number for groups)

Method 2 — Via API:

curl https://api.telegram.org/bot<YourBotToken>/getUpdates

Look for "chat":{"id":-1234567890} in the JSON response.

Step 3 — Set Up Google Sheets

Create a new Google Sheet with two sheets:

Sheet 1: “rawdata”

This stores raw attendance logs.

Headers (Row 1):

userId | name | inTime | OutTime

Example data:

7650552265 | John Doe    | 1743132592 | 1743168676
1110309276 | Jane Smith  | 1743134245 |

userId: Telegram user ID (auto-filled by workflow)
name: User’s first name from Telegram (auto-filled)
inTime: Unix timestamp when user sent “IN”
OutTime: Unix timestamp when user sent “OUT” (empty until checkout)

Sheet 2: “parsed”

This automatically processes raw data into human-readable format.

Headers (Row 1):

userId | name | inTime | OutTime | row | currentStatus | isLatestForUser | inTimeUTC | outTimeUTC | hoursSpent | inDate | inMonth | inYear | inHour | InMinute | outDate | outMonth | outYear | outHour | outMinute | trueInDateTime | trueOutDateTime

Formulas (Row 2, then copy down):

A2: =rawdata!A2
B2: =rawdata!B2
C2: =rawdata!C2
D2: =rawdata!D2
E2: =ROW()-1
F2: =IF(D2="","IN","OUT")
G2: =IF(COUNTIFS($A$2:$A,A2,$C$2:$C,">"&C2)=0,"LATEST","")
H2: =IF(C2="","",TEXT(C2/86400 + DATE(1970,1,1) + TIME(5,30,0), "yyyy-mm-dd hh:mm:ss"))
I2: =IF(D2="","",TEXT(D2/86400 + DATE(1970,1,1) + TIME(5,30,0), "yyyy-mm-dd hh:mm:ss"))
J2: =IF(D2="","",TEXT((D2-C2)/86400,"h""h"" m""m"""))
K2: =IF(C2="","",DAY(C2/86400 + DATE(1970,1,1) + TIME(5,30,0)))
L2: =IF(C2="","",MONTH(C2/86400 + DATE(1970,1,1) + TIME(5,30,0)))
M2: =IF(C2="","",YEAR(C2/86400 + DATE(1970,1,1) + TIME(5,30,0)))
N2: =IF(C2="","",HOUR(C2/86400 + DATE(1970,1,1) + TIME(5,30,0)))
O2: =IF(C2="","",MINUTE(C2/86400 + DATE(1970,1,1) + TIME(5,30,0)))

Continue similar patterns for outDate through trueOutDateTime columns.

Timezone adjustment: Change TIME(5,30,0) to your offset:

IST (India): TIME(5,30,0) ✓
EST: TIME(-5,0,0)
PST: TIME(-8,0,0)
UTC: TIME(0,0,0)

Key columns explained:

currentStatus: “IN” if OutTime is empty, “OUT” if filled
isLatestForUser: “LATEST” for each user’s most recent record (used by workflow)
hoursSpent: Auto-calculated time difference (e.g., “9h 33m”)

Sample sheet: View live example

Copy the spreadsheet ID from URL:

https://docs.google.com/spreadsheets/d/1RsUStkD-LC6179-IK0CnjjC4fA19N-sSOGij4R7Tit8/edit
                                      ↑________________ID________________↑

Step 4 — Get OpenAI API Key (Optional)

Why: AI distinguishes between attendance commands and regular chat.

Without AI:

“in” → ✓ Check in
“Good morning” → ✗ Ignored (no exact match)
“ot” (typo for “OUT”) → ✗ Ignored

With AI:

“in” → ✓ Check in
“Good morning” → ✗ Ignored (AI recognizes it’s chat)
“ot” → ✓ AI suggests: “Please send ‘IN’ or ‘OUT’”

Get API key:

Go to OpenAI Platform
Sign up / log in
Navigate to API keys
Create new secret key
Copy and save it

Cost: GPT-4o-mini costs ~$0.15 per 1M input tokens. For attendance messages, that’s roughly $0.0001 per message (one-hundredth of a cent).

Skip AI?: Remove these nodes from the workflow:

“If message not empty”
“decide if attendance mistake message”
“If attendance mistake message”

Connect “if OUT” false branch directly to “suggest user” node.

Step 5 — Import n8n Workflow

Download the workflow JSON: attendance_template.json

In n8n:

Click Workflows → Add workflow → Import from File
Upload attendance_template.json
Workflow opens in editor

Step 6 — Configure n8n Credentials

Add Telegram credential:

Click any Telegram node
Credential for Telegram → Create New
Access Token: Paste bot token from BotFather
Test → Save

Add Google Sheets credential:

Click any Google Sheets node
Credential for Google Sheets → Create New
Sign in with Google → Allow access
Save

Add OpenAI credential (if using AI):

Click “decide if attendance mistake message” node
Credential for OpenAI → Create New
API Key: Paste OpenAI key
Save

Step 7 — Update Configuration Values

⚠️ Critical: Replace dummy values

A. Group IDs

Node: “allow only specific group”

Find and replace:

-1001234567890  →  Your actual group ID
-1009876543210  →  Delete or add second group ID

Click the node → Edit conditions → Update the rightValue fields.

B. Spreadsheet ID

Nodes to update (7 total):

get latest data for user for in
get latest data for user for OUT
insert IN
insert IN1
insert IN2
update OUT
get data with hours spent

In each node:

Click node → Document dropdown
Delete current value
Type to search → Select your spreadsheet
Sheet Name → Select “rawdata” or “parsed” as needed

Or manually replace:

REPLACE_WITH_YOUR_SPREADSHEET_ID  →  Your actual ID

Sheet mapping:

Nodes with “insert” or “update OUT” → Use rawdata (gid=0)
Nodes with “get latest” or “get data with hours” → Use parsed (gid=1)

Step 8 — Activate Workflow

Save the workflow (Ctrl+S or click Save)
Toggle Active switch in top-right (should turn green)
Execute workflow once manually to verify credentials

Step 9 — Test

In your Telegram group:

Send: IN

Expected:

Your check-in has been recorded, [YourName]

Send: OUT

Expected:

Your check-out has been recorded, [YourName]. You spent 0h 1m.

Check Google Sheets:

rawdata should have a new row with your userId, name, inTime, OutTime
parsed should show the same data with calculated fields

Test error cases:

Send: IN (while already checked in)

You are already marked as IN. Please check out before checking in again, [YourName].

Send: OUT (without checking in first)

You need to check in before you can check out, [YourName].

Send: Good morning team (with AI enabled)

(No response - AI recognizes it's not attendance)

Send: ot (with AI enabled)

Please send either "IN" or "OUT" to record your attendance, [YourName].

Workflow Logic Explained

Message Flow

Telegram Message
    ↓
Extract fields (userId, name, chatId, message, timestamp)
    ↓
Filter by allowed groups (reject if wrong group)
    ↓
Sanitize message (remove punctuation, uppercase, trim)
    ↓
┌─────────────┴──────────────┐
│                            │
Message = "IN"          Message = "OUT"
│                            │
Get user's latest status     Get user's latest status
from parsed sheet            from parsed sheet
│                            │
Check: not already IN?       Check: currently IN?
│                            │
✓ Insert to rawdata          ✓ Update OutTime in rawdata
│                            │
✓ Reply: "Checked in"        Wait 1 second (formulas calculate)
                             │
                             ✓ Read hoursSpent from parsed
                             │
                             ✓ Reply: "Checked out, spent Xh Ym"

State Validation

The workflow uses the isLatestForUser flag in the parsed sheet to track each user’s current state.

Example data:

userId     | currentStatus | isLatestForUser
-----------|---------------|----------------
123456     | OUT          | LATEST         ← User's most recent record
123456     | IN           |                ← Previous record (ignored)
789012     | IN           | LATEST         ← Another user's latest

When user sends “IN”:

Query parsed sheet for rows where userId = <user> AND isLatestForUser = "LATEST"
If no rows found → First time check-in → Allow
If row found with currentStatus = "OUT" → Last action was checkout → Allow
If row found with currentStatus = "IN" → Already checked in → Reject with error

When user sends “OUT”:

Query parsed sheet for latest record
If no rows found → Never checked in → Reject
If currentStatus = "OUT" → Already checked out → Reject
If currentStatus = "IN" → Valid checkout → Update rawdata

10-Hour Safety Window

Problem: User checks in Monday 9 AM, forgets to check out, tries to check in Tuesday 8 AM.

Without safety: Error: “Already marked as IN”

With safety (10-hour rule):

hoursSinceCheckIn = (now - lastInTime) / 3600
if (hoursSinceCheckIn > 10) {
    // Allow new check-in (day rollover assumed)
} else {
    // Reject (probable mistake)
}

Customize: Edit “If before 10 hours” node → Change 10 to your preferred value.

AI Message Classification

AI node prompt:

You are a Telegram chatbot assistant that manages attendance.

Users send messages like "in" or "out" to mark attendance.
Sometimes they make mistakes like "ot", "OUT @9", or "in noww"
  → These are *mistaken attempts* at attendance.

Other times users send *real messages* like greetings or comments
  → These should be IGNORED by the attendance bot.

Classify each message as:
- "attendance_mistake" — typo/variation of IN/OUT
- "real_message" — general conversation

Respond in JSON with type, confidence (0-1), and reason.

Examples:

“in” → Not sent to AI (already exact match)
“ot” → AI: {"type": "attendance_mistake", "confidence": 0.9}
“Good morning team” → AI: {"type": "real_message", "confidence": 0.95}
“out at 5” → AI: {"type": "attendance_mistake", "confidence": 0.85}

The workflow only responds to attendance_mistake classification.

Advanced Features

Multi-Organization Support

Use case: Track attendance for multiple companies/teams in one sheet.

Add third sheet “orgMapping”:

Unique Ids | Name      | Org
-----------|-----------|----------
123456     | John Doe  | CompanyA
789012     | Jane      | CompanyB

Benefits:

Segregate reports by organization
Single workflow for multiple teams
Easy filtering in Google Sheets

Usage:

=VLOOKUP(A2, orgMapping!A:C, 3, FALSE)

Add this to parsed sheet to auto-populate organization.

Custom Messages

Edit Telegram reply nodes to customize responses:

Check-in message (node: “reply to IN”):

✅ Welcome back, {{ $json.name }}! Your attendance has been marked.

Check-out message (node: “reply to OUT”):

👋 See you tomorrow, {{ $json.name }}! You worked {{ $json.hoursSpent }} today.

Error messages: Edit nodes like “suggest mark OUT before IN again”

Additional Commands

Add “BREAK” command:

Duplicate “if IN” node
Change condition: message = "BREAK"
Insert special marker to rawdata (e.g., inTime = 0)
Add filter in parsed sheet to exclude breaks from hours calculation

Add “STATUS” command:

Add new IF node: message = "STATUS"
Query parsed sheet for latest record
Reply with current status:

You are currently marked as {{ $json.currentStatus }}.
Last check-in: {{ $json.inTimeUTC }}

Export Automation

Daily summary via email:

Add Schedule Trigger node (every day at 6 PM)
Add Google Sheets node → Read parsed sheet (today’s date only)
Add Send Email node → Attach CSV
Send to manager/HR

Slack integration:

Add Slack node after check-in/out
Post to #attendance channel:

John Doe checked in at 9:30 AM

Troubleshooting

Bot Doesn’t Respond

Check:

Workflow is Active (green toggle)
Telegram credentials valid (test connection)
Bot added to group (check Telegram group members)
Group ID configured correctly (verify it’s negative number)

Debug:

n8n → Executions tab
Find recent execution
Check error messages

Google Sheets Not Updating

Symptoms: No new rows in rawdata after sending messages

Solutions:

Verify Google Sheets credential has write permission (not just read)
Check spreadsheet ID is correct (compare URL)
Ensure sheet names are exactly “rawdata” and “parsed” (case-sensitive)
Test manually: Click “insert IN” node → Execute node

Permission error:

Error: Insufficient Permission

Fix: Re-create Google Sheets credential → Allow all requested permissions

Wrong Timezone

Symptoms: inTimeUTC shows 5:30 hours off

Cause: Incorrect TIME() offset in formulas

Fix:

Open Google Sheets → “parsed” sheet
Find formula with TIME(5,30,0)
Change to your offset (see Step 3)
Copy formula down all rows

Verify: Check if inTimeUTC matches your local time when you sent message

AI Responds Incorrectly

Issue: Regular chat triggers attendance response

Solution 1 — Adjust AI prompt:

Edit “decide if attendance mistake message” node
Add examples to system prompt:

More examples:
- "Anyone going to lunch?" → real_message
- "I'm out" → attendance_mistake (context matters)

Solution 2 — Increase confidence threshold:

Edit “If attendance mistake message” node
Add condition: confidence > 0.8

Solution 3 — Disable AI:

Remove AI nodes entirely
Only exact “IN”/“OUT” matches trigger attendance

Duplicate Entries

Symptoms: Same check-in recorded multiple times

Cause: Race condition (user clicked multiple times fast)

Prevention: Add Merge node with “Wait for first message” mode

Cleanup:

Open rawdata sheet
Sort by userId, then inTime
Manually delete duplicates
parsed sheet auto-updates

Costs

Monthly Estimate (10 users, 22 working days)

Google Sheets: Free

Storage: Unlimited (within Google Drive quota)
API calls: Free (100 requests/100 seconds limit)

OpenAI GPT-4o-mini: ~$0.50/month

10 users × 22 days × 2 messages (IN/OUT) = 440 messages
440 × $0.0001 = $0.044/month
Add ~10x for mistakes/chat = $0.50/month

n8n: Free (self-hosted)

Cloud version: $20/month for starter plan
Self-hosted: $0 (just server costs)

Telegram: Free

Total: $0 (self-hosted n8n) or ~$20.50 (n8n cloud)

Cost Optimization

Reduce OpenAI costs:

Disable AI for small teams (just use exact matching)
Cache recent messages (avoid re-analyzing)
Use shorter prompts (fewer input tokens)

Google Sheets alternative:

Use Airtable (free tier: 1,200 records/base)
Use Supabase (PostgreSQL, free tier: 500MB)
Self-host PostgreSQL

Security Considerations

Keep Private

Telegram bot token: Never commit to git, share publicly, or expose in client-side code.

Google Sheets credential: Store securely. Anyone with this can access all sheets in your account.

OpenAI API key: Set spending limits in OpenAI dashboard ($5/month max).

Group Access Control

Problem: Anyone in Telegram group can mark attendance for themselves.

Solution: This is by design (trust-based system).

Alternative: Add authentication:

Maintain whitelist of allowed userIds in Google Sheet
Add filter node: userId in whitelist
Reject messages from unknown users

Data Privacy

What’s logged:

Telegram userId (visible to group admins anyway)
First name (public in Telegram)
Timestamps

Not logged:

Telegram username
Phone number
Message history (only current message processed)

GDPR compliance: Add data export/deletion workflow if needed.

Improvements

Better UI

Telegram Bot Commands:

/status — Show current status (IN/OUT)
/today — Show today's hours
/week — Show this week's total
/help — Show usage instructions

Implement using additional IF nodes in workflow.

Analytics Dashboard

Google Data Studio:

Connect to Google Sheets
Create dashboard:
- Total hours per user (this month)
- Average check-in time
- Late arrivals (after 10 AM)
- Overtime (>8 hours/day)

Chart types:

Bar chart: Hours by user
Line chart: Check-in time trend
Pie chart: Department-wise hours

Notifications

Late check-in alert:

// Add to workflow after check-in
if (hour > 10) {  // After 10 AM
    sendSlackMessage("#alerts", "Late check-in: ${name} at ${hour}:${minute}")
}

End of day reminder:

Schedule Trigger (every day at 7 PM)
  ↓
Query users with currentStatus = "IN"
  ↓
Send Telegram message: "Don't forget to check out!"

Mobile App

Progressive Web App (PWA):

Create simple HTML form
Submit via Telegram Bot API
Install as home screen app on mobile
No need to open Telegram group

Advantages:

Cleaner UX than Telegram
Can add features (geolocation, camera)
Still uses same n8n backend

Notes

Reliability: n8n has built-in retry logic. If Google Sheets API fails, workflow retries up to 5 times.
Scalability: Tested with 50 users. Google Sheets handles 5M cells per sheet.
Backup: Google Sheets auto-saves. Export CSV weekly for local backup.
Offline: Users can send messages offline. Telegram queues them, workflow processes when online.

Summary

You’ve built a production-ready attendance system:

✓ Telegram bot for user interface
✓ n8n workflow for automation logic
✓ Google Sheets for data storage
✓ AI-powered message filtering (optional)
✓ Smart validation (no double check-ins)
✓ Auto-calculated hours
✓ Error handling and retries
✓ Multi-user support

Use it for:

Office attendance tracking
Remote team time logging
Freelancer hour tracking
Event check-in/check-out
Any simple IN/OUT tracking need

Next steps:

Add your team members to the group
Monitor for a week, adjust messages as needed
Add analytics dashboard for insights
Extend with custom commands based on feedback

n8n template: View on n8n.io (share publicly after setup)

What#

Why#

The Problem#

This Solution#

Why These Tools?#

How#

Step 1 — Create Telegram Bot#

Step 2 — Get Group Chat ID#

Step 3 — Set Up Google Sheets#

Sheet 1: “rawdata”#

Sheet 2: “parsed”#

Step 4 — Get OpenAI API Key (Optional)#

Step 5 — Import n8n Workflow#

Step 6 — Configure n8n Credentials#

Step 7 — Update Configuration Values#

A. Group IDs#

B. Spreadsheet ID#

Step 8 — Activate Workflow#

Step 9 — Test#

Workflow Logic Explained#

Message Flow#

State Validation#

10-Hour Safety Window#

AI Message Classification#

Advanced Features#

Multi-Organization Support#

Custom Messages#

Additional Commands#

Export Automation#

Troubleshooting#

Bot Doesn’t Respond#

Google Sheets Not Updating#

Wrong Timezone#

AI Responds Incorrectly#

Duplicate Entries#

Costs#

Monthly Estimate (10 users, 22 working days)#

Cost Optimization#

Security Considerations#

Keep Private#

Group Access Control#

Data Privacy#

Improvements#

Better UI#

Analytics Dashboard#

Notifications#

Mobile App#

Notes#

Summary#