Project Overview
Developed an automated CRM system in Google Sheets for a client whose sales team was drowning in manual lead management across multiple spreadsheets. The system eliminates manual tracking, automatically imports fresh data from external platforms, moves leads through the pipeline, logs all employee activity, and delivers daily performance reports without any human intervention.
The core challenge: Sales reps were spending hours daily copy-pasting new lead data from external platforms, manually updating lead statuses, moving data between sheets, and managers had zero visibility into daily activity or employee performance.
Key Features
- Automated Data Import: Eliminates daily copy-paste routine by automatically pulling fresh lead data from external platform and synchronizing it with zero manual intervention.
- Intelligent Lead Pipeline Management: Automated lead movement between "Today's Leads," "Client Potentiels," and "Closed Clients" based on status changes with daily reset functionality.
- Employee Activity Monitoring: Complete audit trail capturing every edit with employee email, timestamp, and specific action in a dedicated tracking sheet.
- Automated Daily Reporting: Daily email reports summarizing each employee's activity and lead conversion with comprehensive performance snapshots sent to management.
Technologies Used
- Google Apps Script (JavaScript): Core automation engine with onEdit() triggers, time-based triggers for daily imports, and Gmail API integration for automated reporting.
- Google Sheets: Multi-sheet architecture for organized data flow with custom formulas and user-friendly interface for sales team adoption.
- External Platform Integration: API connections for automated data retrieval with data transformation and cleaning before import.
Screenshots
Automated daily employee report generation - JavaScript code that compiles employee activity data and sends formatted email reports to management without manual intervention.
Smart lead pipeline automation - Code that automatically moves leads between 'Today_Leads', 'Client Potentials', and 'Clients' sheets based on status changes in real-time.
Live CRM interface showing automated lead data with status tracking, employee assignments, and pipeline management - all populated automatically from external data sources.
Outcome & Impact
Operational Efficiency: 100% elimination of daily copy-paste routine (saved 30+ minutes per employee daily), 80% reduction in manual data entry, and automated daily reporting from 2-hour manual process to zero effort.
Management Visibility: Real-time employee performance tracking with complete audit trail and daily automated insights without manual compilation.
Business Results: Sales team focuses entirely on selling instead of data management while management gets clear visibility into daily operations with reliable, consistent data.
Technical Achievement: Built entirely with native Google Workspace tools with seamless external platform integration, zero additional software costs, and maintainable JavaScript codebase.