# Directory Structure
```
├── .gitignore
├── .python-version
├── main.py
├── pyproject.toml
├── README.md
├── server.py
├── sheets_functions.py
└── uv.lock
```
# Files
--------------------------------------------------------------------------------
/.python-version:
--------------------------------------------------------------------------------
```
3.12
```
--------------------------------------------------------------------------------
/.gitignore:
--------------------------------------------------------------------------------
```
# Byte-compiled / optimized / DLL files
__pycache__/
*.py[cod]
*$py.class
# C extensions
*.so
# Distribution / packaging
.Python
build/
develop-eggs/
dist/
downloads/
eggs/
.eggs/
lib/
lib64/
parts/
sdist/
var/
wheels/
share/python-wheels/
*.egg-info/
.installed.cfg
*.egg
MANIFEST
# PyInstaller
# Usually these files are written by a python script from a template
# before PyInstaller builds the exe, so as to inject date/other infos into it.
*.manifest
*.spec
# Installer logs
pip-log.txt
pip-delete-this-directory.txt
# Unit test / coverage reports
htmlcov/
.tox/
.nox/
.coverage
.coverage.*
.cache
nosetests.xml
coverage.xml
*.cover
*.py,cover
.hypothesis/
.pytest_cache/
cover/
# Translations
*.mo
*.pot
# Django stuff:
*.log
local_settings.py
db.sqlite3
db.sqlite3-journal
# Flask stuff:
instance/
.webassets-cache
# Scrapy stuff:
.scrapy
# Sphinx documentation
docs/_build/
# PyBuilder
.pybuilder/
target/
# Jupyter Notebook
.ipynb_checkpoints
# IPython
profile_default/
ipython_config.py
# pyenv
# For a library or package, you might want to ignore these files since the code is
# intended to run in multiple environments; otherwise, check them in:
# .python-version
# pipenv
# According to pypa/pipenv#598, it is recommended to include Pipfile.lock in version control.
# However, in case of collaboration, if having platform-specific dependencies or dependencies
# having no cross-platform support, pipenv may install dependencies that don't work, or not
# install all needed dependencies.
#Pipfile.lock
# UV
# Similar to Pipfile.lock, it is generally recommended to include uv.lock in version control.
# This is especially recommended for binary packages to ensure reproducibility, and is more
# commonly ignored for libraries.
#uv.lock
# poetry
# Similar to Pipfile.lock, it is generally recommended to include poetry.lock in version control.
# This is especially recommended for binary packages to ensure reproducibility, and is more
# commonly ignored for libraries.
# https://python-poetry.org/docs/basic-usage/#commit-your-poetrylock-file-to-version-control
#poetry.lock
# pdm
# Similar to Pipfile.lock, it is generally recommended to include pdm.lock in version control.
#pdm.lock
# pdm stores project-wide configurations in .pdm.toml, but it is recommended to not include it
# in version control.
# https://pdm.fming.dev/latest/usage/project/#working-with-version-control
.pdm.toml
.pdm-python
.pdm-build/
# PEP 582; used by e.g. github.com/David-OConnor/pyflow and github.com/pdm-project/pdm
__pypackages__/
# Celery stuff
celerybeat-schedule
celerybeat.pid
# SageMath parsed files
*.sage.py
# Environments
.env
.venv
env/
venv/
ENV/
env.bak/
venv.bak/
# Spyder project settings
.spyderproject
.spyproject
# Rope project settings
.ropeproject
# mkdocs documentation
/site
# mypy
.mypy_cache/
.dmypy.json
dmypy.json
# Pyre type checker
.pyre/
# pytype static type analyzer
.pytype/
# Cython debug symbols
cython_debug/
# PyCharm
# JetBrains specific template is maintained in a separate JetBrains.gitignore that can
# be found at https://github.com/github/gitignore/blob/main/Global/JetBrains.gitignore
# and can be added to the global gitignore or merged into this file. For a more nuclear
# option (not recommended) you can uncomment the following to ignore the entire idea folder.
#.idea/
# Ruff stuff:
.ruff_cache/
# PyPI configuration file
.pypirc
# Jetbrains
.idea
# Project files
credentials.json
token.json
```
--------------------------------------------------------------------------------
/README.md:
--------------------------------------------------------------------------------
```markdown
# Google Sheets MCP
Model Context Protocol (MCP) integration with Google Sheets
## Features
This integration allows the LLM to
- List all the spreadsheets of the user
- Create a new spreadsheet
- Copy a spreadsheet to another
- Write and Edit cells
- Fill tool for cells
## Get Started
1. [Create a new Google Cloud project](https://console.cloud.google.com/projectcreate)
2. [Enable the Google Drive API](https://console.cloud.google.com/workspace-api/products)
3. [Configure an OAuth consent screen](https://console.cloud.google.com/apis/credentials/consent) ("internal" is fine for testing)
4. [Create an OAuth Client ID](https://console.cloud.google.com/apis/credentials/oauthclient) for application type "Desktop App"
5. Download the JSON file of your client's OAuth keys
6. Rename the key file to `credentials.json` and place into the root of this repo (i.e. `google-sheets-mcp/credentials.json`)
> Note: If setting the user type to external, make sure to add your email for testing
```
--------------------------------------------------------------------------------
/main.py:
--------------------------------------------------------------------------------
```python
```
--------------------------------------------------------------------------------
/pyproject.toml:
--------------------------------------------------------------------------------
```toml
[project]
name = "googlesheetsmcp"
version = "0.1.0"
description = "Add your description here"
readme = "README.md"
requires-python = ">=3.12"
dependencies = [
"google-api-python-client>=2.164.0",
"google-auth-httplib2>=0.2.0",
"google-auth-oauthlib>=1.2.1",
"mcp[cli]>=1.4.1",
]
```
--------------------------------------------------------------------------------
/server.py:
--------------------------------------------------------------------------------
```python
from mcp.server.fastmcp import FastMCP
from sheets_functions import get_user_spreadsheet_ids, create_new_spreadsheet, copy_to_spreadsheet, mass_edit_spreadsheet, fill_ranges_with_colors_in_spreadsheet
mcp = FastMCP("Google Sheets MCP")
@mcp.tool()
def get_spreadsheets():
"""
Gets all the spreadsheets of the authenticated user
return:
A seperated string of spreadsheet names and IDs
"""
spreadsheets = get_user_spreadsheet_ids()
if spreadsheets is None:
return "Unable to get spreadsheets"
if len(spreadsheets) == 0:
return "No spreadsheets found"
return "\n---\n".join(spreadsheets)
@mcp.tool()
def create_spreadsheet(title: str):
"""
Creates a spreadsheet with the given title
:param title: The title of the spreadsheet
:return: A string that contains the spreadsheet title and ID
"""
spreadsheet = create_new_spreadsheet(title)
if spreadsheet is None:
return "Unable to create spreadsheet"
return f"Created new spreadsheet with title {spreadsheet[0]} and ID {spreadsheet[1]}"
@mcp.tool()
def copy_spreadsheet(sheet_id_to: str, sheet_id_from: str):
"""
Copies a spreadsheet from sheet_id_from to sheet_id_to
:param sheet_id_to: The ID of the sheet to copy
:param sheet_id_from: The ID of the sheet that is being copied from
:return:
"""
speadsheets = copy_to_spreadsheet(sheet_id_to, sheet_id_from)
if speadsheets is None:
return "Unable to copy spreadsheet"
return f"Copied spreadsheet {speadsheets[0]} to {speadsheets[1]} with sheet name {speadsheets[2]}"
@mcp.tool()
def edit_spreadsheet(spreadsheet_id, ranges_and_values):
"""
Fills specified ranges in a spreadsheet with colors.
:param spreadsheet_id: The ID of the spreadsheet to edit.
:param ranges_and_colors:
A list of tuples, where each tuple contains:
1. A string representing the range to fill with a color (e.g., "A1:B2").
2. A string representing the color to fill the range with, provided in hex format (e.g., "#FF5733").
Example:
ranges_and_colors = [
("A1:B2", "#FF5733"),
("C1:D2", "#4287f5"),
("E3:F3", "#FFC300"),
]
In this example:
- The range "A1:B2" will be filled with the color "#FF5733" (a shade of red).
- The range "C1:D2" will be filled with the color "#4287f5" (a shade of blue).
- The range "E3:F3" will be filled with the color "#FFC300" (a shade of yellow).
The color is applied to the entire range of cells specified.
"""
result = mass_edit_spreadsheet(spreadsheet_id, ranges_and_values)
if result is None:
return "Unable to edit spreadsheet"
return f"{result.get('totalUpdatedCells')} cells updated."
@mcp.tool()
def fill_spreadsheet(spreadsheet_id, ranges_and_colors):
"""
Fills specified ranges in a spreadsheet with colors.
:param spreadsheet_id: The ID of the spreadsheet to edit.
:param ranges_and_colors:
A list of tuples, where each tuple contains:
1. A string representing the range to fill with a color (e.g., "A1:B2").
2. A string representing the color to fill the range with, provided in hex format (e.g., "#FF5733").
Example:
ranges_and_colors = [
("A1:B2", "#FF5733"),
("C1:D2", "#4287f5"),
("E3:F3", "#FFC300"),
]
In this example:
- The range "A1:B2" will be filled with the color "#FF5733" (a shade of red).
- The range "C1:D2" will be filled with the color "#4287f5" (a shade of blue).
- The range "E3:F3" will be filled with the color "#FFC300" (a shade of yellow).
The color is applied to the entire range of cells specified.
:param value_input_option:
The input option for how the data should be entered. This parameter is not used in this function as it primarily deals with formatting and color filling, but it's included for consistency with Google Sheets API conventions.
:return:
A dictionary with the result of the batch update, or None if there was an error. The dictionary contains the number of updated cells.
"""
result = fill_ranges_with_colors_in_spreadsheet(spreadsheet_id, ranges_and_colors)
if result is None:
return "Unable to fill spreadsheet"
return f"Cells updated with colors."
if __name__ == "__main__":
mcp.run(transport='stdio')
```
--------------------------------------------------------------------------------
/sheets_functions.py:
--------------------------------------------------------------------------------
```python
import os.path
from typing import List, Any
from google.auth.transport.requests import Request
from google.oauth2.credentials import Credentials
from google_auth_oauthlib.flow import InstalledAppFlow
from googleapiclient.discovery import build
from googleapiclient.errors import HttpError
# If modifying these scopes, delete the file token.json.
SCOPES = ["https://www.googleapis.com/auth/drive.readonly", "https://www.googleapis.com/auth/spreadsheets"]
def get_user_spreadsheet_ids() -> List[str] | None:
creds = None
if os.path.exists("token.json"):
creds = Credentials.from_authorized_user_file("token.json", SCOPES)
if not creds or not creds.valid:
if creds and creds.expired and creds.refresh_token:
creds.refresh(Request())
else:
flow = InstalledAppFlow.from_client_secrets_file("credentials.json", SCOPES)
creds = flow.run_local_server(port=0)
with open("token.json", "w") as token:
token.write(creds.to_json())
try:
service = build("drive", "v3", credentials=creds)
results = service.files().list(q="mimeType='application/vnd.google-apps.spreadsheet'",
fields="files(id, name)").execute()
files = results.get("files", [])
if not files:
print("No spreadsheets found.")
return []
return [f"{file["name"]}: {file["id"]}" for file in files]
except HttpError as err:
print(f"An error occurred: {err}")
return None
def create_new_spreadsheet(title: str) -> tuple[str, str] | None:
creds = None
if os.path.exists("token.json"):
creds = Credentials.from_authorized_user_file("token.json", SCOPES)
if not creds or not creds.valid:
if creds and creds.expired and creds.refresh_token:
creds.refresh(Request())
else:
flow = InstalledAppFlow.from_client_secrets_file("credentials.json", SCOPES)
creds = flow.run_local_server(port=0)
with open("token.json", "w") as token:
token.write(creds.to_json())
try:
service = build("sheets", "v4", credentials=creds)
spreadsheet = {"properties": {"title": title}}
spreadsheet = (
service.spreadsheets()
.create(body=spreadsheet, fields="spreadsheetId")
.execute()
)
print(f"Spreadsheet ID: {(spreadsheet.get('spreadsheetId'))}")
return (title, spreadsheet.get("spreadsheetId"))
except HttpError as error:
print(f"An error occurred: {error}")
return None
def copy_to_spreadsheet(sheet_id_to: str, sheet_id_from: str) -> tuple[str, str, str] | None:
creds = None
if os.path.exists("token.json"):
creds = Credentials.from_authorized_user_file("token.json", SCOPES)
if not creds or not creds.valid:
if creds and creds.expired and creds.refresh_token:
creds.refresh(Request())
else:
flow = InstalledAppFlow.from_client_secrets_file("credentials.json", SCOPES)
creds = flow.run_local_server(port=0)
with open("token.json", "w") as token:
token.write(creds.to_json())
try:
service = build("sheets", "v4", credentials=creds)
# Get the name of the source spreadsheet and the sheet to copy
source_spreadsheet = service.spreadsheets().get(spreadsheetId=sheet_id_from).execute()
source_spreadsheet_name = source_spreadsheet["properties"]["title"]
sheet_id_from_actual = source_spreadsheet["sheets"][0]["properties"]["sheetId"]
sheet_name_from = source_spreadsheet["sheets"][0]["properties"]["title"]
# Get the name of the destination spreadsheet
destination_spreadsheet = service.spreadsheets().get(spreadsheetId=sheet_id_to).execute()
destination_spreadsheet_name = destination_spreadsheet["properties"]["title"]
# Copy the sheet to the destination
copied_sheet = service.spreadsheets().sheets().copyTo(
spreadsheetId=sheet_id_from,
sheetId=sheet_id_from_actual,
body={"destinationSpreadsheetId": sheet_id_to}
).execute()
# Return the relevant names
return (
source_spreadsheet_name,
destination_spreadsheet_name,
copied_sheet["title"]
)
except HttpError as error:
print(f"An error occurred: {error}")
return None
def mass_edit_spreadsheet(spreadsheet_id: str, ranges_and_values: List[tuple[str, List[List[Any]]]],
value_input_option: str = "USER_ENTERED") -> dict | None:
creds = None
# Check if the token.json file exists and load credentials from it
if os.path.exists("token.json"):
creds = Credentials.from_authorized_user_file("token.json", SCOPES)
# If credentials are not valid, refresh or authenticate again
if not creds or not creds.valid:
if creds and creds.expired and creds.refresh_token:
creds.refresh(Request())
else:
flow = InstalledAppFlow.from_client_secrets_file("credentials.json", SCOPES)
creds = flow.run_local_server(port=0)
with open("token.json", "w") as token:
token.write(creds.to_json())
try:
service = build("sheets", "v4", credentials=creds)
# Prepare the request body for batch update
data = [
{"range": range_name, "values": values}
for range_name, values in ranges_and_values
]
body = {
"valueInputOption": value_input_option,
"data": data
}
# Execute the batch update request
result = service.spreadsheets().values().batchUpdate(
spreadsheetId=spreadsheet_id, body=body
).execute()
# Return the result which contains the number of updated cells
print(f"{result.get('totalUpdatedCells')} cells updated.")
return result
except HttpError as error:
print(f"An error occurred: {error}")
return None
def hex_to_rgb(hex_color: str) -> dict:
"""
Converts a hex color code (e.g., "#FF5733") to RGB format for Google Sheets API.
:param hex_color: The hex color code (e.g., "#FF5733").
:return: A dictionary with the RGB values for Google Sheets backgroundColor (e.g., {"red": 1.0, "green": 0.341, "blue": 0.2}).
"""
hex_color = hex_color.lstrip('#') # Remove leading '#' if present
r, g, b = bytes.fromhex(hex_color) # Convert hex to RGB
return {
"red": r / 255,
"green": g / 255,
"blue": b / 255
}
def fill_ranges_with_colors_in_spreadsheet(spreadsheet_id: str, ranges_and_colors: List[tuple[str, str]],
value_input_option: str = "USER_ENTERED") -> dict | None:
creds = None
# Check if the token.json file exists and load credentials from it
if os.path.exists("token.json"):
creds = Credentials.from_authorized_user_file("token.json", SCOPES)
# If credentials are not valid, refresh or authenticate again
if not creds or not creds.valid:
if creds and creds.expired and creds.refresh_token:
creds.refresh(Request())
else:
flow = InstalledAppFlow.from_client_secrets_file("credentials.json", SCOPES)
creds = flow.run_local_server(port=0)
with open("token.json", "w") as token:
token.write(creds.to_json())
try:
service = build("sheets", "v4", credentials=creds)
# Prepare the requests for filling colors
requests = []
for range_to_fill, hex_color in ranges_and_colors:
# Convert hex color to RGB format
rgb_color = hex_to_rgb(hex_color)
# Parse the range (e.g., "A1:B2")
range_parts = range_to_fill.split(":")
start_cell = range_parts[0]
end_cell = range_parts[1]
# Calculate the row and column indices
start_row = int(start_cell[1:]) - 1 # Convert to 0-indexed
start_col = ord(start_cell[0].upper()) - ord('A') # Convert column letter to index
end_row = int(end_cell[1:]) # Exclusive, so we do not subtract 1 here
end_col = ord(end_cell[0].upper()) - ord('A') + 1 # To include the last column
# Prepare the cells to fill within the specified range
rows = []
for row_index in range(start_row, end_row):
row_values = []
for col_index in range(start_col, end_col):
row_values.append({
"userEnteredFormat": {
"backgroundColor": rgb_color
}
})
rows.append({"values": row_values})
# Create the updateCells request
requests.append({
"updateCells": {
"range": {
"sheetId": 0, # Assuming sheetId 0, update with correct sheetId if needed
"startRowIndex": start_row,
"endRowIndex": end_row,
"startColumnIndex": start_col,
"endColumnIndex": end_col
},
"rows": rows,
"fields": "userEnteredFormat.backgroundColor"
}
})
# Execute the batch update request
body = {"requests": requests}
result = service.spreadsheets().batchUpdate(spreadsheetId=spreadsheet_id, body=body).execute()
# Return the result which contains the number of updated cells
print(f"Cells updated with colors.")
return result
except HttpError as error:
print(f"An error occurred: {error}")
return None
if __name__ == "__main__":
ranges_and_colors = [
("A1:B2", "#FF5733"), # Hex color for red-orange
("C3:D4", "#FFFF00"), # Hex color for yellow
("E5:F6", "#00FF00"), # Hex color for green
("G7:H8", "#800080") # Hex color for purple
]
fill_ranges_with_colors_in_spreadsheet(
spreadsheet_id="1bsmwdHv5wcxPbHnex4RGkTrwUoR-vl10gdO-L5Ohlrg", ranges_and_colors=ranges_and_colors
)
```