# MCP Google Sheets Server

[![License: MIT](https://img.shields.io/badge/License-MIT-yellow.svg)](https://opensource.org/licenses/MIT)
[![TypeScript](https://img.shields.io/badge/TypeScript-5.5-blue.svg)](https://www.typescriptlang.org/)
[![Node.js](https://img.shields.io/badge/Node.js-%3E%3D18.0.0-green.svg)](https://nodejs.org/)

A comprehensive [Model Context Protocol (MCP)](https://modelcontextprotocol.io/) server that provides full programmatic access to Google Sheets. This server enables AI assistants and applications to create, read, update, delete, share, and format Google Spreadsheets through a standardized MCP interface.

## ✨ Features

- **📊 Full CRUD Operations**: Create, read, update, and delete spreadsheets and individual sheets
- **📝 Data Management**: Read and write cell ranges, append rows, update individual cells
- **👥 Permission Management**: Share spreadsheets with users, manage access permissions, list current permissions
- **🎨 Formatting Support**: Apply cell formatting, adjust column widths and row heights, merge cells
- **🔐 Secure Authentication**: Service Account-based authentication for server-to-server access
- **🔍 Discovery**: List and search through accessible spreadsheets
- **📦 TypeScript**: Fully typed with comprehensive type definitions
- **🚀 MCP Compatible**: Works with any MCP-compatible client (Cursor, Claude Desktop, etc.)

## 🚀 Quick Start

### Prerequisites

- Node.js >= 18.0.0
- A Google Cloud Project with Google Sheets API and Google Drive API enabled
- A Google Service Account with appropriate permissions

### Installation

```bash
# Clone the repository
git clone https://github.com/yourusername/mcp-google-sheets.git
cd mcp-google-sheets

# Install dependencies
npm install

# Build the project
npm run build
```

### Google Cloud Setup

1. **Create a Google Cloud Project**
   - Go to [Google Cloud Console](https://console.cloud.google.com/)
   - Create a new project or select an existing one

2. **Enable Required APIs**
   - Navigate to "APIs & Services" > "Library"
   - Enable the following APIs:
     - Google Sheets API
     - Google Drive API

3. **Create a Service Account**
   - Go to "APIs & Services" > "Credentials"
   - Click "Create Credentials" > "Service Account"
   - Fill in the service account details
   - Click "Create and Continue"
   - Grant the service account the "Editor" role (or create a custom role with necessary permissions)
   - Click "Done"

4. **Generate Service Account Key**
   - Click on the created service account
   - Go to the "Keys" tab
   - Click "Add Key" > "Create new key"
   - Select "JSON" format
   - Download the JSON file

5. **Configure Credentials**

   You have two options for providing credentials:

   **Option 1: Environment Variables (Recommended for Production)**
   
   Create a `.env` file in the project root:
   ```env
   SERVICE_ACCOUNT_EMAIL=your-service-account@project-id.iam.gserviceaccount.com
   SERVICE_ACCOUNT_PRIVATE_KEY="-----BEGIN PRIVATE KEY-----\n...\n-----END PRIVATE KEY-----\n"
   SERVICE_ACCOUNT_PROJECT_ID=your-project-id
   SERVICE_ACCOUNT_PRIVATE_KEY_ID=your-private-key-id
   SERVICE_ACCOUNT_CLIENT_ID=your-client-id
   ```

   **Option 2: JSON File**
   
   Place your downloaded service account JSON file in a `credentials` directory:
   ```bash
   mkdir credentials
   mv /path/to/your-service-account.json credentials/service-account.json
   ```
   
   Or set the path via environment variable:
   ```env
   SERVICE_ACCOUNT_PATH=/path/to/your-service-account.json
   ```

6. **Share Existing Spreadsheets (Optional)**
   
   If you want the service account to access existing spreadsheets, share them with the service account email:
   - Open your Google Sheet
   - Click "Share"
   - Add the service account email (e.g., `your-service-account@project-id.iam.gserviceaccount.com`)
   - Grant "Editor" permissions for full access

### Running the Server

```bash
# Development mode (with hot reload)
npm run dev

# Production mode
npm start
```

The server will start and listen for MCP requests via stdio.

## 🔧 MCP Client Configuration

### Cursor IDE

Add the following to your Cursor MCP settings (usually in `~/.cursor/mcp.json` or Cursor settings):

```json
{
  "mcpServers": {
    "google-sheets": {
      "command": "node",
      "args": ["/absolute/path/to/mcp-google-sheets/dist/index.js"],
      "env": {
        "SERVICE_ACCOUNT_PATH": "/absolute/path/to/credentials/service-account.json"
      }
    }
  }
}
```

Or using environment variables:

```json
{
  "mcpServers": {
    "google-sheets": {
      "command": "node",
      "args": ["/absolute/path/to/mcp-google-sheets/dist/index.js"],
      "env": {
        "SERVICE_ACCOUNT_EMAIL": "your-service-account@project-id.iam.gserviceaccount.com",
        "SERVICE_ACCOUNT_PRIVATE_KEY": "-----BEGIN PRIVATE KEY-----\n...\n-----END PRIVATE KEY-----\n",
        "SERVICE_ACCOUNT_PROJECT_ID": "your-project-id"
      }
    }
  }
}
```

### Claude Desktop

Add to your Claude Desktop configuration file (usually `~/Library/Application Support/Claude/claude_desktop_config.json` on macOS):

```json
{
  "mcpServers": {
    "google-sheets": {
      "command": "node",
      "args": ["/absolute/path/to/mcp-google-sheets/dist/index.js"],
      "env": {
        "SERVICE_ACCOUNT_PATH": "/absolute/path/to/credentials/service-account.json"
      }
    }
  }
}
```

## 📚 Available Tools

### Create Operations

- **`create_spreadsheet`** - Create a new Google Spreadsheet with optional initial data
  ```typescript
  {
    title: string;
    initialData?: {
      sheetName: string;
      values: any[][];
    };
  }
  ```

- **`create_sheet`** - Add a new sheet tab to an existing spreadsheet
  ```typescript
  {
    spreadsheetId: string;
    sheetName: string;
    rows?: number;      // Default: 1000
    columns?: number;   // Default: 26
  }
  ```

### Read Operations

- **`read_range`** - Read data from a specific range
  ```typescript
  {
    spreadsheetId: string;
    range: string;  // e.g., "Sheet1!A1:B10"
  }
  ```

- **`read_sheet`** - Read all data from a specific sheet
  ```typescript
  {
    spreadsheetId: string;
    sheetName: string;
  }
  ```

- **`get_spreadsheet_info`** - Get metadata and information about a spreadsheet
  ```typescript
  {
    spreadsheetId: string;
  }
  ```

- **`list_sheets`** - List all sheets in a spreadsheet
  ```typescript
  {
    spreadsheetId: string;
  }
  ```

### Write Operations

- **`write_range`** - Write data to a specific range
  ```typescript
  {
    spreadsheetId: string;
    range: string;
    values: any[][];
    valueInputOption?: 'RAW' | 'USER_ENTERED';  // Default: 'RAW'
  }
  ```

- **`append_rows`** - Append rows to a sheet
  ```typescript
  {
    spreadsheetId: string;
    range: string;
    values: any[][];
    valueInputOption?: 'RAW' | 'USER_ENTERED';
  }
  ```

- **`update_cell`** - Update a single cell value
  ```typescript
  {
    spreadsheetId: string;
    range: string;  // e.g., "A1"
    value: any;
    valueInputOption?: 'RAW' | 'USER_ENTERED';
  }
  ```

### Delete Operations

- **`delete_sheet`** - Delete a sheet tab by name
  ```typescript
  {
    spreadsheetId: string;
    sheetName: string;
  }
  ```

- **`delete_spreadsheet`** - Delete an entire spreadsheet
  ```typescript
  {
    spreadsheetId: string;
  }
  ```

### Share & Permissions

- **`share_spreadsheet`** - Share spreadsheet with a user or group
  ```typescript
  {
    spreadsheetId: string;
    emailAddress: string;
    role: 'reader' | 'writer' | 'commenter';
    sendNotificationEmail?: boolean;  // Default: true
  }
  ```

- **`list_permissions`** - List all permissions for a spreadsheet
  ```typescript
  {
    spreadsheetId: string;
  }
  ```

### List Operations

- **`list_spreadsheets`** - List all accessible Google Spreadsheets
  ```typescript
  {
    pageSize?: number;  // Default: 100
    query?: string;     // Optional search query
  }
  ```

## 💡 Usage Examples

### Creating a Spreadsheet with Initial Data

```json
{
  "name": "create_spreadsheet",
  "arguments": {
    "title": "Sales Report Q1 2024",
    "initialData": {
      "sheetName": "Sales",
      "values": [
        ["Product", "Quantity", "Revenue"],
        ["Widget A", 150, "$15,000"],
        ["Widget B", 200, "$20,000"],
        ["Widget C", 100, "$10,000"]
      ]
    }
  }
}
```

### Reading Data from a Range

```json
{
  "name": "read_range",
  "arguments": {
    "spreadsheetId": "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms",
    "range": "Sheet1!A1:C10"
  }
}
```

### Writing Data to a Range

```json
{
  "name": "write_range",
  "arguments": {
    "spreadsheetId": "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms",
    "range": "Sheet1!A1",
    "values": [
      ["Name", "Email", "Department"],
      ["John Doe", "john@example.com", "Engineering"],
      ["Jane Smith", "jane@example.com", "Marketing"]
    ],
    "valueInputOption": "USER_ENTERED"
  }
}
```

### Sharing a Spreadsheet

```json
{
  "name": "share_spreadsheet",
  "arguments": {
    "spreadsheetId": "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms",
    "emailAddress": "collaborator@example.com",
    "role": "writer",
    "sendNotificationEmail": true
  }
}
```

## 🛠️ Development

```bash
# Install dependencies
npm install

# Run in development mode
npm run dev

# Build for production
npm run build

# Type checking
npm run type-check

# Start production server
npm start
```

## 🔒 Security Considerations

- **Never commit service account credentials** to version control
- Use environment variables for credentials in production environments
- Regularly rotate service account keys
- Limit service account permissions to only necessary scopes
- Use the principle of least privilege when granting permissions
- Store credentials securely and restrict file permissions:
  ```bash
  chmod 600 credentials/service-account.json
  ```

## 🐛 Troubleshooting

### Common Issues

**1. "Service account credentials not found"**
- Ensure `SERVICE_ACCOUNT_PATH` points to the correct JSON file, or
- Set `SERVICE_ACCOUNT_EMAIL` and `SERVICE_ACCOUNT_PRIVATE_KEY` environment variables
- Check file permissions and that the file exists

**2. "Access denied" or "Permission denied" errors**
- Share the spreadsheet with the service account email address
- Ensure the service account has "Editor" permissions for full access
- Verify that Google Sheets API and Google Drive API are enabled in your Google Cloud project
- Check that the service account has the correct IAM roles

**3. "Spreadsheet not found"**
- Verify the spreadsheet ID is correct (from the URL: `https://docs.google.com/spreadsheets/d/{SPREADSHEET_ID}/edit`)
- Ensure the service account has access to the spreadsheet
- Check that the spreadsheet hasn't been deleted

**4. "API not enabled" errors**
- Go to Google Cloud Console > APIs & Services > Library
- Enable Google Sheets API
- Enable Google Drive API
- Wait a few minutes for the APIs to propagate

**5. Connection or timeout issues**
- Check network connectivity
- Verify Google APIs are accessible from your network
- Check service account key validity and expiration
- Ensure firewall rules allow outbound HTTPS connections

## 📝 License

This project is licensed under the MIT License - see the [LICENSE](LICENSE) file for details.

## 🤝 Contributing

Contributions are welcome! Please feel free to submit a Pull Request. For major changes, please open an issue first to discuss what you would like to change.

1. Fork the repository
2. Create your feature branch (`git checkout -b feature/amazing-feature`)
3. Commit your changes (`git commit -m 'Add some amazing feature'`)
4. Push to the branch (`git push origin feature/amazing-feature`)
5. Open a Pull Request

## 🙏 Acknowledgments

- Built with [Model Context Protocol SDK](https://github.com/modelcontextprotocol/sdk)
- Powered by [Google APIs Node.js Client](https://github.com/googleapis/google-api-nodejs-client)
- Inspired by the MCP community

## 📞 Support

If you encounter any issues or have questions:

1. Check the [Troubleshooting](#-troubleshooting) section
2. Search existing [Issues](https://github.com/yourusername/mcp-google-sheets/issues)
3. Create a new issue with detailed information about your problem

---

**Made with ❤️ for the MCP community**