MCP Google Sheets Server

  

A comprehensive Model Context Protocol (MCP) 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

# 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
• Create a new project or select an existing one

1. Enable Required APIs

• Navigate to "APIs & Services" > "Library"
• Enable the following APIs:
• Google Sheets API
• Google Drive API

1. 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"

1. 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

1. 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 ``

1. 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

# 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):

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

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

{
  "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

  {
    title: string;
    initialData?: {
      sheetName: string;
      values: any[][];
    };
  }

• create_sheet - Add a new sheet tab to an existing spreadsheet

  {
    spreadsheetId: string;
    sheetName: string;
    rows?: number;      // Default: 1000
    columns?: number;   // Default: 26
  }

Read Operations

• read_range - Read data from a specific range

  {
    spreadsheetId: string;
    range: string;  // e.g., "Sheet1!A1:B10"
  }

• read_sheet - Read all data from a specific sheet

  {
    spreadsheetId: string;
    sheetName: string;
  }

• get_spreadsheet_info - Get metadata and information about a spreadsheet

  {
    spreadsheetId: string;
  }

• list_sheets - List all sheets in a spreadsheet

  {
    spreadsheetId: string;
  }

Write Operations

• write_range - Write data to a specific range

  {
    spreadsheetId: string;
    range: string;
    values: any[][];
    valueInputOption?: 'RAW' | 'USER_ENTERED';  // Default: 'RAW'
  }

• append_rows - Append rows to a sheet

  {
    spreadsheetId: string;
    range: string;
    values: any[][];
    valueInputOption?: 'RAW' | 'USER_ENTERED';
  }

• update_cell - Update a single cell value

  {
    spreadsheetId: string;
    range: string;  // e.g., "A1"
    value: any;
    valueInputOption?: 'RAW' | 'USER_ENTERED';
  }

Delete Operations

• delete_sheet - Delete a sheet tab by name

  {
    spreadsheetId: string;
    sheetName: string;
  }

• delete_spreadsheet - Delete an entire spreadsheet

  {
    spreadsheetId: string;
  }

Share & Permissions

• share_spreadsheet - Share spreadsheet with a user or group

  {
    spreadsheetId: string;
    emailAddress: string;
    role: 'reader' | 'writer' | 'commenter';
    sendNotificationEmail?: boolean;  // Default: true
  }

• list_permissions - List all permissions for a spreadsheet

  {
    spreadsheetId: string;
  }

List Operations

• list_spreadsheets - List all accessible Google Spreadsheets

  {
    pageSize?: number;  // Default: 100
    query?: string;     // Optional search query
  }

💡 Usage Examples

Creating a Spreadsheet with Initial Data

{
  "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

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

Writing Data to a Range

{
  "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

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

🛠️ Development

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

  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 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
• Powered by Google APIs Node.js Client
• Inspired by the MCP community

📞 Support

If you encounter any issues or have questions:

1. Check the Troubleshooting section
2. Search existing Issues
3. Create a new issue with detailed information about your problem

---

Made with ❤️ for the MCP community