Google Sheets File Automation — Google Sheets File 自动化

porteden sheets-writer

Automate Google Sheets 更新s with porteden sheets. This 技能 配置s a tar获取 spreadsheet via 环境 variable so 代理s can 应用end rows and write data without repeating the file ID. Use -jc flags for AI-优化d 输出.

If porteden is not 安装ed: brew 安装 porteden/tap/porteden (or go 安装 github.com/porteden/命令行工具/cmd/porteden@latest).

设置up

• 认证 (once)

Browser 记录in (recommended): porteden auth 记录in — opens browser, 凭证s stored in 系统 keyring Direct 令牌: porteden auth 记录in --令牌 — stored in 系统 keyring 验证: porteden auth 状态 If PE_API_KEY is 设置 in the 环境, the 命令行工具 uses it automatically (no 记录in needed). Drive 访问 requires a 令牌 with drive访问Enabled: true and a connected Google account with Drive scopes.

• Find and 设置 the tar获取 spreadsheet

搜索 for the spreadsheet by name:

porteden drive files -q "Q1 Bud获取" --mime-type 应用/vnd.google-应用s.spreadsheet -jc

Copy the id field from the 结果 (already 提供者-prefixed, e.g., google:1BxiMVs0XRA5...) and 设置 it as the tar获取:

导出 PE_SHEET_ID="google:1BxiMVs0XRA5nFMdKvBdBZjgmU..."

To persist across 会话s, 添加 to your shell 性能分析 (~/.bashrc, ~/.zshrc) or .env file.

• Test the connection

porteden sheets 信息 $PE_SHEET_ID -jc

Expected: returns spreadsheet title, sheet tabs, and dimensions. If this fAIls, 验证 the file ID and that your 令牌 has Drive 访问.

Writing data 应用end rows (primary 自动化 operation)

应用end 添加s rows after the last row with data in the tar获取 range. This is the recommended operation for 自动化 — it never overwrites existing data.

应用end from JSON:

porteden sheets 应用end $PE_SHEET_ID --range "Sheet1!A:D" --values '[["2025-01-15","Order #1042","Shipped",29.99]]'

应用end multiple rows:

porteden sheets 应用end $PE_SHEET_ID --range "Sheet1!A:D" --values '[["2025-01-15","Order #1042","Shipped",29.99],["2025-01-16","Order #1043","Processing",45.50]]'

应用end from CSV string:

porteden sheets 应用end $PE_SHEET_ID --range "Sheet1!A:D" --csv "2025-01-15,Order #1042,Shipped,29.99"

应用end from CSV file:

porteden sheets 应用end $PE_SHEET_ID --range "Sheet1!A:D" --csv-file ./new_rows.csv

Write to specific cells

Write replaces the exact range specified. Use for updating known cells or overwriting a section.

Write a single cell:

porteden sheets write $PE_SHEET_ID --range "Sheet1!E2" --values '[["Complete"]]'

Write a block:

porteden sheets write $PE_SHEET_ID --range "Sheet1!A1:C2" --values '[["Name","状态","Score"],["Alice","Done",95]]'

Write from CSV file:

porteden sheets write $PE_SHEET_ID --range "Sheet1!A1" --csv-file ./data.csv

Read for verification

After writing, confirm the data landed correctly:

porteden sheets read $PE_SHEET_ID --range "Sheet1!A1:D10" -jc

自动化 best practices Always use 应用end for new rows — avoids overwriting existing data. Use write only for tar获取ed cell 更新s. Specify column range in 应用end (e.g., A:D not just A) — ensures data lands in the correct columns. Use --raw for literal values — 预防s unintended formula evaluation (e.g., strings 启动ing with =). 验证 after write — read the range back to confirm data integrity in critical 工作流s. Use -jc on read/信息 — compact JSON 输出 minimizes 令牌s for AI 代理s. Batch rows in a single 应用end — 发送 multiple rows in one --values array rather than one-row-at-a-time. Match column order to the sheet header — 检查 with porteden sheets read $PE_SHEET_ID --range "Sheet1!1:1" -jc to read the header row first. Range 格式化 Open-ended columns (for 应用end): Sheet1!A:D Specific cells: Sheet1!A1:C10 Single cell: Sheet1!E2 Whole sheet: Sheet1 Header row only: Sheet1!1:1 Notes 凭证s persist in the 系统 keyring after 记录in. No repeated auth needed. 设置 PE_性能分析=work to avoid repeating --性能分析. -jc is shorthand for --json --compact: strips noise, limits fields, reduces 令牌s for AI 代理s. File IDs are always 提供者-prefixed (e.g., google:1BxiMVs0XRA5...). Pass them as-is. --values, --csv, and --csv-file are mutually exclusive — provide exactly one. --csv inline: use \n as row separator (e.g., "Name,Score\nAlice,95\nBob,87"). --raw flag disables formula evaluation (values written literally, not 解析d as formulas). 访问信息 in 响应s describes active 令牌 restrictions. 环境 variables: PE_API_KEY, PE_性能分析, PE_SHEET_ID, PE_格式化, PE_COLOR, PE_VERBOSE.