Back to skills
SkillHub ClubWrite Technical DocsFull StackData / AITech Writer
google-sheets
Manage Google Sheets with comprehensive spreadsheet operations including reading/writing cell values, formulas, formatting, sheet management, and batch operations. Use for spreadsheet data operations, cell ranges, formulas, formatting, batch updates, and data analysis workflows. Shares OAuth token with email, calendar, contacts, drive, and docs skills.
Packaged view
This page reorganizes the original catalog entry around fit, installability, and workflow context first. The original raw source lives below.
Stars
4
Hot score
81
Updated
March 20, 2026
Overall rating
C3.7
Composite score
3.7
Best-practice grade
B73.6
Install command
npx @skill-hub/cli install arlenagreer-claude-configuration-docs-google-sheets
Repository
arlenagreer/claude_configuration_docs
Skill path: skills/google-sheets
Manage Google Sheets with comprehensive spreadsheet operations including reading/writing cell values, formulas, formatting, sheet management, and batch operations. Use for spreadsheet data operations, cell ranges, formulas, formatting, batch updates, and data analysis workflows. Shares OAuth token with email, calendar, contacts, drive, and docs skills.
Open repositoryBest for
Primary workflow: Write Technical Docs.
Technical facets: Full Stack, Data / AI, Tech Writer.
Target audience: everyone.
License: Unknown.
Original source
Catalog source: SkillHub Club.
Repository owner: arlenagreer.
This is still a mirrored public skill entry. Review the repository before installing into production workflows.
What it helps with
- Install google-sheets into Claude Code, Codex CLI, Gemini CLI, or OpenCode workflows
- Review https://github.com/arlenagreer/claude_configuration_docs before adding google-sheets to shared team environments
- Use google-sheets for productivity workflows
Works across
Claude CodeCodex CLIGemini CLIOpenCode
Favorites: 0.
Sub-skills: 0.
Aggregator: No.
Original source / Raw SKILL.md
---
name: google-sheets
description: Manage Google Sheets with comprehensive spreadsheet operations including reading/writing cell values, formulas, formatting, sheet management, and batch operations. Use for spreadsheet data operations, cell ranges, formulas, formatting, batch updates, and data analysis workflows. Shares OAuth token with email, calendar, contacts, drive, and docs skills.
category: productivity
version: 1.0.0
key_capabilities: read/write cells, append rows, apply formatting, create sheets, batch operations, A1 notation support
when_to_use: Spreadsheet data operations, cell ranges, formulas, formatting, batch updates, data analysis workflows
---
# Google Sheets Management Skill
## Purpose
Manage Google Sheets spreadsheets with comprehensive operations:
- Read cell values and formulas
- Write and update cell values
- Append rows to sheets
- Clear cell ranges
- Create new sheets within spreadsheets
- Basic cell formatting (bold, italic, colors)
- Batch updates for efficiency
- Get spreadsheet metadata
- Share OAuth token with all Google skills
**Integration**: Works seamlessly with google-drive skill for file creation and management
**📚 Additional Resources**:
- See `references/integration-patterns.md` for complete workflow examples
- See `references/troubleshooting.md` for error handling and debugging
- See `references/cli-patterns.md` for CLI interface design rationale
## When to Use This Skill
Use this skill when:
- User requests spreadsheet operations: "Read the data from my spreadsheet", "Update the budget sheet"
- User wants to create or modify data: "Add a row to the tracking sheet", "Update cell B5"
- User mentions formulas: "Write a formula to sum column A", "Update the calculation"
- User requests formatting: "Make the header row bold", "Highlight the total in yellow"
- User needs batch operations: "Update multiple ranges", "Fill in the entire data set"
- User asks about spreadsheet structure: "How many sheets are in this workbook?", "What columns exist?"
**📋 Discovering Your Spreadsheets**:
To list or search for spreadsheets, use the google-drive skill:
```bash
# List recent spreadsheets
~/.claude/skills/google-drive/scripts/drive_manager.rb search \
--query "mimeType='application/vnd.google-apps.spreadsheet'" \
--max-results 50
# Search by name
~/.claude/skills/google-drive/scripts/drive_manager.rb search \
--query "name contains 'Budget' and mimeType='application/vnd.google-apps.spreadsheet'"
```
## Core Workflows
### 1. Read Cell Values
**Read single cell**:
```bash
echo '{
"spreadsheet_id": "abc123xyz",
"range": "Sheet1!A1"
}' | ~/.claude/skills/google-sheets/scripts/sheets_manager.rb read
```
**Read range of cells**:
```bash
echo '{
"spreadsheet_id": "abc123xyz",
"range": "Sheet1!A1:D10"
}' | ~/.claude/skills/google-sheets/scripts/sheets_manager.rb read
```
**Read entire column**:
```bash
echo '{
"spreadsheet_id": "abc123xyz",
"range": "Sheet1!A:A"
}' | ~/.claude/skills/google-sheets/scripts/sheets_manager.rb read
```
**Read entire row**:
```bash
echo '{
"spreadsheet_id": "abc123xyz",
"range": "Sheet1!1:1"
}' | ~/.claude/skills/google-sheets/scripts/sheets_manager.rb read
```
**Output Format**:
```json
{
"status": "success",
"operation": "read",
"spreadsheet_id": "abc123xyz",
"range": "Sheet1!A1:D10",
"values": [
["Header1", "Header2", "Header3", "Header4"],
["Value1", "Value2", "Value3", "Value4"]
],
"row_count": 2
}
```
### 2. Write Cell Values
**Write single cell**:
```bash
echo '{
"spreadsheet_id": "abc123xyz",
"range": "Sheet1!A1",
"values": [["Hello World"]]
}' | ~/.claude/skills/google-sheets/scripts/sheets_manager.rb write
```
**Write range of cells**:
```bash
echo '{
"spreadsheet_id": "abc123xyz",
"range": "Sheet1!A1:B2",
"values": [
["Name", "Age"],
["Alice", 30]
]
}' | ~/.claude/skills/google-sheets/scripts/sheets_manager.rb write
```
**Write with formulas**:
```bash
echo '{
"spreadsheet_id": "abc123xyz",
"range": "Sheet1!C1",
"values": [["=SUM(A1:A10)"]],
"input_option": "USER_ENTERED"
}' | ~/.claude/skills/google-sheets/scripts/sheets_manager.rb write
```
**Input Options**:
- `USER_ENTERED` (default): Parses input as if typed by user (formulas, dates, numbers)
- `RAW`: Stores input exactly as provided (everything as strings)
**Output Format**:
```json
{
"status": "success",
"operation": "write",
"spreadsheet_id": "abc123xyz",
"range": "Sheet1!A1:B2",
"updated_cells": 4,
"updated_rows": 2,
"updated_columns": 2
}
```
### 3. Append Rows
**Append single row**:
```bash
echo '{
"spreadsheet_id": "abc123xyz",
"range": "Sheet1!A1",
"values": [["New", "Row", "Data"]]
}' | ~/.claude/skills/google-sheets/scripts/sheets_manager.rb append
```
**Append multiple rows**:
```bash
echo '{
"spreadsheet_id": "abc123xyz",
"range": "Sheet1!A1",
"values": [
["Row1Col1", "Row1Col2"],
["Row2Col1", "Row2Col2"],
["Row3Col1", "Row3Col2"]
]
}' | ~/.claude/skills/google-sheets/scripts/sheets_manager.rb append
```
**How Append Works**:
- Finds the last row with data in the specified range
- Appends new rows immediately after
- Does not overwrite existing data
- Perfect for logging, tracking, and data collection
### 4. Clear Cell Values
**Clear specific range**:
```bash
echo '{
"spreadsheet_id": "abc123xyz",
"range": "Sheet1!A1:D10"
}' | ~/.claude/skills/google-sheets/scripts/sheets_manager.rb clear
```
**Clear entire sheet**:
```bash
echo '{
"spreadsheet_id": "abc123xyz",
"range": "Sheet1"
}' | ~/.claude/skills/google-sheets/scripts/sheets_manager.rb clear
```
**Important**: Clear only removes cell values, not formatting or formulas
### 5. Get Spreadsheet Metadata
```bash
echo '{
"spreadsheet_id": "abc123xyz"
}' | ~/.claude/skills/google-sheets/scripts/sheets_manager.rb metadata
```
**Output Format**:
```json
{
"status": "success",
"operation": "metadata",
"spreadsheet_id": "abc123xyz",
"title": "Budget 2024",
"locale": "en_US",
"timezone": "America/Chicago",
"sheets": [
{
"sheet_id": 0,
"title": "Sheet1",
"index": 0,
"row_count": 1000,
"column_count": 26
},
{
"sheet_id": 123456,
"title": "Summary",
"index": 1,
"row_count": 100,
"column_count": 10
}
]
}
```
### 6. Create New Sheet
**Create sheet with default size**:
```bash
echo '{
"spreadsheet_id": "abc123xyz",
"title": "Q4 Data"
}' | ~/.claude/skills/google-sheets/scripts/sheets_manager.rb create_sheet
```
**Create sheet with custom size**:
```bash
echo '{
"spreadsheet_id": "abc123xyz",
"title": "Large Dataset",
"row_count": 5000,
"column_count": 50
}' | ~/.claude/skills/google-sheets/scripts/sheets_manager.rb create_sheet
```
**Default Dimensions**:
- Rows: 1000
- Columns: 26 (A-Z)
### 7. Basic Cell Formatting
**Format header row (bold + background color)**:
```bash
echo '{
"spreadsheet_id": "abc123xyz",
"sheet_id": 0,
"start_row": 0,
"end_row": 1,
"start_col": 0,
"end_col": 5,
"format": {
"bold": true,
"fontSize": 12,
"backgroundColor": {
"red": 0.9,
"green": 0.9,
"blue": 0.9,
"alpha": 1
}
}
}' | ~/.claude/skills/google-sheets/scripts/sheets_manager.rb format
```
**Available Format Options**:
- `bold`: true/false
- `italic`: true/false
- `fontSize`: Number (e.g., 10, 12, 14)
- `backgroundColor`: Object with red, green, blue, alpha (0-1 scale)
**Important Notes**:
- Row and column indices are 0-based (first row = 0, first column = 0)
- Ranges are half-open: start is inclusive, end is exclusive
- To format row 1 (the first row): `start_row: 0, end_row: 1`
### 8. Batch Updates
**Update multiple ranges efficiently**:
```bash
echo '{
"spreadsheet_id": "abc123xyz",
"updates": [
{
"range": "Sheet1!A1:A3",
"values": [["Value1"], ["Value2"], ["Value3"]]
},
{
"range": "Sheet1!B1:B3",
"values": [["100"], ["200"], ["300"]]
},
{
"range": "Sheet1!C1",
"values": [["=SUM(B1:B3)"]]
}
]
}' | ~/.claude/skills/google-sheets/scripts/sheets_manager.rb batch_update
```
**Benefits**:
- Single API call for multiple updates
- More efficient than individual writes
- Atomic operation (all succeed or all fail)
- Perfect for populating templates or data imports
## A1 Notation Reference
**Single Cells**:
- `A1`: First cell
- `B5`: Column B, Row 5
- `Z10`: Column Z, Row 10
**Ranges**:
- `A1:B10`: Rectangle from A1 to B10
- `C5:F20`: Rectangle from C5 to F20
**Entire Rows/Columns**:
- `A:A`: Entire column A
- `C:E`: Columns C through E
- `1:1`: Entire row 1
- `5:10`: Rows 5 through 10
**Named Sheets**:
- `Sheet1!A1:B10`: Range on specific sheet
- `Q4 Data!A1`: Cell A1 on "Q4 Data" sheet
- Use single quotes for sheet names with spaces: `'Budget 2024'!A1`
## Natural Language Examples
### User Says: "Read the budget data from cells A1 to D10"
```bash
echo '{
"spreadsheet_id": "[GET_FROM_CONTEXT_OR_ASK_USER]",
"range": "Sheet1!A1:D10"
}' | ~/.claude/skills/google-sheets/scripts/sheets_manager.rb read
```
### User Says: "Add a new row with Name: John, Age: 30, City: Chicago"
```bash
echo '{
"spreadsheet_id": "[SPREADSHEET_ID]",
"range": "Sheet1!A1",
"values": [["John", 30, "Chicago"]]
}' | ~/.claude/skills/google-sheets/scripts/sheets_manager.rb append
```
### User Says: "Update cell B5 to the value 1000"
```bash
echo '{
"spreadsheet_id": "[SPREADSHEET_ID]",
"range": "Sheet1!B5",
"values": [[1000]]
}' | ~/.claude/skills/google-sheets/scripts/sheets_manager.rb write
```
### User Says: "Write a formula in C10 to sum all values in column C from rows 1 to 9"
```bash
echo '{
"spreadsheet_id": "[SPREADSHEET_ID]",
"range": "Sheet1!C10",
"values": [["=SUM(C1:C9)"]],
"input_option": "USER_ENTERED"
}' | ~/.claude/skills/google-sheets/scripts/sheets_manager.rb write
```
### User Says: "Make the first row bold with a gray background"
```bash
# First get metadata to find sheet_id
echo '{"spreadsheet_id":"[SPREADSHEET_ID]"}' | ~/.claude/skills/google-sheets/scripts/sheets_manager.rb metadata
# Then format the row (assuming sheet_id is 0)
echo '{
"spreadsheet_id": "[SPREADSHEET_ID]",
"sheet_id": 0,
"start_row": 0,
"end_row": 1,
"start_col": 0,
"end_col": 26,
"format": {
"bold": true,
"backgroundColor": {"red": 0.9, "green": 0.9, "blue": 0.9, "alpha": 1}
}
}' | ~/.claude/skills/google-sheets/scripts/sheets_manager.rb format
```
### User Says: "Clear all data from the sheet"
```bash
echo '{
"spreadsheet_id": "[SPREADSHEET_ID]",
"range": "Sheet1"
}' | ~/.claude/skills/google-sheets/scripts/sheets_manager.rb clear
```
## Integration with Google Drive Skill
**Create Spreadsheet + Populate Data Workflow**:
1. **Create spreadsheet file** (using google-drive skill):
```bash
# See google-drive skill for file creation
# Returns spreadsheet_id
```
2. **Populate with data** (using this skill):
```bash
echo '{
"spreadsheet_id": "[ID_FROM_DRIVE_SKILL]",
"range": "Sheet1!A1:C3",
"values": [
["Name", "Age", "City"],
["Alice", 30, "Chicago"],
["Bob", 25, "New York"]
]
}' | ~/.claude/skills/google-sheets/scripts/sheets_manager.rb write
```
3. **Share spreadsheet** (using google-drive skill):
```bash
# See google-drive skill for sharing operations
```
## Authentication Setup
**Shared OAuth Token**:
- Uses same token as email, calendar, contacts, drive, and docs skills
- Location: `~/.claude/.google/token.json`
- Credentials: `~/.claude/.google/client_secret.json`
**Required Scopes**:
- `https://www.googleapis.com/auth/spreadsheets` (Sheets operations)
- `https://www.googleapis.com/auth/drive` (Drive integration)
- `https://www.googleapis.com/auth/documents` (Docs integration)
- `https://www.googleapis.com/auth/calendar` (Calendar integration)
- `https://www.googleapis.com/auth/contacts` (Contacts integration)
- `https://www.googleapis.com/auth/gmail.modify` (Gmail integration)
**First-Time Setup**:
1. Run any sheets operation
2. Script will prompt for authorization URL
3. Visit URL and authorize all Google services
4. Enter authorization code when prompted
5. Token stored for future use across all Google skills
**Re-authorization**:
- Token automatically refreshes when expired
- If refresh fails, re-run authorization flow
- One authorization grants access to all Google skills
## Bundled Resources
### Scripts
**`scripts/sheets_manager.rb`**
- Comprehensive Google Sheets API wrapper
- All core operations: read, write, append, clear, metadata
- Sheet management: create new sheets within spreadsheets
- Basic formatting: bold, italic, colors, font size
- Batch updates for efficiency
- Shared OAuth with all Google skills
**Operations**:
- `auth`: Complete OAuth authorization
- `read`: Read cell values
- `write`: Write cell values
- `append`: Append rows to sheet
- `clear`: Clear cell values
- `metadata`: Get spreadsheet metadata
- `create_sheet`: Create new sheet within spreadsheet
- `format`: Update cell formatting
- `batch_update`: Batch update multiple ranges
**Output Format**:
- JSON with `status: 'success'` or `status: 'error'`
- Operation-specific data in response
- Exit codes: 0=success, 1=failed, 2=auth, 3=api, 4=args
**Ruby Gem Requirement**:
```bash
gem install google-apis-sheets_v4
```
### References
**`references/sheets_operations.md`**
- Complete operation reference with examples
- Parameter documentation for all operations
- Common use cases and patterns
- Error scenarios and solutions
**`references/cell_formats.md`**
- Cell formatting options and examples
- Color specifications (RGB + alpha)
- Text formatting (bold, italic, size)
- Background colors and patterns
- Format combinations and best practices
### Examples
**`examples/sample_operations.md`**
- Real-world usage examples
- Common workflows and patterns
- Data import/export scenarios
- Formula writing examples
- Batch operation patterns
## Error Handling
**Authentication Error**:
```json
{
"status": "error",
"error_code": "AUTH_REQUIRED",
"message": "Authorization required. Please visit the URL and enter the code.",
"auth_url": "https://accounts.google.com/o/oauth2/auth?..."
}
```
**Action**: Follow authorization instructions
**API Error**:
```json
{
"status": "error",
"error_code": "API_ERROR",
"operation": "read",
"message": "Sheets API error: Requested entity was not found."
}
```
**Action**: Verify spreadsheet_id and range, check permissions
**Invalid Arguments**:
```json
{
"status": "error",
"error_code": "MISSING_REQUIRED_FIELDS",
"message": "Required fields: spreadsheet_id, range"
}
```
**Action**: Review command parameters and retry
**Range Error**:
```json
{
"status": "error",
"error_code": "API_ERROR",
"message": "Unable to parse range: InvalidRange"
}
```
**Action**: Check A1 notation syntax, ensure sheet name exists
## Best Practices
### Getting Spreadsheet ID
1. **From URL**: Extract from Google Sheets URL
- URL: `https://docs.google.com/spreadsheets/d/ABC123XYZ/edit`
- ID: `ABC123XYZ`
2. **From google-drive skill**: Use search or list operations
3. **Store ID**: Keep commonly-used spreadsheet IDs in context
### Reading Data Efficiently
1. Read only the data you need (specific ranges)
2. Use metadata operation to understand sheet structure first
3. For large datasets, read in chunks
4. Cache read results when making multiple queries
### Writing Data Efficiently
1. Use batch_update for multiple ranges
2. Group related updates into single operations
3. Use append for adding rows (don't overwrite)
4. Prefer USER_ENTERED for formulas and dates
### Formulas
1. Always use `input_option: "USER_ENTERED"` for formulas
2. Formula syntax is standard Google Sheets formula language
3. Example: `=SUM(A1:A10)`, `=AVERAGE(B:B)`, `=IF(C1>100,"High","Low")`
4. Test formulas in Google Sheets UI before automating
### Formatting
1. Get sheet_id from metadata operation first
2. Remember: row/column indices are 0-based
3. Format ranges, not individual cells for efficiency
4. Background colors use 0-1 scale (0=0%, 0.5=50%, 1=100%)
### Sheet Management
1. Check existing sheets with metadata before creating
2. Use descriptive sheet names
3. Default size (1000x26) works for most use cases
4. Create larger sheets only when needed
## Quick Reference
**Read values**:
```bash
echo '{"spreadsheet_id":"ID","range":"Sheet1!A1:B10"}' | sheets_manager.rb read
```
**Write values**:
```bash
echo '{"spreadsheet_id":"ID","range":"Sheet1!A1","values":[["Data"]]}' | sheets_manager.rb write
```
**Append rows**:
```bash
echo '{"spreadsheet_id":"ID","range":"Sheet1!A1","values":[["Row1"],["Row2"]]}' | sheets_manager.rb append
```
**Write formula**:
```bash
echo '{"spreadsheet_id":"ID","range":"Sheet1!C1","values":[["=SUM(A1:A10)"]],"input_option":"USER_ENTERED"}' | sheets_manager.rb write
```
**Get metadata**:
```bash
echo '{"spreadsheet_id":"ID"}' | sheets_manager.rb metadata
```
**Clear range**:
```bash
echo '{"spreadsheet_id":"ID","range":"Sheet1!A1:Z100"}' | sheets_manager.rb clear
```
**Create sheet**:
```bash
echo '{"spreadsheet_id":"ID","title":"New Sheet"}' | sheets_manager.rb create_sheet
```
**Format cells**:
```bash
echo '{"spreadsheet_id":"ID","sheet_id":0,"start_row":0,"end_row":1,"start_col":0,"end_col":5,"format":{"bold":true}}' | sheets_manager.rb format
```
**Batch update** (multiple operations in one call):
```bash
echo '{
"spreadsheet_id": "ID",
"requests": [
{
"updateCells": {
"range": {"sheetId": 0, "startRowIndex": 0, "endRowIndex": 1, "startColumnIndex": 0, "endColumnIndex": 5},
"fields": "userEnteredFormat.backgroundColor,userEnteredFormat.textFormat.bold",
"userEnteredFormat": {
"backgroundColor": {"red": 0.2, "green": 0.6, "blue": 0.9},
"textFormat": {"bold": true}
}
}
},
{
"updateCells": {
"range": {"sheetId": 0, "startRowIndex": 1, "endRowIndex": 10, "startColumnIndex": 0, "endColumnIndex": 1},
"fields": "userEnteredFormat.textFormat.italic",
"userEnteredFormat": {
"textFormat": {"italic": true}
}
}
}
]
}' | sheets_manager.rb batch_update
```
## Common Workflows
### Data Entry Workflow
1. Get metadata to understand structure
2. Append new rows with data
3. Optionally format new rows
4. Verify with read operation
### Report Generation Workflow
1. Clear existing data (optional)
2. Write headers with formatting
3. Batch update data rows
4. Write formula rows for calculations
5. Format summary/total rows
### Data Analysis Workflow
1. Read data range
2. Process data in your code
3. Write results to new range or sheet
4. Add formulas for ongoing calculations
### Template Population Workflow
1. Create spreadsheet from template (google-drive)
2. Batch update with personalized data
3. Apply formatting to key areas
4. Share with collaborators (google-drive)
## Version History
- **1.0.0** (2025-11-10) - Initial google-sheets skill with comprehensive spreadsheet operations: read/write cells, append rows, clear ranges, sheet management, basic formatting, batch updates, and shared OAuth token with all Google skills (email, calendar, contacts, drive, docs)
---
**Dependencies**: Ruby with `google-apis-sheets_v4`, `google-apis-drive_v3`, `google-apis-docs_v1`, `google-apis-calendar_v3`, `google-apis-people_v1`, `googleauth` gems (shared with all Google skills)
---
## Referenced Files
> The following files are referenced in this skill and included for context.
### references/integration-patterns.md
```markdown
# Google Workspace Integration Patterns
Complete workflows combining google-drive, google-sheets, and google-docs skills for powerful automation.
## Pattern 1: Data Report Generation
**Use Case**: Extract spreadsheet data, analyze it, and generate a formatted document report.
**Workflow**:
```bash
# Step 1: Find the source spreadsheet
~/.claude/skills/google-drive/scripts/drive_manager.rb search \
--query "Sales Q4 2024" \
--mime-type "application/vnd.google-apps.spreadsheet"
# Step 2: Read the data
echo '{
"spreadsheet_id": "SPREADSHEET_ID_FROM_STEP1",
"range": "Q4 Data!A1:F100"
}' | ~/.claude/skills/google-sheets/scripts/sheets_manager.rb read
# Step 3: Process data with Claude (analyze trends, calculate metrics)
# Step 4: Create a new document for the report
echo '{
"title": "Q4 2024 Sales Analysis Report"
}' | ~/.claude/skills/google-docs/scripts/docs_manager.rb create
# Step 5: Write report sections
echo '{
"document_id": "DOC_ID_FROM_STEP4",
"text": "# Q4 2024 Sales Analysis\n\n## Executive Summary\n\n[Claude-generated insights]"
}' | ~/.claude/skills/google-docs/scripts/docs_manager.rb append
# Step 6: Share the report
~/.claude/skills/google-drive/scripts/drive_manager.rb share \
--file-id "DOC_ID_FROM_STEP4" \
--email "[email protected]" \
--role "reader"
```
**Expected Output**: Formatted Google Doc with analysis, shared with stakeholders.
---
## Pattern 2: Workspace Organization
**Use Case**: Create organized project workspace with folders, documents, and spreadsheets.
**Workflow**:
```bash
# Step 1: Create project folder
~/.claude/skills/google-drive/scripts/drive_manager.rb create-folder \
--name "Project Alpha" \
--parent-folder-id "root"
# Step 2: Create subfolders
~/.claude/skills/google-drive/scripts/drive_manager.rb create-folder \
--name "Documents" \
--parent-folder-id "PROJECT_FOLDER_ID_FROM_STEP1"
~/.claude/skills/google-drive/scripts/drive_manager.rb create-folder \
--name "Spreadsheets" \
--parent-folder-id "PROJECT_FOLDER_ID_FROM_STEP1"
# Step 3: Create project documents
echo '{
"title": "Project Alpha - Requirements",
"parent_folder_id": "DOCUMENTS_FOLDER_ID"
}' | ~/.claude/skills/google-docs/scripts/docs_manager.rb create
# Step 4: Create tracking spreadsheet
# (Use google-sheets create operation with parent_folder_id)
# Step 5: Share entire project folder
~/.claude/skills/google-drive/scripts/drive_manager.rb share \
--file-id "PROJECT_FOLDER_ID_FROM_STEP1" \
--email "[email protected]" \
--role "writer"
```
**Expected Output**: Organized folder structure with permissions set for team collaboration.
---
## Pattern 3: Bulk Document Updates
**Use Case**: Search for documents matching criteria and update content across multiple files.
**Workflow**:
```bash
# Step 1: Find all matching documents
~/.claude/skills/google-drive/scripts/drive_manager.rb search \
--query "Status Report" \
--mime-type "application/vnd.google-apps.document"
# Step 2: For each document, perform updates
# (Loop through results from Step 1)
for DOC_ID in $(jq -r '.files[].id' search_results.json); do
# Read current content
CONTENT=$(~/.claude/skills/google-docs/scripts/docs_manager.rb read "$DOC_ID")
# Update with new information
echo "{
\"document_id\": \"$DOC_ID\",
\"find_text\": \"Status: Pending\",
\"replace_text\": \"Status: Completed\"
}" | ~/.claude/skills/google-docs/scripts/docs_manager.rb replace
done
```
**Expected Output**: All matching documents updated with new status.
---
## Pattern 4: Spreadsheet Consolidation
**Use Case**: Combine data from multiple spreadsheets into a master sheet.
**Workflow**:
```bash
# Step 1: Find all department spreadsheets
~/.claude/skills/google-drive/scripts/drive_manager.rb search \
--query "Department Budget 2024"
# Step 2: Create master consolidation sheet
echo '{
"title": "Master Budget 2024 - All Departments"
}' | ~/.claude/skills/google-sheets/scripts/sheets_manager.rb create
# Step 3: Read data from each department sheet
for SHEET_ID in $(jq -r '.files[].id' search_results.json); do
echo "{
\"spreadsheet_id\": \"$SHEET_ID\",
\"range\": \"Budget!A2:E100\"
}" | ~/.claude/skills/google-sheets/scripts/sheets_manager.rb read >> consolidated_data.json
done
# Step 4: Write consolidated data to master sheet
echo '{
"spreadsheet_id": "MASTER_SHEET_ID",
"range": "Consolidated!A2",
"values": [/* combined data from step 3 */]
}' | ~/.claude/skills/google-sheets/scripts/sheets_manager.rb write
```
**Expected Output**: Single master spreadsheet with data from all departments.
---
## Pattern 5: Document Template System
**Use Case**: Create documents from templates with personalized content.
**Workflow**:
```bash
# Step 1: Find template document
~/.claude/skills/google-drive/scripts/drive_manager.rb search \
--query "Invoice Template"
# Step 2: Copy template for new document
~/.claude/skills/google-drive/scripts/drive_manager.rb copy \
--file-id "TEMPLATE_ID" \
--name "Invoice #1234 - Acme Corp"
# Step 3: Read template content
NEW_DOC_ID=$(jq -r '.id' copy_result.json)
~/.claude/skills/google-docs/scripts/docs_manager.rb read "$NEW_DOC_ID"
# Step 4: Replace placeholders with actual data
echo "{
\"document_id\": \"$NEW_DOC_ID\",
\"find_text\": \"{{CLIENT_NAME}}\",
\"replace_text\": \"Acme Corporation\"
}" | ~/.claude/skills/google-docs/scripts/docs_manager.rb replace
echo "{
\"document_id\": \"$NEW_DOC_ID\",
\"find_text\": \"{{INVOICE_NUMBER}}\",
\"replace_text\": \"#1234\"
}" | ~/.claude/skills/google-docs/scripts/docs_manager.rb replace
# Step 5: Move to appropriate folder
~/.claude/skills/google-drive/scripts/drive_manager.rb move \
--file-id "$NEW_DOC_ID" \
--destination-folder-id "INVOICES_FOLDER_ID"
```
**Expected Output**: Personalized document created from template in correct location.
---
## Pattern 6: Data Collection Pipeline
**Use Case**: Aggregate data from multiple sources into a tracking spreadsheet.
**Workflow**:
```bash
# Step 1: Create or find tracking spreadsheet
TRACKING_SHEET_ID="your_tracking_sheet_id"
# Step 2: Search for data source documents
~/.claude/skills/google-drive/scripts/drive_manager.rb search \
--query "Survey Results 2024"
# Step 3: Extract data from each source
for DOC_ID in $(jq -r '.files[].id' search_results.json); do
# Read document
CONTENT=$(~/.claude/skills/google-docs/scripts/docs_manager.rb read "$DOC_ID")
# Parse content with Claude to extract structured data
# (Claude processes CONTENT and formats for spreadsheet)
# Append to tracking sheet
echo "{
\"spreadsheet_id\": \"$TRACKING_SHEET_ID\",
\"range\": \"Data!A:F\",
\"values\": [[/* extracted data */]]
}" | ~/.claude/skills/google-sheets/scripts/sheets_manager.rb append
done
```
**Expected Output**: Tracking spreadsheet populated with data from all source documents.
---
## Common Issues & Troubleshooting
### Issue: File ID vs File Path Confusion
**Problem**: Commands fail because wrong identifier type used.
**Solution**:
- **google-drive**: Uses `--file-id` for existing files
- **google-sheets**: Uses `spreadsheet_id` in JSON
- **google-docs**: Uses `document_id` in JSON or as argument
Always use the ID (long alphanumeric string) from search/list results, not the file path.
### Issue: Permission Denied Errors
**Problem**: Operations fail with "Insufficient Permission" error.
**Solution**:
```bash
# Check current permissions
~/.claude/skills/google-drive/scripts/drive_manager.rb list-permissions \
--file-id "FILE_ID"
# Add yourself as writer if needed
~/.claude/skills/google-drive/scripts/drive_manager.rb share \
--file-id "FILE_ID" \
--email "[email protected]" \
--role "writer"
```
### Issue: Search Returns Too Many Results
**Problem**: Search is too broad and returns unrelated files.
**Solution**: Use more specific query with operators:
```bash
# Combine multiple criteria
~/.claude/skills/google-drive/scripts/drive_manager.rb search \
--query "name contains 'Budget' and mimeType='application/vnd.google-apps.spreadsheet'"
# Search in specific folder
~/.claude/skills/google-drive/scripts/drive_manager.rb search \
--query "'FOLDER_ID' in parents"
```
---
## Tips for Efficient Workflows
1. **Cache File IDs**: Store frequently-used file IDs in variables or files
2. **Use Batch Operations**: google-sheets batch_update for multiple changes
3. **Error Handling**: Always check JSON response for `"success": true` before proceeding
4. **Permission Planning**: Set folder permissions rather than individual files
5. **Template Reuse**: Create template documents for consistent formatting
6. **Search Optimization**: Use mimeType filters to narrow results quickly
---
## Advanced: Skill Composition Patterns
### Listing Spreadsheets (Without Native List Operation)
Use google-drive with mimeType filter:
```bash
~/.claude/skills/google-drive/scripts/drive_manager.rb search \
--query "mimeType='application/vnd.google-apps.spreadsheet'" \
--max-results 50
```
### Listing Documents (Without Native List Operation)
Use google-drive with mimeType filter:
```bash
~/.claude/skills/google-drive/scripts/drive_manager.rb search \
--query "mimeType='application/vnd.google-apps.document'" \
--max-results 50
```
### Finding Recently Modified Files
```bash
~/.claude/skills/google-drive/scripts/drive_manager.rb search \
--query "modifiedTime > '2024-01-01T00:00:00'"
```
---
## Next Steps
- Explore the `references/troubleshooting.md` for detailed error recovery
- See `references/advanced-operations.md` for power user features
- Check skill SKILL.md files for complete operation references
```
### references/troubleshooting.md
```markdown
# Google Skills Troubleshooting Guide
Comprehensive troubleshooting for google-drive, google-sheets, and google-docs skills.
## Exit Codes Reference
All three Google skills use standardized exit codes:
| Code | Meaning | Action Required |
|------|---------|-----------------|
| 0 | Success | None - operation completed successfully |
| 1 | Operation Failed | Check error message for specific issue |
| 2 | Authentication Error | Re-authorize or fix credentials |
| 3 | API Error | Check Google API status, retry later |
| 4 | Invalid Arguments | Review command syntax and parameters |
## Common Authentication Issues
### Error: Missing Credentials File
**Error Message**:
```json
{
"success": false,
"error": "client_secret.json not found",
"error_code": "AUTH_ERROR"
}
```
**Root Cause**: OAuth credentials file not present at expected location.
**Solution Steps**:
1. Verify file exists: `ls ~/.claude/.google/client_secret.json`
2. If missing, download OAuth credentials from Google Cloud Console:
- Go to [Google Cloud Console](https://console.cloud.google.com/)
- Navigate to APIs & Services > Credentials
- Create OAuth 2.0 Client ID (Desktop application)
- Download JSON and save as `~/.claude/.google/client_secret.json`
3. Ensure file has correct permissions: `chmod 600 ~/.claude/.google/client_secret.json`
**Prevention**: Keep backup of `client_secret.json` in secure location.
---
### Error: Invalid Credentials
**Error Message**:
```json
{
"success": false,
"error": "Invalid OAuth credentials",
"error_code": "AUTH_ERROR"
}
```
**Root Cause**: OAuth client configured incorrectly or credentials revoked.
**Solution Steps**:
1. Verify OAuth client is enabled for required APIs:
- Drive API
- Sheets API
- Docs API
- Calendar API (if using calendar skill)
- People API (if using contacts skill)
- Gmail API (if using email skill)
2. Check OAuth consent screen is configured
3. Verify redirect URI is configured: `http://localhost:8080`
4. Download fresh credentials from Google Cloud Console
5. Delete existing token: `rm ~/.claude/.google/token.json`
6. Run any Google skill operation to trigger re-authorization
**Prevention**: Don't manually edit `client_secret.json` file.
---
### Error: Token Expired (Auto-Refresh Failed)
**Error Message**:
```json
{
"success": false,
"error": "Token refresh failed",
"error_code": "AUTH_ERROR"
}
```
**Root Cause**: Refresh token expired or revoked.
**Solution Steps**:
1. Delete expired token: `rm ~/.claude/.google/token.json`
2. Run any Google skill operation to trigger full re-authorization flow
3. Complete OAuth authorization in browser when prompted
4. Verify new token created: `ls -lh ~/.claude/.google/token.json`
**Prevention**:
- Don't manually edit `token.json`
- Complete OAuth flow promptly (don't leave browser window open)
- Check token expiration if using skills infrequently
---
### Error: Insufficient Scope
**Error Message**:
```json
{
"success": false,
"error": "Token missing required scope",
"error_code": "AUTH_ERROR"
}
```
**Root Cause**: Token was created with fewer scopes than currently required.
**Solution Steps**:
1. Delete token to force full re-authorization: `rm ~/.claude/.google/token.json`
2. Run operation again - script will request all required scopes
3. Carefully review scope list in OAuth consent screen
4. Authorize all requested scopes
**Prevention**: When adding new Google skills, re-authorize to include new scopes.
---
## Common Operation Issues
### Error: File Not Found
**Error Message**:
```json
{
"success": false,
"error": "File not found: abc123xyz",
"error_code": "API_ERROR"
}
```
**Root Cause**: File ID doesn't exist or user lacks access permissions.
**Solution Steps**:
1. Verify file ID is correct (long alphanumeric string, not file name)
2. Check file exists with search:
```bash
~/.claude/skills/google-drive/scripts/drive_manager.rb search \
--query "name='Your File Name'"
```
3. Verify you have access to the file:
```bash
~/.claude/skills/google-drive/scripts/drive_manager.rb list-permissions \
--file-id "FILE_ID"
```
4. If file is shared, ensure it's shared with your authenticated account
**Prevention**: Always use file IDs from search/list results, not hardcoded values.
---
### Error: Permission Denied
**Error Message**:
```json
{
"success": false,
"error": "Insufficient permission to access file",
"error_code": "API_ERROR"
}
```
**Root Cause**: User lacks required permission level for requested operation.
**Solution Steps**:
1. Check current permissions:
```bash
~/.claude/skills/google-drive/scripts/drive_manager.rb list-permissions \
--file-id "FILE_ID"
```
2. If you're not the owner, request permission from file owner
3. If you are the owner but using different account, share to authenticated account:
```bash
~/.claude/skills/google-drive/scripts/drive_manager.rb share \
--file-id "FILE_ID" \
--email "[email protected]" \
--role "writer"
```
**Prevention**: Check permissions before performing write/modify operations.
---
### Error: Invalid Range (Sheets Only)
**Error Message**:
```json
{
"success": false,
"error": "Invalid range: InvalidSheet!A1:B10",
"error_code": "API_ERROR"
}
```
**Root Cause**: Sheet name doesn't exist or range syntax incorrect.
**Solution Steps**:
1. Get spreadsheet metadata to see available sheets:
```bash
echo '{"spreadsheet_id":"SPREADSHEET_ID"}' | \
~/.claude/skills/google-sheets/scripts/sheets_manager.rb metadata
```
2. Use exact sheet name (case-sensitive) from metadata
3. Verify A1 notation syntax: `SheetName!A1:B10`
4. Common mistakes:
- Missing sheet name: `A1:B10` (should be `Sheet1!A1:B10`)
- Wrong delimiter: `Sheet1:A1:B10` (should use `!`)
- Invalid range: `A1:ZZ` (should specify both corners)
**Prevention**: Always query metadata first to confirm sheet names.
---
### Error: Index Out of Bounds (Docs Only)
**Error Message**:
```json
{
"success": false,
"error": "Index 5000 exceeds document length",
"error_code": "API_ERROR"
}
```
**Root Cause**: Attempting to insert/delete at position beyond document end.
**Solution Steps**:
1. Read document to get current structure:
```bash
~/.claude/skills/google-docs/scripts/docs_manager.rb structure "DOCUMENT_ID"
```
2. Use `append` operation instead of `insert` for adding to end
3. Calculate correct indices based on content length
4. Note: Document indices are 1-based (first character is index 1)
**Prevention**: Use `append` for end-of-document operations, `structure` to verify indices.
---
## Network and API Issues
### Error: Rate Limit Exceeded
**Error Message**:
```json
{
"success": false,
"error": "Rate limit exceeded, retry after X seconds",
"error_code": "API_ERROR"
}
```
**Root Cause**: Too many API requests in short time period.
**Solution Steps**:
1. Wait time specified in error message
2. Implement exponential backoff for retry logic
3. Use batch operations instead of individual calls:
- google-sheets: Use `batch_update` for multiple formatting operations
- google-drive: Process files in smaller batches
**Prevention**:
- Use batch operations for bulk changes
- Add delays between operations in loops
- Query metadata once and reuse, don't query per operation
---
### Error: Network Timeout
**Error Message**:
```json
{
"success": false,
"error": "Request timeout: Network operation timed out",
"error_code": "API_ERROR"
}
```
**Root Cause**: Network connectivity issue or Google API temporary outage.
**Solution Steps**:
1. Check internet connectivity: `ping google.com`
2. Verify Google API status: [Google Workspace Status Dashboard](https://www.google.com/appsstatus)
3. Retry operation after brief delay
4. For large files, consider splitting into smaller operations
**Prevention**: Implement retry logic with exponential backoff for production workflows.
---
## CLI Interface Issues
### Issue: Command Not Recognized (google-drive)
**Error**: `Unknown command: --list`
**Root Cause**: google-drive uses command-first syntax, not flag-first.
**Solution**:
```bash
# WRONG ❌
drive_manager.rb --list
# CORRECT ✅
drive_manager.rb list
```
**Pattern**: `drive_manager.rb [command] [--flags]`
---
### Issue: JSON Parse Error (google-sheets, google-docs)
**Error**: `Invalid JSON input`
**Root Cause**: Malformed JSON sent to stdin.
**Solution**:
1. Validate JSON syntax with tool: `echo '{ your json }' | jq .`
2. Ensure proper quoting:
```bash
# WRONG ❌ (shell interprets $)
echo '{"key": "$value"}' | sheets_manager.rb read
# CORRECT ✅ (escape or use single quotes)
echo '{"key": "value"}' | sheets_manager.rb read
```
3. Use heredoc for complex JSON:
```bash
sheets_manager.rb read <<'EOF'
{
"spreadsheet_id": "abc123xyz",
"range": "Sheet1!A1:B10"
}
EOF
```
**Prevention**: Test JSON with `jq` before piping to scripts.
---
## Data Format Issues
### Issue: Formula Not Calculating
**Problem**: Formula appears as text `=SUM(A1:A10)` instead of result.
**Root Cause**: Using `RAW` input option instead of `USER_ENTERED`.
**Solution**:
```bash
echo '{
"spreadsheet_id": "ID",
"range": "Sheet1!C1",
"values": [["=SUM(A1:A10)"]],
"input_option": "USER_ENTERED"
}' | sheets_manager.rb write
```
**Prevention**: Always use `"input_option": "USER_ENTERED"` for formulas.
---
### Issue: Date/Time Formatting
**Problem**: Dates appear as numbers (e.g., 44927 instead of 2023-01-15).
**Root Cause**: Google Sheets stores dates as serial numbers.
**Solution**:
1. Apply number format via batch_update:
```bash
echo '{
"spreadsheet_id": "ID",
"requests": [{
"repeatCell": {
"range": {"sheetId": 0, "startRowIndex": 0, "endRowIndex": 10, "startColumnIndex": 0, "endColumnIndex": 1},
"cell": {
"userEnteredFormat": {
"numberFormat": {"type": "DATE", "pattern": "yyyy-mm-dd"}
}
},
"fields": "userEnteredFormat.numberFormat"
}
}]
}' | sheets_manager.rb batch_update
```
**Prevention**: Apply formatting after writing date values.
---
## Performance Issues
### Issue: Slow Large File Operations
**Problem**: Operations on large spreadsheets/documents timeout or take excessive time.
**Solution**:
1. **For Sheets**: Use specific ranges instead of entire sheets:
```bash
# SLOW ❌
echo '{"spreadsheet_id":"ID","range":"Sheet1"}' | sheets_manager.rb read
# FAST ✅
echo '{"spreadsheet_id":"ID","range":"Sheet1!A1:Z1000"}' | sheets_manager.rb read
```
2. **For Docs**: Read structure first, then specific sections:
```bash
# Get structure to understand document
docs_manager.rb structure "DOC_ID"
# Read only needed content range
```
3. **For Drive**: Limit search results:
```bash
drive_manager.rb search --query "name contains 'Report'" --max-results 50
```
**Prevention**: Query only data you need, use pagination for large result sets.
---
## Debugging Tips
### Enable Verbose Output
Add debug logging to troubleshoot issues:
```bash
# For google-drive
DEBUG=1 drive_manager.rb list
# For google-sheets and google-docs
# Add debug flag to JSON input (if supported)
```
### Inspect API Responses
Save JSON output for inspection:
```bash
drive_manager.rb list > output.json
cat output.json | jq .
```
### Test Authentication Separately
Verify OAuth flow works:
```bash
# Delete token
rm ~/.claude/.google/token.json
# Run simple operation
drive_manager.rb list
# Should prompt for authorization
# Complete OAuth flow in browser
# Verify token created
ls -lh ~/.claude/.google/token.json
```
### Validate File IDs
Test file ID is accessible:
```bash
drive_manager.rb get --file-id "FILE_ID"
```
---
## Getting Help
### Check Logs
Ruby gem errors may provide additional context:
```bash
# Check Ruby gem installation
gem list | grep google-apis
# Verify required gems installed
gem install google-apis-drive_v3 google-apis-sheets_v4 google-apis-docs_v1 googleauth
```
### Verify API Enablement
Ensure APIs are enabled in Google Cloud Console:
1. Visit [Google Cloud Console](https://console.cloud.google.com/)
2. Navigate to APIs & Services > Library
3. Search and enable:
- Google Drive API
- Google Sheets API
- Google Docs API
- (Plus Calendar, People, Gmail if using those skills)
### Test with Google API Explorer
Validate operations work outside skill scripts:
- [Drive API Explorer](https://developers.google.com/drive/api/v3/reference)
- [Sheets API Explorer](https://developers.google.com/sheets/api/reference/rest)
- [Docs API Explorer](https://developers.google.com/docs/api/reference/rest)
---
## Additional Resources
- See `integration-patterns.md` for workflow examples
- Check main SKILL.md files for operation syntax
- Review Ruby script source for implementation details
- Consult Google Workspace API documentation for advanced usage
```
### references/cli-patterns.md
```markdown
# CLI Interface Patterns - Design Rationale
Understanding the different command-line interface patterns used across google-drive, google-sheets, and google-docs skills.
## Design Philosophy
Each Google skill uses the CLI pattern that best matches its typical use case complexity:
| Skill | Primary Pattern | Rationale |
|-------|----------------|-----------|
| **google-drive** | Command + Flags | Simple file operations benefit from shell-friendly syntax |
| **google-sheets** | JSON via stdin | Complex multi-dimensional data requires structured input |
| **google-docs** | Mixed (args + JSON) | Balance between simple reads and complex formatting |
---
## google-drive: Command + Flags Pattern
### Design Choice
```bash
drive_manager.rb [command] [--flags]
```
**Why This Pattern?**
1. **Shell Integration**: Drive operations commonly used in shell pipelines
2. **Simplicity**: Most Drive operations have few parameters (file ID, query, path)
3. **Composability**: Easy to combine with other Unix tools
4. **Familiarity**: Matches standard CLI tools (ls, grep, find)
### Examples
```bash
# List files - no complex parameters needed
drive_manager.rb list
# Search with simple query
drive_manager.rb search --query "name contains 'Report'"
# Download file - straightforward parameters
drive_manager.rb download --file-id "abc123xyz" --save-path "./download"
# Share file - few clear parameters
drive_manager.rb share --file-id "abc123xyz" --email "[email protected]" --role "reader"
```
### When Flags Work Well
- ✅ **2-5 parameters**: Easy to specify on command line
- ✅ **Flat structure**: No nested objects or arrays
- ✅ **Shell scripting**: Frequently used in loops and pipelines
- ✅ **String values**: Most parameters are simple strings or numbers
### Advanced Usage
For complex operations, google-drive can accept JSON stdin (future enhancement):
```bash
# Current: Simple flag-based
drive_manager.rb share --file-id "ID" --email "[email protected]" --role "writer"
# Future: JSON for batch operations
echo '{
"file_id": "ID",
"permissions": [
{"email": "[email protected]", "role": "writer"},
{"email": "[email protected]", "role": "reader"}
]
}' | drive_manager.rb share --json
```
---
## google-sheets: JSON via stdin Pattern
### Design Choice
```bash
echo '{...}' | sheets_manager.rb [command]
```
**Why This Pattern?**
1. **Data Complexity**: Spreadsheet operations involve multi-dimensional arrays
2. **Structured Input**: Cell ranges, formatting, and formulas need clear structure
3. **Batch Operations**: Multiple ranges and operations in single call
4. **Type Preservation**: JSON maintains data types (numbers vs strings)
### Examples
```bash
# Write data - 2D array of values
echo '{
"spreadsheet_id": "abc123xyz",
"range": "Sheet1!A1:C3",
"values": [
["Name", "Age", "City"],
["Alice", 30, "NYC"],
["Bob", 25, "LA"]
]
}' | sheets_manager.rb write
# Batch update - complex formatting across multiple ranges
echo '{
"spreadsheet_id": "abc123xyz",
"requests": [
{
"updateCells": {
"range": {...},
"userEnteredFormat": {...}
}
},
{
"updateCells": {
"range": {...},
"userEnteredFormat": {...}
}
}
]
}' | sheets_manager.rb batch_update
```
### When JSON Works Well
- ✅ **Nested data**: Arrays within objects, complex hierarchies
- ✅ **Multiple values**: Cell ranges, formatting options, batch requests
- ✅ **Type safety**: Preserve numbers, booleans, null values
- ✅ **API alignment**: Google Sheets API uses JSON, direct mapping
### Simple Operation Shortcuts
For common simple operations, flags can provide shortcuts (future enhancement):
```bash
# Current: JSON for everything
echo '{"spreadsheet_id":"ID","range":"Sheet1!A1"}' | sheets_manager.rb read
# Future: Flag shortcuts for simple reads
sheets_manager.rb read --id "ID" --range "Sheet1!A1"
# But complex operations still use JSON
echo '{...}' | sheets_manager.rb batch_update # No flag equivalent
```
---
## google-docs: Mixed Pattern
### Design Choice
```bash
# Simple operations: Direct arguments
docs_manager.rb read <document_id>
# Complex operations: JSON via stdin
echo '{...}' | docs_manager.rb insert
```
**Why This Pattern?**
1. **Flexibility**: Optimize for both simple and complex operations
2. **Readability**: Document ID as positional argument is clear
3. **Structured edits**: Text formatting requires nested JSON
4. **Progressive complexity**: Start simple, scale to complex
### Examples
**Simple Operations** (direct arguments):
```bash
# Read document - just needs ID
docs_manager.rb read "abc123xyz"
# Get structure - one parameter
docs_manager.rb structure "abc123xyz"
```
**Complex Operations** (JSON stdin):
```bash
# Insert formatted text at specific position
echo '{
"document_id": "abc123xyz",
"index": 1,
"text": "New paragraph",
"format": {
"bold": true,
"fontSize": 14
}
}' | docs_manager.rb insert
# Replace with formatting
echo '{
"document_id": "abc123xyz",
"find_text": "old text",
"replace_text": "new text",
"format": {"bold": true}
}' | docs_manager.rb replace
```
### When Mixed Pattern Works Well
- ✅ **Progressive disclosure**: Simple tasks stay simple
- ✅ **Flexibility**: Complex operations available when needed
- ✅ **Learning curve**: Easy entry point, power user features available
- ✅ **Common case optimization**: Most operations are reads (simple)
---
## Pattern Comparison
### Use Case: Read Operation
**google-drive** (simple flag):
```bash
drive_manager.rb get --file-id "abc123xyz"
```
**Why**: File metadata is flat structure, few parameters.
**google-sheets** (JSON):
```bash
echo '{"spreadsheet_id":"ID","range":"Sheet1!A1:C10"}' | sheets_manager.rb read
```
**Why**: Range specification benefits from structured format, may need multiple ranges.
**google-docs** (direct arg):
```bash
docs_manager.rb read "abc123xyz"
```
**Why**: Only needs document ID, simplest possible interface.
### Use Case: Update Operation
**google-drive** (flags):
```bash
drive_manager.rb update --file-id "ID" --name "New Name" --description "Updated"
```
**Why**: Few flat parameters, clear command-line representation.
**google-sheets** (JSON):
```bash
echo '{
"spreadsheet_id": "ID",
"range": "Sheet1!A1:C3",
"values": [["Data", "More", "Values"], ...]
}' | sheets_manager.rb write
```
**Why**: 2D array of values impossible to represent clearly with flags.
**google-docs** (JSON):
```bash
echo '{
"document_id": "ID",
"index": 10,
"text": "Inserted text",
"format": {"bold": true, "italic": true}
}' | docs_manager.rb insert
```
**Why**: Multiple nested parameters (position, text, formatting).
---
## Best Practices by Skill
### google-drive Best Practices
✅ **DO**: Use flags for simple operations
```bash
drive_manager.rb search --query "type:document"
drive_manager.rb download --file-id "ID" --save-path "./file"
```
❌ **DON'T**: Try to force JSON when flags are sufficient
```bash
# Unnecessary complexity
echo '{"file_id":"ID"}' | drive_manager.rb get # No benefit over flags
```
💡 **TIP**: Use shell variables for repeated file IDs
```bash
FILE_ID="abc123xyz"
drive_manager.rb get --file-id "$FILE_ID"
drive_manager.rb download --file-id "$FILE_ID" --save-path "./download"
```
### google-sheets Best Practices
✅ **DO**: Use JSON for all operations
```bash
# Properly structured
echo '{
"spreadsheet_id": "ID",
"range": "Sheet1!A1:B10",
"values": [[1, 2], [3, 4]]
}' | sheets_manager.rb write
```
❌ **DON'T**: Try to inline complex JSON on command line
```bash
# Hard to read and maintain
echo '{"spreadsheet_id":"ID","range":"Sheet1!A1:B10","values":[[1,2],[3,4]]}' | sheets_manager.rb write
```
💡 **TIP**: Use heredoc for complex operations
```bash
sheets_manager.rb write <<'EOF'
{
"spreadsheet_id": "ID",
"range": "Sheet1!A1:C3",
"values": [
["Header1", "Header2", "Header3"],
["Value1", "Value2", "Value3"]
]
}
EOF
```
### google-docs Best Practices
✅ **DO**: Use direct arguments for simple reads
```bash
docs_manager.rb read "document_id"
docs_manager.rb structure "document_id"
```
✅ **DO**: Use JSON for complex operations
```bash
echo '{
"document_id": "ID",
"text": "New content",
"format": {"bold": true}
}' | docs_manager.rb append
```
💡 **TIP**: Combine simple and complex operations
```bash
# Get structure first (simple)
STRUCTURE=$(docs_manager.rb structure "ID")
# Then perform complex formatted insertion (JSON)
echo "{
\"document_id\": \"ID\",
\"index\": 1,
\"text\": \"Formatted text\"
}" | docs_manager.rb insert
```
---
## Why Not Standardize Everything?
### Option 1: All Flags (google-drive style)
**Problems**:
- ❌ Can't represent 2D arrays for spreadsheets
- ❌ Complex nested structures impossible (formatting options)
- ❌ Batch operations require multiple command invocations
- ❌ Type ambiguity (is "123" a string or number?)
### Option 2: All JSON (google-sheets style)
**Problems**:
- ❌ Overkill for simple operations (listing files)
- ❌ Poor shell integration and composability
- ❌ More verbose for common simple cases
- ❌ Harder to use interactively
### Option 3: Current Approach (Best of Both)
**Benefits**:
- ✅ Each skill optimized for its primary use case
- ✅ Simple operations stay simple
- ✅ Complex operations have necessary power
- ✅ Consistent within each skill
- ✅ Can evolve independently based on user needs
---
## Future Enhancements
### google-drive: Add Optional JSON Mode
```bash
# Current flag-based for simple sharing
drive_manager.rb share --file-id "ID" --email "[email protected]"
# Future JSON for batch permissions
echo '{
"file_id": "ID",
"permissions": [
{"email": "[email protected]", "role": "writer"},
{"email": "[email protected]", "role": "reader"},
{"type": "domain", "domain": "company.com", "role": "reader"}
]
}' | drive_manager.rb share --json
```
### google-sheets: Add Simple Flag Shortcuts
```bash
# Current JSON for all operations
echo '{"spreadsheet_id":"ID","range":"A1"}' | sheets_manager.rb read
# Future flags for simple reads
sheets_manager.rb read --id "ID" --range "Sheet1!A1"
# JSON still available for complex operations
echo '{...complex batch operation...}' | sheets_manager.rb batch_update
```
### google-docs: Maintain Mixed Approach
- Keep simple operations with direct arguments
- Keep complex operations with JSON
- Add more format shortcuts for common cases
---
## Key Takeaways
1. **Different problems need different interfaces**: File operations vs spreadsheet data vs document formatting have distinct complexity profiles.
2. **Optimize for the common case**: google-drive optimizes for simple shell operations, google-sheets for structured data manipulation.
3. **Progressive disclosure**: google-docs starts simple (read with ID) and scales to complex (formatted insertions with JSON).
4. **Consistency within skill matters more**: Each skill is internally consistent, reducing cognitive load when using one skill extensively.
5. **Evolution over revolution**: Skills can add complementary patterns (flags to google-sheets, JSON to google-drive) without breaking existing usage.
---
## Related Documentation
- See `integration-patterns.md` for workflow examples using all three patterns
- See `troubleshooting.md` for common CLI syntax errors and solutions
- Check main SKILL.md files for complete operation syntax references
```
### references/sheets_operations.md
```markdown
# Google Sheets Operations Reference
Complete reference for all spreadsheet operations available in the google-sheets skill.
## Table of Contents
1. [Read Operations](#read-operations)
2. [Write Operations](#write-operations)
3. [Append Operations](#append-operations)
4. [Clear Operations](#clear-operations)
5. [Metadata Operations](#metadata-operations)
6. [Sheet Management](#sheet-management)
7. [Format Operations](#format-operations)
8. [Batch Operations](#batch-operations)
9. [Common Patterns](#common-patterns)
10. [Error Codes](#error-codes)
---
## Read Operations
### Read Cell Values
**Command**: `read`
**Required Parameters**:
- `spreadsheet_id`: The unique ID of the spreadsheet
- `range`: A1 notation range to read
**Optional Parameters**: None
**Examples**:
```bash
# Read single cell
echo '{"spreadsheet_id":"abc123","range":"Sheet1!A1"}' | sheets_manager.rb read
# Read range
echo '{"spreadsheet_id":"abc123","range":"Sheet1!A1:D10"}' | sheets_manager.rb read
# Read entire column
echo '{"spreadsheet_id":"abc123","range":"Sheet1!A:A"}' | sheets_manager.rb read
# Read entire row
echo '{"spreadsheet_id":"abc123","range":"Sheet1!1:1"}' | sheets_manager.rb read
# Read from named sheet with spaces
echo '{"spreadsheet_id":"abc123","range":"'\''Budget 2024'\''!A1:Z100"}' | sheets_manager.rb read
```
**Success Response**:
```json
{
"status": "success",
"operation": "read",
"spreadsheet_id": "abc123",
"range": "Sheet1!A1:D10",
"values": [
["Header1", "Header2", "Header3", "Header4"],
["Value1", "Value2", "Value3", "Value4"]
],
"row_count": 2
}
```
**Notes**:
- Empty cells are returned as empty strings within rows
- Rows with all empty cells may be omitted
- Formulas are returned as their calculated values, not the formula text
- Returns raw values without formatting
---
## Write Operations
### Write Cell Values
**Command**: `write`
**Required Parameters**:
- `spreadsheet_id`: The unique ID of the spreadsheet
- `range`: A1 notation range to write to
- `values`: 2D array of values to write
**Optional Parameters**:
- `input_option`: How to interpret input data
- `USER_ENTERED` (default): Parse as if typed by user (formulas, dates, numbers)
- `RAW`: Store exactly as provided (everything as strings)
**Examples**:
```bash
# Write single cell
echo '{
"spreadsheet_id": "abc123",
"range": "Sheet1!A1",
"values": [["Hello World"]]
}' | sheets_manager.rb write
# Write range
echo '{
"spreadsheet_id": "abc123",
"range": "Sheet1!A1:B2",
"values": [
["Name", "Age"],
["Alice", 30]
]
}' | sheets_manager.rb write
# Write formula
echo '{
"spreadsheet_id": "abc123",
"range": "Sheet1!C1",
"values": [["=SUM(A1:A10)"]],
"input_option": "USER_ENTERED"
}' | sheets_manager.rb write
# Write date (parsed automatically with USER_ENTERED)
echo '{
"spreadsheet_id": "abc123",
"range": "Sheet1!D1",
"values": [["2024-11-10"]],
"input_option": "USER_ENTERED"
}' | sheets_manager.rb write
# Write numbers
echo '{
"spreadsheet_id": "abc123",
"range": "Sheet1!E1:E3",
"values": [[100], [200], [300]]
}' | sheets_manager.rb write
```
**Success Response**:
```json
{
"status": "success",
"operation": "write",
"spreadsheet_id": "abc123",
"range": "Sheet1!A1:B2",
"updated_cells": 4,
"updated_rows": 2,
"updated_columns": 2
}
```
**Notes**:
- Overwrites existing data in the range
- Use USER_ENTERED for formulas, dates, and automatic type parsing
- Use RAW to store literal strings
- Values array must be 2D (array of arrays)
- Mismatched dimensions will write partial data
---
## Append Operations
### Append Rows
**Command**: `append`
**Required Parameters**:
- `spreadsheet_id`: The unique ID of the spreadsheet
- `range`: Starting range (e.g., "Sheet1!A1")
- `values`: 2D array of rows to append
**Optional Parameters**:
- `input_option`: How to interpret input data (same as write)
**Examples**:
```bash
# Append single row
echo '{
"spreadsheet_id": "abc123",
"range": "Sheet1!A1",
"values": [["New", "Row", "Data"]]
}' | sheets_manager.rb append
# Append multiple rows
echo '{
"spreadsheet_id": "abc123",
"range": "Sheet1!A1",
"values": [
["Row1Col1", "Row1Col2", "Row1Col3"],
["Row2Col1", "Row2Col2", "Row2Col3"],
["Row3Col1", "Row3Col2", "Row3Col3"]
]
}' | sheets_manager.rb append
# Append with formulas
echo '{
"spreadsheet_id": "abc123",
"range": "Sheet1!A1",
"values": [["Value", "100", "=B1*2"]],
"input_option": "USER_ENTERED"
}' | sheets_manager.rb append
```
**Success Response**:
```json
{
"status": "success",
"operation": "append",
"spreadsheet_id": "abc123",
"range": "Sheet1!A10:C12",
"updated_cells": 9,
"updated_rows": 3
}
```
**How Append Works**:
- Finds the last row with data in the specified range
- Appends new rows immediately after the last data row
- Does not overwrite existing data
- Table range in response shows where data was appended
**Use Cases**:
- Logging data over time
- Adding entries to a tracker
- Building datasets incrementally
- Recording events or transactions
---
## Clear Operations
### Clear Cell Values
**Command**: `clear`
**Required Parameters**:
- `spreadsheet_id`: The unique ID of the spreadsheet
- `range`: A1 notation range to clear
**Optional Parameters**: None
**Examples**:
```bash
# Clear specific range
echo '{
"spreadsheet_id": "abc123",
"range": "Sheet1!A1:D10"
}' | sheets_manager.rb clear
# Clear entire sheet
echo '{
"spreadsheet_id": "abc123",
"range": "Sheet1"
}' | sheets_manager.rb clear
# Clear entire column
echo '{
"spreadsheet_id": "abc123",
"range": "Sheet1!C:C"
}' | sheets_manager.rb clear
# Clear entire row
echo '{
"spreadsheet_id": "abc123",
"range": "Sheet1!5:5"
}' | sheets_manager.rb clear
```
**Success Response**:
```json
{
"status": "success",
"operation": "clear",
"spreadsheet_id": "abc123",
"range": "Sheet1!A1:D10"
}
```
**Important Notes**:
- Clears cell **values** only
- Does NOT remove cell formatting (colors, fonts, etc.)
- Does NOT remove formulas (they remain but show no value)
- Does NOT delete rows or columns
- To remove formatting, use format operations with default styles
---
## Metadata Operations
### Get Spreadsheet Metadata
**Command**: `metadata`
**Required Parameters**:
- `spreadsheet_id`: The unique ID of the spreadsheet
**Optional Parameters**: None
**Example**:
```bash
echo '{
"spreadsheet_id": "abc123"
}' | sheets_manager.rb metadata
```
**Success Response**:
```json
{
"status": "success",
"operation": "metadata",
"spreadsheet_id": "abc123",
"title": "Budget 2024",
"locale": "en_US",
"timezone": "America/Chicago",
"sheets": [
{
"sheet_id": 0,
"title": "Sheet1",
"index": 0,
"row_count": 1000,
"column_count": 26
},
{
"sheet_id": 123456,
"title": "Q4 Summary",
"index": 1,
"row_count": 500,
"column_count": 15
}
]
}
```
**Use Cases**:
- Discover available sheets in a spreadsheet
- Get sheet_id for format operations
- Check spreadsheet structure before operations
- Verify spreadsheet properties (timezone, locale)
**Notes**:
- `sheet_id` is required for format operations (not the same as index)
- `index` indicates the order of sheets (0-based)
- `row_count` and `column_count` show sheet dimensions
---
## Sheet Management
### Create New Sheet
**Command**: `create_sheet`
**Required Parameters**:
- `spreadsheet_id`: The unique ID of the spreadsheet
- `title`: Name for the new sheet
**Optional Parameters**:
- `row_count`: Number of rows (default: 1000)
- `column_count`: Number of columns (default: 26)
**Examples**:
```bash
# Create sheet with default size
echo '{
"spreadsheet_id": "abc123",
"title": "Q4 Data"
}' | sheets_manager.rb create_sheet
# Create large sheet
echo '{
"spreadsheet_id": "abc123",
"title": "Large Dataset",
"row_count": 5000,
"column_count": 50
}' | sheets_manager.rb create_sheet
# Create small sheet
echo '{
"spreadsheet_id": "abc123",
"title": "Lookup Table",
"row_count": 100,
"column_count": 10
}' | sheets_manager.rb create_sheet
```
**Success Response**:
```json
{
"status": "success",
"operation": "create_sheet",
"spreadsheet_id": "abc123",
"sheet_id": 987654,
"title": "Q4 Data",
"row_count": 1000,
"column_count": 26
}
```
**Notes**:
- New sheet is created at the end of existing sheets
- Sheet title must be unique within the spreadsheet
- `sheet_id` in response is needed for format operations
- Default size (1000x26) is suitable for most use cases
---
## Format Operations
### Update Cell Formatting
**Command**: `format`
**Required Parameters**:
- `spreadsheet_id`: The unique ID of the spreadsheet
- `sheet_id`: The numeric sheet ID (from metadata operation)
- `start_row`: Starting row index (0-based)
- `end_row`: Ending row index (exclusive, 0-based)
- `start_col`: Starting column index (0-based)
- `end_col`: Ending column index (exclusive, 0-based)
- `format`: Format object with styling options
**Format Options**:
- `bold`: Boolean (true/false)
- `italic`: Boolean (true/false)
- `fontSize`: Number (e.g., 10, 12, 14)
- `backgroundColor`: Object with `red`, `green`, `blue`, `alpha` (0-1 scale)
**Examples**:
```bash
# Format header row (bold)
echo '{
"spreadsheet_id": "abc123",
"sheet_id": 0,
"start_row": 0,
"end_row": 1,
"start_col": 0,
"end_col": 10,
"format": {
"bold": true,
"fontSize": 12
}
}' | sheets_manager.rb format
# Highlight cells with background color
echo '{
"spreadsheet_id": "abc123",
"sheet_id": 0,
"start_row": 5,
"end_row": 6,
"start_col": 2,
"end_col": 3,
"format": {
"backgroundColor": {
"red": 1.0,
"green": 0.9,
"blue": 0.0,
"alpha": 1.0
}
}
}' | sheets_manager.rb format
# Format with multiple styles
echo '{
"spreadsheet_id": "abc123",
"sheet_id": 0,
"start_row": 0,
"end_row": 1,
"start_col": 0,
"end_col": 5,
"format": {
"bold": true,
"italic": false,
"fontSize": 14,
"backgroundColor": {
"red": 0.85,
"green": 0.85,
"blue": 0.85,
"alpha": 1.0
}
}
}' | sheets_manager.rb format
```
**Success Response**:
```json
{
"status": "success",
"operation": "format",
"spreadsheet_id": "abc123",
"sheet_id": 0,
"formatted_range": "R0C0:R1C10"
}
```
**Important Notes**:
- Indices are 0-based (first row = 0, first column = 0)
- End indices are exclusive (to format row 1: start_row=0, end_row=1)
- Get sheet_id from metadata operation first
- RGB colors use 0-1 scale (not 0-255)
- Alpha channel: 1.0 = fully opaque, 0.0 = fully transparent
**Common Color Values**:
- White: `{"red": 1, "green": 1, "blue": 1, "alpha": 1}`
- Black: `{"red": 0, "green": 0, "blue": 0, "alpha": 1}`
- Light Gray: `{"red": 0.9, "green": 0.9, "blue": 0.9, "alpha": 1}`
- Yellow: `{"red": 1, "green": 1, "blue": 0, "alpha": 1}`
- Light Blue: `{"red": 0.8, "green": 0.9, "blue": 1, "alpha": 1}`
---
## Batch Operations
### Batch Update Multiple Ranges
**Command**: `batch_update`
**Required Parameters**:
- `spreadsheet_id`: The unique ID of the spreadsheet
- `updates`: Array of update objects, each containing:
- `range`: A1 notation range
- `values`: 2D array of values
**Examples**:
```bash
# Update multiple ranges at once
echo '{
"spreadsheet_id": "abc123",
"updates": [
{
"range": "Sheet1!A1:A3",
"values": [["Name"], ["Alice"], ["Bob"]]
},
{
"range": "Sheet1!B1:B3",
"values": [["Age"], [30], [25]]
},
{
"range": "Sheet1!C1:C3",
"values": [["City"], ["Chicago"], ["New York"]]
}
]
}' | sheets_manager.rb batch_update
# Update with formulas
echo '{
"spreadsheet_id": "abc123",
"updates": [
{
"range": "Sheet1!A1:A5",
"values": [[100], [200], [300], [400], [500]]
},
{
"range": "Sheet1!B1",
"values": [["=SUM(A1:A5)"]]
},
{
"range": "Sheet1!B2",
"values": [["=AVERAGE(A1:A5)"]]
}
]
}' | sheets_manager.rb batch_update
```
**Success Response**:
```json
{
"status": "success",
"operation": "batch_update",
"spreadsheet_id": "abc123",
"total_updated_cells": 15,
"total_updated_rows": 5,
"responses": 3
}
```
**Benefits**:
- Single API call for multiple updates
- More efficient than individual write operations
- Atomic operation (all updates succeed or all fail)
- Perfect for populating templates or importing data
**Use Cases**:
- Populating data entry templates
- Importing CSV or JSON data
- Setting up calculated fields with formulas
- Updating multiple sheets simultaneously
---
## Common Patterns
### Data Import Pattern
```bash
# 1. Clear existing data
echo '{"spreadsheet_id":"abc123","range":"Sheet1"}' | sheets_manager.rb clear
# 2. Write headers with formatting
echo '{
"spreadsheet_id": "abc123",
"sheet_id": 0,
"start_row": 0,
"end_row": 1,
"start_col": 0,
"end_col": 5,
"format": {"bold": true, "backgroundColor": {"red": 0.9, "green": 0.9, "blue": 0.9, "alpha": 1}}
}' | sheets_manager.rb format
# 3. Batch update data
echo '{
"spreadsheet_id": "abc123",
"updates": [
{"range": "Sheet1!A1:E1", "values": [["Name", "Age", "City", "Score", "Rank"]]},
{"range": "Sheet1!A2:E5", "values": [...data rows...]}
]
}' | sheets_manager.rb batch_update
```
### Logging Pattern
```bash
# Append new log entry with timestamp
echo '{
"spreadsheet_id": "abc123",
"range": "Logs!A1",
"values": [["2024-11-10 10:30:00", "INFO", "Operation completed successfully"]],
"input_option": "USER_ENTERED"
}' | sheets_manager.rb append
```
### Report Generation Pattern
```bash
# 1. Create new sheet for report
echo '{
"spreadsheet_id": "abc123",
"title": "Monthly Report - Nov 2024"
}' | sheets_manager.rb create_sheet
# 2. Populate report data
echo '{
"spreadsheet_id": "abc123",
"updates": [
{"range": "Monthly Report - Nov 2024!A1", "values": [["Monthly Sales Report"]]},
{"range": "Monthly Report - Nov 2024!A3:C3", "values": [["Product", "Sales", "Profit"]]},
{"range": "Monthly Report - Nov 2024!A4:C10", "values": [...report data...]}
]
}' | sheets_manager.rb batch_update
```
---
## Error Codes
### Common Errors
**AUTH_REQUIRED**:
- OAuth authorization needed
- Follow auth_url in response
- Run auth command with code
**API_ERROR**:
- Google Sheets API returned an error
- Check error message and details
- Common causes:
- Invalid spreadsheet_id
- Invalid range notation
- Permission denied
- Rate limit exceeded
**MISSING_REQUIRED_FIELDS**:
- Required parameters missing from request
- Review command documentation
- Check JSON structure
**INVALID_COMMAND**:
- Unknown command specified
- Use one of: read, write, append, clear, metadata, create_sheet, format, batch_update
**Operation Specific Errors**:
- `READ_FAILED`: Error reading cell values
- `WRITE_FAILED`: Error writing cell values
- `APPEND_FAILED`: Error appending rows
- `CLEAR_FAILED`: Error clearing values
- `METADATA_FAILED`: Error retrieving metadata
- `CREATE_SHEET_FAILED`: Error creating sheet
- `FORMAT_FAILED`: Error updating format
- `BATCH_UPDATE_FAILED`: Error in batch update
### Troubleshooting
**"Requested entity was not found"**:
- Spreadsheet ID is incorrect
- Sheet name in range doesn't exist
- Verify ID from Google Sheets URL
**"Unable to parse range"**:
- Invalid A1 notation
- Check sheet name spelling
- Use single quotes for sheet names with spaces
**"The caller does not have permission"**:
- Spreadsheet not shared with authorized account
- Share spreadsheet with OAuth email
- Check sharing settings in Google Sheets
**"Quota exceeded"**:
- Too many API calls in short time
- Wait and retry
- Use batch operations to reduce API calls
---
**Last Updated**: November 10, 2025
**Version**: 1.0.0
```
### references/cell_formats.md
```markdown
# Cell Formatting Reference
Complete guide to cell formatting options in Google Sheets skill.
## Table of Contents
1. [Formatting Overview](#formatting-overview)
2. [Text Formatting](#text-formatting)
3. [Background Colors](#background-colors)
4. [Color Reference](#color-reference)
5. [Format Combinations](#format-combinations)
6. [Best Practices](#best-practices)
7. [Common Formatting Patterns](#common-formatting-patterns)
---
## Formatting Overview
### Format Command Structure
```bash
echo '{
"spreadsheet_id": "abc123",
"sheet_id": 0,
"start_row": 0,
"end_row": 1,
"start_col": 0,
"end_col": 5,
"format": {
"bold": true,
"italic": false,
"fontSize": 12,
"backgroundColor": {
"red": 0.9,
"green": 0.9,
"blue": 0.9,
"alpha": 1.0
}
}
}' | sheets_manager.rb format
```
### Key Concepts
**Row/Column Indices**:
- 0-based indexing
- End indices are exclusive
- Example: To format row 1 (the first row):
- `start_row: 0, end_row: 1`
- Example: To format columns A-E (first 5 columns):
- `start_col: 0, end_col: 5`
**Sheet ID vs Sheet Index**:
- `sheet_id`: Unique numeric identifier (use this for formatting)
- `sheet_index`: Position in sheet list (0, 1, 2, ...)
- Get `sheet_id` from metadata operation
**Format Application**:
- Applied to entire specified range
- Overwrites existing formatting in that range
- Does not affect cell values
---
## Text Formatting
### Bold Text
**Property**: `bold`
**Type**: Boolean
**Values**: `true` or `false`
```bash
# Make text bold
echo '{
"spreadsheet_id": "abc123",
"sheet_id": 0,
"start_row": 0,
"end_row": 1,
"start_col": 0,
"end_col": 10,
"format": {
"bold": true
}
}' | sheets_manager.rb format
# Remove bold
echo '{
"spreadsheet_id": "abc123",
"sheet_id": 0,
"start_row": 0,
"end_row": 1,
"start_col": 0,
"end_col": 10,
"format": {
"bold": false
}
}' | sheets_manager.rb format
```
**Use Cases**:
- Header rows
- Section titles
- Emphasis on key data
- Summary rows
### Italic Text
**Property**: `italic`
**Type**: Boolean
**Values**: `true` or `false`
```bash
# Make text italic
echo '{
"spreadsheet_id": "abc123",
"sheet_id": 0,
"start_row": 5,
"end_row": 6,
"start_col": 0,
"end_col": 5,
"format": {
"italic": true
}
}' | sheets_manager.rb format
```
**Use Cases**:
- Notes or comments
- Dates or timestamps
- Secondary information
- Placeholder text
### Font Size
**Property**: `fontSize`
**Type**: Number
**Common Values**: 8, 9, 10, 11, 12, 14, 16, 18, 24
```bash
# Large header text
echo '{
"spreadsheet_id": "abc123",
"sheet_id": 0,
"start_row": 0,
"end_row": 1,
"start_col": 0,
"end_col": 10,
"format": {
"fontSize": 14,
"bold": true
}
}' | sheets_manager.rb format
# Small footnote text
echo '{
"spreadsheet_id": "abc123",
"sheet_id": 0,
"start_row": 20,
"end_row": 21,
"start_col": 0,
"end_col": 5,
"format": {
"fontSize": 9,
"italic": true
}
}' | sheets_manager.rb format
```
**Recommended Sizes**:
- **8-9**: Fine print, footnotes
- **10-11**: Regular body text
- **12**: Standard size, good readability
- **14**: Section headers, important data
- **16-18**: Main headers
- **24+**: Title text
---
## Background Colors
### Color Object Structure
```json
{
"backgroundColor": {
"red": 0.9,
"green": 0.9,
"blue": 0.9,
"alpha": 1.0
}
}
```
**Properties**:
- `red`: Float 0.0 - 1.0 (0% to 100%)
- `green`: Float 0.0 - 1.0 (0% to 100%)
- `blue`: Float 0.0 - 1.0 (0% to 100%)
- `alpha`: Float 0.0 - 1.0 (transparency: 0=transparent, 1=opaque)
### RGB Scale Conversion
**From 0-255 to 0-1 scale**:
```
0-1 value = (0-255 value) / 255
```
**Examples**:
- RGB(255, 255, 255) → (1.0, 1.0, 1.0) - White
- RGB(128, 128, 128) → (0.5, 0.5, 0.5) - Gray
- RGB(255, 0, 0) → (1.0, 0.0, 0.0) - Red
- RGB(230, 230, 230) → (0.9, 0.9, 0.9) - Light Gray
### Basic Background Colors
```bash
# Light gray background (headers)
echo '{
"spreadsheet_id": "abc123",
"sheet_id": 0,
"start_row": 0,
"end_row": 1,
"start_col": 0,
"end_col": 10,
"format": {
"backgroundColor": {
"red": 0.9,
"green": 0.9,
"blue": 0.9,
"alpha": 1.0
}
}
}' | sheets_manager.rb format
# Yellow highlight
echo '{
"spreadsheet_id": "abc123",
"sheet_id": 0,
"start_row": 5,
"end_row": 6,
"start_col": 2,
"end_col": 3,
"format": {
"backgroundColor": {
"red": 1.0,
"green": 1.0,
"blue": 0.0,
"alpha": 1.0
}
}
}' | sheets_manager.rb format
# Light blue background
echo '{
"spreadsheet_id": "abc123",
"sheet_id": 0,
"start_row": 10,
"end_row": 11,
"start_col": 0,
"end_col": 5,
"format": {
"backgroundColor": {
"red": 0.8,
"green": 0.9,
"blue": 1.0,
"alpha": 1.0
}
}
}' | sheets_manager.rb format
```
---
## Color Reference
### Neutral Colors
| Color | Red | Green | Blue | Use Case |
|-------|-----|-------|------|----------|
| White | 1.0 | 1.0 | 1.0 | Default, clean |
| Light Gray | 0.9 | 0.9 | 0.9 | Headers, sections |
| Medium Gray | 0.7 | 0.7 | 0.7 | Disabled, inactive |
| Dark Gray | 0.5 | 0.5 | 0.5 | Emphasis |
| Black | 0.0 | 0.0 | 0.0 | Strong contrast |
### Highlight Colors
| Color | Red | Green | Blue | Use Case |
|-------|-----|-------|------|----------|
| Light Yellow | 1.0 | 1.0 | 0.8 | Attention, caution |
| Yellow | 1.0 | 1.0 | 0.0 | Highlight, warning |
| Light Green | 0.8 | 1.0 | 0.8 | Success, positive |
| Green | 0.0 | 1.0 | 0.0 | Active, go |
| Light Red | 1.0 | 0.8 | 0.8 | Error, caution |
| Red | 1.0 | 0.0 | 0.0 | Alert, danger |
| Light Blue | 0.8 | 0.9 | 1.0 | Information |
| Blue | 0.0 | 0.5 | 1.0 | Primary, active |
### Pastel Colors (Professional)
| Color | Red | Green | Blue | Use Case |
|-------|-----|-------|------|----------|
| Pastel Pink | 1.0 | 0.9 | 0.95 | Soft emphasis |
| Pastel Peach | 1.0 | 0.95 | 0.85 | Warm highlight |
| Pastel Yellow | 1.0 | 1.0 | 0.9 | Subtle attention |
| Pastel Green | 0.9 | 1.0 | 0.9 | Success, positive |
| Pastel Blue | 0.9 | 0.95 | 1.0 | Information |
| Pastel Purple | 0.95 | 0.9 | 1.0 | Alternative |
### Data Visualization Colors
| Color | Red | Green | Blue | Use Case |
|-------|-----|-------|------|----------|
| Light Orange | 1.0 | 0.85 | 0.7 | Metrics, KPIs |
| Orange | 1.0 | 0.65 | 0.0 | Warnings |
| Light Teal | 0.7 | 0.95 | 0.95 | Data ranges |
| Teal | 0.0 | 0.8 | 0.8 | Categories |
| Light Purple | 0.9 | 0.8 | 1.0 | Grouping |
| Purple | 0.5 | 0.0 | 0.8 | Special cases |
---
## Format Combinations
### Header Row (Professional)
```bash
echo '{
"spreadsheet_id": "abc123",
"sheet_id": 0,
"start_row": 0,
"end_row": 1,
"start_col": 0,
"end_col": 10,
"format": {
"bold": true,
"fontSize": 12,
"backgroundColor": {
"red": 0.85,
"green": 0.85,
"blue": 0.85,
"alpha": 1.0
}
}
}' | sheets_manager.rb format
```
### Title Row (Large and Bold)
```bash
echo '{
"spreadsheet_id": "abc123",
"sheet_id": 0,
"start_row": 0,
"end_row": 1,
"start_col": 0,
"end_col": 10,
"format": {
"bold": true,
"fontSize": 16,
"backgroundColor": {
"red": 0.2,
"green": 0.4,
"blue": 0.8,
"alpha": 1.0
}
}
}' | sheets_manager.rb format
```
### Summary Row (Bold with Yellow)
```bash
echo '{
"spreadsheet_id": "abc123",
"sheet_id": 0,
"start_row": 15,
"end_row": 16,
"start_col": 0,
"end_col": 10,
"format": {
"bold": true,
"fontSize": 12,
"backgroundColor": {
"red": 1.0,
"green": 1.0,
"blue": 0.8,
"alpha": 1.0
}
}
}' | sheets_manager.rb format
```
### Warning Cell (Bold with Light Red)
```bash
echo '{
"spreadsheet_id": "abc123",
"sheet_id": 0,
"start_row": 10,
"end_row": 11,
"start_col": 5,
"end_col": 6,
"format": {
"bold": true,
"fontSize": 12,
"backgroundColor": {
"red": 1.0,
"green": 0.8,
"blue": 0.8,
"alpha": 1.0
}
}
}' | sheets_manager.rb format
```
### Note Row (Italic with Light Blue)
```bash
echo '{
"spreadsheet_id": "abc123",
"sheet_id": 0,
"start_row": 20,
"end_row": 21,
"start_col": 0,
"end_col": 10,
"format": {
"italic": true,
"fontSize": 10,
"backgroundColor": {
"red": 0.9,
"green": 0.95,
"blue": 1.0,
"alpha": 1.0
}
}
}' | sheets_manager.rb format
```
---
## Best Practices
### Readability
1. **Use sufficient contrast**: Dark text on light backgrounds, or vice versa
2. **Avoid bright colors**: Use pastel shades for backgrounds
3. **Consistent sizing**: Use 2-3 font sizes maximum per sheet
4. **Bold for emphasis**: Don't overuse - reserve for important data
### Professional Appearance
1. **Neutral color palette**: Grays, blues, and greens are professional
2. **Subtle highlights**: Light pastels instead of bright neons
3. **Consistent patterns**: Use same formatting for same types of data
4. **White space**: Don't fill every cell with color
### Performance
1. **Format ranges, not individual cells**: More efficient API usage
2. **Batch formatting operations**: Combine multiple format calls when possible
3. **Reuse formats**: Apply same format to multiple similar ranges
4. **Minimal formatting**: Only format what needs emphasis
### Accessibility
1. **High contrast**: Ensure text is readable against background
2. **Don't rely on color alone**: Use text labels or icons too
3. **Consistent patterns**: Similar data gets similar formatting
4. **Test readability**: View sheet at different zoom levels
---
## Common Formatting Patterns
### Spreadsheet Template Pattern
```bash
# 1. Title row
echo '{
"spreadsheet_id": "abc123",
"sheet_id": 0,
"start_row": 0,
"end_row": 1,
"start_col": 0,
"end_col": 10,
"format": {
"bold": true,
"fontSize": 14,
"backgroundColor": {"red": 0.2, "green": 0.4, "blue": 0.8, "alpha": 1.0}
}
}' | sheets_manager.rb format
# 2. Header row
echo '{
"spreadsheet_id": "abc123",
"sheet_id": 0,
"start_row": 2,
"end_row": 3,
"start_col": 0,
"end_col": 10,
"format": {
"bold": true,
"fontSize": 11,
"backgroundColor": {"red": 0.85, "green": 0.85, "blue": 0.85, "alpha": 1.0}
}
}' | sheets_manager.rb format
# 3. Alternating row colors (light gray)
echo '{
"spreadsheet_id": "abc123",
"sheet_id": 0,
"start_row": 4,
"end_row": 5,
"start_col": 0,
"end_col": 10,
"format": {
"backgroundColor": {"red": 0.95, "green": 0.95, "blue": 0.95, "alpha": 1.0}
}
}' | sheets_manager.rb format
# 4. Total row
echo '{
"spreadsheet_id": "abc123",
"sheet_id": 0,
"start_row": 15,
"end_row": 16,
"start_col": 0,
"end_col": 10,
"format": {
"bold": true,
"backgroundColor": {"red": 1.0, "green": 1.0, "blue": 0.8, "alpha": 1.0}
}
}' | sheets_manager.rb format
```
### Data Dashboard Pattern
```bash
# KPI cells (bold, large, light blue)
echo '{
"spreadsheet_id": "abc123",
"sheet_id": 0,
"start_row": 0,
"end_row": 2,
"start_col": 0,
"end_col": 4,
"format": {
"bold": true,
"fontSize": 14,
"backgroundColor": {"red": 0.8, "green": 0.9, "blue": 1.0, "alpha": 1.0}
}
}' | sheets_manager.rb format
# Positive metrics (light green)
echo '{
"spreadsheet_id": "abc123",
"sheet_id": 0,
"start_row": 5,
"end_row": 8,
"start_col": 2,
"end_col": 3,
"format": {
"backgroundColor": {"red": 0.8, "green": 1.0, "blue": 0.8, "alpha": 1.0}
}
}' | sheets_manager.rb format
# Negative metrics (light red)
echo '{
"spreadsheet_id": "abc123",
"sheet_id": 0,
"start_row": 9,
"end_row": 12,
"start_col": 2,
"end_col": 3,
"format": {
"backgroundColor": {"red": 1.0, "green": 0.8, "blue": 0.8, "alpha": 1.0}
}
}' | sheets_manager.rb format
```
### Report Format Pattern
```bash
# Section headers (bold, medium gray)
echo '{
"spreadsheet_id": "abc123",
"sheet_id": 0,
"start_row": 0,
"end_row": 1,
"start_col": 0,
"end_col": 8,
"format": {
"bold": true,
"fontSize": 13,
"backgroundColor": {"red": 0.7, "green": 0.7, "blue": 0.7, "alpha": 1.0}
}
}' | sheets_manager.rb format
# Subheaders (bold, light gray)
echo '{
"spreadsheet_id": "abc123",
"sheet_id": 0,
"start_row": 3,
"end_row": 4,
"start_col": 0,
"end_col": 8,
"format": {
"bold": true,
"backgroundColor": {"red": 0.9, "green": 0.9, "blue": 0.9, "alpha": 1.0}
}
}' | sheets_manager.rb format
# Notes (italic, small, light blue)
echo '{
"spreadsheet_id": "abc123",
"sheet_id": 0,
"start_row": 20,
"end_row": 22,
"start_col": 0,
"end_col": 8,
"format": {
"italic": true,
"fontSize": 9,
"backgroundColor": {"red": 0.9, "green": 0.95, "blue": 1.0, "alpha": 1.0}
}
}' | sheets_manager.rb format
```
---
## Index Calculation Examples
### Converting Row/Column to Indices
**Example: Format row 5 (fifth row)**
- Row 5 is index 4 (0-based)
- To format: `start_row: 4, end_row: 5`
**Example: Format columns A-F**
- A=0, B=1, C=2, D=3, E=4, F=5
- To format A-F: `start_col: 0, end_col: 6` (6 is exclusive)
**Example: Format cells B3:D7**
- Row 3 = index 2, Row 7 = index 6
- Column B = index 1, Column D = index 3
- To format: `start_row: 2, end_row: 7, start_col: 1, end_col: 4`
### Quick Reference Table
| Excel Range | start_row | end_row | start_col | end_col |
|-------------|-----------|---------|-----------|---------|
| A1:A1 | 0 | 1 | 0 | 1 |
| A1:E1 | 0 | 1 | 0 | 5 |
| A1:A10 | 0 | 10 | 0 | 1 |
| B2:D5 | 1 | 5 | 1 | 4 |
| C10:F15 | 9 | 15 | 2 | 6 |
---
**Last Updated**: November 10, 2025
**Version**: 1.0.0
```
### examples/sample_operations.md
```markdown
# Google Sheets Sample Operations
Real-world usage examples demonstrating common workflows and patterns with the Google Sheets Agent Skill.
---
## Table of Contents
1. [Basic Operations](#basic-operations)
2. [Data Management](#data-management)
3. [Formatting Workflows](#formatting-workflows)
4. [Integration Examples](#integration-examples)
5. [Advanced Patterns](#advanced-patterns)
---
## Basic Operations
### Reading Data from a Sheet
**Scenario**: Read sales data from a quarterly report.
```bash
# Read a specific range
echo '{
"operation": "read",
"spreadsheet_id": "1abc123xyz456",
"range": "Q1 Sales!A1:E10"
}' | ~/.claude/skills/google-sheets/scripts/sheets_manager.rb
# Expected output:
{
"status": "success",
"values": [
["Date", "Product", "Quantity", "Price", "Total"],
["2025-01-05", "Widget A", "50", "$25.00", "$1,250.00"],
["2025-01-12", "Widget B", "30", "$40.00", "$1,200.00"]
],
"range": "Q1 Sales!A1:E10",
"majorDimension": "ROWS"
}
```
**Natural Language**: "Read the sales data from cells A1 to E10 in the Q1 Sales sheet"
---
### Writing Data to Cells
**Scenario**: Update monthly expenses in a budget tracker.
```bash
# Write expense data
echo '{
"operation": "write",
"spreadsheet_id": "1abc123xyz456",
"range": "Expenses!A2:D5",
"values": [
["2025-11-01", "Office Supplies", "Stationery", "150.00"],
["2025-11-05", "Travel", "Conference", "850.00"],
["2025-11-10", "Marketing", "Social Media Ads", "500.00"],
["2025-11-15", "Utilities", "Internet", "120.00"]
],
"input_option": "USER_ENTERED"
}' | ~/.claude/skills/google-sheets/scripts/sheets_manager.rb
# Expected output:
{
"status": "success",
"updated_range": "Expenses!A2:D5",
"updated_rows": 4,
"updated_columns": 4,
"updated_cells": 16
}
```
**Natural Language**: "Write the November expenses to the Expenses sheet starting at row 2"
---
## Data Management
### Creating a Budget Template
**Workflow**: Create a new budget sheet with categories and formulas.
```bash
# Step 1: Create a new sheet
echo '{
"operation": "create_sheet",
"spreadsheet_id": "1abc123xyz456",
"title": "2025 Budget"
}' | ~/.claude/skills/google-sheets/scripts/sheets_manager.rb
# Step 2: Write headers
echo '{
"operation": "write",
"spreadsheet_id": "1abc123xyz456",
"range": "2025 Budget!A1:E1",
"values": [["Category", "Planned", "Actual", "Difference", "% Variance"]],
"input_option": "USER_ENTERED"
}' | ~/.claude/skills/google-sheets/scripts/sheets_manager.rb
# Step 3: Write categories with formulas
echo '{
"operation": "write",
"spreadsheet_id": "1abc123xyz456",
"range": "2025 Budget!A2:E6",
"values": [
["Housing", "2000", "", "=B2-C2", "=IF(B2>0,(C2-B2)/B2,0)"],
["Transportation", "500", "", "=B3-C3", "=IF(B3>0,(C3-B3)/B3,0)"],
["Food", "800", "", "=B4-C4", "=IF(B4>0,(C4-B4)/B4,0)"],
["Utilities", "300", "", "=B5-C5", "=IF(B5>0,(C5-B5)/B5,0)"],
["Entertainment", "200", "", "=B6-C6", "=IF(B6>0,(C6-B6)/B6,0)"]
],
"input_option": "USER_ENTERED"
}' | ~/.claude/skills/google-sheets/scripts/sheets_manager.rb
# Step 4: Add totals row
echo '{
"operation": "write",
"spreadsheet_id": "1abc123xyz456",
"range": "2025 Budget!A7:E7",
"values": [["TOTAL", "=SUM(B2:B6)", "=SUM(C2:C6)", "=SUM(D2:D6)", "=IF(B7>0,(C7-B7)/B7,0)"]],
"input_option": "USER_ENTERED"
}' | ~/.claude/skills/google-sheets/scripts/sheets_manager.rb
```
**Natural Language**: "Create a budget tracker for 2025 with categories, planned amounts, actual amounts, and variance calculations"
---
### Appending Transaction Records
**Scenario**: Add new transactions to a running log without overwriting existing data.
```bash
# Append transactions to the end of the sheet
echo '{
"operation": "append",
"spreadsheet_id": "1abc123xyz456",
"range": "Transactions!A:D",
"values": [
["2025-11-10", "Lunch", "Dining", "45.00"],
["2025-11-10", "Gas", "Transportation", "60.00"],
["2025-11-10", "Groceries", "Food", "120.00"]
],
"input_option": "USER_ENTERED"
}' | ~/.claude/skills/google-sheets/scripts/sheets_manager.rb
# Expected output:
{
"status": "success",
"updated_range": "Transactions!A25:D27",
"updated_rows": 3,
"updated_columns": 4,
"updated_cells": 12,
"message": "Appended 3 rows"
}
```
**Natural Language**: "Add today's transactions to the transaction log"
---
## Formatting Workflows
### Creating a Professional Report Header
**Workflow**: Format a report header with styling.
```bash
# Step 1: Write header text
echo '{
"operation": "write",
"spreadsheet_id": "1abc123xyz456",
"range": "Report!A1:F1",
"values": [["Q4 2025 Sales Report"]],
"input_option": "USER_ENTERED"
}' | ~/.claude/skills/google-sheets/scripts/sheets_manager.rb
# Step 2: Format header (bold, large font, blue background)
echo '{
"operation": "format",
"spreadsheet_id": "1abc123xyz456",
"sheet_id": 0,
"start_row": 0,
"end_row": 1,
"start_column": 0,
"end_column": 6,
"format": {
"text_format": {
"bold": true,
"fontSize": 14
},
"background_color": {
"red": 0.26,
"green": 0.52,
"blue": 0.96
}
}
}' | ~/.claude/skills/google-sheets/scripts/sheets_manager.rb
# Step 3: Write and format column headers
echo '{
"operation": "write",
"spreadsheet_id": "1abc123xyz456",
"range": "Report!A2:F2",
"values": [["Region", "Product", "Units Sold", "Revenue", "Growth %", "Target"]],
"input_option": "USER_ENTERED"
}' | ~/.claude/skills/google-sheets/scripts/sheets_manager.rb
echo '{
"operation": "format",
"spreadsheet_id": "1abc123xyz456",
"sheet_id": 0,
"start_row": 1,
"end_row": 2,
"start_column": 0,
"end_column": 6,
"format": {
"text_format": {
"bold": true
},
"background_color": {
"red": 0.85,
"green": 0.85,
"blue": 0.85
}
}
}' | ~/.claude/skills/google-sheets/scripts/sheets_manager.rb
```
**Natural Language**: "Create a professional report header with blue background and bold column headers"
---
### Conditional Formatting for Data Visualization
**Scenario**: Highlight high-performing sales regions.
```bash
# Format cells with revenue >$50,000 in green
echo '{
"operation": "format",
"spreadsheet_id": "1abc123xyz456",
"sheet_id": 0,
"start_row": 2,
"end_row": 10,
"start_column": 3,
"end_column": 4,
"format": {
"background_color": {
"red": 0.72,
"green": 0.88,
"blue": 0.80
}
}
}' | ~/.claude/skills/google-sheets/scripts/sheets_manager.rb
```
**Natural Language**: "Highlight the revenue column in green for high-performing regions"
---
## Integration Examples
### Creating a Spreadsheet from Scratch
**Workflow**: Combine google-drive and google-sheets skills.
```bash
# Step 1: Create spreadsheet file (using google-drive skill)
echo '{
"operation": "create",
"name": "Project Tracker 2025",
"mime_type": "application/vnd.google-apps.spreadsheet"
}' | ~/.claude/skills/google-drive/scripts/drive_manager.rb
# Returns: {"file_id": "1new_spreadsheet_id"}
# Step 2: Add project data (using google-sheets skill)
echo '{
"operation": "write",
"spreadsheet_id": "1new_spreadsheet_id",
"range": "Sheet1!A1:E1",
"values": [["Task", "Owner", "Status", "Due Date", "Priority"]],
"input_option": "USER_ENTERED"
}' | ~/.claude/skills/google-sheets/scripts/sheets_manager.rb
# Step 3: Share with team (using google-drive skill)
echo '{
"operation": "share",
"file_id": "1new_spreadsheet_id",
"email": "[email protected]",
"role": "writer"
}' | ~/.claude/skills/google-drive/scripts/drive_manager.rb
```
**Natural Language**: "Create a new project tracker spreadsheet and share it with the team"
---
### Exporting Data to CSV
**Workflow**: Read sheet data and export to CSV (using google-drive skill).
```bash
# Step 1: Read data from sheet
echo '{
"operation": "read",
"spreadsheet_id": "1abc123xyz456",
"range": "Sales Data!A1:Z1000"
}' | ~/.claude/skills/google-sheets/scripts/sheets_manager.rb > data.json
# Step 2: Export to CSV (using google-drive skill)
echo '{
"operation": "export",
"file_id": "1abc123xyz456",
"format": "csv",
"output_path": "/Users/arlenagreer/Downloads/sales_data.csv"
}' | ~/.claude/skills/google-drive/scripts/drive_manager.rb
```
**Natural Language**: "Export the sales data sheet to CSV format"
---
## Advanced Patterns
### Batch Update for Performance
**Scenario**: Update multiple sections of a sheet efficiently.
```bash
# Single API call for multiple updates
echo '{
"operation": "batch_update",
"spreadsheet_id": "1abc123xyz456",
"requests": [
{
"updateCells": {
"range": {
"sheetId": 0,
"startRowIndex": 0,
"endRowIndex": 1,
"startColumnIndex": 0,
"endColumnIndex": 5
},
"rows": [
{
"values": [
{"userEnteredValue": {"stringValue": "Dashboard"}},
{"userEnteredValue": {"stringValue": ""}},
{"userEnteredValue": {"stringValue": ""}},
{"userEnteredValue": {"stringValue": ""}},
{"userEnteredValue": {"stringValue": ""}}
]
}
],
"fields": "userEnteredValue"
}
},
{
"repeatCell": {
"range": {
"sheetId": 0,
"startRowIndex": 0,
"endRowIndex": 1,
"startColumnIndex": 0,
"endColumnIndex": 5
},
"cell": {
"userEnteredFormat": {
"textFormat": {
"bold": true,
"fontSize": 16
},
"backgroundColor": {
"red": 0.26,
"green": 0.52,
"blue": 0.96
}
}
},
"fields": "userEnteredFormat(textFormat,backgroundColor)"
}
}
]
}' | ~/.claude/skills/google-sheets/scripts/sheets_manager.rb
```
**Natural Language**: "Create a dashboard header with title and formatting in a single operation"
---
### Creating a Data Dashboard
**Complete Workflow**: Build a formatted dashboard with data and charts.
```bash
# Step 1: Create dashboard sheet
echo '{
"operation": "create_sheet",
"spreadsheet_id": "1abc123xyz456",
"title": "Dashboard"
}' | ~/.claude/skills/google-sheets/scripts/sheets_manager.rb
# Step 2: Write KPI headers
echo '{
"operation": "write",
"spreadsheet_id": "1abc123xyz456",
"range": "Dashboard!A1:D1",
"values": [["Revenue", "Customers", "Conversion Rate", "AOV"]],
"input_option": "USER_ENTERED"
}' | ~/.claude/skills/google-sheets/scripts/sheets_manager.rb
# Step 3: Write KPI values with formulas
echo '{
"operation": "write",
"spreadsheet_id": "1abc123xyz456",
"range": "Dashboard!A2:D2",
"values": [["=SUM(Sales!D:D)", "=COUNTA(Sales!A:A)-1", "=B2/COUNTA(Visitors!A:A)", "=A2/B2"]],
"input_option": "USER_ENTERED"
}' | ~/.claude/skills/google-sheets/scripts/sheets_manager.rb
# Step 4: Format KPI section
echo '{
"operation": "format",
"spreadsheet_id": "1abc123xyz456",
"sheet_id": 1234567890,
"start_row": 0,
"end_row": 2,
"start_column": 0,
"end_column": 4,
"format": {
"text_format": {
"bold": true,
"fontSize": 12
},
"background_color": {
"red": 0.85,
"green": 0.92,
"blue": 0.95
}
}
}' | ~/.claude/skills/google-sheets/scripts/sheets_manager.rb
```
**Natural Language**: "Build a dashboard with KPIs including revenue, customers, conversion rate, and average order value"
---
### Data Validation and Cleanup
**Workflow**: Clear and prepare a sheet for new data.
```bash
# Step 1: Get sheet metadata to identify the right sheet
echo '{
"operation": "metadata",
"spreadsheet_id": "1abc123xyz456"
}' | ~/.claude/skills/google-sheets/scripts/sheets_manager.rb
# Step 2: Clear existing data (keeping headers)
echo '{
"operation": "clear",
"spreadsheet_id": "1abc123xyz456",
"range": "Data Import!A2:Z1000"
}' | ~/.claude/skills/google-sheets/scripts/sheets_manager.rb
# Step 3: Write new validated data
echo '{
"operation": "write",
"spreadsheet_id": "1abc123xyz456",
"range": "Data Import!A2:C100",
"values": [
["2025-11-10", "Valid Record", "100.00"],
["2025-11-10", "Valid Record", "250.00"]
],
"input_option": "USER_ENTERED"
}' | ~/.claude/skills/google-sheets/scripts/sheets_manager.rb
```
**Natural Language**: "Clear the old data and import the new validated records"
---
## Common Patterns
### Reading with Error Handling
```bash
# Try to read, check for errors
RESULT=$(echo '{
"operation": "read",
"spreadsheet_id": "1abc123xyz456",
"range": "Sheet1!A1:B10"
}' | ~/.claude/skills/google-sheets/scripts/sheets_manager.rb)
if echo "$RESULT" | jq -e '.status == "success"' > /dev/null; then
echo "✅ Read successful"
echo "$RESULT" | jq '.values'
else
echo "❌ Read failed"
echo "$RESULT" | jq '.error'
fi
```
---
### Writing with Validation
```bash
# Validate data before writing
DATA='[["2025-11-10", "Valid", "100"]]'
if echo "$DATA" | jq -e 'length > 0' > /dev/null; then
echo "{
\"operation\": \"write\",
\"spreadsheet_id\": \"1abc123xyz456\",
\"range\": \"Sheet1!A2:C2\",
\"values\": $DATA,
\"input_option\": \"USER_ENTERED\"
}" | ~/.claude/skills/google-sheets/scripts/sheets_manager.rb
else
echo '{"status": "error", "message": "No data to write"}'
fi
```
---
### Appending with Auto-Range
```bash
# Append to any column, script finds the right row
echo '{
"operation": "append",
"spreadsheet_id": "1abc123xyz456",
"range": "Log!A:E",
"values": [["'$(date +%Y-%m-%d)'", "System", "Info", "Operation complete", "Success"]],
"input_option": "USER_ENTERED"
}' | ~/.claude/skills/google-sheets/scripts/sheets_manager.rb
```
---
## Best Practices
1. **Use USER_ENTERED for formulas**: Always use `"input_option": "USER_ENTERED"` when writing formulas
2. **Batch operations**: Use `batch_update` for multiple changes to reduce API calls
3. **Format after data**: Write data first, then apply formatting for cleaner workflows
4. **Check metadata**: Use `metadata` operation to get sheet IDs before formatting
5. **Clear carefully**: Use specific ranges when clearing to avoid deleting headers
6. **Test ranges**: Verify ranges with small datasets before bulk operations
7. **Handle errors**: Always check operation status before proceeding
8. **Use A1 notation**: Prefer readable A1 notation (e.g., "A1:B10") over R1C1
---
## Quick Reference Commands
```bash
# Authentication check
echo '{"operation": "auth"}' | sheets_manager.rb
# Read data
echo '{"operation": "read", "spreadsheet_id": "ID", "range": "Sheet!A1:B10"}' | sheets_manager.rb
# Write data
echo '{"operation": "write", "spreadsheet_id": "ID", "range": "Sheet!A1:B2", "values": [["A","B"]], "input_option": "USER_ENTERED"}' | sheets_manager.rb
# Append data
echo '{"operation": "append", "spreadsheet_id": "ID", "range": "Sheet!A:B", "values": [["C","D"]], "input_option": "USER_ENTERED"}' | sheets_manager.rb
# Clear range
echo '{"operation": "clear", "spreadsheet_id": "ID", "range": "Sheet!A1:B10"}' | sheets_manager.rb
# Get metadata
echo '{"operation": "metadata", "spreadsheet_id": "ID"}' | sheets_manager.rb
# Create sheet
echo '{"operation": "create_sheet", "spreadsheet_id": "ID", "title": "New Sheet"}' | sheets_manager.rb
# Format cells
echo '{"operation": "format", "spreadsheet_id": "ID", "sheet_id": 0, "start_row": 0, "end_row": 1, "start_column": 0, "end_column": 2, "format": {"text_format": {"bold": true}}}' | sheets_manager.rb
# Batch update
echo '{"operation": "batch_update", "spreadsheet_id": "ID", "requests": [...]}' | sheets_manager.rb
```
---
## Version History
- **v1.0** (2025-11-10): Initial examples with core operations and workflows
```