# Directory Structure
```
├── .gitignore
├── .python-version
├── main.py
├── pyproject.toml
├── README.md
├── server.py
├── sheets_functions.py
└── uv.lock
```
# Files
--------------------------------------------------------------------------------
/.python-version:
--------------------------------------------------------------------------------
```
1 | 3.12
2 |
```
--------------------------------------------------------------------------------
/.gitignore:
--------------------------------------------------------------------------------
```
1 | # Byte-compiled / optimized / DLL files
2 | __pycache__/
3 | *.py[cod]
4 | *$py.class
5 |
6 | # C extensions
7 | *.so
8 |
9 | # Distribution / packaging
10 | .Python
11 | build/
12 | develop-eggs/
13 | dist/
14 | downloads/
15 | eggs/
16 | .eggs/
17 | lib/
18 | lib64/
19 | parts/
20 | sdist/
21 | var/
22 | wheels/
23 | share/python-wheels/
24 | *.egg-info/
25 | .installed.cfg
26 | *.egg
27 | MANIFEST
28 |
29 | # PyInstaller
30 | # Usually these files are written by a python script from a template
31 | # before PyInstaller builds the exe, so as to inject date/other infos into it.
32 | *.manifest
33 | *.spec
34 |
35 | # Installer logs
36 | pip-log.txt
37 | pip-delete-this-directory.txt
38 |
39 | # Unit test / coverage reports
40 | htmlcov/
41 | .tox/
42 | .nox/
43 | .coverage
44 | .coverage.*
45 | .cache
46 | nosetests.xml
47 | coverage.xml
48 | *.cover
49 | *.py,cover
50 | .hypothesis/
51 | .pytest_cache/
52 | cover/
53 |
54 | # Translations
55 | *.mo
56 | *.pot
57 |
58 | # Django stuff:
59 | *.log
60 | local_settings.py
61 | db.sqlite3
62 | db.sqlite3-journal
63 |
64 | # Flask stuff:
65 | instance/
66 | .webassets-cache
67 |
68 | # Scrapy stuff:
69 | .scrapy
70 |
71 | # Sphinx documentation
72 | docs/_build/
73 |
74 | # PyBuilder
75 | .pybuilder/
76 | target/
77 |
78 | # Jupyter Notebook
79 | .ipynb_checkpoints
80 |
81 | # IPython
82 | profile_default/
83 | ipython_config.py
84 |
85 | # pyenv
86 | # For a library or package, you might want to ignore these files since the code is
87 | # intended to run in multiple environments; otherwise, check them in:
88 | # .python-version
89 |
90 | # pipenv
91 | # According to pypa/pipenv#598, it is recommended to include Pipfile.lock in version control.
92 | # However, in case of collaboration, if having platform-specific dependencies or dependencies
93 | # having no cross-platform support, pipenv may install dependencies that don't work, or not
94 | # install all needed dependencies.
95 | #Pipfile.lock
96 |
97 | # UV
98 | # Similar to Pipfile.lock, it is generally recommended to include uv.lock in version control.
99 | # This is especially recommended for binary packages to ensure reproducibility, and is more
100 | # commonly ignored for libraries.
101 | #uv.lock
102 |
103 | # poetry
104 | # Similar to Pipfile.lock, it is generally recommended to include poetry.lock in version control.
105 | # This is especially recommended for binary packages to ensure reproducibility, and is more
106 | # commonly ignored for libraries.
107 | # https://python-poetry.org/docs/basic-usage/#commit-your-poetrylock-file-to-version-control
108 | #poetry.lock
109 |
110 | # pdm
111 | # Similar to Pipfile.lock, it is generally recommended to include pdm.lock in version control.
112 | #pdm.lock
113 | # pdm stores project-wide configurations in .pdm.toml, but it is recommended to not include it
114 | # in version control.
115 | # https://pdm.fming.dev/latest/usage/project/#working-with-version-control
116 | .pdm.toml
117 | .pdm-python
118 | .pdm-build/
119 |
120 | # PEP 582; used by e.g. github.com/David-OConnor/pyflow and github.com/pdm-project/pdm
121 | __pypackages__/
122 |
123 | # Celery stuff
124 | celerybeat-schedule
125 | celerybeat.pid
126 |
127 | # SageMath parsed files
128 | *.sage.py
129 |
130 | # Environments
131 | .env
132 | .venv
133 | env/
134 | venv/
135 | ENV/
136 | env.bak/
137 | venv.bak/
138 |
139 | # Spyder project settings
140 | .spyderproject
141 | .spyproject
142 |
143 | # Rope project settings
144 | .ropeproject
145 |
146 | # mkdocs documentation
147 | /site
148 |
149 | # mypy
150 | .mypy_cache/
151 | .dmypy.json
152 | dmypy.json
153 |
154 | # Pyre type checker
155 | .pyre/
156 |
157 | # pytype static type analyzer
158 | .pytype/
159 |
160 | # Cython debug symbols
161 | cython_debug/
162 |
163 | # PyCharm
164 | # JetBrains specific template is maintained in a separate JetBrains.gitignore that can
165 | # be found at https://github.com/github/gitignore/blob/main/Global/JetBrains.gitignore
166 | # and can be added to the global gitignore or merged into this file. For a more nuclear
167 | # option (not recommended) you can uncomment the following to ignore the entire idea folder.
168 | #.idea/
169 |
170 | # Ruff stuff:
171 | .ruff_cache/
172 |
173 | # PyPI configuration file
174 | .pypirc
175 |
176 | # Jetbrains
177 | .idea
178 |
179 | # Project files
180 | credentials.json
181 | token.json
```
--------------------------------------------------------------------------------
/README.md:
--------------------------------------------------------------------------------
```markdown
1 | # Google Sheets MCP
2 | Model Context Protocol (MCP) integration with Google Sheets
3 |
4 | ## Features
5 | This integration allows the LLM to
6 | - List all the spreadsheets of the user
7 | - Create a new spreadsheet
8 | - Copy a spreadsheet to another
9 | - Write and Edit cells
10 | - Fill tool for cells
11 |
12 | ## Get Started
13 | 1. [Create a new Google Cloud project](https://console.cloud.google.com/projectcreate)
14 | 2. [Enable the Google Drive API](https://console.cloud.google.com/workspace-api/products)
15 | 3. [Configure an OAuth consent screen](https://console.cloud.google.com/apis/credentials/consent) ("internal" is fine for testing)
16 | 4. [Create an OAuth Client ID](https://console.cloud.google.com/apis/credentials/oauthclient) for application type "Desktop App"
17 | 5. Download the JSON file of your client's OAuth keys
18 | 6. Rename the key file to `credentials.json` and place into the root of this repo (i.e. `google-sheets-mcp/credentials.json`)
19 |
20 | > Note: If setting the user type to external, make sure to add your email for testing
```
--------------------------------------------------------------------------------
/main.py:
--------------------------------------------------------------------------------
```python
1 |
```
--------------------------------------------------------------------------------
/pyproject.toml:
--------------------------------------------------------------------------------
```toml
1 | [project]
2 | name = "googlesheetsmcp"
3 | version = "0.1.0"
4 | description = "Add your description here"
5 | readme = "README.md"
6 | requires-python = ">=3.12"
7 | dependencies = [
8 | "google-api-python-client>=2.164.0",
9 | "google-auth-httplib2>=0.2.0",
10 | "google-auth-oauthlib>=1.2.1",
11 | "mcp[cli]>=1.4.1",
12 | ]
13 |
```
--------------------------------------------------------------------------------
/server.py:
--------------------------------------------------------------------------------
```python
1 | from mcp.server.fastmcp import FastMCP
2 | from sheets_functions import get_user_spreadsheet_ids, create_new_spreadsheet, copy_to_spreadsheet, mass_edit_spreadsheet, fill_ranges_with_colors_in_spreadsheet
3 |
4 | mcp = FastMCP("Google Sheets MCP")
5 |
6 | @mcp.tool()
7 | def get_spreadsheets():
8 | """
9 | Gets all the spreadsheets of the authenticated user
10 |
11 | return:
12 | A seperated string of spreadsheet names and IDs
13 | """
14 | spreadsheets = get_user_spreadsheet_ids()
15 | if spreadsheets is None:
16 | return "Unable to get spreadsheets"
17 |
18 | if len(spreadsheets) == 0:
19 | return "No spreadsheets found"
20 |
21 | return "\n---\n".join(spreadsheets)
22 |
23 |
24 | @mcp.tool()
25 | def create_spreadsheet(title: str):
26 | """
27 | Creates a spreadsheet with the given title
28 | :param title: The title of the spreadsheet
29 | :return: A string that contains the spreadsheet title and ID
30 | """
31 | spreadsheet = create_new_spreadsheet(title)
32 |
33 | if spreadsheet is None:
34 | return "Unable to create spreadsheet"
35 |
36 | return f"Created new spreadsheet with title {spreadsheet[0]} and ID {spreadsheet[1]}"
37 |
38 |
39 | @mcp.tool()
40 | def copy_spreadsheet(sheet_id_to: str, sheet_id_from: str):
41 | """
42 | Copies a spreadsheet from sheet_id_from to sheet_id_to
43 |
44 | :param sheet_id_to: The ID of the sheet to copy
45 | :param sheet_id_from: The ID of the sheet that is being copied from
46 | :return:
47 | """
48 | speadsheets = copy_to_spreadsheet(sheet_id_to, sheet_id_from)
49 |
50 | if speadsheets is None:
51 | return "Unable to copy spreadsheet"
52 |
53 | return f"Copied spreadsheet {speadsheets[0]} to {speadsheets[1]} with sheet name {speadsheets[2]}"
54 |
55 |
56 | @mcp.tool()
57 | def edit_spreadsheet(spreadsheet_id, ranges_and_values):
58 | """
59 | Fills specified ranges in a spreadsheet with colors.
60 |
61 | :param spreadsheet_id: The ID of the spreadsheet to edit.
62 |
63 | :param ranges_and_colors:
64 | A list of tuples, where each tuple contains:
65 | 1. A string representing the range to fill with a color (e.g., "A1:B2").
66 | 2. A string representing the color to fill the range with, provided in hex format (e.g., "#FF5733").
67 |
68 | Example:
69 | ranges_and_colors = [
70 | ("A1:B2", "#FF5733"),
71 | ("C1:D2", "#4287f5"),
72 | ("E3:F3", "#FFC300"),
73 | ]
74 |
75 | In this example:
76 | - The range "A1:B2" will be filled with the color "#FF5733" (a shade of red).
77 | - The range "C1:D2" will be filled with the color "#4287f5" (a shade of blue).
78 | - The range "E3:F3" will be filled with the color "#FFC300" (a shade of yellow).
79 |
80 | The color is applied to the entire range of cells specified.
81 |
82 | """
83 |
84 | result = mass_edit_spreadsheet(spreadsheet_id, ranges_and_values)
85 | if result is None:
86 | return "Unable to edit spreadsheet"
87 | return f"{result.get('totalUpdatedCells')} cells updated."
88 |
89 |
90 | @mcp.tool()
91 | def fill_spreadsheet(spreadsheet_id, ranges_and_colors):
92 | """
93 | Fills specified ranges in a spreadsheet with colors.
94 |
95 | :param spreadsheet_id: The ID of the spreadsheet to edit.
96 |
97 | :param ranges_and_colors:
98 | A list of tuples, where each tuple contains:
99 | 1. A string representing the range to fill with a color (e.g., "A1:B2").
100 | 2. A string representing the color to fill the range with, provided in hex format (e.g., "#FF5733").
101 |
102 | Example:
103 | ranges_and_colors = [
104 | ("A1:B2", "#FF5733"),
105 | ("C1:D2", "#4287f5"),
106 | ("E3:F3", "#FFC300"),
107 | ]
108 |
109 | In this example:
110 | - The range "A1:B2" will be filled with the color "#FF5733" (a shade of red).
111 | - The range "C1:D2" will be filled with the color "#4287f5" (a shade of blue).
112 | - The range "E3:F3" will be filled with the color "#FFC300" (a shade of yellow).
113 |
114 | The color is applied to the entire range of cells specified.
115 |
116 | :param value_input_option:
117 | 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.
118 |
119 | :return:
120 | A dictionary with the result of the batch update, or None if there was an error. The dictionary contains the number of updated cells.
121 | """
122 |
123 | result = fill_ranges_with_colors_in_spreadsheet(spreadsheet_id, ranges_and_colors)
124 | if result is None:
125 | return "Unable to fill spreadsheet"
126 | return f"Cells updated with colors."
127 |
128 | if __name__ == "__main__":
129 | mcp.run(transport='stdio')
130 |
```
--------------------------------------------------------------------------------
/sheets_functions.py:
--------------------------------------------------------------------------------
```python
1 | import os.path
2 | from typing import List, Any
3 |
4 | from google.auth.transport.requests import Request
5 | from google.oauth2.credentials import Credentials
6 | from google_auth_oauthlib.flow import InstalledAppFlow
7 | from googleapiclient.discovery import build
8 | from googleapiclient.errors import HttpError
9 |
10 | # If modifying these scopes, delete the file token.json.
11 | SCOPES = ["https://www.googleapis.com/auth/drive.readonly", "https://www.googleapis.com/auth/spreadsheets"]
12 |
13 |
14 | def get_user_spreadsheet_ids() -> List[str] | None:
15 | creds = None
16 | if os.path.exists("token.json"):
17 | creds = Credentials.from_authorized_user_file("token.json", SCOPES)
18 | if not creds or not creds.valid:
19 | if creds and creds.expired and creds.refresh_token:
20 | creds.refresh(Request())
21 | else:
22 | flow = InstalledAppFlow.from_client_secrets_file("credentials.json", SCOPES)
23 | creds = flow.run_local_server(port=0)
24 | with open("token.json", "w") as token:
25 | token.write(creds.to_json())
26 |
27 | try:
28 | service = build("drive", "v3", credentials=creds)
29 | results = service.files().list(q="mimeType='application/vnd.google-apps.spreadsheet'",
30 | fields="files(id, name)").execute()
31 | files = results.get("files", [])
32 |
33 | if not files:
34 | print("No spreadsheets found.")
35 | return []
36 |
37 | return [f"{file["name"]}: {file["id"]}" for file in files]
38 |
39 | except HttpError as err:
40 | print(f"An error occurred: {err}")
41 | return None
42 |
43 |
44 | def create_new_spreadsheet(title: str) -> tuple[str, str] | None:
45 | creds = None
46 | if os.path.exists("token.json"):
47 | creds = Credentials.from_authorized_user_file("token.json", SCOPES)
48 | if not creds or not creds.valid:
49 | if creds and creds.expired and creds.refresh_token:
50 | creds.refresh(Request())
51 | else:
52 | flow = InstalledAppFlow.from_client_secrets_file("credentials.json", SCOPES)
53 | creds = flow.run_local_server(port=0)
54 | with open("token.json", "w") as token:
55 | token.write(creds.to_json())
56 |
57 | try:
58 | service = build("sheets", "v4", credentials=creds)
59 | spreadsheet = {"properties": {"title": title}}
60 | spreadsheet = (
61 | service.spreadsheets()
62 | .create(body=spreadsheet, fields="spreadsheetId")
63 | .execute()
64 | )
65 | print(f"Spreadsheet ID: {(spreadsheet.get('spreadsheetId'))}")
66 | return (title, spreadsheet.get("spreadsheetId"))
67 | except HttpError as error:
68 | print(f"An error occurred: {error}")
69 | return None
70 |
71 |
72 | def copy_to_spreadsheet(sheet_id_to: str, sheet_id_from: str) -> tuple[str, str, str] | None:
73 | creds = None
74 | if os.path.exists("token.json"):
75 | creds = Credentials.from_authorized_user_file("token.json", SCOPES)
76 | if not creds or not creds.valid:
77 | if creds and creds.expired and creds.refresh_token:
78 | creds.refresh(Request())
79 | else:
80 | flow = InstalledAppFlow.from_client_secrets_file("credentials.json", SCOPES)
81 | creds = flow.run_local_server(port=0)
82 | with open("token.json", "w") as token:
83 | token.write(creds.to_json())
84 |
85 | try:
86 | service = build("sheets", "v4", credentials=creds)
87 |
88 | # Get the name of the source spreadsheet and the sheet to copy
89 | source_spreadsheet = service.spreadsheets().get(spreadsheetId=sheet_id_from).execute()
90 | source_spreadsheet_name = source_spreadsheet["properties"]["title"]
91 | sheet_id_from_actual = source_spreadsheet["sheets"][0]["properties"]["sheetId"]
92 | sheet_name_from = source_spreadsheet["sheets"][0]["properties"]["title"]
93 |
94 | # Get the name of the destination spreadsheet
95 | destination_spreadsheet = service.spreadsheets().get(spreadsheetId=sheet_id_to).execute()
96 | destination_spreadsheet_name = destination_spreadsheet["properties"]["title"]
97 |
98 | # Copy the sheet to the destination
99 | copied_sheet = service.spreadsheets().sheets().copyTo(
100 | spreadsheetId=sheet_id_from,
101 | sheetId=sheet_id_from_actual,
102 | body={"destinationSpreadsheetId": sheet_id_to}
103 | ).execute()
104 |
105 | # Return the relevant names
106 | return (
107 | source_spreadsheet_name,
108 | destination_spreadsheet_name,
109 | copied_sheet["title"]
110 | )
111 |
112 | except HttpError as error:
113 | print(f"An error occurred: {error}")
114 | return None
115 |
116 |
117 | def mass_edit_spreadsheet(spreadsheet_id: str, ranges_and_values: List[tuple[str, List[List[Any]]]],
118 | value_input_option: str = "USER_ENTERED") -> dict | None:
119 | creds = None
120 | # Check if the token.json file exists and load credentials from it
121 | if os.path.exists("token.json"):
122 | creds = Credentials.from_authorized_user_file("token.json", SCOPES)
123 |
124 | # If credentials are not valid, refresh or authenticate again
125 | if not creds or not creds.valid:
126 | if creds and creds.expired and creds.refresh_token:
127 | creds.refresh(Request())
128 | else:
129 | flow = InstalledAppFlow.from_client_secrets_file("credentials.json", SCOPES)
130 | creds = flow.run_local_server(port=0)
131 | with open("token.json", "w") as token:
132 | token.write(creds.to_json())
133 |
134 | try:
135 | service = build("sheets", "v4", credentials=creds)
136 |
137 | # Prepare the request body for batch update
138 | data = [
139 | {"range": range_name, "values": values}
140 | for range_name, values in ranges_and_values
141 | ]
142 |
143 | body = {
144 | "valueInputOption": value_input_option,
145 | "data": data
146 | }
147 |
148 | # Execute the batch update request
149 | result = service.spreadsheets().values().batchUpdate(
150 | spreadsheetId=spreadsheet_id, body=body
151 | ).execute()
152 |
153 | # Return the result which contains the number of updated cells
154 | print(f"{result.get('totalUpdatedCells')} cells updated.")
155 | return result
156 |
157 | except HttpError as error:
158 | print(f"An error occurred: {error}")
159 | return None
160 |
161 |
162 | def hex_to_rgb(hex_color: str) -> dict:
163 | """
164 | Converts a hex color code (e.g., "#FF5733") to RGB format for Google Sheets API.
165 |
166 | :param hex_color: The hex color code (e.g., "#FF5733").
167 | :return: A dictionary with the RGB values for Google Sheets backgroundColor (e.g., {"red": 1.0, "green": 0.341, "blue": 0.2}).
168 | """
169 | hex_color = hex_color.lstrip('#') # Remove leading '#' if present
170 | r, g, b = bytes.fromhex(hex_color) # Convert hex to RGB
171 | return {
172 | "red": r / 255,
173 | "green": g / 255,
174 | "blue": b / 255
175 | }
176 |
177 |
178 | def fill_ranges_with_colors_in_spreadsheet(spreadsheet_id: str, ranges_and_colors: List[tuple[str, str]],
179 | value_input_option: str = "USER_ENTERED") -> dict | None:
180 |
181 | creds = None
182 | # Check if the token.json file exists and load credentials from it
183 | if os.path.exists("token.json"):
184 | creds = Credentials.from_authorized_user_file("token.json", SCOPES)
185 |
186 | # If credentials are not valid, refresh or authenticate again
187 | if not creds or not creds.valid:
188 | if creds and creds.expired and creds.refresh_token:
189 | creds.refresh(Request())
190 | else:
191 | flow = InstalledAppFlow.from_client_secrets_file("credentials.json", SCOPES)
192 | creds = flow.run_local_server(port=0)
193 | with open("token.json", "w") as token:
194 | token.write(creds.to_json())
195 |
196 | try:
197 | service = build("sheets", "v4", credentials=creds)
198 |
199 | # Prepare the requests for filling colors
200 | requests = []
201 |
202 | for range_to_fill, hex_color in ranges_and_colors:
203 | # Convert hex color to RGB format
204 | rgb_color = hex_to_rgb(hex_color)
205 |
206 | # Parse the range (e.g., "A1:B2")
207 | range_parts = range_to_fill.split(":")
208 | start_cell = range_parts[0]
209 | end_cell = range_parts[1]
210 |
211 | # Calculate the row and column indices
212 | start_row = int(start_cell[1:]) - 1 # Convert to 0-indexed
213 | start_col = ord(start_cell[0].upper()) - ord('A') # Convert column letter to index
214 | end_row = int(end_cell[1:]) # Exclusive, so we do not subtract 1 here
215 | end_col = ord(end_cell[0].upper()) - ord('A') + 1 # To include the last column
216 |
217 | # Prepare the cells to fill within the specified range
218 | rows = []
219 | for row_index in range(start_row, end_row):
220 | row_values = []
221 | for col_index in range(start_col, end_col):
222 | row_values.append({
223 | "userEnteredFormat": {
224 | "backgroundColor": rgb_color
225 | }
226 | })
227 | rows.append({"values": row_values})
228 |
229 | # Create the updateCells request
230 | requests.append({
231 | "updateCells": {
232 | "range": {
233 | "sheetId": 0, # Assuming sheetId 0, update with correct sheetId if needed
234 | "startRowIndex": start_row,
235 | "endRowIndex": end_row,
236 | "startColumnIndex": start_col,
237 | "endColumnIndex": end_col
238 | },
239 | "rows": rows,
240 | "fields": "userEnteredFormat.backgroundColor"
241 | }
242 | })
243 |
244 | # Execute the batch update request
245 | body = {"requests": requests}
246 | result = service.spreadsheets().batchUpdate(spreadsheetId=spreadsheet_id, body=body).execute()
247 |
248 | # Return the result which contains the number of updated cells
249 | print(f"Cells updated with colors.")
250 | return result
251 |
252 | except HttpError as error:
253 | print(f"An error occurred: {error}")
254 | return None
255 |
256 |
257 | if __name__ == "__main__":
258 | ranges_and_colors = [
259 | ("A1:B2", "#FF5733"), # Hex color for red-orange
260 | ("C3:D4", "#FFFF00"), # Hex color for yellow
261 | ("E5:F6", "#00FF00"), # Hex color for green
262 | ("G7:H8", "#800080") # Hex color for purple
263 | ]
264 | fill_ranges_with_colors_in_spreadsheet(
265 | spreadsheet_id="1bsmwdHv5wcxPbHnex4RGkTrwUoR-vl10gdO-L5Ohlrg", ranges_and_colors=ranges_and_colors
266 | )
267 |
268 |
```