# Google Sheets MCP Server
A Model Context Protocol (MCP) server that provides comprehensive Google Sheets integration. This server enables you to create, read, update, and manage Google Sheets spreadsheets programmatically.
## 🎯 Purpose
This server enables you to:
- **Create** new Google Sheets spreadsheets with custom sheet names
- **Read** data from any range in a spreadsheet
- **Write** data to specific ranges
- **Append** new rows to existing data
- **Clear** ranges of data
- **Get** spreadsheet information and metadata
- **Batch update** multiple ranges efficiently
## 🛠️ Available Tools
### Core Operations
- **create-spreadsheet**
- Creates a new Google Sheets spreadsheet
- Input: `title` (required), `sheet_names` (optional array)
- Returns: Spreadsheet ID, URL, and created sheet names
- **read-range**
- Reads data from a specific range
- Input: `spreadsheet_id`, `range_name` (e.g., 'Sheet1!A1:C10')
- Returns: 2D array of cell values
- **write-range**
- Writes data to a specific range (overwrites existing data)
- Input: `spreadsheet_id`, `range_name`, `values` (2D array)
- Returns: Update statistics
- **append-rows**
- Appends rows to the end of a range
- Input: `spreadsheet_id`, `range_name`, `values` (2D array)
- Returns: Update statistics
- **clear-range**
- Clears all data from a specified range
- Input: `spreadsheet_id`, `range_name`
- Returns: Confirmation of cleared range
- **get-spreadsheet-info**
- Gets metadata about a spreadsheet
- Input: `spreadsheet_id`
- Returns: Title, URL, sheet information, dimensions
- **batch-update**
- Performs multiple range updates in a single request
- Input: `spreadsheet_id`, `updates` (array of range/values pairs)
- Returns: Total update statistics
### Prompts
- **manage-sheets**: General Google Sheets management prompt for AI assistants
## 🚀 Setup
### 1. Google Sheets API Setup
1. [Create a Google Cloud project](https://console.cloud.google.com/projectcreate) or use an existing one
2. [Enable the Google Sheets API](https://console.cloud.google.com/apis/library/sheets.googleapis.com)
3. [Configure an OAuth consent screen](https://console.cloud.google.com/apis/credentials/consent)
- Select "External" for testing purposes
- Add your email as a test user
4. Add OAuth scope: `https://www.googleapis.com/auth/spreadsheets`
5. [Create OAuth 2.0 Client ID credentials](https://console.cloud.google.com/apis/credentials/oauthclient)
- Choose "Desktop Application"
6. Download the credentials JSON file
7. Save it securely and note the file path
### 2. Installation
Using [uv](https://docs.astral.sh/uv/) (recommended):
```bash
cd sheets-mcp-server
uv sync
```
### 3. Authentication
On first run, the server will launch a browser for OAuth authentication.
Access tokens will be saved to the specified `--token-path` for future use.
## 💼 Usage
### Standalone Usage
```bash
uv run sheets \
--creds-file-path /path/to/your/credentials.json \
--token-path /path/to/your/tokens.json
```
### Integration with Claude Desktop
Add to your `claude_desktop_config.json`:
```json
{
"mcpServers": {
"google-sheets": {
"command": "uv",
"args": [
"--directory",
"/absolute/path/to/sheets-mcp-server",
"run",
"sheets",
"--creds-file-path",
"/path/to/your/credentials.json",
"--token-path",
"/path/to/your/tokens.json"
]
}
}
}
```
### Integration with Other MCP Clients
This server follows the standard MCP protocol and can be integrated with any MCP-compatible client.
## 📋 Usage Examples
### Creating a New Spreadsheet
```json
{
"tool": "create-spreadsheet",
"arguments": {
"title": "My Data Analysis",
"sheet_names": ["Data", "Analysis", "Charts"]
}
}
```
### Reading Data
```json
{
"tool": "read-range",
"arguments": {
"spreadsheet_id": "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms",
"range_name": "Sheet1!A1:E10"
}
}
```
### Writing Data
```json
{
"tool": "write-range",
"arguments": {
"spreadsheet_id": "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms",
"range_name": "Sheet1!A1:C3",
"values": [
["Name", "Age", "City"],
["Alice", "30", "New York"],
["Bob", "25", "San Francisco"]
]
}
}
```
### Appending New Data
```json
{
"tool": "append-rows",
"arguments": {
"spreadsheet_id": "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms",
"range_name": "Sheet1!A:C",
"values": [
["Charlie", "35", "Chicago"],
["Diana", "28", "Boston"]
]
}
}
```
### Batch Updates
```json
{
"tool": "batch-update",
"arguments": {
"spreadsheet_id": "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms",
"updates": [
{
"range": "Sheet1!A1:B2",
"values": [["Header1", "Header2"], ["Data1", "Data2"]]
},
{
"range": "Sheet1!D1:E2",
"values": [["Header3", "Header4"], ["Data3", "Data4"]]
}
]
}
}
```
## 🧪 Testing
### With MCP Inspector
Test the server using [MCP Inspector](https://modelcontextprotocol.io/docs/tools/inspector):
```bash
npx @modelcontextprotocol/inspector uv run sheets \
--creds-file-path /path/to/credentials.json \
--token-path /path/to/tokens.json
```
### Manual Testing
1. **Create a test spreadsheet**
2. **Read some data** to verify connectivity
3. **Write test data** to ensure write permissions work
4. **Try different range formats** (A1 notation, named ranges, etc.)
## 📊 Common Use Cases
### Data Analysis Workflows
```
1. Create spreadsheet for analysis
2. Import raw data via append-rows
3. Read data for processing
4. Write calculated results back
5. Generate reports and summaries
```
### Content Management
```
1. Create content tracking spreadsheet
2. Append new content entries
3. Update status and metadata
4. Generate content reports
```
### Project Management
```
1. Create project tracking sheet
2. Add tasks and milestones
3. Update progress and status
4. Generate project dashboards
```
### Data Synchronization
```
1. Read data from external systems
2. Transform and validate data
3. Write to Google Sheets for sharing
4. Keep data synchronized across platforms
```
## 🔧 Advanced Features
### Range Formats Supported
- **A1 notation**: `Sheet1!A1:C10`
- **Named ranges**: `MyNamedRange`
- **Entire columns**: `Sheet1!A:C`
- **Entire rows**: `Sheet1!1:5`
- **Open-ended ranges**: `Sheet1!A1:C`
### Error Handling
The server includes comprehensive error handling for:
- Authentication failures and token refresh
- Network timeouts and connectivity issues
- Invalid spreadsheet IDs or range names
- Permission errors
- API quota limits
- Malformed data inputs
### Performance Considerations
- Uses `asyncio.to_thread` for non-blocking API calls
- Supports batch operations for efficiency
- Handles Google Sheets API rate limits gracefully
- Optimized for both small and large data operations
## 🔒 Security & Permissions
### Required OAuth Scopes
- `https://www.googleapis.com/auth/spreadsheets` - Full access to Google Sheets
### Security Best Practices
- Store credentials securely
- Use environment variables for sensitive paths
- Implement proper access controls
- Regularly rotate access tokens
- Monitor API usage and quotas
## 🤝 Contributing & Extending
This server is designed to be easily extensible. Common enhancements:
### Additional Features
- **Formatting operations** (bold, colors, borders)
- **Formula support** for calculated cells
- **Chart creation** and management
- **Conditional formatting** rules
- **Data validation** constraints
- **Pivot tables** and summaries
### Integration Enhancements
- **Database connectors** for data import/export
- **CSV/Excel file** import/export
- **Real-time collaboration** features
- **Webhook notifications** for changes
- **Advanced search** and filtering
### Performance Optimizations
- **Caching strategies** for frequently accessed data
- **Streaming support** for large datasets
- **Parallel processing** for bulk operations
- **Connection pooling** for high-throughput scenarios
## 📚 API Reference
### Google Sheets API Limits
- **100 requests per 100 seconds per user**
- **1000 requests per 100 seconds** (total quota)
- **Maximum 10 million cells** per spreadsheet
- **Maximum 200 sheets** per spreadsheet
### Response Formats
All tools return structured responses with:
- **Status indicators** (success/error)
- **Detailed error messages** when applicable
- **Update statistics** for write operations
- **Structured data** for read operations
## 🆘 Troubleshooting
### Common Issues
1. **Authentication Errors**
- Verify credentials file path
- Check OAuth consent screen configuration
- Ensure correct scopes are configured
2. **Permission Errors**
- Verify spreadsheet sharing permissions
- Check if spreadsheet exists
- Ensure account has edit access
3. **Range Errors**
- Validate A1 notation format
- Check sheet names for typos
- Verify range bounds
4. **Quota Exceeded**
- Implement request throttling
- Use batch operations when possible
- Monitor usage in Google Cloud Console
---
**Ready to supercharge your Google Sheets workflows with automated operations!** 🚀
