ClawHub

Google Sheets File Automation

Workflows

Automated Google Sheets Data Writer — Append rows, update cells, and automate spreadsheet data pipelines with 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.

2. Find and set the target spreadsheet

Search for 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 as the target:

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

To persist across sessions, add to your shell profile (~/.bashrc, ~/.zshrc) or .env file.

3. Test the connection

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.

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. 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","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