# Skill: Add Google Sheets Integration

Use when you need agents to read from or write to Google Sheets — for example,
processing a list of rows, enriching product data, or writing LLM output back to a spreadsheet.

## What gets set up

- Install: `pip install "arccrew[sheets]"`
- One-time OAuth2 auth flow to generate `credentials/authorized_user.json`
- Tools available in any agent: `read_sheet_row`, `write_sheet_row`

## Install

```bash
pip install "arccrew[sheets]"
# Installs: gspread, google-auth, google-auth-oauthlib
```

Add to `requirements.txt`:
```
arccrew[sheets]
```

## Auth setup (one-time)

The recommended auth method is OAuth2 as the sheet owner — this bypasses Google Sheets
protected ranges without needing to share the sheet with a service account email.

### Step 1: Create OAuth2 credentials in Google Cloud Console

1. Go to https://console.cloud.google.com
2. Create a project (or use an existing one)
3. Enable **Google Sheets API** and **Google Drive API**
4. Go to **APIs & Services → Credentials → Create Credentials → OAuth 2.0 Client ID**
5. Application type: **Desktop app**
6. Download the JSON file → save as `credentials/oauth_client.json`

### Step 2: Run the auth setup script

Create `tools/auth_setup.py` in the project:

```python
"""One-time OAuth2 authorization flow. Run once to generate authorized_user.json."""
import json
from pathlib import Path
from google_auth_oauthlib.flow import InstalledAppFlow

SCOPES = [
    "https://www.googleapis.com/auth/spreadsheets",
    "https://www.googleapis.com/auth/drive.file",
]

def main():
    Path("credentials").mkdir(exist_ok=True)
    flow = InstalledAppFlow.from_client_secrets_file(
        "credentials/oauth_client.json", SCOPES
    )
    creds = flow.run_local_server(port=0)
    token_data = {
        "token": creds.token,
        "refresh_token": creds.refresh_token,
        "token_uri": creds.token_uri,
        "client_id": creds.client_id,
        "client_secret": creds.client_secret,
        "scopes": list(creds.scopes),
    }
    with open("credentials/authorized_user.json", "w") as f:
        json.dump(token_data, f, indent=2)
    print("✓ Saved credentials/authorized_user.json")

if __name__ == "__main__":
    main()
```

Run it:
```bash
python tools/auth_setup.py
# Opens browser → authorize → saves credentials/authorized_user.json
```

The token auto-refreshes after that — no re-auth needed.

### Service account (alternative)

If you prefer a service account:
1. Go to **APIs & Services → Credentials → Create Credentials → Service Account**
2. Download JSON → save as `credentials/google_service_account.json`
3. Share the sheet with the service account email

Note: service accounts cannot access protected ranges unless explicitly added as editors.

### .gitignore

Add to `.gitignore`:
```
credentials/authorized_user.json
credentials/google_service_account.json
credentials/oauth_client.json
```

## Usage in agents

```python
from arccrew.tools import get_sheets_tools

# In agent's execute():
result = await self.run_react(
    task=task,
    tools=get_sheets_tools() + get_research_tools(),
)
```

### Tool: `read_sheet_row`

Reads one row and returns a JSON dict keyed by column headers:

```
read_sheet_row(
    spreadsheet_id="1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgVE2upms",
    sheet_name="Products",
    row_number=2,   # 1-indexed; row 1 is always headers
)
# → {"Name": "Widget A", "Price": "9.99", "Status": "", "__row_number__": 2}

# row_number=0 → auto-select first row where the last column is empty
read_sheet_row(..., row_number=0)
```

### Tool: `write_sheet_row`

Writes fields back, matched by column header name:

```
write_sheet_row(
    spreadsheet_id="1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgVE2upms",
    sheet_name="Products",
    row_number=2,
    content_json='{"Description": "A quality widget", "Status": "done"}',
)
# → "OK: updated 2 cells in row 2"
```

Only the specified columns are updated — other columns are left untouched.
Unknown column names are silently skipped.

### Helper: `count_unprocessed`

Call directly (not as an agent tool) to decide how many rows need processing:

```python
from arccrew.tools.sheets_tools import count_unprocessed

remaining = count_unprocessed(
    spreadsheet_id="...",
    sheet_name="Products",
    status_column="Status",
    done_value="done",
)
print(f"{remaining} rows left to process")
```

## Full pipeline example

```python
from arccrew import BaseAgent, track_timing
from arccrew.tools import get_sheets_tools, get_research_tools
from arccrew.tools.sheets_tools import count_unprocessed
from langgraph.types import Command
from pathlib import Path

SPREADSHEET_ID = "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgVE2upms"

class ProductEnricherAgent(BaseAgent):
    def __init__(self):
        super().__init__(name="product_enricher", prompts_dir=Path("prompts"))

    @property
    def system_prompt(self) -> str:
        return self.get_prompt_manager().assemble_prompt("product_enricher")

    @track_timing
    async def execute(self, state: dict) -> Command:
        task = state["tasks"][state["current_task_index"]]["description"]
        result = await self.run_react(
            task=task,
            tools=get_sheets_tools() + get_research_tools(),
        )
        return Command(goto="__end__", update={"result": self.extract_json(result)})
```

Prompt (`prompts/product_enricher.md`):
```markdown
You are a product enrichment agent. For each task:
1. Call read_sheet_row with row_number=0 to get the next unprocessed row
2. Research the product using web_search
3. Call write_sheet_row to save Description, Category, and set Status="done"
```

## Environment variables

No env vars required — auth is file-based. The spreadsheet ID is passed as a tool argument.

## Troubleshooting

| Error | Cause | Fix |
|-------|-------|-----|
| `No Google credentials found` | Missing credentials files | Run `python tools/auth_setup.py` |
| `PERMISSION_DENIED` | Service account not added to sheet | Share sheet with service account email |
| `Worksheet not found` | Wrong sheet name | Check tab name in Google Sheets UI |
| `Token expired` | OAuth token stale | arccrew auto-refreshes; if fails, re-run auth_setup.py |
| `ImportError: gspread` | Package not installed | `pip install "arccrew[sheets]"` |
