MCP Google Sheets

🚀 How to Integrate Google Sheets with Claude: A Complete Guide

This guide provides a step - by - step process to integrate Google Sheets with Claude, enabling seamless data management and interaction.

🚀 Quick Start

Prerequisites

Before you start, make sure you have the following tools installed:

• Python 3.6 or higher
• pip
• Google Chrome or other modern browsers

✨ Step 1: Configure Authentication

Method 1: Use a Service Account (Recommended)

1. Create a Service Account

   • Log in to the Google Cloud Console.
   • Navigate to Project Settings > People > Add Member.
   • Select "Service account" and assign the required permissions (e.g., "Compute Engine Admin" or "Storage Admin").

2. Download the JSON Key File

   • After completion, click on the project name to enter the project overview page.
   • In the left - hand menu, select "APIs & Services" > "Credentials".
   • Click "Create Credentials" and choose "Service account key".
   • Download the generated JSON file (usually named service - account - key.json).

3. Set Environment Variables

   export SERVICE_ACCOUNT_PATH="/path/to/service-account-key.json"
   export DRIVE_FOLDER_ID="your-shared-folder-id"
   

Method 2: Use OAuth 2.0

1. Create an OAuth 2.0 Client ID

   • In the Google Cloud Console, go to "APIs & Services" > "Credentials".
   • Click "Create Credentials" and select "OAuth client ID".
   • Configure the redirect URI (e.g., http://localhost:8000/callback).

2. Get User Consent

   • After running the script, the browser will automatically open the authorization page.
   • The user needs to log in to their Google account and grant the required permissions.

3. Set Environment Variables

   export CREDENTIALS_PATH="/path/to/credentials.json"
   export TOKEN_PATH="/path/to/token.json"
   

Method 3: Use CREDENTIALS_CONFIG (Base64 - encoded)

1. Convert the JSON Key File to a Base64 String

   • Use a tool like the base64 command or an online converter.
   • Example:

     echo '{"type":"service_account","project_id":"your-project-id","private_key_id":"key-id","private_key":"your-private-key","client_email":"service-account@your-project.iam.gserviceaccount.com","token_uri":"https://accounts.google.com/o/oauth2/token","auth_provider_x509_cert_url":"https://www.googleapis.com/robot/v1/metadata/x509/certs"}' | base64 -w 0 > credentials_base64.txt
     

2. Set Environment Variables

   export CREDENTIALS_CONFIG="Base64-encoded-string"
   export DRIVE_FOLDER_ID="your-shared-folder-id"
   

📦 Step 2: Install Dependencies

pip install google-api-python-client fastmcp

💻 Step 3: Write Code

Basic Usage

Example: Read Spreadsheet Content

from fastmcp import FastMCP
from googleapiclient.discovery import build

# Initialize the FastMCP client
client = FastMCP(
    auth_method="service_account",
    service_account_key_path="/path/to/service-account-key.json"
)

# Create the Sheets API service
sheets_service = build('sheets', 'v4', credentials=client.credentials)

# Get spreadsheet data
spreadsheet_id = "your-spreadsheet-id"
result = sheets_service.spreadsheets().values().get(
    spreadsheetId=spreadsheet_id,
    range="A1:B2"
).execute()

print(result)

Advanced Usage

Example: Create a New Worksheet

from fastmcp import FastMCP
from googleapiclient.discovery import build

# Initialize the FastMCP client
client = FastMCP(
    auth_method="service_account",
    service_account_key_path="/path/to/service-account-key.json"
)

# Create the Sheets API service
sheets_service = build('sheets', 'v4', credentials=client.credentials)

# New worksheet request body
new_sheet_request = {
    "addSheet": {
        "properties": {
            "title": "New Sheet",
            "sheetType": "GRID"
        }
    }
}

# Execute the API call
result = sheets_service.spreadsheets().batchUpdate(
    spreadsheetId="your-spreadsheet-id",
    body={"requests": [new_sheet_request]}
).execute()

print("New worksheet has been created")

💻 Step 4: Integrate in Claude

Example Configuration

{
  "configurations": {
    "google_sheets": {
      "auth_method": "service_account",
      "service_account_key_path": "/path/to/service-account-key.json",
      "spreadsheet_id": "your-spreadsheet-id",
      "drive_folder_id": "your-shared-folder-id"
    }
  },
  "enabled_providers": ["google_sheets"]
}

💻 Step 5: Usage Instructions

1. Start the Service

   python your_script.py
   

2. Send a Request

   • Example:

     {
       "provider": "google_sheets",
       "operation": "read_range",
       "params": {
         "spreadsheet_id": "your-spreadsheet-id",
         "range": "A1:A10"
       }
     }
     

3. Handle the Response

   • The service will return the result in JSON format:

     {
       "status": "success",
       "data": {
         "values": [
           ["Header"],
           ["Row 1 Data"],
           ...
         ]
       }
     }
     

📚 Additional Notes

⚠️ Important Note

• Error Handling: Make sure to add appropriate exception handling in your code to catch and retry possible API call errors.
• Logging: It is recommended to enable logging for debugging and monitoring the service status.
• Security Tips:
  • Avoid directly committing the key file to the version control system.
  • Regularly update the service account key.
  • Use environment variables to manage sensitive information in the production environment.

By following these steps, you can successfully integrate Google Sheets with Claude and expand and customize it as needed.