Seamless Google Sheets Integration with LLMs: The mcp-google-sheets Server

Unlock the power of Large Language Models (LLMs) by connecting them directly to your Google Sheets data with the mcp-google-sheets server. This Model Context Protocol (MCP) server acts as a bridge, enabling your LLM applications to create, read, update, and manage spreadsheets effortlessly. Imagine AI-powered workflows that automatically analyze data, generate reports, or even populate spreadsheets based on natural language instructions.

Core Functionality: Tools for Spreadsheet Interaction

The mcp-google-sheets server exposes a suite of powerful tools, each designed to facilitate specific interactions with your Google Sheets data:

• get_sheet_data: Extracts data from a specified sheet within a Google Spreadsheet.

  • Input: spreadsheet_id (string, the unique identifier in the URL), sheet (string, the sheet name), and an optional range (string, A1 notation like 'A1:C10').
  • Output: A 2D array representing the sheet data.
  • Example: Retrieve sales figures for a specific month.

• update_cells: Modifies cell values within a Google Spreadsheet.

  • Input: spreadsheet_id (string), sheet (string), range (string, A1 notation), and data (2D array, the new values).
  • Output: Confirmation of the update operation.
  • Example: Update inventory levels after a sale.

• batch_update_cells: Efficiently updates multiple ranges within a Google Spreadsheet in a single operation.

  • Input: spreadsheet_id (string), sheet (string), and ranges (object, a dictionary mapping range strings to 2D arrays of values).
  • Output: Confirmation of the batch update operation.
  • Example: Simultaneously update prices and quantities for multiple products.

• list_sheets: Retrieves a list of all sheet names within a Google Spreadsheet.

  • Input: spreadsheet_id (string).
  • Output: A list of sheet names.
  • Example: Dynamically populate a dropdown menu with available sheets.

• list_spreadsheets: Lists all spreadsheets accessible within the configured Google Drive folder.

  • Input: None.
  • Output: A list of spreadsheets, each containing its ID and title.
  • Note: When using service account authentication, this lists spreadsheets in the shared folder.
  • Example: Provide a user with a selection of available spreadsheets.

• create_spreadsheet: Creates a new Google Spreadsheet with a specified title.

  • Input: title (string, the desired spreadsheet title).
  • Output: Information about the newly created spreadsheet, including its ID.
  • Note: With service account authentication and a configured folder ID, the spreadsheet is created in that folder.
  • Example: Automatically generate a new spreadsheet for each new project.

• create_sheet: Adds a new sheet (tab) to an existing Google Spreadsheet.

  • Input: spreadsheet_id (string), title (string, the desired sheet title).
  • Output: Information about the newly created sheet.
  • Example: Create a new sheet for each month in a budget spreadsheet.

• Additional Tools: The server also provides tools for more advanced operations, including add_rows, add_columns, copy_sheet, and rename_sheet, offering comprehensive control over your spreadsheets.

Resource Endpoint

• spreadsheet://{spreadsheet_id}/info: Retrieves basic information about a Google Spreadsheet.
  • Output: A JSON string containing spreadsheet metadata.
  • Example: Verify the existence and properties of a spreadsheet before performing other operations.

Installation and Configuration: Connecting to Google Sheets

Before running the mcp-google-sheets server, you'll need to configure access to your Google Sheets data through the Google Cloud Platform and choose an authentication method.

Google Cloud Platform Setup (Required)

1. Create a Google Cloud Project:
   • Navigate to the Google Cloud Console.
   • Create a new project or select an existing one.
2. Enable APIs:
   • Enable the Google Sheets API and Google Drive API for your project.

Authentication Methods: Choosing the Right Approach

The mcp-google-sheets server supports two authentication methods, each suited for different use cases:

1. Service Account Authentication (Recommended for Production)

Service accounts provide a secure and non-interactive way to authenticate, ideal for automated processes and server environments.

• Benefits:

  • No browser interaction required.
  • Suitable for headless environments.
  • More stable authentication than OAuth tokens.
  • Perfect for server deployments and automation.

• Setup:

  1. Create a Service Account:
     • Go to Google Cloud Console → IAM & Admin → Service Accounts.
     • Create a new service account with a descriptive name.
     • Grant it appropriate roles (e.g., Google Sheets API access).
     • Download the JSON key file.
  2. Create a Shared Folder in Google Drive:
     • Create a dedicated folder in Google Drive (e.g., "LLM Sheets").
     • Note the folder's ID from its URL.
     • Share the folder with the service account's email address (found in the JSON key file) and grant "Editor" access.
  3. Set Environment Variables:
     • SERVICE_ACCOUNT_PATH: Path to the service account JSON key file.
     • DRIVE_FOLDER_ID: ID of the Google Drive folder shared with the service account.

2. OAuth 2.0 Authentication (Suitable for Development)

OAuth 2.0 requires browser interaction for initial setup, making it suitable for personal use and development environments.

• Setup:

  1. Configure OAuth:
     • Configure the OAuth consent screen in the Google Cloud Console.
     • Create OAuth 2.0 Client ID credentials (Desktop application type).
     • Download the credentials JSON file and save it as credentials.json.
  2. Set Environment Variables:
     • CREDENTIALS_PATH: Path to the downloaded OAuth credentials file (default: credentials.json).
     • TOKEN_PATH: Path where the authentication token will be stored (default: token.json).

Setting Environment Variables (Platform-Specific)

Linux/Mac:

# Service Account Authentication
export SERVICE_ACCOUNT_PATH=/path/to/your/service-account-key.json
export DRIVE_FOLDER_ID=your_shared_folder_id_here

# OR OAuth Authentication
export CREDENTIALS_PATH=/path/to/your/credentials.json
export TOKEN_PATH=/path/to/your/token.json

Windows:

# Service Account Authentication
set SERVICE_ACCOUNT_PATH=C:\path\to\your\service-account-key.json
set DRIVE_FOLDER_ID=your_shared_folder_id_here

# OR OAuth Authentication
set CREDENTIALS_PATH=C:\path\to\your\credentials.json
set TOKEN_PATH=C:\path\to\your\token.json

Running the Server: Bringing it to Life

You can run the mcp-google-sheets server using two methods:

1. Using uvx (Recommended for General Use)

uvx allows you to run the server directly without installation, simplifying the process.

# Set environment variables first, then run the server
uvx mcp-google-sheets

2. For Development and Modifications

If you plan to modify the server's code:

1. Clone the Repository:

   git clone https://github.com/yourusername/mcp-google-sheets.git
   cd mcp-google-sheets

2. Run with uv:

   # Set environment variables first, then run
   uv run mcp-google-sheets

Authentication Flow: How the Server Connects

The server intelligently selects the authentication method based on the configured environment variables:

1. Service Account Check: It first attempts to authenticate using service account credentials.
2. OAuth Fallback: If service account authentication fails or isn't configured, it falls back to the OAuth flow.

With service account authentication, the server operates directly on spreadsheets within the shared Google Drive folder without requiring browser interaction.

With OAuth authentication, the first time you run the server, it will open a browser window to authenticate with your Google account. After successful authentication, a token will be saved in the location specified by the TOKEN_PATH environment variable.

Integration with Claude Desktop: A Practical Example

To integrate the mcp-google-sheets server with Claude Desktop, add the following configuration to your claude_desktop_config.json file:

"mcpServers": {
  "google-sheets": {
    "command": "uvx",
    "args": ["mcp-google-sheets"],
    "env": {
      "SERVICE_ACCOUNT_PATH": "/path/to/your/service-account-key.json",
      "DRIVE_FOLDER_ID": "your_shared_folder_id_here"
    }
  }
}

Note: Adjust the paths and folder ID to match your specific configuration. For OAuth, replace the SERVICE_ACCOUNT_PATH and DRIVE_FOLDER_ID with CREDENTIALS_PATH and TOKEN_PATH respectively.

Example Prompts for Claude: Unleashing the Power

Once the MCP server is connected to Claude, you can use natural language prompts to interact with your Google Sheets data:

• "List all spreadsheets in my shared folder."
• "Create a new spreadsheet titled 'Budget 2024'."
• "Get the data from Sheet1 in my spreadsheet with ID 1A2B3C4D5E6F7G8H."
• "Add 3 rows to the beginning of Sheet2 in my spreadsheet."
• "Update cells A1:B2 in my spreadsheet with the values [[1, 2], [3, 4]]."
• "List all the sheets in my Budget spreadsheet."
• "Copy the 'March' sheet from my Q1 spreadsheet to my Annual spreadsheet and rename it to 'Q1-March'."
• "Add a new sheet named 'Q4' to my Annual Budget spreadsheet."

These prompts demonstrate the power of combining LLMs with the mcp-google-sheets server, enabling you to automate spreadsheet tasks and gain deeper insights from your data.