Google Sheets File Automation

SKILL.md

---
name: porteden-sheets-writer
description: Automated Google Sheets Data Writer — Append rows, update cells, and automate spreadsheet data pipelines with a pre-configured target sheet.
homepage: https://porteden.com
metadata: {"openclaw":{"emoji":"🤖","requires":{"bins":["porteden"],"env":["PE_API_KEY","PE_SHEET_ID"]},"primaryEnv":"PE_API_KEY","install":[{"id":"brew","kind":"brew","formula":"porteden/tap/porteden","bins":["porteden"],"label":"Install porteden (brew)"},{"id":"go","kind":"go","module":"github.com/porteden/cli/cmd/porteden@latest","bins":["porteden"],"label":"Install porteden (go)"}]}}
---

# 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:

```bash
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:

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

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

### 3. Test the connection

```bash
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:
  ```bash
  porteden sheets append $PE_SHEET_ID --range "Sheet1!A:D" --values '[["2025-01-15","Order #1042","Shipped",29.99]]'
  ```

- Append multiple rows:
  ```bash
  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:
  ```bash
  porteden sheets append $PE_SHEET_ID --range "Sheet1!A:D" --csv "2025-01-15,Order #1042,Shipped,29.99"
  ```

- Append from CSV file:
  ```bash
  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:
  ```bash
  porteden sheets write $PE_SHEET_ID --range "Sheet1!E2" --values '[["Complete"]]'
  ```

- Write a block:
  ```bash
  porteden sheets write $PE_SHEET_ID --range "Sheet1!A1:C2" --values '[["Name","Status","Score"],["Alice","Done",95]]'
  ```

- Write from CSV file:
  ```bash
  porteden sheets write $PE_SHEET_ID --range "Sheet1!A1" --csv-file ./data.csv
  ```

### Read for verification

After writing, confirm the data landed correctly:

```bash
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`.