Google Sheets API Skill

Read and update Google Sheets spreadsheets using Python scripts and uv package manager.

Prerequisites

1. Install gcloud CLI

설치 가이드: https://cloud.google.com/sdk/docs/install-sdk

# Linux (Debian/Ubuntu)
sudo apt-get install apt-transport-https ca-certificates gnupg curl
curl https://packages.cloud.google.com/apt/doc/apt-key.gpg | sudo gpg --dearmor -o /usr/share/keyrings/cloud.google.gpg
echo "deb [signed-by=/usr/share/keyrings/cloud.google.gpg] https://packages.cloud.google.com/apt cloud-sdk main" | sudo tee -a /etc/apt/sources.list.d/google-cloud-sdk.list
sudo apt-get update && sudo apt-get install google-cloud-cli

# macOS
brew install google-cloud-sdk

# Windows
# https://cloud.google.com/sdk/docs/install-sdk 에서 설치 파일 다운로드

2. Authentication Setup (one-time)

# gcloud 초기화 (최초 1회)
gcloud init

# ADC 로그인
gcloud auth application-default login --scopes=https://www.googleapis.com/auth/cloud-platform,https://www.googleapis.com/auth/spreadsheets

# quota project 설정 (팀 공용 프로젝트)
gcloud auth application-default set-quota-project data-470906

Credential 파일 위치:

• Linux/macOS: ~/.config/gcloud/application_default_credentials.json
• Windows: %APPDATA%\gcloud\application_default_credentials.json

Execution Pattern

uv run --no-project --with google-auth --with google-api-python-client python scripts/sheet_api.py [command] [args]

Available Commands

Get Spreadsheet Info

uv run --no-project --with google-auth --with google-api-python-client \
    python scripts/sheet_api.py info <spreadsheet_id_or_url>

Read Range

# Table format (default)
uv run --no-project --with google-auth --with google-api-python-client \
    python scripts/sheet_api.py read <spreadsheet> <range>

# JSON format
uv run --no-project --with google-auth --with google-api-python-client \
    python scripts/sheet_api.py read <spreadsheet> <range> --format json -o output.json

# CSV format
uv run --no-project --with google-auth --with google-api-python-client \
    python scripts/sheet_api.py read <spreadsheet> <range> --format csv -o output.csv

Update Range

# Prepare JSON file with values
echo '{"values": [["A", "B"], [1, 2]]}' > data.json

uv run --no-project --with google-auth --with google-api-python-client \
    python scripts/sheet_api.py update <spreadsheet> <range> -f data.json

Append Rows

uv run --no-project --with google-auth --with google-api-python-client \
    python scripts/sheet_api.py append <spreadsheet> <range> -f rows.json

Clear Range

uv run --no-project --with google-auth --with google-api-python-client \
    python scripts/sheet_api.py clear <spreadsheet> <range>

Formatting Commands

Background Color

uv run --no-project --with google-auth --with google-api-python-client \
    python scripts/sheet_api.py bgcolor <spreadsheet> <range> '#4285F4'

# With sheet name
    python scripts/sheet_api.py bgcolor <spreadsheet> <range> '#FF0000' --sheet 'Sheet1'

Borders

# Default solid borders
uv run --no-project --with google-auth --with google-api-python-client \
    python scripts/sheet_api.py border <spreadsheet> <range>

# Custom style
    python scripts/sheet_api.py border <spreadsheet> <range> --style SOLID_THICK --color '#000000'

# Outer borders only
    python scripts/sheet_api.py border <spreadsheet> <range> --outer-only

Border styles: NONE, SOLID, SOLID_MEDIUM, SOLID_THICK, DASHED, DOTTED, DOUBLE

Text Format

# Bold
uv run --no-project --with google-auth --with google-api-python-client \
    python scripts/sheet_api.py textfmt <spreadsheet> <range> --bold

# Multiple options
    python scripts/sheet_api.py textfmt <spreadsheet> <range> --bold --italic --size 14 --color '#FF0000'

Format as Table

헤더 스타일링 + 테두리 한번에 적용:

uv run --no-project --with google-auth --with google-api-python-client \
    python scripts/sheet_api.py table <spreadsheet> <range>

# Custom colors
    python scripts/sheet_api.py table <spreadsheet> <range> --header-color '#34A853' --border-color '#000000'

Pivot Table

uv run --no-project --with google-auth --with google-api-python-client \
    python scripts/sheet_api.py pivot <spreadsheet> <source_range> <target_cell> \
        --rows 0 --values '2:SUM'

# Multiple rows and values
    python scripts/sheet_api.py pivot <spreadsheet> 'A1:D100' 'F1' \
        --rows 0,1 --values '2:SUM,3:COUNT' --columns 4

Options:

• --rows: Column offsets for row grouping (0-indexed, comma-separated)
• --values: Column:Function pairs (e.g., 2:SUM,3:AVERAGE)
• --columns: Column offsets for column grouping (optional)
• --source-sheet, --target-sheet: Sheet names (optional)

Summarize functions: SUM, COUNT, COUNTA, AVERAGE, MAX, MIN, MEDIAN

A1 Notation

Notation	Description
A1	Single cell
A1:B5	Range
Sheet1!A1:B5	Range in specific sheet
A:A	Entire column
1:1	Entire row

Output Formats

table (default)

Range: Sheet1!A1:C3
Size: 3 rows x 3 cols
---
[0] ['Name', 'Score', 'Grade']
[1] ['Alice', 95, 'A']
[2] ['Bob', 87, 'B']

json

{
  "range": "Sheet1!A1:C3",
  "values": [
    ["Name", "Score", "Grade"],
    ["Alice", 95, "A"],
    ["Bob", 87, "B"]
  ],
  "rows": 3,
  "cols": 3
}

csv

Name,Score,Grade
Alice,95,A
Bob,87,B

Script Modules

• scripts/utils.py - Authentication, service builder
• scripts/sheet_api.py - Sheets operations and CLI

Error Handling

Error	Cause	Solution
401	Expired credentials	Re-run gcloud auth application-default login
403	No access	Share spreadsheet with your account
404	Not found	Check spreadsheet ID

Troubleshooting

Credentials expired

gcloud auth application-default login --scopes=https://www.googleapis.com/auth/cloud-platform,https://www.googleapis.com/auth/spreadsheets

Quota project error

gcloud auth application-default set-quota-project data-470906