gsheets-mcp by jayeshchowdary - MCP Server

Google Sheets MCP Server

A Model Context Protocol (MCP) server that provides tools for interacting with Google Sheets and Google Drive using OAuth2 authentication.

Features

Authentication: OAuth2-based authentication with Google APIs
Google Sheets: Create, read, update, and delete spreadsheets and worksheets
Data Operations: Batch operations and data filtering
Formatting: Cell formatting, charts, and conditional formatting
Pagination: Smart pagination for large datasets with max limits and smooth navigation

Prerequisites

Python 3.12+
UV package manager
Google Cloud Project with Google Sheets API enabled
OAuth2 credentials from Google Cloud Console

Quick Start

1. Clone and Setup

# Clone the repository
git clone https://github.com/jayeshchowdary/gsheets-mcp
cd gsheets-mcp

# Install dependencies with UV
uv sync

2. Authentication Setup

Download your credentials.json from Google Cloud Console
Place it in the project directory
Run authentication:
```
uv run authenticate.py
```
Follow the prompts to complete OAuth2 authentication

3. Start the MCP Server

# Start the server
uv run simplemcp.py

4. Test with MCP Inspector

# In another terminal, start the inspector
npx @modelcontextprotocol/inspector

# Connect to your server using the inspector interface
# Command: uv
# Args: run simplemcp.py
# Working Directory: . (current directory)

Project Structure

gsheets-mcp/
├── pyproject.toml          # UV project configuration
├── uv.lock                 # Locked dependencies
├── simplemcp.py            # Main MCP server
├── authenticate.py         # Authentication script
├── credentials.json        # OAuth2 credentials (not in git)
├── .token.json            # Authentication token (not in git)
├── mcp-server-config.json # MCP client configuration
└── README.md              # This file

Available Tools

Spreadsheet Management

list_sheets - List Google Sheets in your Drive with pagination support
create_google_sheet - Create a new Google Sheet
delete_spreadsheet - Delete a Google Sheet
get_spreadsheet_info - Get spreadsheet metadata

Worksheet Management

add_worksheet - Add a new worksheet to a spreadsheet
delete_sheet - Delete a worksheet from a spreadsheet
get_sheet_names - Get worksheet names with pagination support
find_worksheet_by_title - Find worksheet by exact title
copy_sheet_to_another_spreadsheet - Copy sheet between spreadsheets

Data Operations

get_cell_data - Get data from specific cells
update_sheet_data - Update cell data
append_values_to_spreadsheet - Append data to spreadsheet
batch_update_by_filter - Update values by data filter
execute_sql_query - Execute SQL-like queries on sheets

Data Filtering

get_spreadsheet_by_data_filter - Get data using filters
batch_get_spreadsheet_values_by_data_filter - Get values by data filter
batch_clear_spreadsheet_values - Clear multiple ranges
batch_clear_values_by_data_filter - Clear values by data filter

Table Operations

list_tables - List tables in a spreadsheet with pagination support
get_table_schema - Get table structure and schema
create_sheet_from_json - Create sheet from JSON data

Formatting and Charts

format_cell - Apply cell formatting
create_chart - Create charts in sheets
set_basic_filter - Set basic filters
clear_basic_filter - Clear basic filters

Dimension Management

append_dimension - Add rows/columns
insert_dimension - Insert rows/columns at specific positions
delete_dimension - Delete rows/columns
create_spreadsheet_column - Create new columns
create_spreadsheet_row - Create new rows

Developer Metadata

create_developer_metadata - Create metadata
search_developer_metadata - Search metadata
delete_developer_metadata - Delete metadata

Properties Management

update_sheet_properties - Update worksheet properties
update_spreadsheet_properties - Update spreadsheet properties

Drive Operations

search_spreadsheets - Search for spreadsheets in Drive with pagination support

Pagination Features

The server now includes smart pagination for tools that return large datasets:

Google Drive API Pagination

list_sheets: Use max_results and page_token for seamless pagination
search_spreadsheets: Use max_results and page_token for search results

Manual Pagination

get_sheet_names: Use max_sheets and start_index for worksheet names
list_tables: Use max_tables and start_index for table listings

Benefits

Faster Response Times: Smaller chunks load faster
Better Memory Management: Avoid overwhelming memory with large datasets
Smooth User Experience: Progressive loading for better UX
Scalable Operations: Handle thousands of items efficiently

For detailed pagination usage, see PAGINATION_GUIDE.md.

Development

Adding Dependencies

# Add new package
uv add package-name

# Sync after changes
uv sync

Testing

# Test if server can import
uv run python -c "import simplemcp; print('✅ Ready!')"

# Test credentials
uv run python -c "import simplemcp; print(f'Credentials: {simplemcp.creds is not None}')"

Troubleshooting

Common Issues

"Credentials file not found"
- Ensure credentials.json exists in project directory
- Run uv run authenticate.py to set up authentication
"Token file not found"
- Run uv run authenticate.py to create a new token
"Module not found"
- Run uv sync to install dependencies
"Authentication required"
- Run uv run authenticate.py to set up authentication

UV-Specific Issues

"Command not found: uv"
- Install UV: curl -LsSf https://astral.sh/uv/install.sh | sh
- Restart your terminal
"Failed to install dependencies"
- Clear UV cache: uv cache clean
- Reinstall: uv sync --reinstall

Security Notes

Never commit credentials.json or .token.json to version control
Keep your OAuth2 credentials secure
Tokens are automatically refreshed when needed

Support

For issues or questions:

Check the troubleshooting section above
Verify UV is properly installed and dependencies are synced
Ensure Google Sheets API is enabled in your Google Cloud Project
Check that your OAuth2 credentials have the correct scopes