GitHub Copilot CLI Challenge

This is a submission for the GitHub Copilot CLI Challenge

What I Built

This is a submission for the GitHub Copilot CLI Challenge

What I Built

EmailToGSheets is an automated email processing system that reads emails from an IMAP server, extracts structured data using AI, and syncs it to Google Sheets in real-time. It's a production-grade solution designed for organizations that need to convert email communications into actionable spreadsheet data with automatic SLA (Service Level Agreement) tracking.

Key Features:

• Smart Email Processing: Automatically fetches and filters emails from specific senders using IMAP
• AI-Powered Analysis: Integrates Hugging Face AI to analyze and categorize email content into business categories (Bugs, Operation Support, Meeting, Maintenance Support)
• SLA Tracking: Automatically calculates response times between customer emails and helpdesk replies with 24-hour SLA monitoring
• Google Sheets Integration: Direct synchronization to Google Sheets with automatic OAuth2 token management and batch updates
• Smart Ticket System: Automatic ticket ID generation with date-based sequencing and thread detection
• Automated Scheduling: Built-in cron scheduler for regular processing (every 30 minutes)
• Fallback System: Manual CSV processing mode when IMAP is blocked by firewalls
• Docker Deployment: Containerized application with health checks and persistent volumes
• Token Management: Automatic OAuth2 token refresh with dedicated CLI tool for health checks
• VPS Deployment: CI/CD pipeline with GitHub Actions for automated deployment
• Smart Caching: Email cache system to avoid reprocessing and optimize performance

The Problem It Solves:

Many organizations receive structured data through email (support requests, forms, notifications) but need this data in spreadsheets for analysis, tracking, or integration with other systems. Manually copying email data to spreadsheets is time-consuming and error-prone. EmailToGSheets automates this entire workflow, ensuring data is always up-to-date, properly categorized, and includes SLA metrics for support team performance tracking.

Demo

Repository: wira-works/emailtogsheets

Architecture Overview:

Email Server (IMAP) 
    ↓
EmailReader + AI Analyzer + SLA Calculator
    ↓
CSV Writer + Cache
    ↓
Google Sheets API (Batch Updates)
    ↓
Live Spreadsheet with SLA Tracking

Quick Start Commands:

# Local Operations
npm start                 # Run email processor
npm run schedule          # Start scheduler (30-min interval)
npm run token:check       # Check OAuth token status
npm run token:refresh     # Force token refresh

# Health Monitoring
./check-health.sh         # Complete system dashboard

# VPS Deployment
npm run sync:vps          # Bidirectional sync with VPS
npm run deploy:setup      # Interactive deployment wizard

Key Components:

1. EmailReader (lib/emailReader.js): IMAP connection, email filtering, and SLA calculation
2. AIAnalyzer (lib/aiAnalyzer.js): AI-powered content extraction with fallback pattern matching
3. GoogleSheetsIntegration (lib/googleSheets.js): OAuth2 authentication, batch updates, and SLA column management
4. EmailCache (lib/emailCache.js): Ticket metadata tracking and SLA calculation caching
5. OAuth2TokenManager (lib/oauth2TokenManager.js): Automatic token refresh system
6. Scheduler (scheduler.js): Cron-based automation with configurable intervals

Production Features:

• Automated token refresh every 30 minutes
• Email caching to prevent duplicate processing
• SLA tracking with 24-hour threshold monitoring
• AI email categorization with 3-tier fallback strategy
• Comprehensive logging system
• Health check dashboard
• Backup and disaster recovery scripts
• SSH-based VPS synchronization
• Docker deployment with GitHub Container Registry (GHCR)

My Experience with GitHub Copilot CLI

GitHub Copilot CLI transformed how I built this project. It wasn't just about writing bash scripts—Copilot became my pair programming partner, helping me tackle complex business logic, AI integration, and infrastructure automation.

1. AI-Powered Email Categorization Logic

The Challenge: Build an intelligent email classifier that categorizes support tickets into 4 business categories (Bugs, Operation Support, Meeting, Maintenance Support) using both AI and fallback pattern matching.

How Copilot Helped:

When I started typing the function signature for email analysis, Copilot immediately suggested a three-tier fallback strategy that I hadn't even considered:

1. First tier: Try Hugging Face AI API for intelligent categorization
2. Second tier: Fall back to enhanced pattern matching if AI fails
3. Third tier: Simple keyword-based analysis as final fallback

The generated code included proper error handling at each level, ensuring the system would never crash even if the AI service was down. Copilot also suggested Indonesian language patterns (like "migrasi dokumen" for document migration and "bls:" for Indonesian reply prefixes) which was crucial for my business context.

Copilot's Impact:

• Suggested three-tier fallback strategy I wouldn't have thought of
• Generated Indonesian language pattern matching for local business context
• Helped structure defensive error handling with graceful degradation
• Saved 4+ hours of researching Hugging Face API integration

2. SLA (Service Level Agreement) Calculation Engine

The Challenge: Calculate response times between customer emails and helpdesk replies, tracking if responses met the 24-hour SLA, with support for email threading and escalations.

How Copilot Helped:

This was the most complex part of the project. I needed to:

• Match customer emails with helpdesk replies using email threading
• Support both English ("Re:") and Indonesian ("Bls:") reply prefixes
• Calculate accurate response times
• Handle edge cases (missing metadata, out-of-order emails)

When I wrote a comment describing what I needed, Copilot generated code that used RFC 5322 email headers (In-Reply-To and References) to properly detect email threads - something I had no idea existed! It also suggested checking both the message ID references AND subject line patterns for more reliable matching.

For the caching system, Copilot recommended a "lazy initialization" pattern that auto-creates missing metadata instead of failing, which solved a major bug where helpdesk replies processed before customer emails would crash the system.

Copilot's Impact:

• Taught me about RFC 5322 email headers for proper threading
• Suggested bilingual subject matching (English "Re:" + Indonesian "Bls:")
• Generated lazy initialization pattern preventing crashes from missing data
• Created human-readable time formatting (minutes/hours/days)
• Saved 6+ hours of email protocol research and edge case handling

3. Google Sheets Batch Updates

The Challenge: Update SLA data in Google Sheets without overwriting existing data, using batch operations for performance.

How Copilot Helped:

Initially, I was updating Google Sheets one row at a time, which was extremely slow (30+ seconds for 50 rows). When I asked Copilot how to optimize this, it suggested batch updates using the batchUpdate API.

The tricky part was converting column indices to A1 notation (like "A1:E1"). Copilot generated the perfect formula: String.fromCharCode(65 + columnIndex) to convert numbers to letters. It also showed me how to update only specific columns without touching the rest of the row, preserving existing data.

Copilot's Impact:

• Suggested batch API calls making updates 10x faster (3 seconds vs 30 seconds)
• Generated A1 notation conversion formula I would have struggled with
• Helped implement partial row updates to preserve existing data
• Saved 3+ hours of Google Sheets API documentation reading

4. Docker & VPS Deployment Automation

How Copilot Helped:

For Docker deployment, when I started writing the docker-compose.yml, Copilot suggested adding:

• Health checks using Node.js to verify token file exists
• Resource limits (CPU and memory) for production stability
• Proper volume mounts with read-only flags where appropriate
• Automatic restart policies

For the VPS sync script, Copilot generated a complete bidirectional sync solution using scp with custom SSH ports, something I would have spent hours researching. It even added colored output and confirmation prompts for safety.

The deployment script included automatic rollback on failure - backing up the current docker-compose.yml, attempting deployment, and restoring the backup if anything fails. This production-grade pattern saved me from several outages during testing.

Copilot's Impact:

• Production-grade patterns (health checks, resource limits, rollback on failure)
• Security best practices (custom SSH ports, proper file permissions)
• Defensive programming (confirmation prompts, colored output for clarity)
• Saved 5+ hours of DevOps research and testing

5. OAuth2 Token Management

How Copilot Helped:

Google's OAuth2 flow is notoriously complex. Copilot helped me:

• Understand the token refresh flow and expiry detection
• Calculate time remaining in human-readable format
• Build a CLI tool with commands like token:check, token:refresh, token:info
• Implement automatic refresh every 30 minutes to prevent expiration

The token manager Copilot helped create became one of the most-used tools during development and production monitoring.

Copilot's Impact:

• Explained OAuth2 refresh flow clearly through generated code
• Created a production-ready CLI tool for token management
• Saved 2+ hours of OAuth2 documentation reading

Overall Development Impact

Key Takeaways

1. Context-Aware Learning: Copilot understood my business requirements (SLA tracking, email threading, Indonesian language support) and suggested solutions that fit perfectly

2. Production-Quality Patterns: Instead of quick hacks, Copilot suggested proper error handling, fallback strategies, and defensive programming patterns that made the code production-ready

3. Teaching Tool: Through its suggestions, Copilot taught me about RFC 5322 email headers, Google Sheets batch operations, A1 notation, Docker best practices, and OAuth2 flows - knowledge I'll use in future projects

4. Confidence Booster: Tackled complex features (AI integration, SLA tracking, email threading) that I would have avoided or oversimplified without Copilot's guidance

5. Real-time Documentation: Instead of switching between code and documentation sites, Copilot provided working examples with proper API usage right in my editor

Would I build this without Copilot? Probably not. The combination of email protocol handling, AI integration, SLA calculations, and VPS deployment would have felt overwhelming. Copilot made it approachable by breaking down complexity into manageable, well-structured code snippets.

Most Surprising Moment: When Copilot suggested using RFC 5322 email headers for thread detection - I had no idea these headers existed, but they solved my biggest problem (matching customer emails with replies) perfectly.

Most Valuable Feature: The three-tier fallback strategy for AI analysis. This pattern made the system resilient and ensured it would always work, even when external services failed.

Project Stats:

• Lines of Code: ~3000+ (JavaScript + Shell Scripts)
• Docker: Fully containerized
• Status: Production Ready
• Development Time: 8 hours (vs 29 hours estimated without Copilot)
• Key Dependencies: googleapis, node-cron, imap, axios, huggingface

Technologies: Node.js, JavaScript, Docker, Shell Scripting, Google APIs, IMAP, OAuth2, GitHub Actions, VPS Deployment, AI/ML (Hugging Face)