google-sheets

What this skill does

# Google Sheets

Interact with Google Sheets for spreadsheet management, data manipulation, and formula operations.

## Installation

**Dependencies**: `pip install --user google-auth google-auth-oauthlib google-api-python-client keyring pyyaml`

## Setup Verification

After installation, verify the skill is properly configured:

```bash
$SKILL_DIR/scripts/google-sheets.py check
```

This will check:
- Python dependencies (google-auth, google-auth-oauthlib, google-api-python-client, keyring, pyyaml)
- Authentication configuration
- Connectivity to Google Sheets API

If anything is missing, the check command will provide setup instructions.

## Authentication

Google Sheets uses OAuth 2.0 for authentication. For complete setup instructions, see:

1. [GCP Project Setup Guide](https://github.com/odyssey4me/agent-skills/blob/main/docs/gcp-project-setup.md) - Create project, enable Sheets API
2. [Google OAuth Setup Guide](https://github.com/odyssey4me/agent-skills/blob/main/docs/google-oauth-setup.md) - Configure credentials

### Quick Start

1. Create `~/.config/agent-skills/google.yaml`:
   ```yaml
   oauth_client:
     client_id: your-client-id.apps.googleusercontent.com
     client_secret: your-client-secret
   ```

2. Run `$SKILL_DIR/scripts/google-sheets.py check` to trigger OAuth flow and verify setup.

On scope or authentication errors, see the [OAuth troubleshooting guide](https://github.com/odyssey4me/agent-skills/blob/main/docs/google-oauth-setup.md#troubleshooting).

## Commands

See [permissions.md](references/permissions.md) for read/write classification of each command.

### check

Verify configuration and connectivity.

```bash
$SKILL_DIR/scripts/google-sheets.py check
```

This validates:
- Python dependencies are installed
- Authentication is configured
- Can connect to Google Sheets API
- Creates a test spreadsheet to verify write access

### auth setup

Store OAuth 2.0 client credentials for custom OAuth flow.

```bash
$SKILL_DIR/scripts/google-sheets.py auth setup \
  --client-id YOUR_CLIENT_ID \
  --client-secret YOUR_CLIENT_SECRET
```

Credentials are saved to `~/.config/agent-skills/google-sheets.yaml`.

**Options:**
- `--client-id` - OAuth 2.0 client ID (required)
- `--client-secret` - OAuth 2.0 client secret (required)

### auth reset

Clear stored OAuth token. The next command that needs authentication will trigger re-authentication automatically.

```bash
$SKILL_DIR/scripts/google-sheets.py auth reset
```

Use this when you encounter scope or authentication errors.

### auth status

Show current OAuth token information without making API calls.

```bash
$SKILL_DIR/scripts/google-sheets.py auth status
```

Displays: whether a token is stored, granted scopes, refresh token presence, token expiry, and client ID.

### spreadsheets create

Create a new Google Sheets spreadsheet.

```bash
$SKILL_DIR/scripts/google-sheets.py spreadsheets create --title "My Spreadsheet"
```

**Options:**
- `--title` - Spreadsheet title (required)
- `--sheets` - Comma-separated sheet names (optional)

**Example:**
```bash
# Create with default Sheet1
$SKILL_DIR/scripts/google-sheets.py spreadsheets create --title "Sales Data"

# Create with custom sheets
$SKILL_DIR/scripts/google-sheets.py spreadsheets create \
  --title "Q1 Report" \
  --sheets "Summary,January,February,March"

# Output:
# ✓ Spreadsheet created successfully
# Title: Q1 Report
# Spreadsheet ID: 1abc...xyz
# Sheets: 4 (Summary, January, February, March)
# URL: https://docs.google.com/spreadsheets/d/1abc...xyz/edit
```

### spreadsheets get

Get spreadsheet metadata and structure.

```bash
$SKILL_DIR/scripts/google-sheets.py spreadsheets get SPREADSHEET_ID
```

**Arguments:**
- `spreadsheet_id` - The Google Sheets spreadsheet ID

**Example:**
```bash
$SKILL_DIR/scripts/google-sheets.py spreadsheets get 1abc...xyz

# Output:
# Title: Sales Data
# Spreadsheet ID: 1abc...xyz
# Sheets: 2 (Sheet1, Summary)
# URL: https://docs.google.com/spreadsheets/d/1abc...xyz/edit
```

### values read

Read cell values from a range.

```bash
$SKILL_DIR/scripts/google-sheets.py values read SPREADSHEET_ID --range "Sheet1!A1:D5"
```

**Arguments:**
- `spreadsheet_id` - The Google Sheets spreadsheet ID

**Options:**
- `--range` - Range in A1 notation (required, e.g., "Sheet1!A1:D5")
- `--format` - Value format: FORMATTED_VALUE (default), UNFORMATTED_VALUE, or FORMULA

**Example:**
```bash
# Read a range
$SKILL_DIR/scripts/google-sheets.py values read 1abc...xyz --range "Sheet1!A1:C3"

# Output (formatted as table):
# Name      | Age | City
# Alice     | 30  | NYC
# Bob       | 25  | LA

# Read formulas
$SKILL_DIR/scripts/google-sheets.py values read 1abc...xyz \
  --range "Sheet1!D1:D10" \
  --format FORMULA
```

See [references/range-notation.md](references/range-notation.md) for A1 notation details.

### values write

Write values to a range.

```bash
$SKILL_DIR/scripts/google-sheets.py values write SPREADSHEET_ID \
  --range "Sheet1!A1" \
  --values '[[\"Name\",\"Age\"],[\"Alice\",30]]'
```

**Arguments:**
- `spreadsheet_id` - The Google Sheets spreadsheet ID

**Options:**
- `--range` - Starting range in A1 notation (required)
- `--values` - Values as JSON 2D array (required)

**Example:**
```bash
# Write data starting at A1
$SKILL_DIR/scripts/google-sheets.py values write 1abc...xyz \
  --range "Sheet1!A1" \
  --values '[[\"Product\",\"Price\",\"Quantity\"],[\"Widget\",9.99,100]]'

# Write a single row
$SKILL_DIR/scripts/google-sheets.py values write 1abc...xyz \
  --range "Sheet1!A5" \
  --values '[[\"Total\",999,50]]'

# Output:
# ✓ Values written successfully
#   Updated cells: 6
#   Updated range: Sheet1!A1:C2
```

**Note:** Values are entered as the user would type them. Formulas start with `=`.

### values append

Append rows to the end of a sheet.

```bash
$SKILL_DIR/scripts/google-sheets.py values append SPREADSHEET_ID \
  --range "Sheet1" \
  --values '[[\"New\",\"Row\",\"Data\"]]'
```

**Arguments:**
- `spreadsheet_id` - The Google Sheets spreadsheet ID

**Options:**
- `--range` - Sheet name or range (required)
- `--values` - Values as JSON 2D array (required)

**Example:**
```bash
# Append a single row
$SKILL_DIR/scripts/google-sheets.py values append 1abc...xyz \
  --range "Sheet1" \
  --values '[[\"Charlie\",35,\"Chicago\"]]'

# Append multiple rows
$SKILL_DIR/scripts/google-sheets.py values append 1abc...xyz \
  --range "Sheet1" \
  --values '[[\"David\",28,\"Boston\"],[\"Eve\",32,\"Seattle\"]]'

# Output:
# ✓ Values appended successfully
#   Updated cells: 3
#   Updated range: Sheet1!A4:C4
```

### values clear

Clear values in a range.

```bash
$SKILL_DIR/scripts/google-sheets.py values clear SPREADSHEET_ID --range "Sheet1!A1:D10"
```

**Arguments:**
- `spreadsheet_id` - The Google Sheets spreadsheet ID

**Options:**
- `--range` - Range in A1 notation (required)

**Example:**
```bash
# Clear a range
$SKILL_DIR/scripts/google-sheets.py values clear 1abc...xyz --range "Sheet1!A1:Z100"

# Output:
# ✓ Values cleared successfully
#   Cleared range: Sheet1!A1:Z100
```

**Warning:** This only clears values, not formatting or formulas in protected cells.

### sheets create

Add a new sheet to a spreadsheet.

```bash
$SKILL_DIR/scripts/google-sheets.py sheets create SPREADSHEET_ID --title "New Sheet"
```

**Arguments:**
- `spreadsheet_id` - The Google Sheets spreadsheet ID

**Options:**
- `--title` - Sheet title (required)

**Example:**
```bash
$SKILL_DIR/scripts/google-sheets.py sheets create 1abc...xyz --title "Q2 Data"

# Output:
# ✓ Sheet created successfully
#   Title: Q2 Data
#   Sheet ID: 123456789
```

### sheets delete

Delete a sheet from a spreadsheet.

```bash
$SKILL_DIR/scripts/google-sheets.py sheets delete SPREADSHEET_ID --sheet-id 123456789
```

**Arguments:**
- `spreadsheet_id` - The Google Sheets spreadsheet ID

**Options:**
- `--sheet-id` - Sheet ID (required, not the title!)

**Example:**
```bash
# Get sheet IDs first
$SKILL_DIR/scripts/google-sheets.py spreadsheets get 1abc...