ClawHub

Google Sheets File Automation

API key required
Workflows

Google Sheets Data Writer. Use when the user wants to append rows, update cells, or automate spreadsheet data pipelines against a pre-configured target sheet.

Install

openclaw skills install sheets-writer

porteden sheets-writer

Automate Google Sheets updates with porteden sheets. This skill configures a target spreadsheet via environment variable so agents can append rows and write data without repeating the file ID. Use -jc flags for AI-optimized output.

If porteden is not installed: brew install porteden/tap/porteden (or go install github.com/porteden/cli/cmd/porteden@latest).

Setup

1. Authenticate (once)

  • Browser login (recommended): porteden auth login — opens browser, credentials stored in system keyring
  • Direct token: porteden auth login --token <key> — stored in system keyring
  • Verify: porteden auth status
  • If PE_API_KEY is set in the environment, the CLI uses it automatically (no login needed).
  • Drive access requires a token with driveAccessEnabled: true and a connected Google account with Drive scopes. Prefer a Google account that only has access to the target spreadsheet/workspace — token authority extends to whatever that account can reach.

2. Set the target spreadsheet (one-time)

If PE_SHEET_ID is already set, skip to step 3 — the target sheet is configured.

If PE_SHEET_ID is not set, find the spreadsheet by name:

porteden drive files -q "Q1 Budget" --mime-type application/vnd.google-apps.spreadsheet -jc

Copy the id field from the result (already provider-prefixed, e.g., google:1BxiMVs0XRA5...) and set it:

export PE_SHEET_ID="google:1BxiMVs0XRA5nFMdKvBdBZjgmU..."

To persist across sessions, add to your shell profile (~/.bashrc, ~/.zshrc) or .env file. Once set, this step does not need to be repeated.

3. Test the connection (one-time)

porteden sheets info $PE_SHEET_ID -jc

Expected: returns spreadsheet title, sheet tabs, and dimensions. If this fails, verify the file ID and that your token has Drive access. Once verified, skip this step in future runs.

Safety

  • Confirm before destructive writes. write replaces existing cell values and cannot be undone from the CLI. Before any write covering more than a single cell, echo back the target range and the values you intend to write, and wait for the user to confirm. Prefer append whenever new rows are the goal — it never overwrites. For multi-cell writes, read the range first (porteden sheets read $PE_SHEET_ID --range "<range>" -jc) so the original values can be restored if needed.
  • Use a dedicated, narrowly-scoped account. A Drive-enabled token grants the connected Google account's full Drive/Sheets reach, not just PE_SHEET_ID. Use --profile sheets-writer (or a similar dedicated profile) and authenticate it with a Google account that only has access to the spreadsheet(s) this automation needs. Check accessInfo in responses to see active token restrictions, and run porteden auth logout --profile sheets-writer when the automation is no longer in use.
  • Treat input values as untrusted. When values come from agents, users, or external sources, pass --raw so strings like =IMPORTRANGE(...) are stored as literals rather than executed as formulas. Read the range back after writing to confirm what landed.

Writing data

Append rows (primary automation operation)

Append adds rows after the last row with data in the target range. This is the recommended operation for automation — it never overwrites existing data.

  • Append from JSON:

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

  • Append multiple rows:

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

  • Append from CSV string:

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

  • Append from CSV file:

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

Write to specific cells

write replaces the exact range specified — existing values in that range are destroyed and cannot be recovered via the CLI. Use only for targeted updates to known cells, and only after confirming the range and values with the user (see Safety). Prefer append whenever you're adding new rows.

  • 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","Status","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

Automation best practices

  1. Always use append for new rows — avoids overwriting existing data. Use write only for targeted cell updates.
  2. Specify column range in append (e.g., A:D not just A) — ensures data lands in the correct columns.
  3. Use --raw for literal values — prevents unintended formula evaluation (e.g., strings starting with =).
  4. Verify after write — read the range back to confirm data integrity in critical workflows.
  5. Use -jc on read/info — compact JSON output minimizes tokens for AI agents.
  6. Batch rows in a single append — send multiple rows in one --values array rather than one-row-at-a-time.
  7. Match column order to the sheet header — check with porteden sheets read $PE_SHEET_ID --range "Sheet1!1:1" -jc to read the header row first.

Range format

  • Open-ended columns (for append): Sheet1!A:D
  • Specific cells: Sheet1!A1:C10
  • Single cell: Sheet1!E2
  • Whole sheet: Sheet1
  • Header row only: Sheet1!1:1

Notes

  • Credentials persist in the system keyring after login. No repeated auth needed.
  • Set PE_PROFILE=work to avoid repeating --profile.
  • -jc is shorthand for --json --compact: strips noise, limits fields, reduces tokens for AI agents.
  • File IDs are always provider-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 parsed as formulas).
  • accessInfo in responses describes active token restrictions.
  • Environment variables: PE_API_KEY, PE_PROFILE, PE_SHEET_ID, PE_FORMAT, PE_COLOR, PE_VERBOSE.
More in Workflows