Google Sheets MCP Server

# Claude Desktop Extension: Google Sheets MCP Server A Claude Desktop Extension (DXT) that provides seamless access to Google Sheets through the Model Context Protocol (MCP). This extension allows Claude to efficiently navigate and analyze Google Sheets data using a two-tool approach designed for handling large spreadsheets with secure service account authentication. ## Features - **Get Spreadsheet Summary**: Retrieve metadata about a Google Sheet including name, sheet count, and sheet names - perfect for navigation and understanding structure - **Get Specific Sheet Data**: Retrieve complete data from a specific worksheet within a spreadsheet - optimized for targeted analysis - **Structured Data Access**: Returns spreadsheet data as JSON including all worksheets, cell values, formatting, and metadata - **Automatic URL Parsing**: Extracts sheet IDs from various Google Sheets URL formats - **Secure Authentication**: Service account-based authentication with Google APIs - **Real-time Access**: Direct integration with Google Sheets API - **Desktop Extension**: Single-click installation in Claude Desktop - **AI-Friendly Format**: Data returned in structured JSON format Claude can analyze, process, and work with directly - **Large Spreadsheet Optimization**: Two-tool approach allows efficient handling of spreadsheets with many sheets by getting overview first ## Prerequisites - Node.js 18+ installed - Google Cloud Project with Google Sheets API enabled - Google Service Account credentials configured - Claude Desktop application ## Installation 1. **Clone the repository**: ```bash git clone https://github.com/stephenyu/mcp-googlesheet cd mcp-googlesheet ``` 2. **Install dependencies**: ```bash npm install ``` 3. **Configure Google Service Account**: - Create a Google Cloud Project - Enable Google Sheets API and Google Drive API - Create a Service Account - Download the service account JSON file - Configure extension settings (see Configuration section) 4. **Install the extension in Claude Desktop**: - Build the extension: `npm run pack` (uses `dxt pack` under the hood) - Open Claude Desktop - Go to Settings → Extensions - Click "Install Extension" - Select the generated `mcp-googlesheet.dxt` file ## Configuration ### Extension Settings The extension requires a Google Service Account JSON credentials file. In Claude Desktop extension settings, configure: - **Credentials JSON File Path**: Path to your Google Service Account JSON file (e.g., `/path/to/service-account-key.json`) This file must contain all necessary authentication information including `client_email`, `private_key`, and `project_id`. ### Testing Your Credentials Before using the extension, you can test your credentials JSON file: ```bash # Test your credentials file npm run test:credentials /path/to/your/service-account-key.json # Or directly with node node test/test-credentials.js /path/to/your/service-account-key.json ``` This will verify that: - The file can be read and parsed - All required fields are present - The private key format is correct (includes BEGIN/END markers) - The credentials are valid ### Credentials JSON File Format Your Google Service Account JSON file should look like this: ```json { "type": "service_account", "project_id": "your-project-id", "private_key_id": "your-private-key-id", "private_key": "-----BEGIN PRIVATE KEY-----\n...\n-----END PRIVATE KEY-----\n", "client_email": "your-service@your-project.iam.gserviceaccount.com", "client_id": "your-client-id", "auth_uri": "https://accounts.google.com/o/oauth2/auth", "token_uri": "https://oauth2.googleapis.com/token", "auth_provider_x509_cert_url": "https://www.googleapis.com/oauth2/v1/certs", "client_x509_cert_url": "https://www.googleapis.com/robot/v1/metadata/x509/your-service%40your-project.iam.gserviceaccount.com", "universe_domain": "googleapis.com" } ``` The extension will automatically extract the required fields (`client_email`, `private_key`, and `project_id`) from this file. ### Google Cloud Setup 1. Go to [Google Cloud Console](https://console.cloud.google.com/) 2. Create a new project or select existing one 3. Enable Google Sheets API and Google Drive API 4. Create a Service Account: - Go to "APIs & Services" → "Credentials" - Click "Create Credentials" → "Service Account" - Download the JSON key file 5. Share your Google Sheets with the service account email For detailed setup instructions, see [SETUP.md](SETUP.md). ## Usage ### Available Tools The extension provides two complementary tools designed for efficient navigation of large spreadsheets: 1. **`get_spreadsheet_summary`**: Get an overview of a Google Sheet - Parameters: url (Complete Google Sheets URL) - Returns: Spreadsheet metadata including title, sheet count, sheet names, creation/modification dates - Use case: Understanding spreadsheet structure and choosing which sheet to analyze 2. **`get_spreadsheet_sheet_data`**: Get complete data from a specific sheet - Parameters: url (Complete Google Sheets URL), sheet_name (Name of the specific sheet) - Returns: Complete cell data, formatting, and metadata for the specified sheet - Use case: Detailed analysis of specific worksheet data ### Example Usage in Claude Desktop ``` User: "Get an overview of this spreadsheet: https://docs.google.com/spreadsheets/d/xyz/edit" Claude: [Uses get_spreadsheet_summary tool to show spreadsheet overview with sheet names] User: "Now get the data from the 'Class Data' sheet" Claude: [Uses get_spreadsheet_sheet_data tool with sheet_name="Class Data" to retrieve complete sheet data] User: "Analyze the Q4 budget data from this URL: https://docs.google.com/spreadsheets/d/abc123/edit#gid=0" Claude: [First uses get_spreadsheet_summary to see available sheets, then get_spreadsheet_sheet_data for the relevant sheet] ``` ### Workflow for Large Spreadsheets The two-tool approach is optimized for large spreadsheets: 1. **Start with Summary**: Use `get_spreadsheet_summary` to see what sheets are available 2. **Choose Target Sheet**: Based on the summary, identify which sheet contains the data you need 3. **Get Specific Data**: Use `get_spreadsheet_sheet_data` to retrieve complete data from the target sheet This approach avoids downloading massive amounts of data when you only need specific worksheets. ### Supported URL Formats The extension can extract sheet IDs from various Google Sheets URL formats: - Standard format: `https://docs.google.com/spreadsheets/d/SHEET_ID/edit` - With view parameters: `https://docs.google.com/spreadsheets/d/SHEET_ID/edit#gid=0` - With additional parameters: `https://docs.google.com/spreadsheets/d/SHEET_ID/edit?usp=sharing` ## Development ### Project Structure ``` mcp-googlesheet/ ├── manifest.json # Extension manifest with tool definitions ├── package.json # Node.js dependencies and scripts ├── server/ │ ├── index.js # MCP server entry point with tool implementations │ ├── sheets.js # Google Sheets API integration │ └── logger.js # Logging utilities ├── test/ # Test files for various functionality ├── README.md # This file ├── SETUP.md # Detailed Google Cloud setup guide ├── ARCHITECTURE.md # Technical architecture details └── TASK_LIST.md # Development progress tracking ``` ### Running in Development 1. **Start the development server**: ```bash npm run dev ``` 2. **Test the extension**: ```bash npm test ``` 3. **Test credentials**: ```bash npm run test:credentials /path/to/your/service-account-key.json ``` 4. **Build the extension**: ```bash npm run pack ``` 5. **Install in Claude Desktop**: - Open the generated `mcp-googlesheet.dxt` file with Claude Desktop ## Security - Service account authentication ensures secure access to user data - No data is stored locally beyond temporary session tokens - All API calls use HTTPS - Service account only has read-only access to spreadsheets - Only the JSON file path is stored in extension settings; credentials remain in your local file system ### Debugging Environment Variables To debug environment variable issues, run the environment test script: ```bash # Test environment variables npm run test:env # Test credentials file specifically npm run test:credentials /path/to/your/service-account-key.json # Test Google API authentication npm run test:auth /path/to/your/service-account-key.json # Test private key formatting npm run test:private-key /path/to/your/service-account-key.json # Test Google Cloud project setup npm run test:gcp /path/to/your/service-account-key.json ``` This will help identify: - Whether environment variables are set correctly - If placeholders are being resolved properly - File access permissions - JSON file validity - Google API authentication status - Google Cloud project configuration ### Debug Mode Enable debug logging by setting: ```env DEBUG=true LOG_LEVEL=debug ``` ## Contributing 1. Fork the repository 2. Create a feature branch 3. Make your changes 4. Add tests for new functionality 5. Submit a pull request ## License GNU General Public License v3.0 or later - see LICENSE file for details This program is free software: you can redistribute it and/or modify it under the terms of the GNU General Public License as published by the Free Software Foundation, either version 3 of the License, or (at your option) any later version. This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details. ## Support For issues and questions: - Check the troubleshooting section - Review the architecture documentation - Open an issue on GitHub ## Changelog ### v1.0.0 - Initial release with structured JSON data access - Two-tool approach for efficient large spreadsheet handling - Support for retrieving spreadsheet summaries and specific sheet data - Automatic URL parsing for sheet ID extraction - Service account authentication - MCP server implementation with comprehensive error handling