Willog Sales CRM

# 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:
   ```bash
   npm install -g @google/clasp
   ```

### Step 1: Create Google Sheet

1. Go to [Google Sheets](https://sheets.google.com) 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](https://script.google.com)
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:
   ```bash
   mkdir willog-sales-crm
   cd willog-sales-crm
   ```

2. Login to clasp:
   ```bash
   clasp login
   ```

3. Create `.clasp.json`:
   ```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`:
```javascript
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](https://aistudio.google.com/apikey)
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:
   ```bash
   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

```bash
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:
```html
<?!= include('styles') ?>   <!-- Includes styles.html -->
<?!= include('scripts') ?>  <!-- Includes scripts.html -->
```

### styles.html
All CSS wrapped in `<style>` tags. Key variables:
```css
--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