# google-sheets

> Create, read, write, format, and manage Google Sheets spreadsheets

- Author: KIrill Enkogu
- Repository: enkogu/pipeline-fullstack-app
- Version: 20260125155958
- Stars: 0
- Forks: 0
- Last Updated: 2026-02-06
- Source: https://github.com/enkogu/pipeline-fullstack-app
- Web: https://mule.run/skillshub/@@enkogu/pipeline-fullstack-app~google-sheets:20260125155958

---

---
name: google-sheets
description: Create, read, write, format, and manage Google Sheets spreadsheets
python_requires:
  - google-api-python-client
  - google-auth
  - google-auth-oauthlib
requires_oauth:
  - sheets
---

# Google Sheets Skill

## ⚠️ SCRIPT NAMES - These Do NOT Exist

Do NOT guess script names. These common mistakes will fail:
| ❌ WRONG (doesn't exist) | ✅ CORRECT (use this) |
|--------------------------|----------------------|
| search-sheets.py | list-sheets.py |
| search-spreadsheet.py | list-sheets.py |
| find-sheets.py | list-sheets.py |
| format-cell.py | format-cells.py |
| create-spreadsheet.py | create-sheet.py |
| get-sheet.py | read-sheet.py |
| update-sheet.py | write-sheet.py |

## Common Mistakes

1. **Wrong script names** (see table above):
   - Always use the EXACT script names listed in this documentation
   - If unsure, use `list-sheets.py` to find spreadsheets (not `search-sheets.py`)

2. **Triple-nested arrays** (see Values Format section below):
   - ❌ `[[["John", "john@email.com"]]]` → API error
   - ✅ `[["John", "john@email.com"]]` → Correct 2D array

3. **Escaping nightmare with positional arrays for nested data**:
   - ❌ `'["ABC123", "A1", "[[\"cell1\",\"cell2\"]]"]'` → Error-prone escaping
   - ✅ Use JSON object format instead (see below)

4. **Grid-based scripts do NOT accept A1 notation or JSON objects**:
   - ❌ `sort-range.py '{"spreadsheet_id": "...", "range": "A2:F100"}'` → FAILS
   - ❌ `sort-range.py '["ABC123", "Sheet1", ...]'` - needs numeric sheet_id
   - ✅ `sort-range.py '["ABC123", "0", "1", "100", "0", "5", "4", "DESCENDING"]'`
   - See "Grid-Based Scripts" section below for complete guide

## JSON Object Format (RECOMMENDED for Complex Data)

For scripts handling nested arrays (`write-sheet.py`, `append-rows.py`), **JSON object format is strongly recommended** to avoid escaping issues:

### write-sheet.py
Param names: `spreadsheet_id`, `range`, `data`
```python
# ✅ RECOMMENDED - Clean, no escaping issues
run_skill("google-sheets/write-sheet.py", '{"spreadsheet_id": "ABC123", "data": [["Header1", "Header2"], ["val1", "val2"]], "range": "Sheet1!A1"}')

# Also works but error-prone for nested data
run_skill("google-sheets/write-sheet.py", '["ABC123", "Sheet1!A1", "[[\"Header1\",\"Header2\"],[\"val1\",\"val2\"]]"]')
```

### append-rows.py
Param names: `spreadsheet_id`, `range`, `data`
```python
# ✅ RECOMMENDED
run_skill("google-sheets/append-rows.py", '{"spreadsheet_id": "ABC123", "data": [["new", "row"]], "range": "Sheet1"}')

# Positional also works
run_skill("google-sheets/append-rows.py", '["ABC123", "Sheet1", "[[\"new\",\"row\"]]"]')
```

**Why JSON object format?**
- No nested quote escaping required
- Data arrays remain readable: `[["a", "b"]]` not `\"[[\\\"a\\\",\\\"b\\\"]]\"`
- Fewer JSON parsing errors
- Parameter order doesn't matter

## Basic Operations

### List Spreadsheets
```
run_skill("google-sheets/list-sheets.py", "")
```

### Create Spreadsheet
```
run_skill("google-sheets/create-sheet.py", "<title>")
```
Example: `run_skill("google-sheets/create-sheet.py", "My New Sheet")`

### Get Metadata
```
run_skill("google-sheets/get-metadata.py", "<spreadsheet_id>")
```

### Read Sheet Data
Param names: `spreadsheet_id`, `range`
```
# Positional format:
run_skill("google-sheets/read-sheet.py", "<spreadsheet_id>")
run_skill("google-sheets/read-sheet.py", '["<spreadsheet_id>", "<range>"]')

# JSON object format:
run_skill("google-sheets/read-sheet.py", '{"spreadsheet_id": "ABC123", "range": "Sheet1!A1:Z100"}')
```
Example: `run_skill("google-sheets/read-sheet.py", '{"spreadsheet_id": "ABC123", "range": "Sheet1!A1:Z100"}')`

### Write Sheet Data
```
run_skill("google-sheets/write-sheet.py", '["<spreadsheet_id>", "<range>", "<values_json>"]')
```
Example: `run_skill("google-sheets/write-sheet.py", '["ABC123", "Sheet1!A1", "[[\"Header1\",\"Header2\"],[\"val1\",\"val2\"]]"]')`

### Append Rows
```
run_skill("google-sheets/append-rows.py", '["<spreadsheet_id>", "<sheet_name>", "<values_json>"]')
```
Example: `run_skill("google-sheets/append-rows.py", '["ABC123", "Sheet1", "[[\"new\",\"row\"]]"]')`

#### Values Format Explained
The `values_json` is a **2D array** (rows containing columns):
```
[[row1_col1, row1_col2, ...],     ← First row
 [row2_col1, row2_col2, ...]]    ← Second row
```

**Single row example** (1 row, 4 columns):
```json
[["John", "john@email.com", "Notes", "8"]]
```
Notice: `[[...]]` = one outer array (rows) containing one inner array (columns).

**Multiple rows example** (2 rows, 2 columns):
```json
[["John", "john@email.com"], ["Jane", "jane@email.com"]]
```

**COMMON MISTAKE**: Do NOT wrap in an extra array!
- ❌ WRONG: `[[["John", "john@email.com"]]]` (triple-nested - causes API error)
- ✅ RIGHT: `[["John", "john@email.com"]]` (double-nested)

### Clear Range
```
run_skill("google-sheets/clear-range.py", '["<spreadsheet_id>", "<range>"]')
```
Example: `run_skill("google-sheets/clear-range.py", '["ABC123", "Sheet1!A1:D10"]')`

## Batch Operations

### Batch Read
```
run_skill("google-sheets/batch-read.py", '["<spreadsheet_id>", "<ranges>"]')
```
Example: `run_skill("google-sheets/batch-read.py", '["ABC123", "Sheet1!A1:B10,Sheet2!C1:D10"]')`

### Batch Write
```
run_skill("google-sheets/batch-write.py", '["<spreadsheet_id>", "<requests_json>"]')
```
Example: `run_skill("google-sheets/batch-write.py", '["ABC123", "[{\"range\":\"Sheet1!A1\",\"values\":[[\"a\",\"b\"]]}]"]')`

## Sheet/Tab Management

### Add Tab
```
run_skill("google-sheets/add-tab.py", '["<spreadsheet_id>", "<tab_name>"]')
```
Example: `run_skill("google-sheets/add-tab.py", '["ABC123", "Q1 2025 Results"]')`

### Delete Tab
```
run_skill("google-sheets/delete-tab.py", '["<spreadsheet_id>", "<sheet_id>"]')
```

## Grid-Based Scripts (IMPORTANT)

These scripts use **raw grid indices**, NOT A1 notation. They require a specific workflow.

### Scripts That Use Grid Indices
- `sort-range.py`
- `add-data-validation.py`
- `add-borders.py`
- `merge-cells.py`
- `add-conditional-formatting.py`
- `freeze-rows-columns.py`
- `auto-resize-columns.py`

### Required Workflow
1. **FIRST** call `get-metadata.py` to get `sheet_id` (integer, usually 0 for first tab)
2. **THEN** call the grid script with positional array format

### Parameter Mapping (A1 → Grid Indices)
| A1 Notation | Grid Index | Notes |
|-------------|------------|-------|
| Column A | 0 | Columns are 0-indexed |
| Column E | 4 | A=0, B=1, C=2, D=3, E=4 |
| Row 1 | 0 | Rows are 0-indexed |
| Row 2 (after header) | 1 | Skip header row |
| Range A2:F100 | start_row=1, end_row=100, start_col=0, end_col=6 | end indices are exclusive |

### Example: sort-range.py
Sort rows 2-100 by column E (Date) in descending order:
```python
# Step 1: Get sheet_id
run_skill("google-sheets/get-metadata.py", "SPREADSHEET_ID")
# Returns: {"sheets": [{"title": "Invoices", "sheetId": 0, ...}]}

# Step 2: Call sort-range with grid indices
run_skill("google-sheets/sort-range.py", '["SPREADSHEET_ID", "0", "1", "100", "0", "6", "4", "DESCENDING"]')
#                                          └─ spreadsheet_id  │    │     │    │    │    │    └─ order
#                                                              │    │     │    │    │    └─ sort_col (E=4)
#                                                              │    │     │    │    └─ end_col (F=5+1)
#                                                              │    │     │    └─ start_col (A=0)
#                                                              │    │     └─ end_row
#                                                              │    └─ start_row (skip header)
#                                                              └─ sheet_id
```

### Example: add-data-validation.py
Add dropdown validation (Yes/No) to column B, rows 2-50:
```python
run_skill("google-sheets/add-data-validation.py", '["SPREADSHEET_ID", "0", "1", "50", "1", "2", "Yes,No"]')
#                                                   └─ spreadsheet_id  │    │     │    │    │    └─ values_csv
#                                                                       │    │     │    │    └─ end_col (B=1+1)
#                                                                       │    │     │    └─ start_col (B=1)
#                                                                       │    │     └─ end_row
#                                                                       │    └─ start_row
#                                                                       └─ sheet_id
```

## Formatting

### Format Cells
```
run_skill("google-sheets/format-cells.py", '["<spreadsheet_id>", "<sheet_name>", "<format_json>"]')
```
Example: `run_skill("google-sheets/format-cells.py", '["ABC123", "Sheet1", "{\"range\":\"A1:D1\",\"bold\":true,\"bg_color\":\"#FFFF00\"}"]')`
Options: bold, italic, font_size, bg_color, text_color, align (left/center/right)

### Add Borders
```
run_skill("google-sheets/add-borders.py", '["<spreadsheet_id>", "<sheet_id>", "<start_row>", "<end_row>", "<start_col>", "<end_col>", "style:SOLID", "color:#000000", "inner:true"]')
```

### Merge Cells
```
run_skill("google-sheets/merge-cells.py", '["<spreadsheet_id>", "<sheet_id>", "<start_row>", "<end_row>", "<start_col>", "<end_col>", "type:MERGE_ALL"]')
```

### Add Conditional Formatting
```
run_skill("google-sheets/add-conditional-formatting.py", '["<spreadsheet_id>", "<sheet_id>", "<start_row>", "<end_row>", "<start_col>", "<end_col>", "<condition_type>", "<value>", "bg_color:#FF0000"]')
```
CONDITION_TYPE: NUMBER_GREATER, NUMBER_LESS, TEXT_CONTAINS, TEXT_EQ, etc.

### Add Data Validation
```
run_skill("google-sheets/add-data-validation.py", '["<spreadsheet_id>", "<sheet_id>", "<start_row>", "<end_row>", "<start_col>", "<end_col>", "<values_csv>", "strict:true"]')
```
Example: `run_skill("google-sheets/add-data-validation.py", '["ABC123", "0", "0", "10", "0", "1", "Yes,No,Maybe"]')`

## Layout

### Freeze Rows/Columns
```
run_skill("google-sheets/freeze-rows-columns.py", '["<spreadsheet_id>", "<sheet_id>", "frozen_rows:0", "frozen_cols:0"]')
```

### Auto-Resize Columns
```
run_skill("google-sheets/auto-resize-columns.py", '["<spreadsheet_id>", "<sheet_id>", "<start_col>", "<end_col>"]')
```

### Set Dimensions
```
run_skill("google-sheets/set-dimensions.py", '["<spreadsheet_id>", "<sheet_id>", "<dimension>", "<start_index>", "<end_index>", "<size_pixels>"]')
```
DIMENSION: ROWS or COLUMNS

### Sort Range
```
run_skill("google-sheets/sort-range.py", '["<spreadsheet_id>", "<sheet_id>", "<start_row>", "<end_row>", "<start_col>", "<end_col>", "<sort_col>", "order:ASCENDING"]')
```

## Charts

### Create Chart
```
run_skill("google-sheets/create-chart.py", '["<spreadsheet_id>", "<sheet_id>", "<start_row>", "<end_row>", "<start_col>", "<end_col>", "<chart_type>", "<title>"]')
```
CHART_TYPE: BAR, LINE, AREA, COLUMN, SCATTER, PIE

## CSV Operations

### Create Sheet from CSV
```
run_skill("google-sheets/create-sheet-from-csv.py", '["<title>", "<csv_file_path>"]')
```
Example: `run_skill("google-sheets/create-sheet-from-csv.py", '["My Sales Report", "data.csv"]')`

### Update Sheet with CSV
```
run_skill("google-sheets/update-sheet-with-csv.py", '["<spreadsheet_id>", "<csv_file_path>", "range:Sheet1!A1", "clear_first:true"]')
```

## Advanced

### Batch Update Spreadsheet
```
run_skill("google-sheets/batch-update-spreadsheet.py", '["<spreadsheet_id>", "<requests_json>"]')
```
Execute multiple API requests in a single batch

## Notes
- Range format: `SheetName!A1:Z100`
- Values must be JSON arrays
- All scripts output JSON to stdout