google-sheets（Google Sheets 表格协作）

Google Sheets

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

Installation

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

Setup Verification

After installation, verify the skill is properly configured:

$SKILL_DIR/scripts/google-sheets.py check

This will check:

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

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

Authentication

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

1. GCP Project Setup Guide - Create project, enable Sheets API
2. Google OAuth Setup Guide - Configure credentials

Quick Start

1. Create ~/.config/agent-skills/google.yaml: oauth_client: client_id: your-client-id.apps.googleusercontent.com client_secret: your-client-secret
2. Run $SKILL_DIR/scripts/google-sheets.py check to trigger OAuth flow and verify setup.

On scope or authentication errors, see the OAuth troubleshooting guide.

Commands

See permissions.md for read/write classification of each command.

check

Verify configuration and connectivity.

$SKILL_DIR/scripts/google-sheets.py check

This validates:

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

auth setup

Store OAuth 2.0 client credentials for custom OAuth flow.

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

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

Options:

• --client-id - OAuth 2.0 client ID (required)
• --client-secret - OAuth 2.0 client secret (required)

auth reset

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

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

Use this when you encounter scope or authentication errors.

auth status

Show current OAuth token information without making API calls.

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

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

spreadsheets create

Create a new Google Sheets spreadsheet.

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

Options:

• --title - Spreadsheet title (required)
• --sheets - Comma-separated sheet names (optional)

Example:

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

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

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

spreadsheets get

Get spreadsheet metadata and structure.

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

Arguments:

• spreadsheet_id - The Google Sheets spreadsheet ID

Example:

$SKILL_DIR/scripts/google-sheets.py spreadsheets get 1abc...xyz

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

values read

Read cell values from a range.

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

Arguments:

• spreadsheet_id - The Google Sheets spreadsheet ID

Options:

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

Example:

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

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

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

See references/range-notation.md for A1 notation details.

values write

Write values to a range.

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

Arguments:

• spreadsheet_id - The Google Sheets spreadsheet ID

Options:

• --range - Starting range in A1 notation (required)
• --values - Values as JSON 2D array (required)

Example:

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

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

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

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

values append

Append rows to the end of a sheet.

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

Arguments:

• spreadsheet_id - The Google Sheets spreadsheet ID

Options:

• --range - Sheet name or range (required)
• --values - Values as JSON 2D array (required)

Example:

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

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

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

values clear

Clear values in a range.

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

Arguments:

• spreadsheet_id - The Google Sheets spreadsheet ID

Options:

• --range - Range in A1 notation (required)

Example:

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

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

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

sheets create

Add a new sheet to a spreadsheet.

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

Arguments:

• spreadsheet_id - The Google Sheets spreadsheet ID

Options:

• --title - Sheet title (required)

Example:

$SKILL_DIR/scripts/google-sheets.py sheets create 1abc...xyz --title "Q2 Data"

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

sheets delete

Delete a sheet from a spreadsheet.

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

Arguments:

• spreadsheet_id - The Google Sheets spreadsheet ID

Options:

• --sheet-id - Sheet ID (required, not the title!)

Example:

# Get sheet IDs first
$SKILL_DIR/scripts/google-sheets.py spreadsheets get 1abc...xyz

# Delete a sheet
$SKILL_DIR/scripts/google-sheets.py sheets delete 1abc...xyz --sheet-id 123456789

# Output:
# ✓ Sheet deleted successfully

Warning: Cannot delete the last remaining sheet in a spreadsheet.

Examples

Create and populate a spreadsheet

# Create spreadsheet
$SKILL_DIR/scripts/google-sheets.py spreadsheets create --title "Employee Data"

# Write headers (use the spreadsheet ID from the output above)
$SKILL_DIR/scripts/google-sheets.py values write $SS_ID \
  --range "Sheet1!A1" \
  --values '[["Name","Department","Salary","Start Date"]]'

# Append employee records
$SKILL_DIR/scripts/google-sheets.py values append $SS_ID \
  --range "Sheet1" \
  --values '[["Alice","Engineering",120000,"2023-01-15"],["Bob","Sales",95000,"2023-03-01"]]'

# Add a summary sheet
$SKILL_DIR/scripts/google-sheets.py sheets create $SS_ID --title "Summary"

# Read the data
$SKILL_DIR/scripts/google-sheets.py values read $SS_ID --range "Sheet1!A1:D10"

Work with formulas

# Write data with formulas
$SKILL_DIR/scripts/google-sheets.py values write $SS_ID \
  --range "Sheet1!A1" \
  --values '[["Item","Price","Qty","Total"],["Widget",10,5,"=B2*C2"],["Gadget",20,3,"=B3*C3"]]'

# Read formulas
$SKILL_DIR/scripts/google-sheets.py values read $SS_ID \
  --range "Sheet1!D2:D3" \
  --format FORMULA

# Read calculated values
$SKILL_DIR/scripts/google-sheets.py values read $SS_ID \
  --range "Sheet1!D2:D3" \
  --format FORMATTED_VALUE

Batch operations

#!/bin/bash
SS_ID="your-spreadsheet-id"

# Clear old data
$SKILL_DIR/scripts/google-sheets.py values clear $SS_ID --range "Sheet1!A1:Z1000"

# Write new data in batches
$SKILL_DIR/scripts/google-sheets.py values write $SS_ID \
  --range "Sheet1!A1" \
  --values '[["Date","Revenue","Expenses","Profit"]]'

for month in Jan Feb Mar; do
  $SKILL_DIR/scripts/google-sheets.py values append $SS_ID \
    --range "Sheet1" \
    --values "[[\"\$month\",10000,7000,\"=B${ROW}-C${ROW}\"]]"
done

Error Handling

Authentication and scope errors are not retryable. If a command fails with an authentication error, insufficient scope error, or permission denied error (exit code 1), stop and inform the user. Do not retry or attempt to fix the issue autonomously — these errors require user interaction (browser-based OAuth consent). Point the user to the OAuth troubleshooting guide.

Retryable errors: Rate limiting (HTTP 429) and temporary server errors (HTTP 5xx) may succeed on retry after a brief wait. All other errors should be reported to the user.

Model Guidance

This skill makes API calls requiring structured input/output. A standard-capability model is recommended.

Troubleshooting

Cannot find spreadsheet

Make sure you're using the correct spreadsheet ID from the URL:

• URL: https://docs.google.com/spreadsheets/d/1abc...xyz/edit
• Spreadsheet ID: 1abc...xyz

Invalid range errors

• Use proper A1 notation: Sheet1!A1:D5
• Sheet names with spaces need quotes: 'My Sheet'!A1:B2
• See references/range-notation.md for details

JSON parsing errors for --values

Ensure proper JSON escaping:

# Correct
--values '[["Hello","World"]]'
--values "[[\"Name\",\"Age\"]]"

# Incorrect
--values [[Hello,World]]  # Missing quotes

Sheet ID vs Sheet Title

Commands use different identifiers:

• sheets create - Uses title (string)
• sheets delete - Uses sheet ID (number)
• Use spreadsheets get to find sheet IDs

API Reference

For advanced usage, see:

• Google Sheets API Documentation
• Reading and writing cell values
• A1 notation reference
• Formula examples