Willog Sales CRM

A Google Apps Script web application for sales lead management with Kanban board, AI assistant, and bilingual (EN/KO) support.

Project Structure

willog-sales-crm/
├── Code.gs           # Backend logic & API integrations (~2,100 lines)
├── index.html        # HTML structure with template includes (~2,100 lines)
├── styles.html       # All CSS styles (~3,600 lines)
├── scripts.html      # All JavaScript (~6,800 lines)
├── appsscript.json   # Apps Script manifest
├── .clasp.json       # Clasp configuration (scriptId)
└── .claspignore      # Files to exclude from push

Tech Stack

• Backend: Google Apps Script (V8 runtime)
• Frontend: Vanilla HTML/CSS/JavaScript (modular files)
• Database: Google Sheets (Leads, Team_Members, Activity_Log sheets)
• APIs: Gmail, Google Calendar, Google Drive, Gemini AI
• Deployment: Apps Script Web App

---

How to Recreate This Project

Prerequisites

1. Google Account with Google Workspace (for Gmail/Calendar integration)
2. Node.js installed (for clasp CLI)
3. Clasp CLI installed globally:

   npm install -g @google/clasp
   

Step 1: Create Google Sheet

1. Go to Google Sheets and create a new spreadsheet

2. Name it "Willog CRM Data" (or any name)

3. Create these sheets (tabs):

   • Leads
   • Team_Members
   • Activity_Log
   • Customers (or auto-created when first lead moves to Won)
   • Lost_Leads (or auto-created when first lead moves to Lost)

4. Leads sheet headers (Row 1):

   Lead_ID, Stage, Contact_Name, Email, Phone, Source, Assigned_Rep, Department, Company, Job_Title, Industry, Company_Size, Role_Type, Website, Location, LinkedIn_URL, Company_Notes, Deal_Name, Deal_Value, Close_Date, Win_Probability, Opportunity_Notes, Proposal_Docs, Budget_Status, Timeline, Pain_Points, Pain_Points_Notes, Current_Stack, Current_Stack_Notes, Use_Case, Objections, Created_By, Created_By_Role, Created_Date, Last_Touch_Date, Is_Archived, Archived_Date, Archived_By, Archive_Reason, Previous_Stage, Previous_Assigned_Rep
   

5. Team_Members sheet headers (Row 1):

   Member_ID, Name, Email, Role, Department
   

6. Activity_Log sheet headers (Row 1):

   Activity_ID, Lead_ID, Activity_Type, Activity_Date, Performed_By, Recipient, Subject, Gmail_Thread_ID, Calendar_Event_ID, Calendar_Event_Link, Notes, Custom_Link
   

7. Customers sheet headers (Row 1) - Auto-created on first Won deal:

   Customer_ID, Lead_ID, Won_Date, Contact_Name, Email, Phone, Source, Assigned_Rep, Department, Company, Job_Title, Industry, Company_Size, Role_Type, Website, Location, LinkedIn_URL, Company_Notes, Deal_Name, Deal_Value, Close_Date, Win_Probability, Opportunity_Notes, Proposal_Docs, Budget_Status, Timeline, Pain_Points, Pain_Points_Notes, Current_Stack, Current_Stack_Notes, Use_Case, Objections, Created_By, Created_By_Role, Created_Date, Last_Touch_Date
   

8. Lost_Leads sheet headers (Row 1) - Auto-created on first Lost deal:

   Lead_ID, Lost_Date, Lost_Reason, Stage_Before_Lost, Contact_Name, Email, Phone, Source, Assigned_Rep, Department, Company, Job_Title, Industry, Company_Size, Role_Type, Website, Location, LinkedIn_URL, Company_Notes, Deal_Name, Deal_Value, Close_Date, Win_Probability, Opportunity_Notes, Proposal_Docs, Budget_Status, Timeline, Pain_Points, Pain_Points_Notes, Current_Stack, Current_Stack_Notes, Use_Case, Objections, Created_By, Created_By_Role, Created_Date, Last_Touch_Date
   

9. Copy the Spreadsheet ID from the URL:

   https://docs.google.com/spreadsheets/d/[THIS_IS_YOUR_SHEET_ID]/edit
   

Step 2: Create Apps Script Project

1. Go to Google Apps Script
2. Click New Project
3. Name it "Willog Sales CRM"
4. Copy the Script ID from Project Settings (gear icon)

Step 3: Set Up Local Development

1. Clone or create project folder:

   mkdir willog-sales-crm
   cd willog-sales-crm
   

2. Login to clasp:

   clasp login
   

3. Create .clasp.json:

   {
     "scriptId": "YOUR_SCRIPT_ID_HERE",
     "rootDir": "."
   }
   

4. Create .claspignore:

   .git/**
   .gitignore
   node_modules/**
   *.md
   .clasp.json
   .claspignore
   *.txt
   *.rtf
   

Step 4: Configure Code.gs

Update the SHEET_ID constant in Code.gs:

const SHEET_ID = 'YOUR_SPREADSHEET_ID_HERE';

Step 5: Enable Required Services

In Apps Script editor:

1. Click Services (+ icon)
2. Add Google Calendar API (v3)
3. Add Google Drive API (v2)

Step 6: Set Up Gemini AI (Optional)

1. Go to Google AI Studio
2. Create an API key
3. In Apps Script editor:
   • Go to Project Settings (gear icon)
   • Scroll to Script Properties
   • Click Add script property
   • Property: GEMINI_API_KEY
   • Value: Your API key

Step 7: Deploy

1. Push files to Apps Script:

   clasp push
   

2. In Apps Script editor:

   • Click Deploy > New deployment
   • Type: Web app
   • Execute as: User accessing the web app
   • Who has access: Anyone within your organization (or as needed)
   • Click Deploy

3. Copy the web app URL

Step 8: First-Time Setup

1. Open the web app URL
2. Authorize the required permissions
3. Register yourself as the first team member
4. Start adding leads!

---

Data Model

Lead Fields

Core: Lead_ID, Stage, Created_By, Created_By_Role, Created_Date, Last_Touch_Date
Contact: Contact_Name, Email, Phone, Source, Assigned_Rep, Department
Company: Company, Job_Title, Industry, Company_Size, Role_Type, Website, Location, LinkedIn_URL, Company_Notes
Opportunity: Deal_Name, Deal_Value, Close_Date, Win_Probability, Opportunity_Notes, Proposal_Docs
Qualification: Budget_Status, Timeline, Pain_Points, Pain_Points_Notes, Current_Stack, Current_Stack_Notes, Use_Case, Objections
Archive: Is_Archived, Archived_Date, Archived_By, Archive_Reason, Previous_Stage, Previous_Assigned_Rep

Activity_Log Fields

Activity_ID, Lead_ID, Activity_Type, Activity_Date, Performed_By, Recipient, Subject, Gmail_Thread_ID, Calendar_Event_ID, Calendar_Event_Link, Notes, Custom_Link

Team Member Fields

Member_ID (TM-XXX), Name, Email, Role (Sales/Marketing/Admin), Department

Customers Fields (Won Deals)

Customer_ID (C-XXX), Lead_ID, Won_Date, + all Lead fields

Lost_Leads Fields

Lead_ID, Lost_Date, Lost_Reason, Stage_Before_Lost, + all Lead fields

Sales Pipeline Stages

1. Lead - Initial contact (requires Contact Info)
2. Qualification/Discovery - Requires Company Info (5 fields)
3. Proposal - Requires Qualification Info (6 fields)
4. Won - Deal closed successfully
5. Lost - Deal lost

User Roles & Permissions

Role	All Leads Tab	My Board Tab	Can Assign	Auto-Assign
Sales	View unassigned	Full access	Yes	To self
Marketing	Full access	Hidden	Yes	No
Admin	Full access	Hidden	Yes	No

Key Features

Views

• All Leads: Table view showing unassigned leads, searchable, filterable by source
• My Board: Kanban board with drag-and-drop, shows assigned rep's leads
• Dashboard: Visual analytics with KPIs and charts

AI Assistant

• Floating chat button (bottom-right corner)
• Powered by Google Gemini 2.5 Flash
• Has access to all CRM data (leads, activities, customers, team)
• Context-aware recommendations when viewing a lead

Gmail Integration

• Send emails directly from lead cards
• Auto-sync sent/received emails to Activity Log
• View email thread history in Activity panel

Google Calendar Integration

• Schedule meetings from lead cards
• Auto-sync calendar events with lead's email
• View meeting details, attendees, and Google Meet links

Activity Log

• Slide-out panel showing all interactions with a lead
• Custom activity logging (calls, meetings, notes)
• Edit/delete custom activities
• Auto-updates Last_Touch_Date

Stage Validation

• Lead → Qualification: Requires Company, Job Title, Industry, Company Size, Role Type
• Qualification → Proposal: Requires Deal Name, Deal Value, Budget Status, Timeline, Pain Points, Current Stack

Archive System

• Archive reasons: Duplicate, Not interested, Lost contact, Other
• Preserves previous stage and assigned rep for restore
• Slide-out panel with search

UI Components

• Readiness indicators (green/orange dots) on cards
• Collapsible form sections with edit/display modes
• Unsaved changes confirmation dialog
• View-only mode when viewing another rep's board
• Distinct pale pastel colors per sales rep

Clasp Commands

clasp login                    # Authenticate with Google
clasp push                     # Deploy local files to Apps Script
echo Yes | clasp push          # Push without confirmation prompt
clasp deploy -d "description"  # Create new versioned deployment
clasp deployments              # List all deployments
clasp undeploy <id>            # Delete a deployment
clasp open                     # Open Apps Script editor in browser

Code.gs Functions

User Functions

• getCurrentUser() - Returns user info from Team_Members sheet
• registerNewUser(userData) - Creates new team member
• getTeamMembers() - Returns all team members
• getSalesReps() - Returns only Sales role members

Lead Functions

• getLeads() - Returns non-archived leads
• getArchivedLeads() - Returns archived leads only
• addLead(leadData) - Creates lead with auto-filled fields
• updateLead(leadId, updates) - Updates specific fields
• assignLead(leadId, repName) - Assigns to sales rep
• deleteLead(leadId) - Permanently deletes lead
• archiveLead(leadId, reason) - Soft-deletes with reason
• restoreLead(leadId) - Restores to previous state

Activity Functions

• addActivity(activityData) - Logs activity and updates Last_Touch_Date
• getActivitiesForLead(leadId) - Returns all activities for a lead
• updateActivity(activityId, updates) - Updates existing activity
• deleteActivity(activityId) - Deletes an activity

Gmail Functions

• syncGmailSentEmails(leadId, email, sinceDate) - Syncs sent emails to Activity Log

Calendar Functions

• syncCalendarEventsForLead(leadId, email, sinceDate) - Syncs calendar events
• getCalendarEventDetails(eventId) - Gets event details (attendees, Meet link)

AI Functions

• askAI(question, leadId) - Main AI query function
• buildAIContext(leadId) - Builds context from all CRM data
• callGeminiAPI(prompt, apiKey) - Calls Gemini 2.5 Flash

Utility Functions

• generateLeadId() - Creates L-XXX format ID
• include(filename) - Includes HTML files for modular structure

File Structure Details

index.html

Main template file with HTML structure. Uses Apps Script templating:

<?!= include('styles') ?>   <!-- Includes styles.html -->
<?!= include('scripts') ?>  <!-- Includes scripts.html -->

styles.html

All CSS wrapped in <style> tags. Key variables:

--blue-accent: #2563EB    /* Primary actions */
--purple-accent: #8B5CF6  /* Toggle buttons */
--destructive: #d4183d    /* Errors/delete */
--muted-foreground: #717182

scripts.html

All JavaScript wrapped in <script> tags. Key DOM IDs:

• #allLeads / #myBoard - Main sections
• #addLeadModal - New lead form
• #leadDetailModal - Edit lead (All Leads)
• #myLeadModal - Edit lead (My Board)
• #archivePanel - Slide-out archive list
• #activityPanel - Slide-out activity log
• #aiChatPanel - AI chat interface

Internationalization (i18n)

• Languages: English (en), Korean (ko)
• Storage: localStorage.setItem('willog_lang', lang)
• Usage:
  • data-i18n="keyName" - Text content
  • data-i18n-placeholder="keyName" - Input placeholders
  • t(key) - JavaScript translation function

When adding new UI text:

1. Add data-i18n="keyName" attribute to element
2. Add key to translations.en object in scripts.html
3. Add Korean translation to translations.ko object

Development Workflow

1. Edit files locally
2. Run clasp push to deploy to Apps Script
3. Test at dev URL
4. When ready, create versioned deployment: clasp deploy -d "description"

Script URL

https://script.google.com/a/macros/willog.io/s/AKfycbwQZqtX2P1yhOWVfNq0Q5dZpWMH0WgQPQlieuYJnmbI/dev

Notes

• Use google.script.run.functionName() for backend calls
• Modular HTML approach with include() function
• Max 20 versioned deployments allowed (delete old ones as needed)
• Rep colors assigned via getRepColor(name) function
• Gemini API uses gemini-2.5-flash model