Google Sheets

Interact with Google Sheets for spreadsheet management, data manipulation, and formula operations.

通过该工具与Google Sheets交互，实现电子表格管理、数据处理及公式操作。

Installation

安装

Install Python dependencies:

bash

pip install --user google-auth google-auth-oauthlib google-api-python-client keyring pyyaml

Download the skill from Releases or use directly from this repository.

安装Python依赖包:

bash

pip install --user google-auth google-auth-oauthlib google-api-python-client keyring pyyaml

下载技能包：从Releases下载，或直接使用本仓库中的内容。

Setup Verification

配置验证

After installation, verify the skill is properly configured:

bash

python scripts/google-sheets.py check

This will check:

Python dependencies (google-auth, google-auth-oauthlib, google-api-python-client, keyring, pyyaml)
Authentication configuration
Connectivity to Google Sheets API

If anything is missing, the check command will provide setup instructions.

安装完成后，验证技能包是否配置正确：

bash

python scripts/google-sheets.py check

该命令将检查以下内容：

Python依赖包（google-auth、google-auth-oauthlib、google-api-python-client、keyring、pyyaml）
认证配置
与Google Sheets API的连通性

若有缺失，检查命令会提供对应的配置指引。

Authentication

认证

Google Sheets uses OAuth 2.0 for authentication. For complete setup instructions, see:

GCP Project Setup Guide - Create project, enable Sheets API
Google OAuth Setup Guide - Configure credentials

Google Sheets采用OAuth 2.0进行认证。完整配置指引请参考：

GCP项目设置指南 - 创建项目，启用Sheets API
Google OAuth设置指南 - 配置凭证

Quick Start

快速开始

Create

~/.config/agent-skills/google.yaml

:

yaml

oauth_client:
  client_id: your-client-id.apps.googleusercontent.com
  client_secret: your-client-secret

Run
```
python scripts/google-sheets.py check
```
to trigger OAuth flow and verify setup.

创建

~/.config/agent-skills/google.yaml

文件：

yaml

oauth_client:
  client_id: your-client-id.apps.googleusercontent.com
  client_secret: your-client-secret

运行
```
python scripts/google-sheets.py check
```
触发OAuth流程并验证配置。

OAuth Scopes

OAuth权限范围

The skill requests granular scopes for different operations:

Scope	Permission	Used For
`spreadsheets.readonly`	Read spreadsheets	Reading cell values and metadata
`spreadsheets`	Full access	Creating and modifying spreadsheets

该技能包针对不同操作请求精细化的权限范围：

权限范围	权限	用途
`spreadsheets.readonly`	只读访问电子表格	读取单元格值和元数据
`spreadsheets`	完全访问权限	创建和修改电子表格

Scope Errors

权限范围错误

If you encounter "insufficient scope" errors, revoke your token and re-authenticate:

Revoke at https://myaccount.google.com/permissions

Clear token:

keyring del agent-skills google-sheets-token-json

Re-run:
```
python scripts/google-sheets.py check
```

若遇到「权限不足」错误，请撤销现有令牌并重新认证：

访问https://myaccount.google.com/permissions进行撤销

清除令牌：

keyring del agent-skills google-sheets-token-json

重新运行：
```
python scripts/google-sheets.py check
```

Commands

命令

check

check（检查）

Verify configuration and connectivity.

bash

python scripts/google-sheets.py check

This validates:

Python dependencies are installed
Authentication is configured
Can connect to Google Sheets API
Creates a test spreadsheet to verify write access

验证配置与连通性。

bash

python scripts/google-sheets.py check

该命令将验证：

Python依赖包已安装
认证已配置
可连接至Google Sheets API
创建测试电子表格以验证写入权限

auth setup

auth setup（认证配置）

Store OAuth 2.0 client credentials for custom OAuth flow.

bash

python scripts/google-sheets.py auth setup \
  --client-id YOUR_CLIENT_ID \
  --client-secret YOUR_CLIENT_SECRET

Credentials are saved to

~/.config/agent-skills/google-sheets.yaml

.

Options:

```
--client-id
```
- OAuth 2.0 client ID (required)
```
--client-secret
```
- OAuth 2.0 client secret (required)

存储OAuth 2.0客户端凭证，用于自定义OAuth流程。

bash

python scripts/google-sheets.py auth setup \
  --client-id YOUR_CLIENT_ID \
  --client-secret YOUR_CLIENT_SECRET

凭证将保存至

~/.config/agent-skills/google-sheets.yaml

。

选项:

```
--client-id
```
- OAuth 2.0客户端ID（必填）
```
--client-secret
```
- OAuth 2.0客户端密钥（必填）

spreadsheets create

spreadsheets create（创建电子表格）

Create a new Google Sheets spreadsheet.

bash

python scripts/google-sheets.py spreadsheets create --title "My Spreadsheet"

Options:

```
--title
```
- Spreadsheet title (required)
```
--sheets
```
- Comma-separated sheet names (optional)
```
--json
```
- Output as JSON

Example:

bash

undefined

创建新的Google Sheets电子表格。

bash

python scripts/google-sheets.py spreadsheets create --title "My Spreadsheet"

选项:

```
--title
```
- 电子表格标题（必填）
```
--sheets
```
- 逗号分隔的工作表名称（可选）
```
--json
```
- 以JSON格式输出结果

示例:

bash

undefined

Create with default Sheet1

创建默认包含Sheet1的电子表格

python scripts/google-sheets.py spreadsheets create --title "Sales Data"

Create with custom sheets

创建包含自定义工作表的电子表格

python scripts/google-sheets.py spreadsheets create
--title "Q1 Report"
--sheets "Summary,January,February,March"

Output:

输出:

✓ Spreadsheet created successfully

Title: Q1 Report

Spreadsheet ID: 1abc...xyz

Sheets: 4 (Summary, January, February, March)

URL: https://docs.google.com/spreadsheets/d/1abc...xyz/edit

undefined

undefined

spreadsheets get

spreadsheets get（获取电子表格信息）

Get spreadsheet metadata and structure.

bash

python scripts/google-sheets.py spreadsheets get SPREADSHEET_ID

Arguments:

```
spreadsheet_id
```
- The Google Sheets spreadsheet ID

Options:

```
--json
```
- Output full spreadsheet structure as JSON

Example:

bash

python scripts/google-sheets.py spreadsheets get 1abc...xyz

获取电子表格的元数据和结构。

bash

python scripts/google-sheets.py spreadsheets get SPREADSHEET_ID

参数:

```
spreadsheet_id
```
- Google Sheets电子表格ID

选项:

```
--json
```
- 以JSON格式输出完整的电子表格结构

示例:

bash

python scripts/google-sheets.py spreadsheets get 1abc...xyz

Output:

输出:

Title: Sales Data

Spreadsheet ID: 1abc...xyz

Sheets: 2 (Sheet1, Summary)

URL: https://docs.google.com/spreadsheets/d/1abc...xyz/edit

undefined

undefined

values read

values read（读取单元格值）

Read cell values from a range.

bash

python scripts/google-sheets.py values read SPREADSHEET_ID --range "Sheet1!A1:D5"

Arguments:

```
spreadsheet_id
```
- The Google Sheets spreadsheet ID

Options:

```
--range
```
- Range in A1 notation (required, e.g., "Sheet1!A1:D5")
```
--format
```
- Value format: FORMATTED_VALUE (default), UNFORMATTED_VALUE, or FORMULA
```
--json
```
- Output as JSON

Example:

bash

undefined

读取指定区域的单元格值。

bash

python scripts/google-sheets.py values read SPREADSHEET_ID --range "Sheet1!A1:D5"

参数:

```
spreadsheet_id
```
- Google Sheets电子表格ID

选项:

```
--range
```
- A1表示法的单元格区域（必填，例如"Sheet1!A1:D5"）
```
--format
```
- 值格式：FORMATTED_VALUE（默认）、UNFORMATTED_VALUE或FORMULA
```
--json
```
- 以JSON格式输出结果

示例:

bash

undefined

Read a range

读取指定区域

python scripts/google-sheets.py values read 1abc...xyz --range "Sheet1!A1:C3"

Output (formatted as table):

输出（表格格式）:

Name | Age | City

Alice | 30 | NYC

Bob | 25 | LA

Read formulas

读取公式

python scripts/google-sheets.py values read 1abc...xyz
--range "Sheet1!D1:D10"
--format FORMULA


See [references/range-notation.md](references/range-notation.md) for A1 notation details.

python scripts/google-sheets.py values read 1abc...xyz
--range "Sheet1!D1:D10"
--format FORMULA


关于A1表示法的详细说明，请参考[references/range-notation.md](references/range-notation.md)。

values write

values write（写入单元格值）

Write values to a range.

bash

python scripts/google-sheets.py values write SPREADSHEET_ID \
  --range "Sheet1!A1" \
  --values '[[\"Name\",\"Age\"],[\"Alice\",30]]'

Arguments:

```
spreadsheet_id
```
- The Google Sheets spreadsheet ID

Options:

```
--range
```
- Starting range in A1 notation (required)
```
--values
```
- Values as JSON 2D array (required)
```
--json
```
- Output API response as JSON

Example:

bash

undefined

向指定区域写入值。

bash

python scripts/google-sheets.py values write SPREADSHEET_ID \
  --range "Sheet1!A1" \
  --values '[[\"Name\",\"Age\"],[\"Alice\",30]]'

参数:

```
spreadsheet_id
```
- Google Sheets电子表格ID

选项:

```
--range
```
- A1表示法的起始区域（必填）
```
--values
```
- 以JSON二维数组格式表示的值（必填）
```
--json
```
- 以JSON格式输出API响应

示例:

bash

undefined

Write data starting at A1

从A1开始写入数据

python scripts/google-sheets.py values write 1abc...xyz
--range "Sheet1!A1"
--values '[["Product","Price","Quantity"],["Widget",9.99,100]]'

Write a single row

写入单行数据

python scripts/google-sheets.py values write 1abc...xyz
--range "Sheet1!A5"
--values '[["Total",999,50]]'

Output:

输出:

✓ Values written successfully

Updated cells: 6

Updated range: Sheet1!A1:C2


**Note:** Values are entered as the user would type them. Formulas start with `=`.


**注意:** 值的输入方式与用户手动输入一致。公式需以`=`开头。

values append

values append（追加行数据）

Append rows to the end of a sheet.

bash

python scripts/google-sheets.py values append SPREADSHEET_ID \
  --range "Sheet1" \
  --values '[[\"New\",\"Row\",\"Data\"]]'

Arguments:

```
spreadsheet_id
```
- The Google Sheets spreadsheet ID

Options:

```
--range
```
- Sheet name or range (required)
```
--values
```
- Values as JSON 2D array (required)
```
--json
```
- Output API response as JSON

Example:

bash

undefined

在工作表末尾追加行数据。

bash

python scripts/google-sheets.py values append SPREADSHEET_ID \
  --range "Sheet1" \
  --values '[[\"New\",\"Row\",\"Data\"]]'

参数:

```
spreadsheet_id
```
- Google Sheets电子表格ID

选项:

```
--range
```
- 工作表名称或区域（必填）
```
--values
```
- 以JSON二维数组格式表示的值（必填）
```
--json
```
- 以JSON格式输出API响应

示例:

bash

undefined

Append a single row

追加单行数据

python scripts/google-sheets.py values append 1abc...xyz
--range "Sheet1"
--values '[["Charlie",35,"Chicago"]]'

Append multiple rows

追加多行数据

python scripts/google-sheets.py values append 1abc...xyz
--range "Sheet1"
--values '[["David",28,"Boston"],["Eve",32,"Seattle"]]'

Output:

输出:

✓ Values appended successfully

Updated cells: 3

Updated range: Sheet1!A4:C4

undefined

undefined

values clear

values clear（清除单元格值）

Clear values in a range.

bash

python scripts/google-sheets.py values clear SPREADSHEET_ID --range "Sheet1!A1:D10"

Arguments:

```
spreadsheet_id
```
- The Google Sheets spreadsheet ID

Options:

```
--range
```
- Range in A1 notation (required)
```
--json
```
- Output API response as JSON

Example:

bash

undefined

清除指定区域的单元格值。

bash

python scripts/google-sheets.py values clear SPREADSHEET_ID --range "Sheet1!A1:D10"

参数:

```
spreadsheet_id
```
- Google Sheets电子表格ID

选项:

```
--range
```
- A1表示法的单元格区域（必填）
```
--json
```
- 以JSON格式输出API响应

示例:

bash

undefined

Clear a range

清除指定区域

python scripts/google-sheets.py values clear 1abc...xyz --range "Sheet1!A1:Z100"

Output:

输出:

✓ Values cleared successfully

Cleared range: Sheet1!A1:Z100


**Warning:** This only clears values, not formatting or formulas in protected cells.


**警告:** 该操作仅清除值，不会清除受保护单元格中的格式或公式。

sheets create

sheets create（创建工作表）

Add a new sheet to a spreadsheet.

bash

python scripts/google-sheets.py sheets create SPREADSHEET_ID --title "New Sheet"

Arguments:

```
spreadsheet_id
```
- The Google Sheets spreadsheet ID

Options:

```
--title
```
- Sheet title (required)
```
--json
```
- Output API response as JSON

Example:

bash

python scripts/google-sheets.py sheets create 1abc...xyz --title "Q2 Data"

向电子表格中添加新的工作表。

bash

python scripts/google-sheets.py sheets create SPREADSHEET_ID --title "New Sheet"

参数:

```
spreadsheet_id
```
- Google Sheets电子表格ID

选项:

```
--title
```
- 工作表标题（必填）
```
--json
```
- 以JSON格式输出API响应

示例:

bash

python scripts/google-sheets.py sheets create 1abc...xyz --title "Q2 Data"

Output:

输出:

✓ Sheet created successfully

Title: Q2 Data

Sheet ID: 123456789

undefined

undefined

sheets delete

sheets delete（删除工作表）

Delete a sheet from a spreadsheet.

bash

python scripts/google-sheets.py sheets delete SPREADSHEET_ID --sheet-id 123456789

Arguments:

```
spreadsheet_id
```
- The Google Sheets spreadsheet ID

Options:

```
--sheet-id
```
- Sheet ID (required, not the title!)
```
--json
```
- Output API response as JSON

Example:

bash

undefined

从电子表格中删除指定工作表。

bash

python scripts/google-sheets.py sheets delete SPREADSHEET_ID --sheet-id 123456789

参数:

```
spreadsheet_id
```
- Google Sheets电子表格ID

选项:

```
--sheet-id
```
- 工作表ID（必填，并非工作表标题！）
```
--json
```
- 以JSON格式输出API响应

示例:

bash

undefined

Get sheet IDs first

先获取工作表ID

python scripts/google-sheets.py spreadsheets get 1abc...xyz --json | jq '.sheets[].properties | {title, sheetId}'

Delete a sheet

删除工作表

python scripts/google-sheets.py sheets delete 1abc...xyz --sheet-id 123456789

Output:

输出:

✓ Sheet deleted successfully


**Warning:** Cannot delete the last remaining sheet in a spreadsheet.


**警告:** 无法删除电子表格中仅剩的最后一个工作表。

Examples

示例

Create and populate a spreadsheet

创建并填充电子表格

bash

undefined

bash

undefined

Create spreadsheet

创建电子表格

SS_ID=$(python scripts/google-sheets.py spreadsheets create
--title "Employee Data" --json | jq -r '.spreadsheetId')

Write headers

写入表头

python scripts/google-sheets.py values write $SS_ID
--range "Sheet1!A1"
--values '[["Name","Department","Salary","Start Date"]]'

Append employee records

追加员工记录

python scripts/google-sheets.py values append $SS_ID
--range "Sheet1"
--values '[["Alice","Engineering",120000,"2023-01-15"],["Bob","Sales",95000,"2023-03-01"]]'

Add a summary sheet

添加汇总工作表

python scripts/google-sheets.py sheets create $SS_ID --title "Summary"

Read the data

读取数据

python scripts/google-sheets.py values read $SS_ID --range "Sheet1!A1:D10"

undefined

python scripts/google-sheets.py values read $SS_ID --range "Sheet1!A1:D10"

undefined

Work with formulas

公式操作

bash

undefined

bash

undefined

Write data with formulas

写入包含公式的数据

python scripts/google-sheets.py values write $SS_ID
--range "Sheet1!A1"
--values '[["Item","Price","Qty","Total"],["Widget",10,5,"=B2C2"],["Gadget",20,3,"=B3C3"]]'

Read formulas

读取公式

python scripts/google-sheets.py values read $SS_ID
--range "Sheet1!D2:D3"
--format FORMULA

Read calculated values

读取计算后的值

python scripts/google-sheets.py values read $SS_ID
--range "Sheet1!D2:D3"
--format FORMATTED_VALUE

undefined

python scripts/google-sheets.py values read $SS_ID
--range "Sheet1!D2:D3"
--format FORMATTED_VALUE

undefined

Batch operations

批量操作

bash

#!/bin/bash
SS_ID="your-spreadsheet-id"

bash

#!/bin/bash
SS_ID="your-spreadsheet-id"

Clear old data

清除旧数据

python scripts/google-sheets.py values clear $SS_ID --range "Sheet1!A1:Z1000"

Write new data in batches

写入新表头

python scripts/google-sheets.py values write $SS_ID
--range "Sheet1!A1"
--values '[["Date","Revenue","Expenses","Profit"]]'

for month in Jan Feb Mar; do python scripts/google-sheets.py values append $SS_ID
--range "Sheet1"
--values "[["$month",10000,7000,"=B${ROW}-C${ROW}"]]" done

undefined

python scripts/google-sheets.py values write $SS_ID
--range "Sheet1!A1"
--values '[["Date","Revenue","Expenses","Profit"]]'

for month in Jan Feb Mar; do python scripts/google-sheets.py values append $SS_ID
--range "Sheet1"
--values "[["$month",10000,7000,"=B${ROW}-C${ROW}"]]" done

undefined

Troubleshooting

故障排查

"Insufficient scope" errors

「权限不足」错误

You need to revoke and re-authenticate to grant additional permissions:

Go to https://myaccount.google.com/permissions
Find "Agent Skills" and remove access

Delete stored token:

keyring del agent-skills google-sheets-token-json

Run
```
python scripts/google-sheets.py check
```
to re-authenticate

你需要撤销现有认证并重新授权以获取额外权限：

访问https://myaccount.google.com/permissions
找到「Agent Skills」并移除访问权限

删除存储的令牌：

keyring del agent-skills google-sheets-token-json

运行
```
python scripts/google-sheets.py check
```
重新认证

Cannot find spreadsheet

无法找到电子表格

Make sure you're using the correct spreadsheet ID from the URL:

URL:

https://docs.google.com/spreadsheets/d/1abc...xyz/edit

Spreadsheet ID:
```
1abc...xyz
```

确保你使用的是URL中的正确电子表格ID：

URL:

https://docs.google.com/spreadsheets/d/1abc...xyz/edit

电子表格ID:
```
1abc...xyz
```

Invalid range errors

无效区域错误

Use proper A1 notation:
```
Sheet1!A1:D5
```
Sheet names with spaces need quotes:
```
'My Sheet'!A1:B2
```
See references/range-notation.md for details

使用正确的A1表示法：
```
Sheet1!A1:D5
```
包含空格的工作表名称需要加引号：
```
'My Sheet'!A1:B2
```
详细说明请参考references/range-notation.md

JSON parsing errors for --values

--values参数JSON解析错误

Ensure proper JSON escaping:

bash

undefined

确保JSON格式正确转义：

bash

undefined

Correct

正确写法

--values '[["Hello","World"]]' --values "[["Name","Age"]]"

Incorrect

错误写法

--values [[Hello,World]] # Missing quotes

undefined

--values [[Hello,World]] # 缺少引号

undefined

Sheet ID vs Sheet Title

工作表ID vs 工作表标题

Commands use different identifiers:

```
sheets create
```
- Uses title (string)
```
sheets delete
```
- Uses sheet ID (number)
Use
```
spreadsheets get --json
```
to find sheet IDs

不同命令使用不同的标识符：

```
sheets create
```
- 使用标题（字符串）
```
sheets delete
```
- 使用工作表ID（数字）
使用
```
spreadsheets get --json
```
命令查找工作表ID

Dependencies not found

依赖包未找到

Install required dependencies:

bash

pip install --user google-auth google-auth-oauthlib google-api-python-client keyring pyyaml

安装所需的依赖包：

bash

pip install --user google-auth google-auth-oauthlib google-api-python-client keyring pyyaml

OAuth flow fails

OAuth流程失败

Ensure your GCP project has:

Google Sheets API enabled (
```
sheets.googleapis.com
```
)
OAuth 2.0 credentials created
OAuth consent screen configured
Your email added as a test user (if app is in testing mode)

See docs/gcp-project-setup.md for detailed instructions.

确保你的GCP项目满足以下条件：

已启用Google Sheets API (
```
sheets.googleapis.com
```
)
已创建OAuth 2.0凭证
已配置OAuth同意屏幕
你的邮箱已添加为测试用户（若应用处于测试模式）

详细指引请参考docs/gcp-project-setup.md。

Related Skills

API Reference

API参考

For advanced usage, see:

Google Sheets API Documentation
Reading and writing cell values
A1 notation reference
Formula examples

如需高级用法，请参考：

Google Sheets API文档
读写单元格值
A1表示法参考
公式示例

google-sheets

Original

Translation

Google Sheets

Google Sheets

Installation

安装

Setup Verification

配置验证

Authentication

认证

Quick Start

快速开始

OAuth Scopes

OAuth权限范围

Scope Errors

权限范围错误

Commands

命令

check

check（检查）

auth setup

auth setup（认证配置）

spreadsheets create

spreadsheets create（创建电子表格）

Create with default Sheet1

创建默认包含Sheet1的电子表格

Create with custom sheets

创建包含自定义工作表的电子表格

Output:

输出:

✓ Spreadsheet created successfully

✓ Spreadsheet created successfully

Title: Q1 Report

Title: Q1 Report

Spreadsheet ID: 1abc...xyz

Spreadsheet ID: 1abc...xyz

Sheets: 4 (Summary, January, February, March)

Sheets: 4 (Summary, January, February, March)

URL: https://docs.google.com/spreadsheets/d/1abc...xyz/edit

URL: https://docs.google.com/spreadsheets/d/1abc...xyz/edit

spreadsheets get

spreadsheets get（获取电子表格信息）

Output:

输出:

Title: Sales Data

Title: Sales Data

Spreadsheet ID: 1abc...xyz

Spreadsheet ID: 1abc...xyz

Sheets: 2 (Sheet1, Summary)

Sheets: 2 (Sheet1, Summary)

URL: https://docs.google.com/spreadsheets/d/1abc...xyz/edit

URL: https://docs.google.com/spreadsheets/d/1abc...xyz/edit

values read

values read（读取单元格值）

Read a range

读取指定区域

Output (formatted as table):

输出（表格格式）:

Name | Age | City

Name | Age | City

Alice | 30 | NYC

Alice | 30 | NYC

Bob | 25 | LA

Bob | 25 | LA

Read formulas

读取公式

values write

values write（写入单元格值）

Write data starting at A1

从A1开始写入数据

Write a single row

写入单行数据

Output:

输出:

✓ Values written successfully

✓ Values written successfully

Updated cells: 6

Updated cells: 6

Updated range: Sheet1!A1:C2