") ``` 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 </article></body></html>

--- 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", "") ``` 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 </article></body></html>