Google Sheets MCP Server

Create a powerful Model Context Protocol (MCP) server for Google Sheets in minutes with our AI Gateway. This guide walks you through setting up seamless spreadsheet automation with enterprise-grade security and instant OAuth authentication.

About Google Sheets API

The Google Sheets API provides programmatic access to create, read, update, and format spreadsheets. It enables powerful automation for data management, analysis, reporting, and collaborative workflows across your organization.

Key Capabilities

• Spreadsheet Management: Create, copy, and delete spreadsheets
• Data Operations: Read, write, and update cell values
• Formatting Control: Apply styles, colors, and borders
• Formula Support: Create and evaluate formulas
• Charts & Pivots: Generate visualizations
• Data Validation: Set rules and dropdowns
• Batch Operations: Multiple updates in one request
• Import/Export: CSV, Excel, PDF formats

API Features

• REST API v4: Modern spreadsheet operations
• Real-time Collaboration: Live updates
• Batch Requests: Efficient bulk operations
• OAuth 2.0: Secure authentication
• Named Ranges: Reference data regions
• Protected Ranges: Control edit access
• Conditional Formatting: Dynamic styling
• Filter Views: Custom data views

What You Can Do with Google Sheets MCP Server

The MCP server transforms Google Sheets API into a natural language interface, enabling AI agents to:

Data Management

• Spreadsheet Operations

  • "Create expense tracking spreadsheet"
  • "Copy template for Q1 planning"
  • "Import CSV sales data"
  • "Archive old project sheets"

• Data Entry & Updates

  • "Add new row with order details"
  • "Update cell B5 with latest revenue"
  • "Insert column for new metric"
  • "Append data from form submission"

• Bulk Operations

  • "Import 1000 rows from database"
  • "Update all prices by 10%"
  • "Clear data older than 90 days"
  • "Copy values to another sheet"

Data Analysis

• Formulas & Calculations

  • "Calculate sum of sales column"
  • "Add VLOOKUP for customer names"
  • "Create running total column"
  • "Apply growth rate formula"

• Data Aggregation

  • "Summarize by department"
  • "Calculate monthly averages"
  • "Find top 10 performers"
  • "Create year-over-year comparison"

• Statistical Analysis

  • "Calculate standard deviation"
  • "Perform regression analysis"
  • "Create correlation matrix"
  • "Generate trend analysis"

Formatting & Visualization

• Cell Formatting

  • "Format currency in column C"
  • "Apply conditional colors to KPIs"
  • "Bold header row"
  • "Set date format to MM/DD/YYYY"

• Conditional Formatting

  • "Highlight values above target"
  • "Color scale for performance"
  • "Mark duplicates in red"
  • "Format based on date ranges"

• Charts & Graphs

  • "Create pie chart of expenses"
  • "Build line graph of trends"
  • "Generate bar chart comparison"
  • "Design dashboard with charts"

Data Validation & Protection

• Validation Rules

  • "Create dropdown for status column"
  • "Set number range 0-100"
  • "Require email format"
  • "Add date picker"

• Range Protection

  • "Protect formula columns"
  • "Lock header rows"
  • "Set edit permissions by user"
  • "Create read-only ranges"

• Data Integrity

  • "Prevent duplicate entries"
  • "Validate against list"
  • "Check data consistency"
  • "Enforce required fields"

Filtering & Sorting

• Filter Operations

  • "Show only active projects"
  • "Filter sales above $10,000"
  • "Display this month's data"
  • "Hide completed tasks"

• Sorting Functions

  • "Sort by date descending"
  • "Order by priority then date"
  • "Alphabetize customer list"
  • "Rank by performance score"

• Filter Views

  • "Create manager dashboard view"
  • "Save filter for weekly report"
  • "Share custom view with team"
  • "Switch between saved filters"

Pivot Tables & Reports

• Pivot Table Creation

  • "Summarize sales by region"
  • "Create expense breakdown"
  • "Analyze by multiple dimensions"
  • "Calculate percentage of total"

• Report Generation

  • "Generate monthly summary"
  • "Create executive dashboard"
  • "Build KPI scorecard"
  • "Design financial report"

• Dynamic Reports

  • "Update pivot with new data"
  • "Refresh calculated fields"
  • "Adjust date ranges"
  • "Modify grouping levels"

Collaboration Features

• Sharing & Permissions

  • "Share with team as editors"
  • "Create view-only link"
  • "Set expiration date"
  • "Transfer ownership"

• Comments & Notes

  • "Add comment to cell A1"
  • "Create note with instructions"
  • "Reply to comment thread"
  • "Resolve completed discussions"

• Version Control

  • "View revision history"
  • "Restore previous version"
  • "Track changes by user"
  • "Compare versions"

Prerequisites

• Access to Cequence AI Gateway
• Google Workspace account
• Google Sheets API enabled
• Appropriate Drive permissions

Step 1: Enable Google Sheets API

1.1 Access Google Cloud Console

1. Go to console.cloud.google.com
2. Select or create a project
3. Enable billing if required

1.2 Enable APIs

1. Navigate to APIs & Services Library
2. Search and enable:
   • Google Sheets API
   • Google Drive API (for file operations)

1.3 Create OAuth 2.0 Credentials

1. Go to APIs & Services Credentials
2. Click Create Credentials OAuth client ID
3. Configure consent screen if needed
4. Application type: Web application
5. Add redirect URI:

   https://auth.aigateway.cequence.ai/v1/outbound/oauth/callback

1.4 Get Credentials

1. Copy Client ID
2. Copy Client Secret
3. Note authorized redirect URIs

Step 2-4: Standard Setup

Follow standard steps to access AI Gateway, find Google Sheets API, and create MCP server.

Step 5: Configure API Endpoints

1. Base URL: https://sheets.googleapis.com/v4
2. Select endpoints:
   • Spreadsheet values endpoints
   • Batch update endpoints
   • Properties endpoints
3. Click Next

Step 6: MCP Server Configuration

1. Name: "Google Sheets Automation"
2. Description: "Spreadsheet data management and analysis"
3. Configure production mode
4. Click Next

Step 7: Configure Authentication

1. Authentication Type: OAuth 2.0
2. Authorization URL:

   https://accounts.google.com/o/oauth2/v2/auth

3. Token URL:

   https://oauth2.googleapis.com/token

4. Enter Client ID and Secret
5. Select required scopes

Available Google Sheets OAuth Scopes

Spreadsheet Access

• https://www.googleapis.com/auth/spreadsheets

  • Full read/write access
  • Create new spreadsheets
  • Update existing sheets
  • Delete spreadsheets

• https://www.googleapis.com/auth/spreadsheets.readonly

  • Read spreadsheet data
  • View formatting
  • Access formulas
  • Export data

Drive Integration

• https://www.googleapis.com/auth/drive.file

  • Access files created by app
  • Required for creating sheets
  • Manage file metadata
  • Organize in folders

• https://www.googleapis.com/auth/drive

  • Full Drive access
  • Access all spreadsheets
  • Move and copy files
  • Share permissions

Additional Scopes

• https://www.googleapis.com/auth/drive.metadata.readonly
  • View file metadata
  • List spreadsheets
  • Check sharing status
  • See revision info

Recommended Scope Combinations

For Data Analysis:

https://www.googleapis.com/auth/spreadsheets.readonly
https://www.googleapis.com/auth/drive.metadata.readonly

For Full Automation:

https://www.googleapis.com/auth/spreadsheets
https://www.googleapis.com/auth/drive.file

For Complete Access:

https://www.googleapis.com/auth/spreadsheets
https://www.googleapis.com/auth/drive

Step 8-10: Complete Setup

Configure security, choose deployment, and deploy.

Using Your Google Sheets MCP Server

With Claude Desktop

{
  "servers": {
    "google-sheets": {
      "url": "your-mcp-server-url",
      "auth": {
        "type": "oauth2",
        "client_id": "your-client-id"
      }
    }
  }
}

Natural Language Commands

• "Create monthly budget spreadsheet"
• "Add sales data to tracking sheet"
• "Calculate total revenue for Q4"
• "Format expense report with conditional colors"
• "Generate chart of monthly trends"

API Integration Example

// Initialize MCP client
const mcpClient = new MCPClient({
  serverUrl: 'your-mcp-server-url',
  auth: {
    type: 'oauth2',
    token: 'access-token'
  }
});

// Create new spreadsheet
const spreadsheet = await mcpClient.googleSheets.create({
  properties: {
    title: 'Sales Tracking 2025',
    locale: 'en_US',
    timeZone: 'America/New_York'
  },
  sheets: [
    {
      properties: {
        title: 'Q1 Sales',
        gridProperties: {
          rowCount: 1000,
          columnCount: 26
        }
      }
    }
  ]
});

// Batch update values
const values = [
  ['Date', 'Product', 'Quantity', 'Price', 'Total'],
  ['2025-01-15', 'Widget A', 10, 29.99, '=C2*D2'],
  ['2025-01-16', 'Widget B', 5, 49.99, '=C3*D3'],
  ['2025-01-17', 'Widget A', 15, 29.99, '=C4*D4']
];

await mcpClient.googleSheets.values.batchUpdate({
  spreadsheetId: spreadsheet.spreadsheetId,
  valueInputOption: 'USER_ENTERED',
  data: [
    {
      range: 'Q1 Sales!A1:E4',
      values: values
    }
  ]
});

// Apply formatting
const requests = [
  // Format header row
  {
    repeatCell: {
      range: {
        sheetId: 0,
        startRowIndex: 0,
        endRowIndex: 1
      },
      cell: {
        userEnteredFormat: {
          backgroundColor: { red: 0.2, green: 0.3, blue: 0.8 },
          textFormat: { bold: true, foregroundColor: { red: 1, green: 1, blue: 1 } }
        }
      },
      fields: 'userEnteredFormat(backgroundColor,textFormat)'
    }
  },
  // Format currency columns
  {
    repeatCell: {
      range: {
        sheetId: 0,
        startRowIndex: 1,
        startColumnIndex: 3,
        endColumnIndex: 5
      },
      cell: {
        userEnteredFormat: {
          numberFormat: {
            type: 'CURRENCY',
            pattern: '$#,##0.00'
          }
        }
      },
      fields: 'userEnteredFormat.numberFormat'
    }
  },
  // Add conditional formatting
  {
    addConditionalFormatRule: {
      rule: {
        ranges: [{
          sheetId: 0,
          startRowIndex: 1,
          startColumnIndex: 4,
          endColumnIndex: 5
        }],
        booleanRule: {
          condition: {
            type: 'NUMBER_GREATER',
            values: [{ userEnteredValue: '500' }]
          },
          format: {
            backgroundColor: { red: 0.8, green: 1, blue: 0.8 }
          }
        }
      }
    }
  }
];

await mcpClient.googleSheets.batchUpdate({
  spreadsheetId: spreadsheet.spreadsheetId,
  requests: requests
});

// Create chart
const chartRequest = {
  addChart: {
    chart: {
      spec: {
        title: 'Sales Trend',
        basicChart: {
          chartType: 'LINE',
          legendPosition: 'BOTTOM_LEGEND',
          axis: [
            { position: 'BOTTOM_AXIS', title: 'Date' },
            { position: 'LEFT_AXIS', title: 'Revenue' }
          ],
          series: [
            {
              series: {
                sourceRange: {
                  sources: [{
                    sheetId: 0,
                    startRowIndex: 1,
                    startColumnIndex: 4,
                    endColumnIndex: 5
                  }]
                }
              }
            }
          ]
        }
      },
      position: {
        overlayPosition: {
          anchorCell: { sheetId: 0, rowIndex: 6, columnIndex: 0 }
        }
      }
    }
  }
};

await mcpClient.googleSheets.batchUpdate({
  spreadsheetId: spreadsheet.spreadsheetId,
  requests: [chartRequest]
});

// Create pivot table
const pivotRequest = {
  updateCells: {
    rows: [
      {
        values: [
          {
            pivotTable: {
              source: {
                sheetId: 0,
                startRowIndex: 0,
                startColumnIndex: 0,
                endColumnIndex: 5
              },
              rows: [
                {
                  sourceColumnOffset: 1,
                  showTotals: true,
                  sortOrder: 'ASCENDING'
                }
              ],
              values: [
                {
                  summarizeFunction: 'SUM',
                  sourceColumnOffset: 4
                }
              ]
            }
          }
        ]
      }
    ],
    start: {
      sheetId: 0,
      rowIndex: 20,
      columnIndex: 0
    },
    fields: 'pivotTable'
  }
};

await mcpClient.googleSheets.batchUpdate({
  spreadsheetId: spreadsheet.spreadsheetId,
  requests: [pivotRequest]
});

Common Use Cases

Data Collection

• Form responses processing
• Survey data aggregation
• IoT data logging
• Web scraping storage

Financial Management

• Budget tracking
• Expense reports
• Invoice generation
• Financial dashboards

Project Management

• Task tracking
• Resource allocation
• Timeline management
• Status reporting

Analytics & Reporting

• KPI dashboards
• Sales analytics
• Performance metrics
• Custom reports

Security Best Practices

1. OAuth Security:

   • Use minimal scopes
   • Implement token refresh
   • Monitor access logs
   • Revoke unused tokens

2. Data Protection:

   • Protect sensitive ranges
   • Control sharing permissions
   • Audit data access
   • Enable version history

3. Formula Security:

   • Validate formula inputs
   • Avoid IMPORTRANGE abuse
   • Check external references
   • Monitor computation usage

Troubleshooting

Common Issues

1. Authentication Errors

   • Verify OAuth credentials
   • Check token expiration
   • Validate redirect URI
   • Review consent screen

2. API Errors

   • Check range syntax (A1 notation)
   • Verify sheet exists
   • Validate value types
   • Review quota limits

3. Formula Errors

   • Check formula syntax
   • Verify cell references
   • Test in UI first
   • Review calculation settings

Getting Help

• Documentation: AI Gateway Docs
• Support: support@cequence.ai
• Google Sheets API: developers.google.com/sheets/api

---