Abstract
This report explores an optimized approach to integrating the Gemini CLI with Google Workspace via an MCP server. Traditionally, this process requires numerous custom tools, which increases development costs. We propose leveraging the inherent JSON schema requirements of the MCP server tools to directly construct request bodies for the batchUpdate methods of the Google Docs, Sheets, and Slides APIs. This approach aims to consolidate document management into just three core tools, significantly streamlining development and offering a scalable, cost-effective solution for Google Workspace automation and broader API integrations.
Introduction
The Gemini CLI provides a powerful way to interact with Google’s AI models from your terminal. When combined with a Model Context Protocol (MCP) server built using Google Apps Script Web Apps, it can be extended to manage Google Workspace services programmatically. My previous reports have detailed this integration, showing how to manage Google Docs, Sheets, and Slides (Ref1, Ref2, Ref3, Ref4, Ref5).
However, a significant challenge emerged: deeply managing these services often required creating a large number of specialized tools, leading to high development costs. This report introduces a more efficient method that addresses this issue.
The key insight is that MCP server tools require parameters defined by a JSON schema. This same structure can be used to construct the request body for the batchUpdate methods available in the Google Docs, Sheets, and Slides APIs. The batchUpdate methods are exceptionally powerful, allowing multiple operations (like inserting text, formatting, and creating tables) to be executed in a single, atomic API call.
By aligning the MCP tool’s schema with the batchUpdate request structure, we can consolidate a vast array of document manipulations into just three core tools—one for each application. This streamlined approach not only simplifies Google Workspace automation but also offers a scalable and cost-effective model for integrating with other APIs that use JSON schemas.
The Core Method: Unified Tools via batchUpdate
To implement this approach, I first used Gemini to generate a comprehensive JSON schema for the batchUpdate methods of the Docs, Sheets, and Slides APIs. The resulting schema, available in my repository at https://github.com/tanaikech/ToolsForMCPServer, is compatible with all batchUpdate methods for each respective API.
The batchUpdate method allows multiple requests to be bundled into a single call, which significantly improves efficiency. However, using it effectively requires specific object identifiers, such as child indexes in Docs, sheet IDs in Sheets, and object IDs in Slides. To retrieve this necessary information, three additional helper tools were created to fetch the document, spreadsheet, or presentation object structure.
Consequently, this entire solution uses a total of just six tools to enable comprehensive management of Google Docs, Sheets, and Slides through natural language commands.
Usage and Sample Prompts
For complete usage instructions, please visit my repository: https://github.com/tanaikech/ToolsForMCPServer?tab=readme-ov-file#usage
This section demonstrates sample prompts and their outcomes using the Gemini CLI with the MCP server.
Important: Before testing, please enable the Google Docs, Google Sheets, and Google Slides APIs in the Google Apps Script project containing the doPost function. Ref
1. Google Docs Examples
Example 1: Insert text and change font color
Prompt:
Document ID is "###". Put a text "sample text" with the font colors of "sample" and "text" to "red" and "blue" into the Google Docs.
Answer:
✔ manage_google_docs_using_docs_api (gas_web_apps MCP Server) {"requests":[{"insertText":{"location":{"index":1},"text":"sam…
✦ The text "sample text" has been inserted into the Google Doc with "sample" in red and "text" in blue.
Generated Request Body:
The tool generates a batchUpdate request that first inserts the text and then applies two separate text style updates in a single API call.
{
"requests": [
{
"insertText": {
"location": {
"index": 1
},
"text": "sample text"
}
},
{
"updateTextStyle": {
"fields": "foregroundColor",
"textStyle": {
"foregroundColor": {
"color": {
"rgbColor": {
"red": 1
}
}
}
},
"range": {
"endIndex": 7,
"startIndex": 1
}
}
},
{
"updateTextStyle": {
"range": {
"endIndex": 12,
"startIndex": 8
},
"fields": "foregroundColor",
"textStyle": {
"foregroundColor": {
"color": {
"rgbColor": {
"blue": 1
}
}
}
}
}
}
],
"documentId": "###"
}
Example 2: Insert a bulleted list
Prompt:
Document ID is "###". Append the following list to Google Docs as a bulleted list.
<List>
sample text1
sample text2
sample text3
</List>
Answer:
✔ manage_google_docs_using_docs_api (gas_web_apps MCP Server) {"requests":[{"insertText":{"location":{"index":1},"text":"sam…
✦ The bulleted list has been successfully appended to the Google Doc.
Generated Request Body:
{
"requests": [
{
"insertText": {
"location": {
"index": 1
},
"text": "sample text1\nsample text2\nsample text3\n"
}
},
{
"createParagraphBullets": {
"range": {
"endIndex": 40,
"startIndex": 1
},
"bulletPreset": "BULLET_DISC_CIRCLE_SQUARE"
}
}
],
"documentId": "###"
}
Example 3: Insert a table with values
Prompt:
Document ID is "###". Put a table with 2 rows and 3 columns at the top of the document with the following values. 1st Row: "A1", "B1", "C1" in columns. 2nd Row: "A2", "B2", "C2" in columns.
Initially, this prompt failed because the model could not generate the correct request body. To fix this, I provided context by having the Gemini CLI read a PDF version (ref1.pdf) of my blog post explaining how to create tables with the Docs API.
Prompt 1: Provide Context
Read the PDF file of "ref1.pdf" in the current directory.
Answer:
✔ ReadFile ref1.pdf
Read pdf file: ref1.pdf
Prompt 2: Re-attempt Mission
Follow the mission by understanding how to create a new table in Google Docs using the Docs API from "ref1.pdf".
<Mission>
Document ID is "###". Put a table with 2 rows and 3 columns at the top of the document with the following values. 1st Row: "A1", "B1", "C1" in columns. 2nd Row: "A2", "B2", "C2" in columns.
</Mission>
Answer:
✔ manage_google_docs_using_docs_api (gas_web_apps MCP Server) {"documentId":"###","…
✦ The table has been successfully created and populated with the specified values.
Generated Request Body:
With the added context, Gemini successfully generated a request body that first inserts a table and then sequentially inserts text into each cell.
{
"documentId": "###",
"requests": [
{
"insertTable": {
"location": {
"index": 1
},
"columns": 3,
"rows": 2
}
},
{
"insertText": {
"text": "C2",
"location": {
"index": 16
}
}
},
{
"insertText": {
"location": {
"index": 14
},
"text": "B2"
}
},
{
"insertText": {
"location": {
"index": 12
},
"text": "A2"
}
},
{
"insertText": {
"location": {
"index": 9
},
"text": "C1"
}
},
{
"insertText": {
"text": "B1",
"location": {
"index": 7
}
}
},
{
"insertText": {
"text": "A1",
"location": {
"index": 5
}
}
}
]
}
2. Google Sheets Examples
Example 1: Insert text and change font color
Prompt:
Spreadsheet ID is "###". Put a text "sample text" with the font colors of "sample" and "text" to "red" and "blue" into a cell "'Sheet1'!A1".
Answer:
✔ get_google_sheet_object_using_sheets_api (gas_web_apps MCP Server) {"spreadsheetId":"###…
✔ manage_google_sheets_using_sheets_api (gas_web_apps MCP Server) {"spreadsheetId":"###…
✦ I have put "sample text" into cell 'Sheet1'!B1 with "sample" in red and "text" in blue.
Generated Request Body:
The get_google_sheet_object_using_sheets_api tool first converts the sheet name “Sheet1” to its required sheetId. Then, manage_google_sheets_using_sheets_api constructs a single repeatCell request to update the cell’s value and text formatting simultaneously.
{
"spreadsheetId":"###",
"requests":[
{
"repeatCell":{
"range":{
"startRowIndex":0,
"startColumnIndex":1,
"endColumnIndex":2,
"sheetId":###,
"endRowIndex":1
},
"fields":"userEnteredValue,textFormatRuns",
"cell":{
"userEnteredValue":{
"stringValue":"sample text"
},
"textFormatRuns":[
{
"format":{
"foregroundColor":{
"red":1
}
},
"startIndex":0
},
{
"format":{
"foregroundColor":{
"blue":1
}
},
"startIndex":7
}
]
}
}
}
]
}
Example 2: Delete and insert a sheet
Prompt:
Spreadsheet ID is "###". Delete "temp1" sheet and insert a new sheet as "temp2".
Answer:
✔ get_google_sheet_object_using_sheets_api (gas_web_apps MCP Server) {"spreadsheetId":"###…
✔ manage_google_sheets_using_sheets_api (gas_web_apps MCP Server) {"spreadsheetId":"###…
✦ I have successfully deleted the "temp1" sheet and inserted a new sheet named "temp2" in the spreadsheet.
Generated Request Body:
The batchUpdate method combines a deleteSheet and an addSheet request into one atomic operation.
{
"requests": [
{
"deleteSheet": {
"sheetId": "###"
}
},
{
"addSheet": {
"properties": {
"title": "temp2"
}
}
}
],
"spreadsheetId": "###"
}
3. Google Slides Examples
Example 1: Insert text and change font color
Prompt:
Presentation ID is "###". Put a text "sample text" with the font colors of "sample" and "text" to "red" and "blue" into the 1st page of the Google Slides.
Answer:
✔ get_google_slides_object_using_slides_api (gas_web_apps MCP Server) {"presentationId":"###…
✔ manage_google_slides_using_slides_api (gas_web_apps MCP Server) {"requests":[{"createShape":{"shapeType":"TEXT_BOX","objec…
✦ I have put "sample text" with "sample" in red and "text" in blue into the 1st page of the Google Slides.
Generated Request Body:
This complex operation, involving creating a text box, inserting text, and applying two different styles, is executed as a single batchUpdate request.
{
"requests": [
{
"createShape": {
"shapeType": "TEXT_BOX",
"objectId": "myTextBox",
"elementProperties": {
"transform": {
"unit": "EMU",
"scaleY": 1,
"translateY": 2000000,
"scaleX": 1,
"translateX": 2000000
},
"pageObjectId": "p",
"size": {
"width": {
"magnitude": 5000000,
"unit": "EMU"
},
"height": {
"unit": "EMU",
"magnitude": 1000000
}
}
}
}
},
{
"insertText": {
"text": "sample text",
"insertionIndex": 0,
"objectId": "myTextBox"
}
},
{
"updateTextStyle": {
"fields": "foregroundColor",
"objectId": "myTextBox",
"textRange": {
"type": "FIXED_RANGE",
"startIndex": 0,
"endIndex": 6
},
"style": {
"foregroundColor": {
"opaqueColor": {
"rgbColor": {
"blue": 0,
"green": 0,
"red": 1
}
}
}
}
}
},
{
"updateTextStyle": {
"fields": "foregroundColor",
"objectId": "myTextBox",
"style": {
"foregroundColor": {
"opaqueColor": {
"rgbColor": {
"red": 0,
"blue": 1,
"green": 0
}
}
}
},
"textRange": {
"startIndex": 7,
"endIndex": 11,
"type": "FIXED_RANGE"
}
}
}
],
"presentationId": "###"
}
Example 2: Put shapes and set texts
Prompt:
Presentation ID is "###". On the 1st page ot Google Slides, put 3 rectangle shapes aligned vertically in the center of the page. And, put the texts of "sample text 1", "sample text 2", and "sample text 3" into each shape.
Answer:
✔ get_google_slides_object_using_slides_api (gas_web_apps MCP Server) {"presentationId":"###…
✔ manage_google_slides_using_slides_api (gas_web_apps MCP Server) {"presentationId":"###…
✦ I have successfully added three rectangle shapes with the specified texts, aligned vertically in the center of the first page
of the Google Slides presentation.
Generated Request Body:
The prompt is translated into six batched requests: one createShape and one insertText for each of the three rectangles.
{
"presentationId": "###",
"requests": [
{
"createShape": {
"objectId": "shape1",
"shapeType": "RECTANGLE",
"elementProperties": {
"pageObjectId": "p",
"transform": {
"scaleY": 1,
"unit": "EMU",
"scaleX": 1,
"translateX": 3572000,
"translateY": 1721750
},
"size": {
"height": {
"magnitude": 500000,
"unit": "EMU"
},
"width": {
"unit": "EMU",
"magnitude": 2000000
}
}
}
}
},
{
"insertText": {
"text": "sample text 1",
"objectId": "shape1"
}
},
{
"createShape": {
"objectId": "shape2",
"elementProperties": {
"transform": {
"translateY": 2321750,
"scaleX": 1,
"unit": "EMU",
"scaleY": 1,
"translateX": 3572000
},
"size": {
"height": {
"magnitude": 500000,
"unit": "EMU"
},
"width": {
"unit": "EMU",
"magnitude": 2000000
}
},
"pageObjectId": "p"
},
"shapeType": "RECTANGLE"
}
},
{
"insertText": {
"objectId": "shape2",
"text": "sample text 2"
}
},
{
"createShape": {
"elementProperties": {
"size": {
"width": {
"magnitude": 2000000,
"unit": "EMU"
},
"height": {
"unit": "EMU",
"magnitude": 500000
}
},
"transform": {
"scaleX": 1,
"translateY": 2921750,
"scaleY": 1,
"unit": "EMU",
"translateX": 3572000
},
"pageObjectId": "p"
},
"objectId": "shape3",
"shapeType": "RECTANGLE"
}
},
{
"insertText": {
"text": "sample text 3",
"objectId": "shape3"
}
}
]
}
4. Advanced Applications
Copy a table from Google Sheets to Google Docs
This example demonstrates a complex, cross-application workflow. Building on “Example 3” for Google Docs, I tested copying a table directly from Sheets to Docs.
Prompt:
Read the PDF file of "ref1.pdf" in the current directory. Follow the mission by understanding how to create a new table in Google Docs using the Docs API from "ref1.pdf".
<Mission>
Document ID and Spreadsheet ID are "###" and "###", respectively. Copy a table from "'Sheet1'!A1:C2" on Google Sheets to the top of the document on Google Docs.
</Mission>
Answer:
✔ ReadFile ref1.pdf
Read pdf file: ref1.pdf
✔ get_values_from_google_sheets (gas_web_apps MCP Server) {"spreadsheetId":"###","r…
[["a1","b1","c1"],["a2","b2","c2"]]
✔ manage_google_docs_using_docs_api (gas_web_apps MCP Server) {"documentId":"###","…
Generated Request Body:
First, the ReadFile tool provided context. Next, the get_values_from_google_sheets tool retrieved the data from the specified range. Finally, Gemini constructed the correct batchUpdate request body for the Docs API to create the table and populate it with the retrieved data.
{
"documentId": "###",
"requests": [
{
"insertTable": {
"location": {
"index": 1
},
"columns": 3,
"rows": 2
}
},
{
"insertText": {
"text": "c2",
"location": {
"index": 16
}
}
},
{
"insertText": {
"location": {
"index": 14
},
"text": "b2"
}
},
{
"insertText": {
"location": {
"index": 12
},
"text": "a2"
}
},
{
"insertText": {
"location": {
"index": 9
},
"text": "c1"
}
},
{
"insertText": {
"text": "b1",
"location": {
"index": 7
}
}
},
{
"insertText": {
"text": "a1",
"location": {
"index": 5
}
}
}
]
}
This successful test shows that by using core tools for the Docs, Sheets, and Slides APIs, complex processes can be accomplished via the Gemini API and MCP, unlocking infinite possibilities.
Note on Error Handling and Limitations
During testing, I observed that the Gemini CLI can exhibit self-correction. Even when an initial request fails, the CLI can automatically revise the request body based on the error message and retry until it succeeds.
While this approach may not handle every conceivable user request perfectly, it provides a powerful and robust foundation for the detailed management of Docs, Sheets, and Slides using natural language.
Summary
- Problem: Automating Google Workspace with Gemini CLI and an MCP server traditionally required numerous, costly custom tools for anything beyond simple tasks.
- Solution: This report proposes a unified approach that consolidates document management into just three core tools by leveraging the
batchUpdatemethods of the Google Docs, Sheets, and Slides APIs. - Methodology: The JSON schema required by the MCP server tools is used to directly construct the request bodies for the powerful
batchUpdateAPI calls, which can handle multiple operations at once. - Implementation: The solution uses six tools in total: one primary
batchUpdatetool for each of the three services, and one helper tool for each to retrieve necessary object IDs. - Benefits: This method dramatically reduces development cost and complexity, and enables sophisticated, cross-application automations—such as copying a table from Sheets to Docs—using simple, natural language prompts.