Google Sheets API Skill

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

Prerequisites

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 에서 설치 파일 다운로드

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 &#x3C;spreadsheet> &#x3C;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 &#x3C;spreadsheet> &#x3C;range> --style SOLID_THICK --color '#000000'

Outer borders only

python scripts/sheet_api.py border &#x3C;spreadsheet> &#x3C;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 &#x3C;spreadsheet> &#x3C;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 &#x3C;spreadsheet> &#x3C;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 &#x3C;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

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