Google Sheets MCP Server

MCP (Model Context Protocol) server for Google Sheets integration. Allows AI assistants to read, write, and manage Google Spreadsheets.

Features

Core Features

• TOON Format - Token-Optimized Output Notation for 40-80% token savings
• Create spreadsheets - Create new Google Spreadsheets with custom sheets
• Read data - Read cell ranges and formulas from spreadsheets
• Write data - Write values and formulas to cell ranges
• Append rows - Add new rows to existing data
• Clear data - Clear cell ranges
• Manage sheets - Add, delete, and duplicate sheets within spreadsheets
• Batch updates - Perform multiple updates in one request

Formula Support

• Read formulas - Extract formulas from cells (not just calculated values)
• Write formulas - Insert formulas into cells

Named Ranges

• Create named ranges - Define named ranges for easier reference
• List named ranges - Get all named ranges in a spreadsheet
• Delete named ranges - Remove named ranges

Advanced Formatting

• Basic formatting - Bold, italic, background colors
• Text alignment - Horizontal and vertical alignment
• Fonts - Font size, font family, text color
• Number formats - Currency, percentage, date, custom formats
• Borders - Cell borders with custom styles and colors
• Text wrapping - Control how text wraps in cells

Data Management

• Data validation - Dropdown lists, number ranges, date ranges, text validation
• Find and replace - Search and replace text across sheets
• Sort ranges - Sort data by one or more columns
• Merge/unmerge cells - Merge cells and unmerge them

Column & Row Operations

• Insert columns/rows - Add new columns or rows
• Delete columns/rows - Remove columns or rows
• Auto-resize - Auto-resize columns/rows to fit content** - Perform multiple updates in one request

Installation

# Using uv (recommended)
uv sync

# Or using pip
pip install -e .

Google Cloud Setup

Option 1: OAuth 2.0 (for personal use)

1. Go to Google Cloud Console
2. Create a new project or select existing one
3. Enable the Google Sheets API:
   • Go to "APIs & Services" > "Library"
   • Search for "Google Sheets API"
   • Click "Enable"
4. Configure OAuth consent screen:
   • Go to "APIs & Services" > "OAuth consent screen"
   • Select "External" user type
   • Fill in required fields
   • Add scope: https://www.googleapis.com/auth/spreadsheets
5. Create OAuth credentials:
   • Go to "APIs & Services" > "Credentials"
   • Click "Create Credentials" > "OAuth client ID"
   • Select "Desktop app"
   • Download the JSON file
6. Save credentials:

   mkdir -p ~/.config/google-sheets-mcp
   mv ~/Downloads/client_secret_*.json ~/.config/google-sheets-mcp/credentials.json
   

Option 2: Service Account (for automated/server use)

1. Go to Google Cloud Console
2. Create a new project or select existing one
3. Enable the Google Sheets API
4. Create a Service Account:
   • Go to "APIs & Services" > "Credentials"
   • Click "Create Credentials" > "Service account"
   • Fill in details and create
5. Create a key:
   • Click on the service account
   • Go to "Keys" tab
   • "Add Key" > "Create new key" > JSON
6. Save the key:

   mkdir -p ~/.config/google-sheets-mcp
   mv ~/Downloads/*.json ~/.config/google-sheets-mcp/service_account.json
   

7. Important: Share your spreadsheets with the service account email (found in the JSON file under client_email)

Configuration

Credentials

Credentials are stored in ~/.config/google-sheets-mcp/:

File	Description
credentials.json	OAuth 2.0 client credentials
token.json	OAuth 2.0 access token (auto-generated)
service_account.json	Service account credentials

The server will automatically use service account if available, otherwise falls back to OAuth.

TOON Format (Token-Optimized Output Notation)

The server supports configurable output formats to minimize token usage when working with LLMs. Create a config.json file in ~/.config/google-sheets-mcp/:

{
  "output_format": "compact"
}

(See config.example.json for a complete example)

Available Formats:

Format	Description	Use Case	Token Savings
minimal	Absolute minimum data only	Maximum token efficiency, basic operations	~60-80%
compact	Essential data with abbreviated keys (default)	Best balance of efficiency and readability	~40-60%
standard	Readable but efficient output	Human-readable while still optimized	~20-30%
detailed	Full verbose output	Debugging, development	0% (full output)

Format Examples:

Creating a spreadsheet:

• Detailed: {"spreadsheet_id": "abc123", "spreadsheet_url": "https://...", "title": "My Sheet", "sheets": ["Sheet1", "Sheet2"]}
• Compact: {"id":"abc123","url":"https://..."}
• Minimal: {"id":"abc123"}

Reading data:

• Detailed: {"range": "Sheet1!A1:C3", "values": [[...]]}
• Compact: {"values":[[...]]}
• Minimal: {"v":[[...]]}

Environment Variable Override:

You can override the format setting using the SHEETS_MCP_FORMAT environment variable:

export SHEETS_MCP_FORMAT=minimal

Configuration in Claude Desktop:

{
  "mcpServers": {
    "google-sheets": {
      "command": "uv",
      "args": ["run", "--directory", "/path/to/google-sheets-mcp-server", "python", "main.py"],
      "env": {
        "SHEETS_MCP_FORMAT": "compact"
      }
    }
  }
}

Usage with Claude Desktop

Add to your Claude Desktop configuration (claude_desktop_config.json):

{
  "mcpServers": {
    "google-sheets": {
      "command": "uv",
      "args": ["run", "--directory", "C:\\Users\\salna\\local-mcp-servers\\google-sheets-mcp-server", "python", "main.py"]
    }
  }
}

Or if installed globally:

{
  "mcpServers": {
    "google-sheets": {
      "command": "google-sheets-mcp"
    }
  }
}

Available Tools

create_spreadsheet

Create a new Google Spreadsheet.

{
  "title": "My Spreadsheet",
  "sheets": ["Sheet1", "Data", "Summary"]
}

get_spreadsheet

Get metadata about a spreadsheet.

{
  "spreadsheet_id": "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms"
}

read_range

Read data from a range.

{
  "spreadsheet_id": "...",
  "range": "Sheet1!A1:D10"
}

write_range

Write data to a range.

{
  "spreadsheet_id": "...",
  "range": "Sheet1!A1:C3",
  "values": [
    ["Name", "Age", "City"],
    ["Alice", 30, "NYC"],
    ["Bob", 25, "LA"]
  ]
}

append_rows

Append rows to existing data.

{
  "spreadsheet_id": "...",
  "range": "Sheet1!A:C",
  "values": [
    ["Charlie", 35, "Chicago"],
    ["Diana", 28, "Boston"]
  ]
}

clear_range

Clear values from a range.

{
  "spreadsheet_id": "...",
  "range": "Sheet1!A1:D10"
}

add_sheet

Add a new sheet to spreadsheet.

{
  "spreadsheet_id": "...",
  "title": "New Sheet"
}

delete_sheet

Delete a sheet (use sheet_id from get_spreadsheet).

{
  "spreadsheet_id": "...",
  "sheet_id": 123456789
}

format_cells

Apply formatting to cells.

{
  "spreadsheet_id": "...",
  "sheet_id": 0,
  "start_row": 0,
  "end_row": 1,
  "start_column": 0,
  "end_column": 3,
  "bold": true,
  "background_color": {"red": 0.9, "green": 0.9, "blue": 0.9}
}

batch_update

Multiple updates in one request.

{
  "spreadsheet_id": "...",
  "data": [
    {"range": "Sheet1!A1", "values": [["Header"]]},
    {"range": "Sheet1!B1", "values": [["Value"]]}
  ]
}

read_formulas

Read formulas from cells (not just their calculated values).

{
  "spreadsheet_id": "...",
  "range": "Sheet1!A1:B5"
}

write_formulas

Write formulas to cells.

{
  "spreadsheet_id": "...",
  "range": "Sheet1!C1:C5",
  "formulas": [
    ["=SUM(A1:B1)"],
    ["=SUM(A2:B2)"],
    ["=SUM(A3:B3)"],
    ["=SUM(A4:B4)"],
    ["=SUM(A5:B5)"]
  ]
}

create_named_range

Create a named range for easier reference in formulas.

{
  "spreadsheet_id": "...",
  "name": "SalesData",
  "sheet_id": 0,
  "start_row": 0,
  "end_row": 10,
  "start_column": 0,
  "end_column": 5
}

list_named_ranges

List all named ranges in a spreadsheet.

{
  "spreadsheet_id": "..."
}

delete_named_range

Delete a named range.

{
  "spreadsheet_id": "...",
  "named_range_id": "..."
}

format_cells_advanced

Apply advanced formatting including alignment, fonts, borders, and number formats.

{
  "spreadsheet_id": "...",
  "sheet_id": 0,
  "start_row": 0,
  "end_row": 1,
  "start_column": 0,
  "end_column": 5,
  "horizontal_alignment": "CENTER",
  "vertical_alignment": "MIDDLE",
  "font_size": 12,
  "font_family": "Arial",
  "text_color": {"red": 0, "green": 0, "blue": 0},
  "number_format": "$#,##0.00",
  "wrap_strategy": "WRAP",
  "border_bottom": {
    "style": "SOLID",
    "color": {"red": 0, "green": 0, "blue": 0}
  }
}

set_data_validation

Set data validation rules (dropdown lists, number ranges, etc.).

{
  "spreadsheet_id": "...",
  "sheet_id": 0,
  "start_row": 1,
  "end_row": 100,
  "start_column": 2,
  "end_column": 3,
  "validation_type": "ONE_OF_LIST",
  "values": ["Option 1", "Option 2", "Option 3"],
  "show_dropdown": true,
  "strict": true
}

For number validation:

{
  "spreadsheet_id": "...",
  "sheet_id": 0,
  "start_row": 1,
  "end_row": 100,
  "start_column": 3,
  "end_column": 4,
  "validation_type": "NUMBER_BETWEEN",
  "min_value": "0",
  "max_value": "100"
}

insert_dimension

Insert columns or rows.

{
  "spreadsheet_id": "...",
  "sheet_id": 0,
  "dimension": "ROWS",
  "start_index": 5,
  "end_index": 10
}

delete_dimension

Delete columns or rows.

{
  "spreadsheet_id": "...",
  "sheet_id": 0,
  "dimension": "COLUMNS",
  "start_index": 2,
  "end_index": 4
}

auto_resize_dimensions

Auto-resize columns or rows to fit content.

{
  "spreadsheet_id": "...",
  "sheet_id": 0,
  "dimension": "COLUMNS",
  "start_index": 0,
  "end_index": 5
}

find_replace

Find and replace text in a sheet.

{
  "spreadsheet_id": "...",
  "sheet_id": 0,
  "find": "old text",
  "replace": "new text",
  "match_case": false,
  "match_entire_cell": false,
  "search_formulas": false
}

duplicate_sheet

Duplicate an existing sheet.

{
  "spreadsheet_id": "...",
  "source_sheet_id": 0,
  "new_sheet_name": "Copy of Sheet1"
}

sort_range

Sort a range by one or more columns.

{
  "spreadsheet_id": "...",
  "sheet_id": 0,
  "start_row": 1,
  "end_row": 100,
  "start_column": 0,
  "end_column": 5,
  "sort_specs": [
    {"dimension_index": 0, "ascending": true},
    {"dimension_index": 1, "ascending": false}
  ]
}

merge_cells

Merge cells in a range.

{
  "spreadsheet_id": "...",
  "sheet_id": 0,
  "start_row": 0,
  "end_row": 1,
  "start_column": 0,
  "end_column": 3,
  "merge_type": "MERGE_ALL"
}

unmerge_cells

Unmerge cells in a range.

{
  "spreadsheet_id": "...",
  "sheet_id": 0,
  "start_row": 0,
  "end_row": 1,
  "start_column": 0,
  "end_column": 3
}

Running Manually

# Run the server
uv run python main.py

# Or after installation
google-sheets-mcp

Development

# Install dev dependencies
uv sync --extra dev

# Run all tests
uv run pytest

# Run tests with verbose output
uv run pytest -v

# Run specific test file
uv run pytest tests/test_new_tools.py -v

# Run tests with coverage
uv run pytest --cov=src/google_sheets_mcp

# Type checking
uv run mypy main.py

Test Coverage

The test suite includes:

• 51 unit tests covering all 26 tools (10 original + 16 new)
• Tool execution tests - Verify each tool's logic and API calls
• TOON formatter tests - Ensure token optimization works correctly
• Mock-based testing - No real API calls required

Test files:

• tests/test_new_tools.py - Tests for all 16 new tools (25 tests)
• tests/test_formatters.py - Tests for TOON formatters (26 tests)
• tests/conftest.py - Shared fixtures and configuration

License

MIT