Google Sheets MCP Server

This MCP server provides tools to interact with Google Sheets, allowing you to read, write, and update data in your spreadsheets.

Prerequisites

• Python 3.8 or higher
• Google Cloud Platform account
• A Google Sheet that you want to interact with

Installation

1. Clone this repository:

git clone <your-repository-url>
cd mcp-server-demo

2. Install the required packages:

pip install google-auth-oauthlib google-auth-httplib2 google-api-python-client python-dotenv

Setting up Google Cloud Platform Credentials

1. Go to the Google Cloud Console

2. Create a new project or select an existing one

3. Enable the Google Sheets API:

   • Go to "APIs & Services" > "Library"
   • Search for "Google Sheets API"
   • Click "Enable"

4. Create credentials:

   • Go to "APIs & Services" > "Credentials"
   • Click "Create Credentials" > "Service Account"
   • Fill in the service account details:
     • Name: Choose a descriptive name
     • ID: Will be auto-generated
     • Description: Optional description
   • Click "Create and Continue"
   • For Role, select "Editor" (or appropriate role for your needs)
   • Click "Continue" and then "Done"

5. Create and download the key:

   • In the service account list, click on your newly created account
   • Go to the "Keys" tab
   • Click "Add Key" > "Create new key"
   • Choose "JSON" format
   • Click "Create"
   • The JSON file will be downloaded automatically

6. Save the credentials:

   • Rename the downloaded JSON file to credentials.json
   • Place it in the mcp-server-demo directory

7. Share your Google Sheet:

   • Open your Google Sheet
   • Click the "Share" button
   • Add the service account email (found in the client_email field of your credentials.json)
   • Give it "Editor" access

Environment Setup

1. Create a .env file in the mcp-server-demo directory:

touch .env

2. Add your Google Sheets API key to the .env file:

GOOGLE_SHEETS_API_KEY=your_api_key_here

Running the Server

1. Install the MCP server:

mcp install main.py

2. The server will start and be ready to accept commands.

Available Tools

List Spreadsheets

/tool list_spreadsheets

Lists all spreadsheets shared with the service account.

Get Spreadsheet Info

/tool get_spreadsheet_info "spreadsheet_id"

Gets information about a specific spreadsheet.

Get Sheet Content

/tool get_sheet_content "spreadsheet_id" "sheet_title"

Retrieves the content of a specific sheet.

Generate Sheet Data

/tool generate_sheet_data "spreadsheet_id" "sheet_title" number_of_records

Generates realistic data based on the sheet's attributes.

Add Data to Sheet

/tool add_data_to_sheet "spreadsheet_id" "sheet_title" "data_string"

Adds pre-generated data to a sheet.

Update Sheet Record

/tool update_sheet_record "spreadsheet_id" "sheet_title" "identifier" "updates"

Updates a record in the sheet based on id or name matching.

Data Formats

For Adding Data

The data string can be in one of these formats:

1. List of lists format: [["value1", "value2"], ["value3", "value4"]]
2. CSV format:

value1,value2
value3,value4

For Updates

Provide the changes in format: column1=value1 column2=value2 Example: age=25 email=xyz@example.com phone=1234567890

Security Notes

1. Never commit credentials.json or .env files to version control
2. Keep your API keys and credentials secure
3. Only share your Google Sheet with necessary service accounts
4. Regularly rotate your API keys and credentials

Troubleshooting

Common Issues

1. Authentication Errors

   • Ensure the service account email has been added as an editor to the spreadsheet
   • Verify that the credentials.json file is valid and not corrupted
   • Check if the Google Sheets API is enabled in your Google Cloud project

2. Permission Errors

   • Make sure the service account has the correct permissions
   • Verify the spreadsheet is shared with the service account email
   • Check if the spreadsheet ID is correct

3. Data Format Errors

   • Ensure the data format matches the expected structure
   • Check if the sheet has the required columns (id or name)
   • Verify the number of columns in the data matches the sheet

Support

If you encounter any issues or have questions, please:

1. Check the error messages in the logs
2. Verify your setup following the instructions above
3. Ensure all prerequisites are met
4. Contact support if the issue persists