googlesheets-automation

Compare original and translation side by side

🇺🇸

Original

English

🇨🇳

Translation

Chinese

Google Sheets Automation via Rube MCP

通过Rube MCP实现Google Sheets自动化

Automate Google Sheets workflows including reading/writing data, managing spreadsheets and tabs, formatting cells, filtering rows, and upserting records through Composio's Google Sheets toolkit.

通过Composio的Google Sheets工具包，自动化Google Sheets工作流，包括读写数据、管理电子表格和标签页、格式化单元格、筛选行以及插入更新记录。

Prerequisites

前提条件

Rube MCP must be connected (RUBE_SEARCH_TOOLS available)
Active Google Sheets connection via
```
RUBE_MANAGE_CONNECTIONS
```
with toolkit
```
googlesheets
```
Always call
```
RUBE_SEARCH_TOOLS
```
first to get current tool schemas

必须已连接Rube MCP（需提供RUBE_SEARCH_TOOLS）
通过
```
RUBE_MANAGE_CONNECTIONS
```
并使用工具包
```
googlesheets
```
建立有效的Google Sheets连接
始终先调用
```
RUBE_SEARCH_TOOLS
```
获取当前工具的架构

Setup

配置步骤

Get Rube MCP: Add

https://rube.app/mcp

as an MCP server in your client configuration. No API keys needed — just add the endpoint and it works.

Verify Rube MCP is available by confirming
```
RUBE_SEARCH_TOOLS
```
responds
Call
```
RUBE_MANAGE_CONNECTIONS
```
with toolkit
```
googlesheets
```
If connection is not ACTIVE, follow the returned auth link to complete Google OAuth
Confirm connection status shows ACTIVE before running any workflows

获取Rube MCP：在客户端配置中添加

https://rube.app/mcp

作为MCP服务器。无需API密钥 — 只需添加端点即可使用。

确认
```
RUBE_SEARCH_TOOLS
```
能正常响应，以此验证Rube MCP是否可用
调用
```
RUBE_MANAGE_CONNECTIONS
```
并指定工具包
```
googlesheets
```
如果连接未处于激活状态，按照返回的授权链接完成Google OAuth认证
在运行任何工作流之前，确认连接状态显示为ACTIVE

Core Workflows

核心工作流

1. Read and Write Data

1. 读写数据

When to use: User wants to read data from or write data to a Google Sheet

Tool sequence:

```
GOOGLESHEETS_SEARCH_SPREADSHEETS
```
- Find spreadsheet by name if ID unknown [Prerequisite]
```
GOOGLESHEETS_GET_SHEET_NAMES
```
- Enumerate tab names to target the right sheet [Prerequisite]
```
GOOGLESHEETS_BATCH_GET
```
- Read data from one or more ranges [Required]
```
GOOGLESHEETS_BATCH_UPDATE
```
- Write data to a range or append rows [Required]
```
GOOGLESHEETS_VALUES_UPDATE
```
- Update a single specific range [Alternative]
```
GOOGLESHEETS_SPREADSHEETS_VALUES_APPEND
```
- Append rows to end of table [Alternative]

Key parameters:

```
spreadsheet_id
```
: Alphanumeric ID from the spreadsheet URL (between '/d/' and '/edit')
```
ranges
```
: A1 notation array (e.g., 'Sheet1!A1:Z1000'); always use bounded ranges
```
sheet_name
```
: Tab name (case-insensitive matching supported)
```
values
```
: 2D array where each inner array is a row
```
first_cell_location
```
: Starting cell in A1 notation (omit to append)
```
valueInputOption
```
: 'USER_ENTERED' (parsed) or 'RAW' (literal)

Pitfalls:

Mis-cased or non-existent tab names error "Sheet 'X' not found"
Empty ranges may omit
```
valueRanges[i].values
```
; treat missing as empty array
```
GOOGLESHEETS_BATCH_UPDATE
```
values must be a 2D array (list of lists), even for a single row
Unbounded ranges like 'A:Z' on sheets with >10,000 rows may cause timeouts; always bound with row limits
Append follows the detected
```
tableRange
```
; use returned
```
updatedRange
```
to verify placement

适用场景：用户需要从Google Sheets读取数据或向其中写入数据

工具序列:

```
GOOGLESHEETS_SEARCH_SPREADSHEETS
```
- 若未知ID，通过名称查找电子表格 [前提步骤]
```
GOOGLESHEETS_GET_SHEET_NAMES
```
- 枚举标签页名称以定位目标工作表 [前提步骤]
```
GOOGLESHEETS_BATCH_GET
```
- 从一个或多个区域读取数据 [必需步骤]
```
GOOGLESHEETS_BATCH_UPDATE
```
- 向某个区域写入数据或追加行 [必需步骤]
```
GOOGLESHEETS_VALUES_UPDATE
```
- 更新单个特定区域 [替代方案]
```
GOOGLESHEETS_SPREADSHEETS_VALUES_APPEND
```
- 向表格末尾追加行 [替代方案]

关键参数:

```
spreadsheet_id
```
: 电子表格URL中位于'/d/'和'/edit/'之间的字母数字ID
```
ranges
```
: A1表示法数组（例如'Sheet1!A1:Z1000'）；始终使用有界区域
```
sheet_name
```
: 标签页名称（支持不区分大小写的匹配）
```
values
```
: 二维数组，每个内部数组代表一行数据
```
first_cell_location
```
: 起始单元格的A1表示法（省略则追加到末尾）
```
valueInputOption
```
: 'USER_ENTERED'（解析后输入）或'RAW'（原样输入）

注意事项:

标签页名称大小写错误或不存在时，会触发"Sheet 'X' not found"错误
空区域可能会省略
```
valueRanges[i].values
```
；需将缺失值视为空数组
```
GOOGLESHEETS_BATCH_UPDATE
```
的values参数必须是二维数组（列表的列表），即使是单行数据也需如此
对于超过10,000行的工作表，使用'A:Z'这类无界区域可能会导致超时；始终要设置行限制
追加操作会遵循检测到的
```
tableRange
```
；可通过返回的
```
updatedRange
```
验证数据位置

2. Create and Manage Spreadsheets

2. 创建与管理电子表格

When to use: User wants to create a new spreadsheet or manage tabs within one

Tool sequence:

```
GOOGLESHEETS_CREATE_GOOGLE_SHEET1
```
- Create a new spreadsheet [Required]
```
GOOGLESHEETS_ADD_SHEET
```
- Add a new tab/worksheet [Required]
```
GOOGLESHEETS_UPDATE_SHEET_PROPERTIES
```
- Rename, hide, reorder, or color tabs [Optional]
```
GOOGLESHEETS_GET_SPREADSHEET_INFO
```
- Get full spreadsheet metadata [Optional]
```
GOOGLESHEETS_FIND_WORKSHEET_BY_TITLE
```
- Check if a specific tab exists [Optional]

Key parameters:

```
title
```
: Spreadsheet or sheet tab name
```
spreadsheetId
```
: Target spreadsheet ID
```
forceUnique
```
: Auto-append suffix if tab name exists (default true)
```
properties.gridProperties
```
: Set row/column counts, frozen rows

Pitfalls:

Sheet names must be unique within a spreadsheet
Default sheet names are locale-dependent ('Sheet1' in English, 'Hoja 1' in Spanish)
Don't use
```
index
```
when creating multiple sheets in parallel (causes 'index too high' errors)
```
GOOGLESHEETS_GET_SPREADSHEET_INFO
```
can return 403 if account lacks access

适用场景：用户需要创建新电子表格或管理其中的标签页

工具序列:

```
GOOGLESHEETS_CREATE_GOOGLE_SHEET1
```
- 创建新电子表格 [必需步骤]
```
GOOGLESHEETS_ADD_SHEET
```
- 添加新标签页/工作表 [必需步骤]
```
GOOGLESHEETS_UPDATE_SHEET_PROPERTIES
```
- 重命名、隐藏、重新排序标签页或设置标签页颜色 [可选步骤]
```
GOOGLESHEETS_GET_SPREADSHEET_INFO
```
- 获取完整的电子表格元数据 [可选步骤]
```
GOOGLESHEETS_FIND_WORKSHEET_BY_TITLE
```
- 检查特定标签页是否存在 [可选步骤]

关键参数:

```
title
```
: 电子表格或标签页的名称
```
spreadsheetId
```
: 目标电子表格ID
```
forceUnique
```
: 若标签页名称已存在，自动追加后缀（默认值为true）
```
properties.gridProperties
```
: 设置行/列数量、冻结行

注意事项:

同一电子表格内的标签页名称必须唯一
默认标签页名称取决于区域设置（英文为'Sheet1'，西班牙语为'Hoja 1'）
并行创建多个标签页时不要使用
```
index
```
参数（会触发'index too high'错误）
若账户无访问权限，
```
GOOGLESHEETS_GET_SPREADSHEET_INFO
```
会返回403错误

3. Search and Filter Rows

3. 搜索与筛选行

When to use: User wants to find specific rows or apply filters to sheet data

Tool sequence:

```
GOOGLESHEETS_LOOKUP_SPREADSHEET_ROW
```
- Find first row matching exact cell value [Required]
```
GOOGLESHEETS_SET_BASIC_FILTER
```
- Apply filter/sort to a range [Alternative]
```
GOOGLESHEETS_CLEAR_BASIC_FILTER
```
- Remove existing filter [Optional]
```
GOOGLESHEETS_BATCH_GET
```
- Read filtered results [Optional]

Key parameters:

```
query
```
: Exact text value to match (matches entire cell content)
```
range
```
: A1 notation range to search within
```
case_sensitive
```
: Boolean for case-sensitive matching (default false)
```
filter.range
```
: Grid range with sheet_id for basic filter
```
filter.criteria
```
: Column-based filter conditions
```
filter.sortSpecs
```
: Sort specifications

Pitfalls:

```
GOOGLESHEETS_LOOKUP_SPREADSHEET_ROW
```
matches entire cell content, not substrings
Sheet names with spaces must be single-quoted in ranges (e.g., "'My Sheet'!A:Z")
Bare sheet names without ranges are not supported for lookup; always specify a range

适用场景：用户需要查找特定行或对工作表数据应用筛选器

工具序列:

```
GOOGLESHEETS_LOOKUP_SPREADSHEET_ROW
```
- 查找与单元格精确值匹配的第一行 [必需步骤]
```
GOOGLESHEETS_SET_BASIC_FILTER
```
- 对某个区域应用筛选/排序 [替代方案]
```
GOOGLESHEETS_CLEAR_BASIC_FILTER
```
- 移除现有筛选器 [可选步骤]
```
GOOGLESHEETS_BATCH_GET
```
- 读取筛选后的结果 [可选步骤]

关键参数:

```
query
```
: 要匹配的精确文本值（匹配整个单元格内容）
```
range
```
: 搜索范围的A1表示法
```
case_sensitive
```
: 是否区分大小写的布尔值（默认值为false）
```
filter.range
```
: 包含sheet_id的网格范围，用于基础筛选
```
filter.criteria
```
: 基于列的筛选条件
```
filter.sortSpecs
```
: 排序规则

注意事项:

```
GOOGLESHEETS_LOOKUP_SPREADSHEET_ROW
```
匹配整个单元格内容，而非子字符串
名称包含空格的标签页，在区域表示法中必须用单引号括起来（例如"'My Sheet'!A:Z"）
查找操作不支持不带区域的裸标签页名称；始终要指定范围

4. Upsert Rows by Key

4. 按主键Upsert行

When to use: User wants to update existing rows or insert new ones based on a unique key column

Tool sequence:

```
GOOGLESHEETS_UPSERT_ROWS
```
- Update matching rows or append new ones [Required]

Key parameters:

```
spreadsheetId
```
: Target spreadsheet ID
```
sheetName
```
: Tab name
```
keyColumn
```
: Column header name used as unique identifier (e.g., 'Email', 'SKU')
```
headers
```
: List of column names for the data
```
rows
```
: 2D array of data rows
```
strictMode
```
: Error on mismatched column counts (default true)

Pitfalls:

```
keyColumn
```
must be an actual header name, NOT a column letter (e.g., 'Email' not 'A')
If
```
headers
```
is NOT provided, first row of
```
rows
```
is treated as headers
With
```
strictMode=true
```
, rows with more values than headers cause an error
Auto-adds missing columns to the sheet

适用场景：用户需要基于唯一键列更新现有行或插入新行

工具序列:

```
GOOGLESHEETS_UPSERT_ROWS
```
- 更新匹配行或追加新行 [必需步骤]

关键参数:

```
spreadsheetId
```
: 目标电子表格ID
```
sheetName
```
: 标签页名称
```
keyColumn
```
: 用作唯一标识符的列标题名称（例如'Email'、'SKU'）
```
headers
```
: 数据的列名称列表
```
rows
```
: 数据行的二维数组
```
strictMode
```
: 列数不匹配时触发错误（默认值为true）

注意事项:

```
keyColumn
```
必须是实际的标题名称，而非列字母（例如使用'Email'而非'A'）
若未提供
```
headers
```
，则
```
rows
```
的第一行会被视为标题
当
```
strictMode=true
```
时，值数量与标题数量不匹配的行会触发错误
会自动向工作表添加缺失的列

5. Format Cells

5. 格式化单元格

When to use: User wants to apply formatting (bold, colors, font size) to cells

Tool sequence:

```
GOOGLESHEETS_GET_SPREADSHEET_INFO
```
- Get numeric sheetId for target tab [Prerequisite]
```
GOOGLESHEETS_FORMAT_CELL
```
- Apply formatting to a range [Required]
```
GOOGLESHEETS_UPDATE_SHEET_PROPERTIES
```
- Change frozen rows, column widths [Optional]

Key parameters:

```
spreadsheet_id
```
: Spreadsheet ID
```
worksheet_id
```
: Numeric sheetId (NOT tab name); get from GET_SPREADSHEET_INFO
```
range
```
: A1 notation (e.g., 'A1:F1') - preferred over index fields
```
bold
```
,
```
italic
```
,
```
underline
```
,
```
strikethrough
```
: Boolean formatting options
```
red
```
,
```
green
```
,
```
blue
```
: Background color as 0.0-1.0 floats (NOT 0-255 ints)
```
fontSize
```
: Font size in points

Pitfalls:

Requires numeric
```
worksheet_id
```
, not tab title; get from spreadsheet metadata
Color channels are 0-1 floats (e.g., 1.0 for full red), NOT 0-255 integers
Responses may return empty reply objects ([{}]); verify formatting via readback
Format one range per call; batch formatting requires separate calls

适用场景：用户需要对单元格应用格式设置（加粗、颜色、字号）

工具序列:

```
GOOGLESHEETS_GET_SPREADSHEET_INFO
```
- 获取目标标签页的数字形式sheetId [前提步骤]
```
GOOGLESHEETS_FORMAT_CELL
```
- 对某个区域应用格式设置 [必需步骤]
```
GOOGLESHEETS_UPDATE_SHEET_PROPERTIES
```
- 修改冻结行、列宽 [可选步骤]

关键参数:

```
spreadsheet_id
```
: 电子表格ID
```
worksheet_id
```
: 数字形式的sheetId（而非标签页名称）；从GET_SPREADSHEET_INFO获取
```
range
```
: A1表示法（例如'A1:F1'） - 优先于索引字段
```
bold
```
,
```
italic
```
,
```
underline
```
,
```
strikethrough
```
: 布尔型格式选项
```
red
```
,
```
green
```
,
```
blue
```
: 背景色，取值为0.0-1.0的浮点数（而非0-255的整数）
```
fontSize
```
: 字号（单位为磅）

注意事项:

需要数字形式的
```
worksheet_id
```
，而非标签页标题；需从电子表格元数据中获取
颜色通道值为0-1的浮点数（例如1.0表示纯红色），而非0-255的整数
响应可能返回空对象数组([{}])；需通过回读验证格式设置是否生效
每次调用仅能格式化一个区域；批量格式化需要单独调用

Common Patterns

常见模式

ID Resolution

ID解析

Spreadsheet name -> ID:
```
GOOGLESHEETS_SEARCH_SPREADSHEETS
```
with
```
query
```
Tab name -> sheetId:
```
GOOGLESHEETS_GET_SPREADSHEET_INFO
```
, extract from sheets metadata
Tab existence check:
```
GOOGLESHEETS_FIND_WORKSHEET_BY_TITLE
```

电子表格名称 -> ID: 使用带
```
query
```
参数的
```
GOOGLESHEETS_SEARCH_SPREADSHEETS
```
标签页名称 -> sheetId: 使用
```
GOOGLESHEETS_GET_SPREADSHEET_INFO
```
，从工作表元数据中提取
标签页存在性检查: 使用
```
GOOGLESHEETS_FIND_WORKSHEET_BY_TITLE
```

Rate Limits

速率限制

Google Sheets enforces strict rate limits:

Max 60 reads/minute and 60 writes/minute
Exceeding limits causes errors; batch operations where possible

Use

GOOGLESHEETS_BATCH_GET

and

GOOGLESHEETS_BATCH_UPDATE

for efficiency

Google Sheets实施严格的速率限制：

最多60次读取/分钟，60次写入/分钟
超出限制会触发错误；尽可能使用批量操作

为提高效率，使用

GOOGLESHEETS_BATCH_GET

和

GOOGLESHEETS_BATCH_UPDATE

Data Patterns

数据模式

Always read before writing to understand existing layout
Use
```
GOOGLESHEETS_UPSERT_ROWS
```
for CRM syncs, inventory updates, and dedup scenarios
Append mode (omit
```
first_cell_location
```
) is safest for adding new records
Use
```
GOOGLESHEETS_CLEAR_VALUES
```
to clear content while preserving formatting

写入前务必先读取，以了解现有布局
CRM同步、库存更新和去重场景下使用
```
GOOGLESHEETS_UPSERT_ROWS
```
添加新记录时，使用追加模式（省略
```
first_cell_location
```
）最为安全
使用
```
GOOGLESHEETS_CLEAR_VALUES
```
清除内容，同时保留格式设置

Known Pitfalls

已知注意事项

Tab names: Locale-dependent defaults; 'Sheet1' may not exist in non-English accounts
Range notation: Sheet names with spaces need single quotes in A1 notation
Unbounded ranges: Can timeout on large sheets; always specify row bounds (e.g., 'A1:Z10000')
2D arrays: All value parameters must be list-of-lists, even for single rows
Color values: Floats 0.0-1.0, not integers 0-255
Formatting IDs:
```
FORMAT_CELL
```
needs numeric sheetId, not tab title
Rate limits: 60 reads/min and 60 writes/min; batch to stay within limits
Delete dimension:
```
GOOGLESHEETS_DELETE_DIMENSION
```
is irreversible; double-check bounds

标签页名称: 默认名称取决于区域设置；非英文账户中可能不存在'Sheet1'
区域表示法: 名称包含空格的标签页，在A1表示法中需要用单引号括起来
无界区域: 在大型工作表上可能会超时；始终要指定行边界（例如'A1:Z10000'）
二维数组: 所有值参数必须是列表的列表，即使是单行数据也需如此
颜色值: 取值为0.0-1.0的浮点数，而非0-255的整数
格式化ID:
```
FORMAT_CELL
```
需要数字形式的sheetId，而非标签页标题
速率限制: 60次读取/分钟，60次写入/分钟；通过批量操作控制在限制范围内
删除维度:
```
GOOGLESHEETS_DELETE_DIMENSION
```
操作不可逆；请仔细检查边界

Quick Reference

快速参考

Task	Tool Slug	Key Params
Search spreadsheets	`GOOGLESHEETS_SEARCH_SPREADSHEETS`	`query` , `search_type`
Create spreadsheet	`GOOGLESHEETS_CREATE_GOOGLE_SHEET1`	`title`
List tabs	`GOOGLESHEETS_GET_SHEET_NAMES`	`spreadsheet_id`
Add tab	`GOOGLESHEETS_ADD_SHEET`	`spreadsheetId` , `title`
Read data	`GOOGLESHEETS_BATCH_GET`	`spreadsheet_id` , `ranges`
Read single range	`GOOGLESHEETS_VALUES_GET`	`spreadsheet_id` , `range`
Write data	`GOOGLESHEETS_BATCH_UPDATE`	`spreadsheet_id` , `sheet_name` , `values`
Update range	`GOOGLESHEETS_VALUES_UPDATE`	`spreadsheet_id` , `range` , `values`
Append rows	`GOOGLESHEETS_SPREADSHEETS_VALUES_APPEND`	`spreadsheetId` , `range` , `values`
Upsert rows	`GOOGLESHEETS_UPSERT_ROWS`	`spreadsheetId` , `sheetName` , `keyColumn` , `rows`
Lookup row	`GOOGLESHEETS_LOOKUP_SPREADSHEET_ROW`	`spreadsheet_id` , `query`
Format cells	`GOOGLESHEETS_FORMAT_CELL`	`spreadsheet_id` , `worksheet_id` , `range`
Set filter	`GOOGLESHEETS_SET_BASIC_FILTER`	`spreadsheetId` , `filter`
Clear values	`GOOGLESHEETS_CLEAR_VALUES`	`spreadsheet_id` , range
Delete rows/cols	`GOOGLESHEETS_DELETE_DIMENSION`	`spreadsheet_id` , `sheet_name` , dimension
Spreadsheet info	`GOOGLESHEETS_GET_SPREADSHEET_INFO`	`spreadsheet_id`
Update tab props	`GOOGLESHEETS_UPDATE_SHEET_PROPERTIES`	`spreadsheetId` , properties

任务	工具标识	关键参数
搜索电子表格	`GOOGLESHEETS_SEARCH_SPREADSHEETS`	`query` , `search_type`
创建电子表格	`GOOGLESHEETS_CREATE_GOOGLE_SHEET1`	`title`
列出标签页	`GOOGLESHEETS_GET_SHEET_NAMES`	`spreadsheet_id`
添加标签页	`GOOGLESHEETS_ADD_SHEET`	`spreadsheetId` , `title`
读取数据	`GOOGLESHEETS_BATCH_GET`	`spreadsheet_id` , `ranges`
读取单个区域	`GOOGLESHEETS_VALUES_GET`	`spreadsheet_id` , `range`
写入数据	`GOOGLESHEETS_BATCH_UPDATE`	`spreadsheet_id` , `sheet_name` , `values`
更新区域	`GOOGLESHEETS_VALUES_UPDATE`	`spreadsheet_id` , `range` , `values`
追加行	`GOOGLESHEETS_SPREADSHEETS_VALUES_APPEND`	`spreadsheetId` , `range` , `values`
Upsert行	`GOOGLESHEETS_UPSERT_ROWS`	`spreadsheetId` , `sheetName` , `keyColumn` , `rows`
查找行	`GOOGLESHEETS_LOOKUP_SPREADSHEET_ROW`	`spreadsheet_id` , `query`
格式化单元格	`GOOGLESHEETS_FORMAT_CELL`	`spreadsheet_id` , `worksheet_id` , `range`
设置筛选器	`GOOGLESHEETS_SET_BASIC_FILTER`	`spreadsheetId` , `filter`
清除值	`GOOGLESHEETS_CLEAR_VALUES`	`spreadsheet_id` , range
删除行/列	`GOOGLESHEETS_DELETE_DIMENSION`	`spreadsheet_id` , `sheet_name` , dimension
电子表格信息	`GOOGLESHEETS_GET_SPREADSHEET_INFO`	`spreadsheet_id`
更新标签页属性	`GOOGLESHEETS_UPDATE_SHEET_PROPERTIES`	`spreadsheetId` , properties