Google Workspace CRM Server

A Python MCP (Model Context Protocol) server that acts as a lightweight CRM by integrating Google Sheets (contact management & generic spreadsheet ops), Gmail (email communication), and Google Docs (document creation). Designed for hosting on Replit with Streamable HTTP transport, compatible with both Claude and ChatGPT as MCP clients.

---

Features

• 30 MCP tools for full CRM + workspace workflow
• Google Sheets: CRM contacts + generic read/write/append on any range, tab management, and new spreadsheet creation
• Gmail integration via OAuth2 for sending, searching, reading emails, replying, and drafting (with SMTP and domain-wide delegation fallbacks)
• Google Docs: Create new documents, append text, read document content
• Agent Cache: Thread-safe in-memory cache with TTL expiration, LRU eviction, namespace support, and bulk operations for fast batch processing
• OAuth2 MCP endpoint protection: Secure the /mcp endpoint with Client Credentials or Authorization Code + PKCE flow — compatible with Claude's Advanced Settings
• Settings dashboard: Web-based UI for credential management, OAuth2 authorization, Google Sheets selection, and comprehensive testing
• Streamable HTTP transport for remote hosting on Replit
• Full audit logging: Every tool call is tracked through 5 stages (received, API call, Google validated, result delivered, delivery confirmed)
• Comprehensive test suite: One-click tests for Service Account, MCP Tools, Gmail Send & Reply, Cache Pipeline, and CRM Contact Management — each writes results to a dedicated Google Sheet tab
• Rate limiting & caching to stay within Google API quotas
• PostgreSQL-backed credentials: All service account keys, OAuth tokens, and settings stored securely in the database — never in code or files

---

MCP Tools (30 total)

CRM Contact Management (8 tools)

Tool	Description	R/W
crm_list_contacts	List all contacts with optional field filtering	Read
crm_get_contact	Get a single contact by email or row number	Read
crm_add_contact	Add a new contact to the CRM	Write
crm_update_contact	Update an existing contact's fields	Write
crm_delete_contact	Soft-delete a contact (moves to "Deleted" sheet)	Destructive
crm_search_contacts	Full-text search across all contact fields	Read
crm_add_note	Append a timestamped note to a contact	Write
crm_get_contact_with_emails	Get contact profile + recent email history	Read

Google Sheets Operations (7 tools)

Tool	Description	R/W
sheets_read_range	Read data from a specific A1 range	Read
sheets_write_range	Write a 2D array to a specific A1 range	Write
sheets_append_rows	Append rows to the end of a sheet tab	Write
sheets_read_all	Read all data from an entire sheet tab	Read
sheets_list_tabs	List all worksheet tabs with dimensions	Read
sheets_create_tab	Create a new worksheet tab with optional headers	Write
sheets_delete_tab	Delete a worksheet tab (permanent, requires confirm)	Destructive

Google Workspace File Creation (4 tools)

Tool	Description	R/W
sheets_create_spreadsheet	Create a brand new Google Sheets file	Write
docs_create_document	Create a new Google Doc with optional initial text	Write
docs_append_text	Append text to an existing Google Doc	Write
docs_read_document	Read the full text of a Google Doc	Read

Email Communication (7 tools)

Tool	Description	R/W
gmail_send_email	Send an email (plain text or HTML)	Write
gmail_search_emails	Search emails using Gmail query syntax	Read
gmail_get_email	Get full email content by message ID	Read
gmail_send_reply	Reply to an existing email thread	Write
gmail_draft_email	Create a draft email without sending	Write
crm_log_email_to_contact	Log an email interaction on a contact's record	Write
crm_get_contact_with_emails	Get contact profile + recent email history	Read

Agent Cache (5 tools)

Tool	Description	R/W
cache_put	Store a value in the agent cache with optional TTL and namespace	Write
cache_get	Retrieve a value from the cache	Read
cache_list	List cache keys, optionally filtered by namespace	Read
cache_delete	Delete a cache entry	Write
cache_stats	View cache statistics (entries, hits, misses, hit rate)	Read

System & Audit (4 tools)

Tool	Description	R/W
system_health_check	Test connectivity to all Google services	Read
audit_get_logs	Query the audit log (filter by tool, stage, errors)	Read
audit_trace_request	Get the full lifecycle trace of a request by ID	Read
audit_confirm_delivery	Confirm the chat agent received a tool result	Write

---

OAuth2 MCP Endpoint Security

The /mcp endpoint can be protected with OAuth2 authentication, preventing unauthorized access.

How It Works

When MCP_CLIENT_ID and MCP_CLIENT_SECRET are configured as secrets:

1. Discovery: Clients query /.well-known/oauth-authorization-server for metadata
2. Authorization: Clients redirect to /authorize with PKCE challenge
3. Token Exchange: Clients exchange the authorization code at /token for a Bearer token
4. Authenticated Access: All /mcp requests require Authorization: Bearer <token>

Without these secrets, the endpoint remains open (backward-compatible).

Supported Flows

• Authorization Code + PKCE — Used by Claude, ChatGPT, and browser-based clients
• Client Credentials — Used by server-to-server integrations

Security Features

• Credentials are only viewable/generatable in the development environment
• The live/deployed app hides Client IDs and blocks credential generation
• Tokens expire after 1 hour
• Authorization codes expire after 5 minutes and are single-use
• PKCE (S256) verification prevents code interception attacks
• Constant-time comparison (HMAC) prevents timing attacks

---

Test Endpoints

The Settings UI provides one-click test buttons for all major services:

Test	Endpoint	Steps	Description
Service Account	/api/service-account/test	3	Verifies Google API connectivity and file access
MCP Tools	/api/mcp-tools/test	8	Creates test tab, writes/reads data, cleans up
Gmail Send & Reply	/api/gmail-test/full	7	Sends email, verifies delivery, sends reply, checks thread
Cache Pipeline	/api/cache/test	7	Reads contacts, caches, creates jokes, drafts & sends email
CRM Contacts	/api/crm-contacts/test	10	Tests all 8 CRM tools, writes results to dated tab
OAuth2 Auth	Settings UI button	3	Verifies unauthenticated access is blocked

---

Audit Logging System

Every tool call is tracked through a 5-stage lifecycle:

1. REQUEST_RECEIVED   → Incoming tool invocation from the chat agent
2. API_CALL           → Each Google API / SMTP call made by the server
3. GOOGLE_VALIDATED   → Confirmation the action landed on the Google side
4. RESULT_DELIVERED   → Response payload returned to the chat agent
5. DELIVERY_CONFIRMED → (Optional) Chat agent confirmed receipt

Every tool response includes an _audit field with the request_id:

{
  "contact": { ... },
  "message": "Contact added at row 5.",
  "_audit": { "request_id": "a1b2c3d4e5f6..." }
}

---

Agent Cache System

The in-memory agent cache enables fast batch processing workflows:

• TTL Expiration: Default 30-minute TTL, configurable per entry
• LRU Eviction: Automatic eviction when cache exceeds 10,000 entries
• Namespace Support: Organize entries by namespace (e.g., contacts, emails)
• Bulk Operations: List and delete entries by namespace
• Statistics: Track hits, misses, and hit rate
• Thread-Safe: Safe for concurrent access across MCP sessions
• Global Singleton: Shared across all tool calls for cross-request persistence

---

Google Sheet Schema

Create a Google Sheet and format the first row as headers:

Column	Header	Example
A	first_name	Ali
B	last_name	Smith
C	email	[email protected]
D	phone	+1-555-0123
E	company	OPTT Health
F	role	CEO
G	status	lead
H	notes	[2025-02-19] Initial outreach...
I	created_at	2025-02-19T10:00:00Z
J	updated_at	2025-02-19T10:00:00Z

Valid status values: lead, prospect, customer, churned

Important: Share the Google Sheet with your service account email (shown in the Settings UI after uploading the service account JSON). Give it Editor access.

---

Setup Instructions

1. Google Cloud Configuration

1. Go to Google Cloud Console
2. Select the project associated with your service account
3. Enable the following APIs:
   • Google Sheets API
   • Google Drive API
   • Gmail API
   • Google Docs API
4. Download the service account JSON key (or copy the existing one)

2. Settings Dashboard (Recommended)

The easiest way to configure everything is through the Settings UI at the root path (/):

1. Upload Service Account: Upload your Google Cloud service account JSON
2. Select CRM Sheet: Browse and select a Google Sheet for CRM data
3. Configure Gmail OAuth: Upload OAuth client credentials and authorize Gmail access
4. Enable MCP Auth: Generate and configure OAuth2 credentials for endpoint protection

3. Gmail Auth — Choose Your Path

Path C — OAuth2 (Recommended):

1. Create OAuth2 credentials in Google Cloud Console (Web Application type)
2. Upload the Client ID and Secret in the Settings UI
3. Click "Authorize Gmail" and complete the browser OAuth flow
4. Tokens are stored securely in PostgreSQL and refresh automatically

Path A — Google Workspace Domain-Wide Delegation: If your Gmail is on a Workspace domain (e.g., [email protected]):

1. Go to Google Workspace Admin Console → Security → API Controls → Domain-wide Delegation
2. Add the service account client ID with Gmail scopes
3. Set [email protected]

Path B — SMTP Fallback: If you use a personal @gmail.com account:

1. Enable 2-Step Verification on your Google Account
2. Generate an App Password at https://myaccount.google.com/apppasswords
3. Set USE_SMTP_FALLBACK=true, [email protected], GMAIL_APP_PASSWORD=xxxx xxxx xxxx xxxx

4. MCP Endpoint Security (Recommended)

To protect the /mcp endpoint with OAuth2:

1. In the Settings UI (development environment), click "Generate Credentials" in the MCP Authentication card
2. Copy the generated MCP_CLIENT_ID and MCP_CLIENT_SECRET
3. Add them as Replit Secrets (Tools → Secrets)
4. Restart the server
5. In Claude's MCP connector Advanced Settings, enter the same Client ID and Client Secret

5. Replit Secrets

Add these as Replit Secrets (Tools → Secrets):

Secret	Required	Description
MCP_CLIENT_ID	Recommended	OAuth2 Client ID for MCP endpoint protection
MCP_CLIENT_SECRET	Recommended	OAuth2 Client Secret for MCP endpoint protection
MCP_SERVER_PORT	No	Default: 5000

All other credentials (service account, OAuth tokens, spreadsheet selection) are managed through the Settings UI and stored in PostgreSQL.

6. Deploy on Replit

1. Fork or import this repository into Replit
2. Configure credentials via the Settings UI
3. Click Run — the server starts on port 5000
4. On startup, connectivity checks run automatically and log results
5. Your MCP endpoint URL will be shown on the Settings dashboard

---

Connecting to MCP Clients

Claude (Web / Desktop)

1. Go to Claude Settings → MCP Servers → Add
2. Enter your MCP endpoint URL: https://your-app.replit.app/mcp
3. In Advanced Settings, enter your MCP_CLIENT_ID and MCP_CLIENT_SECRET
4. Claude will auto-discover the 30 tools

Claude Desktop (Config File)

Add to your claude_desktop_config.json:

{
  "mcpServers": {
    "google-workspace-crm": {
      "url": "https://your-app.replit.app/mcp"
    }
  }
}

Claude Code (CLI)

claude mcp add google-workspace-crm --transport http https://your-app.replit.app/mcp

ChatGPT

ChatGPT supports MCP servers via its plugin/actions system:

1. In your Custom GPT configuration, go to Actions
2. Import the MCP endpoint URL
3. ChatGPT will auto-discover the available tools via Streamable HTTP

Any MCP-Compatible Client

Endpoint: https://your-app.replit.app/mcp
Transport: Streamable HTTP
OAuth2 Metadata: https://your-app.replit.app/.well-known/oauth-authorization-server
Token Endpoint: https://your-app.replit.app/token

---

Project Structure

├── main.py                     # MCP server entry point (30 tools, OAuth2 auth, startup checks)
├── config.py                   # Environment config loader
├── requirements.txt            # Python dependencies
├── tools/
│   ├── sheets.py               # CRM tools (8) + generic sheet tools (7)
│   ├── gmail.py                # Gmail tools (5) + workflow tools (2)
│   ├── workspace.py            # Spreadsheet/Doc creation (4) + audit tools (3)
│   └── cache.py                # Agent cache tools (5)
├── services/
│   ├── google_auth.py          # Service account credential loading
│   ├── sheets_client.py        # gspread client with caching & rate limiting
│   ├── gmail_client.py         # Gmail API / SMTP client wrapper
│   ├── docs_client.py          # Google Docs + Drive API client
│   ├── cache.py                # Thread-safe agent cache (TTL, LRU, namespaces)
│   ├── mcp_auth.py             # OAuth2 auth for MCP endpoint (PKCE, tokens)
│   ├── audit_logger.py         # 5-stage audit log with ring buffer
│   └── health_check.py         # Connectivity testing for all Google services
├── web/
│   ├── routes.py               # Settings UI routes & test endpoints
│   ├── settings_store.py       # PostgreSQL-backed settings storage
│   ├── oauth.py                # Gmail OAuth2 flow handler
│   ├── sheets_browser.py       # Google Sheets browser for selection UI
│   └── templates/
│       └── settings.html       # Settings dashboard UI
└── models/
    └── contact.py              # Pydantic models

---

Rate Limits & Caching

• Google Sheets API: 60 requests/minute (gspread default) — the server enforces this with a token-bucket rate limiter at 55/min
• Sheet data is cached for 30 seconds to minimize API calls on repeated list/search operations
• Agent cache provides 30-minute TTL with 10,000 entry max and LRU eviction
• Audit log keeps last 500 entries in an in-memory ring buffer
• All timestamps are ISO 8601 UTC

---

License

MIT