介绍
# Google Sheets
通过托管的 OAuth 身份验证访问 Google Sheets API。读取和写入电子表格数据、创建工作表、应用格式以及执行批量操作。
## 快速开始
```bash # Read values from a spreadsheet (note: range is URL-encoded) python <<'EOF' import urllib.request, os, json req = urllib.request.Request('https://gateway.maton.ai/google-sheets/v4/spreadsheets/SPREADSHEET_ID/values/Sheet1%21A1%3AD10') req.add_header('Authorization', f'Bearer {os.environ["MATON_API_KEY"]}') print(json.dumps(json.load(urllib.request.urlopen(req)), indent=2)) EOF ```
## 基础 URL
``` https://gateway.maton.ai/google-sheets/{native-api-path} ```
将 `{native-api-path}` 替换为实际的 Google Sheets API 端点路径。网关会将请求代理到 `sheets.googleapis.com` 并自动注入您的 OAuth 令牌。
## 身份验证
所有请求都需要在 Authorization header 中提供 Maton API 密钥：
``` Authorization: Bearer $MATON_API_KEY ```
**环境变量：** 将您的 API 密钥设置为 `MATON_API_KEY`：
```bash export MATON_API_KEY="YOUR_API_KEY" ```
### 获取您的 API 密钥
1. 在 [maton.ai](https://maton.ai) 登录或创建账户 2. 前往 [maton.ai/settings](https://maton.ai/settings) 3. 复制您的 API 密钥
## 连接管理
在 `https://ctrl.maton.ai` 管理您的 Google OAuth 连接。
### 列出连接
```bash python <<'EOF' import urllib.request, os, json req = urllib.request.Request('https://ctrl.maton.ai/connections?app=google-sheets&status=ACTIVE') req.add_header('Authorization', f'Bearer {os.environ["MATON_API_KEY"]}') print(json.dumps(json.load(urllib.request.urlopen(req)), indent=2)) EOF ```
### 创建连接
```bash python <<'EOF' import urllib.request, os, json data = json.dumps({'app': 'google-sheets'}).encode() req = urllib.request.Request('https://ctrl.maton.ai/connections', data=data, method='POST') req.add_header('Authorization', f'Bearer {os.environ["MATON_API_KEY"]}') req.add_header('Content-Type', 'application/json') print(json.dumps(json.load(urllib.request.urlopen(req)), indent=2)) EOF ```
### 获取连接
```bash python <<'EOF' import urllib.request, os, json req = urllib.request.Request('https://ctrl.maton.ai/connections/{connection_id}') req.add_header('Authorization', f'Bearer {os.environ["MATON_API_KEY"]}') print(json.dumps(json.load(urllib.request.urlopen(req)), indent=2)) EOF ```
**响应：** ```json { "connection": { "connection_id": "21fd90f9-5935-43cd-b6c8-bde9d915ca80", "status": "ACTIVE", "creation_time": "2025-12-08T07:20:53.488460Z", "last_updated_time": "2026-01-31T20:03:32.593153Z", "url": "https://connect.maton.ai/?session_token=...", "app": "google-sheets", "metadata": {} } } ```
在浏览器中打开返回的 `url` 以完成 OAuth 授权。
### 删除连接
```bash python <<'EOF' import urllib.request, os, json req = urllib.request.Request('https://ctrl.maton.ai/connections/{connection_id}', method='DELETE') req.add_header('Authorization', f'Bearer {os.environ["MATON_API_KEY"]}') print(json.dumps(json.load(urllib.request.urlopen(req)), indent=2)) EOF ```
### 指定连接
如果您连接了多个 Google 账户，请使用 `Maton-Connection` header 指定要使用的账户：
```bash python <<'EOF' import urllib.request, os, json req = urllib.request.Request('https://gateway.maton.ai/google-sheets/v4/spreadsheets/SPREADSHEET_ID') req.add_header('Authorization', f'Bearer {os.environ["MATON_API_KEY"]}') req.add_header('Maton-Connection', '21fd90f9-5935-43cd-b6c8-bde9d915ca80') print(json.dumps(json.load(urllib.request.urlopen(req)), indent=2)) EOF ```
如果省略，网关将使用默认的（最早的）活动连接。
## API 参考
### 获取电子表格元数据
```bash GET /google-sheets/v4/spreadsheets/{spreadsheetId} ```
### 获取数值
```bash GET /google-sheets/v4/spreadsheets/{spreadsheetId}/values/{range} ```
示例：
```bash GET /google-sheets/v4/spreadsheets/SHEET_ID/values/Sheet1%21A1%3AD10 ```
### 获取多个范围
```bash GET /google-sheets/v4/spreadsheets/{spreadsheetId}/values:batchGet?ranges=Sheet1%21A1%3AB10&ranges=Sheet2%21A1%3AC5 ```
### 更新数值
```bash PUT /google-sheets/v4/spreadsheets/{spreadsheetId}/values/{range}?valueInputOption=USER_ENTERED Content-Type: application/json
{ "values": [ ["A1", "B1", "C1"], ["A2", "B2", "C2"] ] } ```
### 追加数值
```bash POST /google-sheets/v4/spreadsheets/{spreadsheetId}/values/{range}:append?valueInputOption=USER_ENTERED Content-Type: application/json
{ "values": [ ["New Row 1", "Data", "More Data"], ["New Row 2", "Data", "More Data"] ] } ```
### 批量更新数值
```bash POST /google-sheets/v4/spreadsheets/{spreadsheetId}/values:batchUpdate Content-Type: application/json
{ "valueInputOption": "USER_ENTERED", "data": [ {"range": "Sheet1!A1:B2", "values": [["A1", "B1"], ["A2", "B2"]]}, {"range": "Sheet1!D1:E2", "values": [["D1", "E1"], ["D2", "E2"]]} ] } ```
### 清除数值
```bash POST /google-sheets/v4/spreadsheets/{spreadsheetId}/values/{range}:clear ```
### 创建电子表格
```bash POST /google-sheets/v4/spreadsheets Content-Type: application/json
{ "properties": {"title": "New Spreadsheet"}, "sheets": [{"properties": {"title": "Sheet1"}}] } ```
### 批量更新（格式化、添加工作表等）
```bash POST /google-sheets/v4/spreadsheets/{spreadsheetId}:batchUpdate Content-Type: application/json
{ "requests": [ {"addSheet": {"properties": {"title": "New Sheet"}}} ] } ```
## 常用 batchUpdate 请求
### 带格式更新单元格
```json { "updateCells": { "rows": [ {"values": [{"userEnteredValue": {"stringValue": "Name"}}, {"userEnteredValue": {"numberValue": 100}}]} ], "fields": "userEnteredValue", "start": {"sheetId": 0, "rowIndex": 0, "columnIndex": 0} } } ```
### 格式化标题行（粗体 + 背景色）
```json { "repeatCell": { "range": {"sheetId": 0, "startRowIndex": 0, "endRowIndex": 1, "startColumnIndex": 0, "endColumnIndex": 3}, "cell": { "userEnteredFormat": { "backgroundColor": {"red": 0.2, "green": 0.6, "blue": 0.9}, "textFormat": {"bold": true} } }, "fields": "userEnteredFormat(backgroundColor,textFormat)" } } ```
### 自动调整列宽
```json { "autoResizeDimensions": { "dimensions": {"sheetId": 0, "dimension": "COLUMNS", "startIndex": 0, "endIndex": 3} } } ```
### 重命名工作表
```json { "updateSheetProperties": { "properties": {"sheetId": 0, "title": "NewName"}, "fields": "title" } } ```
### 插入行/列
```json { "insertDimension": { "range": {"sheetId": 0, "dimension": "ROWS", "startIndex": 1, "endIndex": 3}, "inheritFromBefore": true } } ```
### 排序范围
```json { "sortRange": { "range": {"sheetId": 0, "startRowIndex": 1, "endRowIndex": 10, "startColumnIndex": 0, "endColumnIndex": 3}, "sortSpecs": [{"dimensionIndex": 1, "sortOrder": "DESCENDING"}] } } ```
### 添加筛选器
```json { "setBasicFilter": { "filter": { "range": {"sheetId": 0, "startRowIndex": 0, "endRowIndex": 100, "startColumnIndex": 0, "endColumnIndex": 5} } } } ```
### 删除工作表
```json { "deleteSheet": {"sheetId": 123456789} } ```
## 数值输入选项
- `RAW` - 数值按原样存储 - `USER_ENTERED` - 数值将被解析，就像在 UI 中输入一样（执行公式，解析数字）
## 范围表示法
- `Sheet1!A1:D10` - 特定范围 - `Sheet1!A:D` - A 到 D 整列 - `Sheet1!1:10` - 1 到 10 整行 - `Sheet1` - 整个工作表 - `A1:D10` - 第一个工作表中的范围
## 代码示例
### JavaScript
```javascript // Read values const response = await fetch( 'https://gateway.maton.ai/google-sheets/v4/spreadsheets/SHEET_ID/values/Sheet1!A1:D10', { headers: { 'Authorization': `Bearer ${process.env.MATON_API_KEY}` } } );
// Write values await fetch( 'https://gateway.maton.ai/google-sheets/v4/spreadsheets/SHEET_ID/values/Sheet1!A1:B2?valueInputOption=USER_ENTERED', { method: 'PUT', headers: { 'Content-Type': 'application/json', 'Authorization': `Bearer ${process.env.MATON_API_KEY}` }, body: JSON.stringify({ values: [['A1', 'B1'], ['A2', 'B2']] }) } ); ```
### Python
```python import os import requests
# Read values response = requests.get( 'https://gateway.maton.ai/google-sheets/v4/spreadsheets/SHEET_ID/values/Sheet1!A1:D10', headers={'Authorization': f'Bearer {os.environ["MATON_API_KEY"]}'} )
# Write values response = requests.put( 'https://gateway.maton.ai/google-sheets/v4/spreadsheets/SHEET_ID/values/Sheet1!A1:B2', headers={'Authorization': f'Bearer {os.environ["MATON_API_KEY"]}'}, params={'valueInputOption': 'USER_ENTERED'}, json={'values': [['A1', 'B1'], ['A2', 'B2']]} ) ```
## 注意事项
- 使用 curl 时，URL 路径中的范围必须进行 URL 编码（! -> %21, : -> %3A）。JavaScript fetch 和 Python requests 会自动处理编码。 - 使用 `valueInputOption=USER_ENTERED` 来解析公式和数字 - 通过 Google Drive API 而非 Sheets API 删除电子表格 - 工作表 ID 是数字，可在电子表格元数据中找到 - 重要：使用 curl 命令时，如果 URL 包含方括号（`fields[]`, `sort[]`, `records[]`），请使用 `curl -g` 以禁用 glob 解析 - 重要：当将 curl 输出通过管道传递给 `jq` 或其他命令时，在某些 shell 环境中，像 `$MATON_API_KEY` 这样的环境变量可能无法正确展开。通过管道传输时，您可能会收到“Invalid API key”（无效的 API 密钥）错误。
## 错误处理
| 状态 | 含义 | |--------|---------| | 400 | 缺少 Google Sheets 连接 | | 401 | 无效或缺少 Maton API 密钥 | | 429 | 速率受限（每账户 10 次/秒）| | 4xx/5xx | 来自 Google Sheets API 的透传错误 |
### 故障排除：API 密钥问题
1. 检查 `MATON_API_KEY` 环境变量是否已设置：
```bash echo $MATON_API_KEY ```
2. 通过列出连接来验证 API 密钥是否有效：
```bash python <<'EOF' import urllib.request, os, json req = urllib.request.Request('https://ctrl.maton.ai/connections') req.add_header('Authorization', f'Bearer {os.environ["MATON_API_KEY"]}') print(json.dumps(json.load(urllib.request.urlopen(req)), indent=2)) EOF ```
### 故障排除：无效的应用名称
1. 确保您的 URL 路径以 `google-sheets` 开头。例如：
- 正确：`https://gateway.maton.ai/google-sheets/v4/spreadsheets/SPREADSHEET_ID` - 错误：`https://gateway.maton.ai/v4/spreadsheets/SPREADSHEET_ID`
## 资源
- [Sheets API 概述](https://developers.google.com/workspace/sheets/api/reference/rest) - [获取电子表格](https://developers.google.com/workspace/sheets/api/reference/rest/v4/spreadsheets/get) - [创建电子表格](https://developers.google.com/workspace/sheets/api/reference/rest/v4/spreadsheets/create) - [批量更新](https://developers.google.com/workspace/sheets/api/reference/rest/v4/spreadsheets/batchUpdate) - [批量更新请求类型](https://developers.google.com/workspace/sheets/api/reference/rest/v4/spreadsheets/request) - [获取数值](https://developers.google.com/workspace/sheets/api/reference/rest/v4/spreadsheets.values/get) - [更新数值](https://developers.google.com/workspace/sheets/api/reference/rest/v4/spreadsheets.values/update) - [追加数值](https://developers.google.com/workspace/sheets/api/reference/rest/v4/spreadsheets.values/append) - [批量获取数值](https://developers.google.com/workspace/sheets/api/reference/rest/v4/spreadsheets.values/batchGet) - [批量更新数值](https://developers.google.com/workspace/sheets/api/reference/rest/v4/spreadsheets.values/batchUpdate) - [Maton 社区](https://discord.com/invite/dBfFAcefs2) - [Maton 支持](mailto:[email protected])