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:

   git clone https://github.com/stephenyu/mcp-googlesheet
   cd mcp-googlesheet

2. Install dependencies:

   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:

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

{
  "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": "[email protected]",
  "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
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.

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:

   npm run dev

2. Test the extension:

   npm test

3. Test credentials:

   npm run test:credentials /path/to/your/service-account-key.json

4. Build the extension:

   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:

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

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