Skip to main content
Glama
Google Sheets MCP Server

Google Sheets MCP Server

by shakhin85

Google Sheets MCP Server

CI codecov Python 3.11+ License: MIT Code style: ruff

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

Related MCP server: gworkspace-mcp

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

Available Tools

29 tools
add_sheetB

Add a new sheet to an existing spreadsheet

Inputs: title (required), spreadsheet_id (required).

append_rowsC

Append rows to the end of a sheet

Inputs: range (required), values (required), spreadsheet_id (required).

auto_resize_dimensionsC

Auto-resize columns or rows to fit content

Inputs: sheet_id (required), dimension (required), end_index (required), start_index (required), +1 more.

batch_updateC

Perform multiple updates in a single request

Inputs: data (required), spreadsheet_id (required).

clear_rangeB

Clear values from a range of cells

Inputs: range (required), spreadsheet_id (required).

create_chartC

Create a chart in a spreadsheet

Inputs: sheet_id (required), chart_type (required), data_range (required), spreadsheet_id (required), +3 more.

create_named_rangeB

Create a named range in the spreadsheet

Inputs: name (required), end_row (required), sheet_id (required), start_row (required), +3 more.

create_spreadsheetC

Create a new Google Spreadsheet

Inputs: title (required), sheets.

create_tableB

Create a native Google Sheets table with defined range, columns, and formatting

Inputs: range (required), sheet_id (required), spreadsheet_id (required), columns, +1 more.

delete_chartA

Delete a chart from a spreadsheet

Inputs: chart_id (required), spreadsheet_id (required).

delete_dimensionC

Delete columns or rows

Inputs: sheet_id (required), dimension (required), end_index (required), start_index (required), +1 more.

delete_named_rangeC

Delete a named range

Inputs: named_range_id (required), spreadsheet_id (required).

delete_sheetB

Delete a sheet from a spreadsheet

Inputs: sheet_id (required), spreadsheet_id (required).

find_replaceC

Find and replace text in a sheet

Inputs: find (required), replace (required), spreadsheet_id (required), sheet_id, +3 more.

format_cellsC

Apply formatting to a range of cells

Inputs: end_row (required), sheet_id (required), start_row (required), end_column (required), +5 more.

format_cells_advancedB

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

Inputs: end_row (required), sheet_id (required), start_row (required), end_column (required), +13 more.

get_spreadsheetB

Get metadata about a spreadsheet including all sheet names

Inputs: spreadsheet_id (required).

insert_dimensionC

Insert columns or rows

Inputs: sheet_id (required), dimension (required), end_index (required), start_index (required), +1 more.

list_chartsC

List all charts in a spreadsheet

Inputs: spreadsheet_id (required), sheet_id.

list_named_rangesA

List all named ranges in a spreadsheet

Inputs: spreadsheet_id (required).

merge_cellsC

Merge cells in a range

Inputs: end_row (required), sheet_id (required), start_row (required), end_column (required), +3 more.

read_formulasB

Read formulas from cells (not just their calculated values)

Inputs: range (required), spreadsheet_id (required).

read_rangeA

Read data from a range of cells in a spreadsheet

Inputs: range (required), spreadsheet_id (required).

set_data_validationB

Set data validation rules for cells (e.g., dropdown lists, number ranges)

Inputs: end_row (required), sheet_id (required), start_row (required), end_column (required), +8 more.

sort_rangeB

Sort a range by one or more columns

Inputs: end_row (required), sheet_id (required), start_row (required), end_column (required), +3 more.

unmerge_cellsC

Unmerge cells in a range

Inputs: end_row (required), sheet_id (required), start_row (required), end_column (required), +2 more.

update_chartB

Update an existing chart in a spreadsheet

Inputs: chart_id (required), spreadsheet_id (required), title, chart_type, +2 more.

write_formulasC

Write formulas to cells

Inputs: range (required), formulas (required), spreadsheet_id (required).

write_rangeB

Write data to a range of cells in a spreadsheet

Inputs: range (required), values (required), spreadsheet_id (required).

Tool Schema Changelog

Recent tool additions, removals, and schema changes observed during successful MCP inspections.

Loading tool schema changelog…

    TDQS

    B3.1/5.0

    Scored across 29 tools

    Disambiguation5/5

    Each tool targets a distinct operation (e.g., read vs write, create vs delete, add vs delete sheet) with no overlapping purposes. Even similar tools like format_cells and format_cells_advanced are clearly differentiated by scope.

    Naming Consistency4/5

    Most tools follow a verb_noun pattern (e.g., create_spreadsheet, delete_sheet, merge_cells). Minor deviations like format_cells_advanced (adjective appended) and auto_resize_dimensions (prefix) are present but do not cause confusion.

    Tool Count4/5

    29 tools is high but justified by the rich feature set of Google Sheets. Each tool serves a clear purpose, and while some users might prefer fewer, the count is not excessive for comprehensive coverage.

    Completeness3/5

    Covers core operations (CRUD for sheets, ranges, charts, named ranges) but missing advanced features like conditional formatting, data filtering, or pivot tables. Agents can accomplish most tasks but may encounter gaps.

    Maintenance

    ActivityInactive
    ResponsivenessNo recent community issues

    Related MCP Connectors

    Related MCP Servers