Google Sheets & Apps Script MCP Server

A production-ready Model Context Protocol (MCP) server for integrating autonomous AI agents with Google Sheets and Google Apps Script.

Features

• Comprehensive Tool Set: 25+ tools for spreadsheet operations, sheet management, data validation, and Apps Script execution
• Self-Healing: Automatic error recovery with circuit breaker pattern
• Intelligent Rate Limiting: Adaptive throttling optimized for Google's free tier (60 req/min, 500 req/hour)
• Automatic Authentication: OAuth 2.0 with automatic token refresh
• Security: Credential validation, input sanitization, and audit logging
• Multiple Deployment Options: Run as stdio server or HTTP API

Quick Start

1. Prerequisites

• Node.js 18+
• Google Cloud account

2. Google Cloud Setup

1. Go to Google Cloud Console
2. Create a new project
3. Enable APIs:
   • Google Sheets API
   • Google Apps Script API
4. Configure OAuth consent screen:
   • User Type: External
   • Add your email as a test user
5. Create OAuth credentials:
   • Credentials > Create Credentials > OAuth client ID
   • Application type: Desktop app
   • Download the JSON file

3. Server Setup

# Install dependencies
npm install

# Copy credentials file (from step 2)
cp your-downloaded-credentials.json ./credentials.json

# Copy and configure environment
cp env.example .env

# Start the server
npm start

4. First-Time Authentication

On first run, the server will provide an OAuth URL. Visit the URL, authorize the application, and copy the authorization code back to complete setup.

Available Tools

Spreadsheet Operations

Tool	Description
create_spreadsheet	Create a new spreadsheet
get_spreadsheet_metadata	Get spreadsheet info and sheet list
get_values	Read values from a range
get_values_formatted	Read values with formatting
update_values	Write values to a range
append_values	Append rows to a sheet
batch_update_values	Update multiple ranges
clear_values	Clear cell values

Sheet Management

Tool	Description
create_sheet	Add a new sheet
delete_sheet	Remove a sheet
update_sheet_properties	Modify sheet settings

Formatting & Validation

Tool	Description
update_cells_format	Apply cell formatting
add_data_validation	Set data validation rules
sort_range	Sort data in a range
find_and_replace	Find and replace text
create_protected_range	Protect cells from editing

Apps Script Execution

Tool	Description
execute_script_function	Run an Apps Script function
execute_script_batch	Run multiple functions
get_script_metadata	Get script project info
list_script_deployments	List script versions

Server Management

Tool	Description
get_auth_url	Get OAuth authorization URL
set_script_id	Configure Apps Script ID
get_server_status	Check server health
reset_circuit_breaker	Reset after errors

Usage Examples

Creating a New Spreadsheet

{
  "name": "create_spreadsheet",
  "arguments": {
    "title": "My AI Trading Journal",
    "sheets": [
      { "title": "Trades", "rowCount": 10000 },
      { "title": "Analysis", "rowCount": 1000 }
    ]
  }
}

Reading Data

{
  "name": "get_values",
  "arguments": {
    "spreadsheet_id": "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms",
    "range": "Sheet1!A1:D10"
  }
}

Writing Data

{
  "name": "update_values",
  "arguments": {
    "spreadsheet_id": "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms",
    "range": "Sheet1!A1",
    "values": [["Date", "Ticker", "Action", "Price"], ["2024-01-15", "AAPL", "BUY", "185.50"]],
    "input_option": "USER_ENTERED"
  }
}

Executing Apps Script

{
  "name": "execute_script_function",
  "arguments": {
    "function_name": "calculatePortfolioReturns",
    "parameters": ["2024-01-01", "2024-12-31"],
    "dev_mode": false
  }
}

Configuration

Environment Variables

Variable	Description	Default
GOOGLE_CREDENTIALS_PATH	Path to OAuth JSON	./credentials.json
GOOGLE_TOKEN_STORAGE_PATH	Token storage file	./token.json
PORT	HTTP server port	3000
LOG_LEVEL	Logging verbosity	info
LOG_FILE	Log file path	-
STDIO_MODE	Use stdio transport	true

Rate Limiting

The server is configured for Google's free tier:

• 60 requests per minute
• 500 requests per hour
• Automatic backoff on 429 errors

Deployment

Running as a Service (Systemd)

# /etc/systemd/system/mcp-server.service
[Unit]
Description=Google Sheets MCP Server
After=network.target

[Service]
Type=simple
User=your-user
WorkingDirectory=/opt/mcp-server
ExecStart=/usr/bin/node dist/index.js
Restart=always
Environment=NODE_ENV=production

[Install]
WantedBy=multi-user.target

Docker Deployment

FROM node:18-alpine

WORKDIR /app
COPY package*.json ./
RUN npm ci --only=production

COPY . .
RUN npm run build

CMD ["node", "dist/index.js"]

# docker-compose.yml
version: '3'
services:
  mcp-server:
    build: .
    volumes:
      - ./credentials.json:/app/credentials.json
      - ./token.json:/app/token.json
      - ./logs:/app/logs
    environment:
      - NODE_ENV=production
      - LOG_LEVEL=info

Architecture

┌─────────────────────────────────────────────────────────┐
│                    MCP Client (AI)                       │
└─────────────────────────┬───────────────────────────────┘
                          │ JSON-RPC
                          ▼
┌─────────────────────────────────────────────────────────┐
│                   MCP Server                            │
│  ┌─────────────┐  ┌─────────────┐  ┌─────────────┐  │
│  │   Auth       │  │   Rate       │  │   Self-      │  │
│  │   Manager    │  │   Limiter    │  │   Healing    │  │
│  └──────┬──────┘  └──────┬──────┘  └──────┬──────┘  │
│         │                │                │            │
│  ┌──────▼───────────────▼────────────────▼──────┐    │
│  │              Google APIs                       │    │
│  │  ┌─────────────┐    ┌─────────────────┐     │    │
│  │  │ Sheets API  │    │ Apps Script API │     │    │
│  │  └─────────────┘    └─────────────────┘     │    │
│  └───────────────────────────────────────────────┘    │
└─────────────────────────────────────────────────────────┘

Security Considerations

1. Credential Storage: Store credentials outside the project directory
2. Token Management: Tokens are automatically rotated
3. Input Validation: All inputs are validated before API calls
4. Audit Logging: All operations are logged for debugging
5. HTTPS: Use HTTPS in production environments

Troubleshooting

"Authentication required"

Run the server and visit the OAuth URL provided. Authorize and enter the code.

"Rate limit exceeded"

The server automatically handles rate limits with exponential backoff. Wait a minute or reset the circuit breaker.

"Script ID not configured"

Use the set_script_id tool to configure your Apps Script project.

License

MIT