# Google Sheets MCP Server

[![CI](https://github.com/YOUR_USERNAME/google-sheets-mcp-server/workflows/CI/badge.svg)](https://github.com/YOUR_USERNAME/google-sheets-mcp-server/actions/workflows/ci.yml)
[![codecov](https://codecov.io/gh/YOUR_USERNAME/google-sheets-mcp-server/branch/master/graph/badge.svg)](https://codecov.io/gh/YOUR_USERNAME/google-sheets-mcp-server)
[![Python 3.11+](https://img.shields.io/badge/python-3.11+-blue.svg)](https://www.python.org/downloads/)
[![License: MIT](https://img.shields.io/badge/License-MIT-yellow.svg)](https://opensource.org/licenses/MIT)
[![Code style: ruff](https://img.shields.io/badge/code%20style-ruff-000000.svg)](https://github.com/astral-sh/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

## Installation

```bash
# 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](https://console.cloud.google.com/)
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:
   ```bash
   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](https://console.cloud.google.com/)
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:
   ```bash
   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/`:

```json
{
  "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:

```bash
export SHEETS_MCP_FORMAT=minimal
```

**Configuration in Claude Desktop:**

```json
{
  "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`):

```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:

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

## Available Tools

### create_spreadsheet
Create a new Google Spreadsheet.
```json
{
  "title": "My Spreadsheet",
  "sheets": ["Sheet1", "Data", "Summary"]
}
```

### get_spreadsheet
Get metadata about a spreadsheet.
```json
{
  "spreadsheet_id": "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms"
}
```

### read_range
Read data from a range.
```json
{
  "spreadsheet_id": "...",
  "range": "Sheet1!A1:D10"
}
```

### write_range
Write data to a range.
```json
{
  "spreadsheet_id": "...",
  "range": "Sheet1!A1:C3",
  "values": [
    ["Name", "Age", "City"],
    ["Alice", 30, "NYC"],
    ["Bob", 25, "LA"]
  ]
}
```

### append_rows
Append rows to existing data.
```json
{
  "spreadsheet_id": "...",
  "range": "Sheet1!A:C",
  "values": [
    ["Charlie", 35, "Chicago"],
    ["Diana", 28, "Boston"]
  ]
}
```

### clear_range
Clear values from a range.
```json
{
  "spreadsheet_id": "...",
  "range": "Sheet1!A1:D10"
}
```

### add_sheet
Add a new sheet to spreadsheet.
```json
{
  "spreadsheet_id": "...",
  "title": "New Sheet"
}
```

### delete_sheet
Delete a sheet (use sheet_id from get_spreadsheet).
```json
{
  "spreadsheet_id": "...",
  "sheet_id": 123456789
}
```

### format_cells
Apply formatting to cells.
```json
{
  "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.
```json
{
  "spreadsheet_id": "...",
  "data": [
    {"range": "Sheet1!A1", "values": [["Header"]]},
    {"range": "Sheet1!B1", "values": [["Value"]]}
  ]
}
```

### read_formulas
Read formulas from cells (not just their calculated values).
```json
{
  "spreadsheet_id": "...",
  "range": "Sheet1!A1:B5"
}
```

### write_formulas
Write formulas to cells.
```json
{
  "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.
```json
{
  "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.
```json
{
  "spreadsheet_id": "..."
}
```

### delete_named_range
Delete a named range.
```json
{
  "spreadsheet_id": "...",
  "named_range_id": "..."
}
```

### format_cells_advanced
Apply advanced formatting including alignment, fonts, borders, and number formats.
```json
{
  "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.).
```json
{
  "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:
```json
{
  "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.
```json
{
  "spreadsheet_id": "...",
  "sheet_id": 0,
  "dimension": "ROWS",
  "start_index": 5,
  "end_index": 10
}
```

### delete_dimension
Delete columns or rows.
```json
{
  "spreadsheet_id": "...",
  "sheet_id": 0,
  "dimension": "COLUMNS",
  "start_index": 2,
  "end_index": 4
}
```

### auto_resize_dimensions
Auto-resize columns or rows to fit content.
```json
{
  "spreadsheet_id": "...",
  "sheet_id": 0,
  "dimension": "COLUMNS",
  "start_index": 0,
  "end_index": 5
}
```

### find_replace
Find and replace text in a sheet.
```json
{
  "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.
```json
{
  "spreadsheet_id": "...",
  "source_sheet_id": 0,
  "new_sheet_name": "Copy of Sheet1"
}
```

### sort_range
Sort a range by one or more columns.
```json
{
  "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.
```json
{
  "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.
```json
{
  "spreadsheet_id": "...",
  "sheet_id": 0,
  "start_row": 0,
  "end_row": 1,
  "start_column": 0,
  "end_column": 3
}
```

## Running Manually

```bash
# Run the server
uv run python main.py

# Or after installation
google-sheets-mcp
```

## Development

```bash
# 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