Google Sheets服务器

# Install globally
npm install -g servalsheets

# Or run directly with npx
npx servalsheets

# Claude Desktop config (~/.claude/claude_desktop_config.json)
{
  "mcpServers": {
    "servalsheets": {
      "command": "npx",
      "args": ["-y", "servalsheets"]
    }
  }
}

npm run start:http        # Start HTTP server
open http://localhost:3000/api-docs  # View Swagger UI

npm install servalsheets

{
  "mcpServers": {
    "servalsheets": {
      "command": "npx",
      "args": ["servalsheets"],
      "env": {
        "GOOGLE_APPLICATION_CREDENTIALS": "/path/to/service-account.json"
      }
    }
  }
}

# Start HTTP server
npm run start:http

# Or with environment variables
PORT=3000 GOOGLE_CLIENT_ID=xxx GOOGLE_CLIENT_SECRET=xxx npm run start:http

npm run auth
npm run start:http

export DEPLOYMENT_MODE=saas
npm run auth
npm run start:http

# 1. Clone and install
git clone https://github.com/khill1269/servalsheets.git
cd servalsheets
npm install

# 2. Create feature branch
git checkout -b fix/your-bug-name

# 3. Make changes (≤3 src/ files recommended)
# Edit src/handlers/values.ts

# 4. Verify (must pass before PR)
npm run verify

# 5. Commit and push
git commit -m "fix(values): handle empty arrays gracefully"
git push origin fix/your-bug-name

npm run verify              # Full verification pipeline
npm run check:drift         # Metadata synchronization
npm run check:placeholders  # No TODO/FIXME in src/
npm run check:silent-fallbacks  # No silent {} returns
npm run check:debug-prints  # No console.log in src/
npm test                    # Run 8,500+ tests

// Read sales data
const result = await sheets_data({
  action: 'read',
  spreadsheetId: '1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms',
  range: { a1: 'Sales!A1:D100' },
  valueRenderOption: 'FORMATTED_VALUE',
});

// Analyze data quality
const analysis = await sheets_analyze({
  action: 'analyze_quality',
  spreadsheetId: '1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms',
  range: { a1: 'Sales!A1:D100' },
});
// Returns: { completeness: 0.95, duplicates: 3, outliers: [...] }

// Preview changes first (dry run)
const preview = await sheets_data({
  action: 'write',
  spreadsheetId: '1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms',
  range: { a1: 'Data!A2:C100' },
  values: newData,
  safety: {
    dryRun: true,
    effectScope: { maxCellsAffected: 500 },
  },
});
// Returns: { dryRun: true, cellsAffected: 297 }

// Execute if safe
if (preview.data.cellsAffected < 500) {
  const result = await sheets_data({
    action: 'write',
    spreadsheetId: '1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms',
    range: { a1: 'Data!A2:C100' },
    values: newData,
    safety: {
      expectedState: { rowCount: 100 },
      autoSnapshot: true,
    },
  });
}

// Query by column header instead of A1 notation
const revenue = await sheets_data({
  action: 'read',
  spreadsheetId: '1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms',
  range: {
    semantic: {
      sheet: 'Q4 Sales',
      column: 'Total Revenue', // Matches header in row 1
      includeHeader: false,
    },
  },
});

// Returns cell values
// {
//   success: true,
//   action: 'read',
//   values: [[5000], [7500], [3200], ...]
// }

// Create a bar chart from data
const chart = await sheets_visualize({
  action: 'create',
  spreadsheetId: '1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms',
  sheetId: 0,
  chartType: 'BAR',
  title: 'Monthly Sales',
  data: { sourceRange: { a1: 'Sales!A1:B12' } },
  position: {
    anchorCell: 'Sheet1!F1',
    width: 600,
    height: 400,
  },
});

// Add conditional formatting rule
const rule = await sheets_format({
  action: 'add_conditional_format',
  spreadsheetId: '1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms',
  sheetId: 0,
  range: { a1: 'Data!B2:B100' },
  rule: {
    type: 'NUMBER_GREATER',
    values: [{ userEnteredValue: '1000' }],
  },
  format: {
    backgroundColor: { red: 0.7, green: 1, blue: 0.7 }, // Light green
  },
});

// Step 1: Generate the XLOOKUP formula
{ "action": "generate_formula", "spreadsheetId": "1BxiMVs0...", "formulaType": "xlookup",
  "description": "Look up product name in Products!A:A and return the category from Products!C:C",
  "targetCell": "D2" }

// Step 2: Write the formula to the dashboard
{ "action": "write", "spreadsheetId": "1BxiMVs0...", "range": "Dashboard!D2",
  "values": [["=XLOOKUP(A2,Products!A:A,Products!C:C,\"Unknown\")"]] }

// Step 3: Format the dashboard
{ "action": "batch_format", "spreadsheetId": "1BxiMVs0...",
  "operations": [{ "range": "Dashboard!A1:E1", "preset": "header_row" }] }

// Step 1: Scout the spreadsheet structure
{ "action": "scout", "spreadsheetId": "1BxiMVs0..." }

// Step 2: Comprehensive analysis
{ "action": "comprehensive", "spreadsheetId": "1BxiMVs0...", "range": "'Q4 Data'!A1:D100" }

// Step 3: Create the chart
{ "action": "suggest_chart", "spreadsheetId": "1BxiMVs0...", "range": "'Q4 Data'!A1:D13" }

// Step 1: Import the CSV
{ "action": "import_csv", "spreadsheetId": "1BxiMVs0...", "sheetName": "Customers",
  "csvData": "Name,Email,Revenue\nAlice,alice@co.com,5000\n..." }

// Step 2: Deduplicate on Email column
{ "action": "deduplicate", "spreadsheetId": "1BxiMVs0...", "range": "Customers!A1:C100",
  "keyColumns": ["Email"], "keep": "first" }

// Step 3: Create a table
{ "action": "create_table", "spreadsheetId": "1BxiMVs0...", "range": "Customers!A1:C50",
  "tableName": "CustomerTable", "hasHeaders": true }

// Step 1: Create a version snapshot before editing
{ "action": "version_create_snapshot", "spreadsheetId": "1BxiMVs0...",
  "name": "Pre-Q4-Budget-Edit", "description": "Snapshot before Q4 budget update" }

// Step 2: Poll until the snapshot task completes
{ "action": "version_snapshot_status", "spreadsheetId": "1BxiMVs0...",
  "taskId": "task_123" }

// Step 3: Share with the team
{ "action": "share_add", "spreadsheetId": "1BxiMVs0...",
  "emailAddress": "finance-team@company.com", "role": "writer",
  "sendNotification": true, "emailMessage": "Q4 budget ready for review" }

// Step 4: Make changes, then create another snapshot
{ "action": "version_create_snapshot", "spreadsheetId": "1BxiMVs0...",
  "name": "Post-Q4-Budget-Edit" }

// Step 1: Name a key assumptions range
{ "action": "add_named_range", "spreadsheetId": "1BxiMVs0...",
  "name": "ASSUMPTIONS",
  "range": "Model!B2:D10" }

// Step 2: Protect it before collaboration
{ "action": "add_protected_range", "spreadsheetId": "1BxiMVs0...",
  "range": "Model!B2:D10",
  "description": "Locked financial assumptions",
  "warningOnly": false }

// Step 3: Add metadata for downstream automation
{ "action": "add_developer_metadata", "spreadsheetId": "1BxiMVs0...",
  "metadataKey": "section",
  "metadataValue": "financial_assumptions",
  "visibility": "DOCUMENT" }

{
  safety: {
    dryRun: true;
  }
}

{
  safety: {
    effectScope: {
      maxCellsAffected: 5000,
      requireExplicitRange: true
    }
  }
}

{
  safety: {
    expectedState: {
      rowCount: 100,
      sheetTitle: 'Sales Data',
      checksum: 'abc123'
    }
  }
}

{
  safety: {
    autoSnapshot: true;
  }
}

// A1 notation
{ a1: "Sheet1!A1:C10" }

// Named range
{ namedRange: "SalesData" }

// Grid coordinates (0-based, end exclusive)
{ grid: { sheetId: 0, startRowIndex: 0, endRowIndex: 10, startColumnIndex: 0, endColumnIndex: 3 } }

// Semantic (header-based)
{ semantic: { sheet: "Sales", column: "Revenue", includeHeader: false } }

export GOOGLE_APPLICATION_CREDENTIALS=/path/to/service-account.json
npx servalsheets

export GOOGLE_ACCESS_TOKEN=ya29.xxx
npx servalsheets

export GOOGLE_CLIENT_ID=xxx
export GOOGLE_CLIENT_SECRET=xxx
npx servalsheets

export GOOGLE_TOKEN_STORE_PATH=~/.config/servalsheets/tokens.enc
export ENCRYPTION_KEY=<64-char-hex-key>
npx servalsheets

openssl rand -hex 32

# Required
SAML_ENTRY_POINT=https://your-idp.example.com/sso/saml
SAML_ISSUER=https://your-servalsheets.example.com
SAML_CERT=-----BEGIN CERTIFICATE-----\n...\n-----END CERTIFICATE-----
SAML_CALLBACK_URL=https://your-servalsheets.example.com/sso/callback

# Optional
SAML_PRIVATE_KEY=<your-pem-private-key>              # for signed requests
SAML_WANT_ASSERTIONS_SIGNED=true                    # default: true
SAML_SIGNATURE_ALGORITHM=sha256                     # default: sha256
SSO_JWT_TTL=3600                                    # token TTL in seconds (default: 1h)
SSO_ALLOWED_CLOCK_SKEW=300                          # clock skew tolerance in seconds

# Required Production Secrets (generate with: openssl rand -hex 32)
export JWT_SECRET=<64-char-hex-string>
export STATE_SECRET=<64-char-hex-string>
export OAUTH_CLIENT_SECRET=<64-char-hex-string>

# OAuth Security (comma-separated list of allowed callback URIs)
export ALLOWED_REDIRECT_URIS=https://your-app.com/callback,https://another-app.com/callback

# Environment mode
export NODE_ENV=production

# Install Redis dependency
npm install redis

# Configure Redis URL
export REDIS_URL=redis://localhost:6379

# Optional: Maximum sessions per user (default: 5)
export MAX_SESSIONS_PER_USER=5

# Optional: Streamable HTTP event store (resumability)
export STREAMABLE_HTTP_EVENT_TTL_MS=300000
export STREAMABLE_HTTP_EVENT_MAX_EVENTS=5000

# Configure rate limits (default: 300 reads/min, 60 writes/min)
export RATE_LIMIT_READS_PER_MINUTE=300
export RATE_LIMIT_WRITES_PER_MINUTE=60

# Enable/disable caching (default: enabled)
export CACHE_ENABLED=true

# Cache configuration
export CACHE_MAX_SIZE_MB=100
export CACHE_TTL_MS=300000  # 5 minutes

# Enable OpenTelemetry tracing
export OTEL_ENABLED=true
export OTEL_LOG_SPANS=true  # Log spans to console

# Request deduplication
export DEDUPLICATION_ENABLED=true
export DEDUP_WINDOW_MS=5000  # 5 seconds

# Log level: debug, info, warn, error (default: info)
export LOG_LEVEL=info

# Log format: json, text (default: json for production)
export LOG_FORMAT=json

# Log file path (optional, defaults to stdout)
export LOG_FILE=/var/log/servalsheets/app.log

# Google API timeout (default: 30s)
export GOOGLE_API_TIMEOUT_MS=30000

# Request timeout (default: 120s)
export REQUEST_TIMEOUT_MS=120000

# Enable/disable HTTP/2 (default: true)
export GOOGLE_API_HTTP2_ENABLED=true

# Maximum concurrent connections (default: 50)
export GOOGLE_API_MAX_SOCKETS=50

# Keep-alive timeout in milliseconds (default: 30000)
export GOOGLE_API_KEEPALIVE_TIMEOUT=30000

# Enable connection pool monitoring (default: false)
export ENABLE_HTTP2_POOL_MONITORING=true

# Monitoring interval in milliseconds (default: 300000 = 5 minutes)
export HTTP2_POOL_MONITOR_INTERVAL_MS=300000

# Enable metrics server (default: false)
export ENABLE_METRICS_SERVER=true

# Metrics server port (default: 9090)
export METRICS_PORT=9090

# Metrics server host (default: 127.0.0.1)
export METRICS_HOST=127.0.0.1

scrape_configs:
  - job_name: 'servalsheets'
    static_configs:
      - targets: ['localhost:9090']
    scrape_interval: 15s

# Prometheus format
curl http://localhost:9090/metrics

# JSON format
curl http://localhost:9090/metrics.json

# Human-readable
curl http://localhost:9090/metrics.txt

# Enable heap monitoring (default: false)
export ENABLE_HEAP_MONITORING=true

# Monitoring interval in milliseconds (default: 1800000 = 30 minutes)
export HEAP_MONITOR_INTERVAL_MS=1800000

# Warning threshold (0-1, default: 0.7 = 70%)
export HEAP_WARNING_THRESHOLD=0.7

# Critical threshold (0-1, default: 0.85 = 85%)
export HEAP_CRITICAL_THRESHOLD=0.85

# Enable heap snapshots at critical threshold (default: false)
export ENABLE_HEAP_SNAPSHOTS=true

# Heap snapshot directory (default: ./heap-snapshots)
export HEAP_SNAPSHOT_PATH=./heap-snapshots

{
  "mcpServers": {
    "servalsheets": {
      "command": "npx",
      "args": ["servalsheets"],
      "env": {
        "NODE_ENV": "production",
        "LOG_LEVEL": "info",
        "LOG_FORMAT": "json",

        "GOOGLE_APPLICATION_CREDENTIALS": "/path/to/service-account.json",
        "GOOGLE_TOKEN_STORE_PATH": "/path/to/tokens.enc",
        "ENCRYPTION_KEY": "<64-char-hex-key>"
      }
    }
  }
}

# Clone repository
git clone https://github.com/khill1269/servalsheets.git
cd servalsheets

# Install dependencies
npm install

# Build
npm run build

# Type check (strict mode)
npm run typecheck

# Run tests (8,500+ tests)
npm test

# Run in development mode
npm run dev

# Start HTTP server
npm run start:http

# Start remote server with OAuth
npm run start:remote

src/
├── schemas/           # Zod schemas for all 25 tools
├── core/              # Core infrastructure
│   ├── intent.ts          # Intent types and mappings
│   ├── batch-compiler.ts  # Compiles intents to API requests
│   ├── rate-limiter.ts    # Token bucket rate limiting
│   ├── diff-engine.ts     # Tiered diff generation
│   ├── policy-enforcer.ts # Safety policy validation
│   └── range-resolver.ts  # Semantic range resolution
├── services/          # External service integrations
│   ├── google-api.ts      # Google API client
│   └── snapshot.ts        # Backup/restore service
├── handlers/          # Tool handlers
├── server.ts          # MCP server (STDIO)
├── http-server.ts     # HTTP/SSE transport
├── oauth-provider.ts  # OAuth 2.1 for Claude Connectors
├── cli.ts             # CLI entry point
└── index.ts           # Main exports

graph LR
    A[User] -->|Natural language| B[Claude Desktop]
    B -->|MCP Protocol| C[ServalSheets MCP Server]
    C -->|Google API v4| D[Google Sheets]
    D -->|Data| C
    C -->|Structured response| B
    B -->|AI response| A

    style A fill:#e1f5ff
    style B fill:#fff3cd
    style C fill:#d4edda
    style D fill:#f8d7da

graph TB
    subgraph "ServalSheets MCP Server"
        CLI[CLI Entry Point]
        MCP[MCP Server]

        subgraph "Handlers (25 Tools)"
            H1[sheets_core]
            H2[sheets_data]
            H3[sheets_format]
            H4[... 22 more]
        end

        subgraph "Core Infrastructure"
            Intent[Intent System]
            Compiler[Batch Compiler]
            RateLimit[Rate Limiter]
            Diff[Diff Engine]
            Policy[Policy Enforcer]
            Range[Range Resolver]
        end

        subgraph "Services"
            GoogleAPI[Google API Client]
            Snapshot[Snapshot Service]
        end

        CLI --> MCP
        MCP --> H1 & H2 & H3 & H4
        H1 & H2 & H3 & H4 --> Intent
        Intent --> Compiler
        Compiler --> Policy
        Policy --> RateLimit
        RateLimit --> GoogleAPI
        GoogleAPI --> Diff
        Diff --> Range
        Range --> Snapshot
    end

    GoogleAPI -->|API Calls| Google[Google Sheets API]

    style CLI fill:#e1f5ff
    style MCP fill:#fff3cd
    style GoogleAPI fill:#d4edda
    style Google fill:#f8d7da

// Example: sheets_auth tool (4 actions)
const SheetsAuthInputSchema = z.object({
  request: z.discriminatedUnion('action', [
    z.object({ action: z.literal('status'), verbosity: VerbositySchema }),
    z.object({ action: z.literal('login'), scopes: ScopesSchema, verbosity: VerbositySchema }),
    z.object({ action: z.literal('callback'), code: z.string(), verbosity: VerbositySchema }),
    z.object({ action: z.literal('logout'), verbosity: VerbositySchema }),
  ]),
});

Tool: sheets_[category]
├─ Input: Discriminated union of action variants
├─ Output: Success/Error discriminated union
├─ Type Narrowing: Auto-generated type guards per action
└─ Handlers: Single method per action variant

// Success response
{
  success: true,
  action: 'read',
  values: [[...cell values...]],
  _meta: { requestId, duration, cacheHit, ... }
}

// Error response
{
  success: false,
  error: {
    code: 'QUOTA_EXCEEDED',
    message: '...',
    retryable: true,
    retryAfterMs: 60000,
    resolution: '...',
    resolutionSteps: ['...']
  }
}

// Error details always include field name and expected format
{
  code: 'VALIDATION_ERROR',
  message: 'Invalid spreadsheetId format',
  field: 'spreadsheetId',
  expectedFormat: 'String matching /^[a-zA-Z0-9-_]{44}$/',
  retryable: false,
  resolution: "Fix the value of 'spreadsheetId' and retry the operation.",
  resolutionSteps: [
    "1. Check the value of 'spreadsheetId'",
    "2. Ensure it matches the required format",
    "3. Expected format: String matching /^[a-zA-Z0-9-_]{44}$/"
  ]
}

// Retryable auth errors include refresh instructions
{
  code: 'TOKEN_EXPIRED',
  message: 'Access token expired',
  retryable: true,
  resolution: 'Re-authenticate and retry the operation.',
  resolutionSteps: [
    '1. Refresh your access token',
    '2. Re-authenticate if refresh fails',
    '3. Retry the operation'
  ]
}

{
  code: 'QUOTA_EXCEEDED',
  message: 'Read quota exceeded (300 reads/min)',
  retryable: true,
  retryAfterMs: 60000,           // Wait 60 seconds before retry
  retryStrategy: 'exponential_backoff',
  quotaType: 'read',             // read | write | requests | unknown
  resetTime: '2026-02-05T18:05:00Z',
  resolution: 'Wait 60 seconds, then retry with optimized batch operations',
  resolutionSteps: [
    '1. Wait 60 seconds before retrying (quota resets at 2026-02-05T18:05:00Z)',
    '2. Optimize future requests:',
    '   - Use batch operations: sheets_data action="batch_read" (saves ~80% quota)',
    '   - Use transactions: sheets_transaction (batches 10+ ops into 1 API call)',
    '   - Enable caching for repeated reads',
    '3. Increase quotas in Google Cloud Console'
  ]
}

// Before: 3 separate API calls = 3 quota units
await sheets_data({ action: 'read', range: 'A1:A100' });
await sheets_data({ action: 'read', range: 'B1:B100' });
await sheets_data({ action: 'read', range: 'C1:C100' });

// After: 1 batch API call = 1 quota unit (saves 66% quota)
await sheets_data({
  action: 'batch_read',
  ranges: ['A1:A100', 'B1:B100', 'C1:C100'],
});

{
  code: 'TRANSACTION_CONFLICT',
  conflictType: 'concurrent_modification',
  message: 'Spreadsheet was edited by another user',
  retryable: true,
  resolutionSteps: [
    '1. Fetch latest state: sheets_core action="get"',
    '2. Apply your changes to the latest version',
    '3. Use transactions for atomic updates: sheets_transaction',
    '4. Lock ranges during edit: sheets_advanced action="add_protected_range"'
  ]
}

{
  conflictType: 'stale_data',
  lastKnownVersion: 42,
  currentVersion: 45,
  resolutionSteps: [
    '1. Fetch fresh data: sheets_core action="get"',
    '2. Invalidate local cache',
    '3. Use webhooks instead of polling: sheets_webhook',
    '4. Set shorter cache TTL'
  ]
}

{
  conflictType: 'version_mismatch',
  resolutionSteps: [
    '1. Get current version: sheets_core action="get"',
    '2. Check version history: sheets_collaborate action="version_list"',
    '3. Use sheets_transaction for multi-step updates'
  ]
}

{
  conflictType: 'merge_conflict',
  resolutionSteps: [
    '1. View current state: sheets_core action="get"',
    '2. Implement resolution strategy:',
    '   - Last-write-wins: Use latest timestamp',
    '   - First-write-wins: Keep original version',
    '   - Three-way merge: Compare base, yours, theirs',
    '   - Custom: Use app-specific merge logic'
  ]
}

{
  code: 'DEADLINE_EXCEEDED',
  retryable: true,
  timeoutMs: 30000,
  operation: 'batch_read_large_range',
  resolutionSteps: [
    '1. Reduce request size by limiting rows/columns',
    '2. Split into smaller batches',
    '3. Use batch operations instead of individual requests',
    '4. Disable formula recalculation if possible',
    '5. Increase timeout setting',
    '6. Verify network connection is stable'
  ],
  retryAfterMs: 60000,
  retryStrategy: 'exponential_backoff'
}

{
  code: 'INVALID_RANGE',
  rangeInput: 'Sheet1!A1:B',  // Invalid (missing end row)
  resolutionSteps: [
    '1. Check A1 notation format: Use "Sheet1!A1:D10" or "A1:D10"',
    '2. Valid examples: "Sheet1!A1", "Sheet1!A:A" (column), "Sheet1!1:1" (row)',
    '3. Escape sheet names with spaces: "\'My Sheet\'!A1:B10"',
    '4. Verify cell coordinates are valid (column A-ZZZ, rows 1-10000000)',
    '5. Try semantic range syntax: {"semantic":{"sheet":"Sales","column":"Revenue"}}'
  ]
}

{
  code: 'SHEET_NOT_FOUND',
  sheetName: 'NonexistentSheet',
  resolutionSteps: [
    '1. List all sheets: sheets_core action="list_sheets"',
    '2. Sheet requested: "NonexistentSheet" (case-sensitive)',
    '3. Verify sheet name spelling exactly as shown in Google Sheets',
    '4. Try using sheet ID (numeric gid) instead of name'
  ]
}

{
  code: 'AMBIGUOUS_RANGE',
  resolutionSteps: [
    '1. Multiple columns match your query',
    '2. Specify exact column name or use A1 notation instead',
    '3. Use sheets_core to see all available columns'
  ]
}

{
  code: 'BATCH_UPDATE_ERROR',
  failedOperations: [
    { index: 2, error: 'Invalid range format' },
    { index: 5, error: 'Circular reference detected' },
    { index: 8, error: 'Protected range violation' }
  ],
  failureRate: '15.0%',
  resolutionSteps: [
    '1. 3 failed operations out of 20 (15.0%)',
    '2. Common issues:',
    '   - Invalid range format in operation (use "Sheet1!A1:B10")',
    '   - Sheet name mismatch (case-sensitive)',
    '   - Circular reference in formulas',
    '   - Protected ranges or sheets',
    '3. Fix each failed operation:',
    '   - Operation 2: Invalid range format',
    '   - Operation 5: Circular reference detected',
    '4. Split into smaller batches if needed (max 50 ops recommended)',
    '5. Use sheets_transaction with corrected operations'
  ]
}

// Exponential backoff with jitter
async function retryWithBackoff(operation: () => Promise<T>, maxRetries: number = 5): Promise<T> {
  for (let attempt = 0; attempt <= maxRetries; attempt++) {
    try {
      return await operation();
    } catch (error) {
      // Check if retryable
      if (!error.retryable || attempt === maxRetries) {
        throw error; // Non-retryable or final attempt
      }

      // Calculate backoff with jitter
      const baseDelay = Math.pow(2, attempt) * 1000; // 1s, 2s, 4s, 8s, ...
      const jitter = Math.random() * 0.1 * baseDelay; // 10% jitter
      const delayMs = baseDelay + jitter;

      // Check if error specifies retry delay
      const specifiedDelay = error.retryAfterMs;
      const actualDelayMs = specifiedDelay || delayMs;

      console.log(
        `Attempt ${attempt + 1}/${maxRetries + 1} failed. ` +
          `Waiting ${(actualDelayMs / 1000).toFixed(1)}s before retry...`
      );

      await new Promise((resolve) => setTimeout(resolve, actualDelayMs));
    }
  }
}

// Track errors by code and retryability
interface ErrorMetrics {
  totalErrors: number;
  errorsByCode: Record<string, number>;
  retryableCount: number;
  nonRetryableCount: number;
  successRateAfterRetry: number;
}

# Enable/disable caching (default: enabled)
export CACHE_ENABLED=true

# Cache size limit (default: 100MB)
export CACHE_MAX_SIZE_MB=100

# Cache TTL (default: 5 minutes = 300000ms)
export CACHE_TTL_MS=300000

# Cache cleanup interval (default: 5 minutes)
export CACHE_CLEANUP_INTERVAL_MS=300000

# Increase TTL to 30 minutes for stable data
export CACHE_TTL_MS=1800000
export CACHE_MAX_SIZE_MB=500  # Larger cache

# Example: Dashboard refreshing every 5 minutes
# First load: 10 API calls
# Refreshes 2-5: 0 API calls (cached)
# Result: 95% quota savings

# Disable caching for absolutely fresh data
export CACHE_ENABLED=false

# OR use webhook-based notifications instead of caching
# This is more efficient than polling with short TTLs

# Short TTL for frequently-changing data
export CACHE_TTL_MS=30000  # 30 seconds

# Use webhooks for critical updates
# Use caching for non-critical metadata

# Example: Real-time metrics with stable schema
# Metrics cache expires every 30s → fresh data
# Schema cache expires every 5 min → reduces overhead

// Access cache statistics
const stats = cacheManager.getStats();
// {
//   totalEntries: 1024,
//   totalSize: 52000000,  // 52MB
//   hits: 4850,
//   misses: 250,
//   hitRate: 0.951,       // 95.1% hit rate
//   byNamespace: {
//     'metadata': 512,
//     'values': 512
//   }
// }

// ❌ Inefficient: 3 API calls = 3 quota units
const range1 = await sheets_data({ action: 'read', range: 'Sales!A1:A100' });
const range2 = await sheets_data({ action: 'read', range: 'Sales!B1:B100' });
const range3 = await sheets_data({ action: 'read', range: 'Sales!C1:C100' });

// ✅ Efficient: 1 API call = 1 quota unit (66% quota savings)
const [range1, range2, range3] = await sheets_data({
  action: 'batch_read',
  ranges: ['Sales!A1:A100', 'Sales!B1:B100', 'Sales!C1:C100'],
});

// ❌ Inefficient: 3 API calls = 3 quota units
await sheets_data({ action: 'write', range: 'Sheet1!A1:A100', values: dataA });
await sheets_data({ action: 'write', range: 'Sheet1!B1:B100', values: dataB });
await sheets_data({ action: 'write', range: 'Sheet1!C1:C100', values: dataC });

// ✅ Efficient: 1 API call = 1 quota unit
await sheets_data({
  action: 'batch_write',
  data: [
    { range: 'Sheet1!A1:A100', values: dataA },
    { range: 'Sheet1!B1:B100', values: dataB },
    { range: 'Sheet1!C1:C100', values: dataC },
  ],
});

// Use transactions for complex multi-step operations
// Each transaction = 1 API call regardless of operation count
await sheets_transaction({
  action: 'begin',
  spreadsheetId: '...'
});

// Queue up to 50 operations
await sheets_transaction({
  action: 'queue',
  operations: [
    { type: 'insert_rows', ... },
    { type: 'format_cells', ... },
    { type: 'set_formulas', ... },
    // ... more operations
  ]
});

await sheets_transaction({
  action: 'commit'
});

// Result: 50+ operations in 1 API call

# Configure per your Google Workspace edition
# Default: 300 reads/min, 60 writes/min (Free tier)

# Business Standard: 600 reads/min, 120 writes/min
export RATE_LIMIT_READS_PER_MINUTE=600
export RATE_LIMIT_WRITES_PER_MINUTE=120

# Business Plus: 900 reads/min, 180 writes/min
export RATE_LIMIT_READS_PER_MINUTE=900
export RATE_LIMIT_WRITES_PER_MINUTE=180

# Enterprise: 1200 reads/min, 240 writes/min
export RATE_LIMIT_READS_PER_MINUTE=1200
export RATE_LIMIT_WRITES_PER_MINUTE=240

// Configuration: 300 reads/min = 5 reads/sec

// Load test 30 spreadsheets worth of data
// Without batching: 30 ops * 10 fields = 300 reads
// Rate limit: 300 reads/min = 5 reads/sec
// Time needed: 300 / 5 = 60 seconds

// With batching: 3 batch_read calls (10 ranges each)
// Time needed: 3 / 5 = 0.6 seconds

// 100x faster with same quota!

Standard rate → 429 Error → Reduce 50% (6 months) → Gradual restore → Normal rate
300 reads/min → 150 reads/min (60s) → 225 → 300

# Enable payload monitoring
export ENABLE_PAYLOAD_MONITORING=true

# Payload size warnings (default: 2MB warning, 10MB hard limit)
export PAYLOAD_WARNING_SIZE_MB=2
export PAYLOAD_MAX_SIZE_MB=10

// Monitor request/response sizes
const metrics = googleApi.getPayloadMetrics();
// {
//   largestRequest: 1500000,  // 1.5MB
//   largestResponse: 2500000, // 2.5MB
//   averageRequestSize: 45000,
//   averageResponseSize: 120000,
//   requestsAbove2MB: 3,
//   requestsAbove10MB: 0
// }

// ❌ Inefficient: Single read of entire sheet (10K rows × 100 cols)
const allData = await sheets_data({
  action: 'read',
  range: 'Sheet1!A1:CV10000',
});

// ✅ Efficient: Paginated reads
const pageSize = 100;
for (let page = 0; page < 100; page++) {
  const startRow = 1 + page * pageSize;
  const endRow = startRow + pageSize - 1;
  const pageData = await sheets_data({
    action: 'read',
    range: `Sheet1!A${startRow}:CV${endRow}`,
  });
  processPage(pageData);
}

# Enable HTTP/2 connection pooling (default: enabled)
export GOOGLE_API_HTTP2_ENABLED=true

# Max concurrent connections (default: 50)
export GOOGLE_API_MAX_SOCKETS=50

# Keep-alive timeout (default: 30 seconds)
export GOOGLE_API_KEEPALIVE_TIMEOUT=30000

# Enable pool monitoring
export ENABLE_HTTP2_POOL_MONITORING=true
export HTTP2_POOL_MONITOR_INTERVAL_MS=300000  # 5 minutes

// Automatic warnings logged every 5 minutes (if enabled)
// Example log output:
// [INFO] HTTP/2 Pool Status: 45 active, 5 free, 0 pending
// [WARN] HTTP/2 Pool: 90% utilization - consider increasing GOOGLE_API_MAX_SOCKETS

# Enable metrics server (default: disabled)
export ENABLE_METRICS_SERVER=true

# Metrics server port (default: 9090)
export METRICS_PORT=9090

# Enable tracing
export OTEL_ENABLED=true
export OTEL_LOG_SPANS=true

# Prometheus format
curl http://localhost:9090/metrics

# JSON format
curl http://localhost:9090/metrics.json

# Human-readable
curl http://localhost:9090/metrics.txt

# Health check
curl http://localhost:9090/health

# Google Sheets API timeout (default: 30 seconds)
export GOOGLE_API_TIMEOUT_MS=30000

# Request timeout (default: 120 seconds)
export REQUEST_TIMEOUT_MS=120000

[ ] Enable caching (CACHE_ENABLED=true, CACHE_TTL_MS=300000)
[ ] Use batch operations (batch_read, batch_write, transactions)
[ ] Configure rate limits per your quota (RATE_LIMIT_READS_PER_MINUTE)
[ ] Monitor payloads (<2MB typical, <10MB max)
[ ] Enable HTTP/2 (GOOGLE_API_HTTP2_ENABLED=true)
[ ] Set up metrics server (ENABLE_METRICS_SERVER=true)
[ ] Monitor cache hit rate (target: >90%)
[ ] Implement pagination for large datasets (>10K rows)
[ ] Use webhooks instead of polling (sheets_webhook)
[ ] Enable heap monitoring for long-running servers (ENABLE_HEAP_MONITORING=true)
[ ] Review slowest requests in metrics (/metrics endpoint)
[ ] Profile connection pool usage (HTTP2_POOL_MONITOR_INTERVAL_MS)
[ ] Test with your actual data volume
[ ] Measure baseline performance before optimizing
[ ] Monitor production metrics continuously

✅ sheets:///{spreadsheetId}
   └─ Spreadsheet metadata (title, sheets, properties)

✅ sheets:///{spreadsheetId}/{range}
   └─ Range values with formatting context

✅ sheets:///{spreadsheetId}/charts
   └─ All charts in spreadsheet

✅ sheets:///{spreadsheetId}/charts/{chartId}
   └─ Individual chart specification

✅ sheets:///{spreadsheetId}/pivots
   └─ Pivot table configurations

✅ sheets:///{spreadsheetId}/quality
   └─ Data quality analysis results

✅ Formulas Reference - All supported Google Sheets functions
✅ Colors Reference - RGB color codes and named colors
✅ Formats Reference - Number, date, currency formats
✅ Conditional Rules - Formatting rule types
✅ Data Validation - Validation condition types
✅ Named Ranges - Documentation and examples
✅ Charts - Chart types and configurations

✅ Create Spreadsheet - Guided sheet creation
✅ Import Data - CSV to Sheets import workflow
✅ Format Data - Formatting and styling guide
✅ Analyze Data - Data analysis workflow
✅ Create Dashboard - Dashboard creation guide
✅ Troubleshoot Issues - Error diagnosis workflow

✅ Background execution - Long-running ops don't block
✅ Progress reporting - Real-time operation status
✅ Cancellation support - AbortController integration
✅ Task store - In-memory (default) or Redis-backed
✅ Result persistence - Completed tasks accessible after execution
✅ Error propagation - Task errors returned to client

// Start background import task
const task = await sheets_composite({
  action: 'import_csv',
  spreadsheetId: '...',
  csvData: largeDataset,
  targetRange: 'Sheet1!A1',
});
// Result: { taskId: 'task-123', progress: 0, status: 'running' }

// Poll for progress
const status = await getTaskStatus(task.taskId);
// { taskId: 'task-123', progress: 45, status: 'running' }

// Wait for completion
await waitForTask(task.taskId);
// { taskId: 'task-123', progress: 100, status: 'completed', result: {...} }

✅ Confirmation requests - Ask before delete/overwrite
✅ Wizard patterns - Step-by-step guidance
✅ Effect scope validation - Warn about large operations
✅ Dry run preview - Preview changes before execution
✅ Undo/rollback - Automatic snapshots for recovery

// 1. Elicitation: Ask for confirmation
const confirm = await sheets_confirm({
  action: 'request',
  title: 'Delete 500 rows?',
  description: 'This operation will delete rows 2-501 from Sheet1',
  warning: 'This cannot be undone without using Sheets version history',
  suggestedAction: 'Create a version snapshot before proceeding',
});

// 2. User confirms or cancels

// 3. Create snapshot before destructive op
await sheets_collaborate({
  action: 'version_create_snapshot',
  description: 'Before bulk delete',
});

// 4. Execute deletion
await sheets_dimensions({
  action: 'delete_rows',
  sheetId: 0,
  startIndex: 1,
  endIndex: 501,
});

✅ Comprehensive analysis - Data quality, patterns, anomalies
✅ Pattern detection - Trends, correlations, seasonality
✅ Chart recommendations - Optimal visualizations
✅ Formula generation - Natural language → formulas
✅ Template suggestions - AI-generated spreadsheet templates
✅ Conflict detection - Concurrent modification warnings

const analysis = await sheets_analyze({
  action: 'analyze_data',
  spreadsheetId: '...',
  range: 'Sales!A1:D100',
  question: 'Calculate total revenue for Q4',
});

// Result includes:
// - Pattern analysis (seasonality, trends)
// - Anomalies detected
// - Recommended formula: =SUM(D2:D100)
// - Chart recommendations: [line_chart, bar_chart, metric_chart]

# Initial log level (default: info)
export LOG_LEVEL=info

# Change at runtime (via logging/setLevel handler)
# Useful for debugging production issues without restart
curl -X POST http://localhost:9090/logging/setLevel \
  -d '{"level":"debug"}'

✅ STDIO - For Claude Desktop, local CLI
✅ HTTP/SSE - For web clients, remote access
✅ Streamable HTTP - For large responses, resumability

# STDIO (default)
npx servalsheets

# HTTP/SSE
PORT=3000 npm run start:http

# HTTP with OAuth
PORT=3000 npm run start:remote

✅ OAuth 2.1 with PKCE
✅ Scoped permissions (per-user, per-resource)
✅ Token encryption (optional)
✅ Rate limiting (per-user)
✅ Input validation (Zod schemas)
✅ Error redaction (no token leakage)
✅ CORS configuration (production ready)
✅ HTTPS enforcement (production mode)

✅ Error handling - Comprehensive with recovery strategies
✅ Monitoring - Metrics, logging, health checks
✅ Observability - OpenTelemetry tracing
✅ High availability - Redis session store, multi-instance
✅ Performance - Batching, caching, rate limiting
✅ Security - OAuth 2.1, encryption, token rotation
✅ Testing - 8,500+ tests, coverage reported in CI
✅ Documentation - 115+ pages, examples for all tools