google-sheets-ops

Google Sheets operation skill (gog v0.10.0)

Operate Google Spreadsheets using the gog CLI.

Execution path: gog

Execution Notes

exec When executing with the tool,timeout: 60 specify
Execute large data writes in chunks

Rules to absolutely follow

1. Cell delimiter is pipe `|`, row delimiter is comma `,`

# ✅ 正しい：1行に3セル
gog sheets update "$ID" "シート1!A1:C1" "値1|値2|値3"

# ✅ 正しい：2行×3列
gog sheets update "$ID" "シート1!A1:C2" "行1列1|行1列2|行1列3,行2列1|行2列2|行2列3"

# ❌ 間違い：スペース区切りは全部1セルに入る
gog sheets update "$ID" "シート1!A1" "値1" "値2" "値3"

2. For data containing commas or pipes, `--values-json`

gog sheets update "$ID" "シート1!A1:C2" --values-json '[
  ["名前", "金額", "備考"],
  ["田中", "500,000", "特記|なし"]
]'

3. For multiple tabs, at create time `--sheets` create them all with

gog sheets create "タイトル" --sheets "タブ1,タブ2,タブ3"

⚠️ gog has no function to add, delete, or rename tabs afterward

4. Check metadata before operations

gog sheets metadata "$ID"  # シート名を確認（日本語ロケールでは「シート1」）

5. Verify after writing

gog sheets get "$ID" "シート1!A1:C2" --json

Command Reference

Read: `get`

gog sheets get <spreadsheetId> <range>

# 例
gog sheets get "$ID" "シート1!A1:C10"
gog sheets get "$ID" "シート1!A:A"      # A列全体
gog sheets get "$ID" "シート1!1:1"      # 1行目全体
gog sheets get "$ID" "シート1" --json   # JSON出力

Write: `update`

gog sheets update <spreadsheetId> <range> <values>

# オプション
--input="USER_ENTERED"      # 数式・日付を解釈（デフォルト）
--input="RAW"               # そのまま文字列として入力
--values-json='[[...]]'     # JSON形式で値指定
--copy-validation-from="Sheet1!A2"  # データ検証をコピー

Example:

# 単一行
gog sheets update "$ID" "シート1!A1:C1" "名前|部署|役職"

# 複数行
gog sheets update "$ID" "シート1!A2:C4" "田中|営業|主任,佐藤|開発|リーダー,鈴木|総務|担当"

# JSON形式（推奨：特殊文字を含む場合）
gog sheets update "$ID" "シート1!A1:B2" --values-json '[["項目","金額"],["売上","1,000,000"]]'

# 数式
gog sheets update "$ID" "シート1!D2" "=SUM(B2:C2)"

Append: `append`

gog sheets append <spreadsheetId> <range> <values>

# 例：シート末尾に行追加
gog sheets append "$ID" "シート1" "新しい|データ|行"

Clear: `clear`

gog sheets clear <spreadsheetId> <range>

# 例
gog sheets clear "$ID" "シート1!A2:C100"

Formatting: `format`

gog sheets format <spreadsheetId> <range> --format-json '<CellFormat>'

# オプション
--format-fields="userEnteredFormat(backgroundColor,textFormat)"

Format example:

# ヘッダー書式（青背景・白太字）
FMT='{"backgroundColor":{"red":0.2,"green":0.4,"blue":0.7},"textFormat":{"bold":true,"foregroundColor":{"red":1,"green":1,"blue":1}}}'
gog sheets format "$ID" "シート1!A1:D1" --format-json "$FMT"

# 太字のみ
gog sheets format "$ID" "シート1!A1:A10" --format-json '{"textFormat":{"bold":true}}'

# 背景色（薄い黄色）
gog sheets format "$ID" "シート1!A1:D1" --format-json '{"backgroundColor":{"red":1,"green":1,"blue":0.8}}'

Create: `create`

gog sheets create <title> [--sheets "タブ1,タブ2"]

# 例
gog sheets create "新規スプレッドシート"
gog sheets create "プロジェクト管理" --sheets "概要,タスク,スケジュール"

Copy: `copy`

gog sheets copy <spreadsheetId> <newTitle>

# 例
gog sheets copy "$TEMPLATE_ID" "請求書_202602_山田様"

Metadata: `metadata`

gog sheets metadata <spreadsheetId>

Export: `export`

gog sheets export <spreadsheetId> --format <csv|xlsx|pdf>

# 例
gog sheets export "$ID" --format csv
gog sheets export "$ID" --format xlsx --output "./backup.xlsx"

Typical workflow

Create a spreadsheet from scratch

# 1. 作成（タブ全部指定）
RESULT=$(gog sheets create "売上管理" --sheets "月次,年次,設定" --json)
ID=$(echo "$RESULT" | jq -r '.spreadsheetId')

# 2. ヘッダー書き込み
gog sheets update "$ID" "月次!A1:E1" "日付|商品|数量|単価|金額"

# 3. ヘッダー書式
FMT='{"backgroundColor":{"red":0.2,"green":0.4,"blue":0.7},"textFormat":{"bold":true,"foregroundColor":{"red":1,"green":1,"blue":1}}}'
gog sheets format "$ID" "月次!A1:E1" --format-json "$FMT"

# 4. サンプルデータ
gog sheets update "$ID" "月次!A2:E3" "2026/02/01|商品A|10|1000|=C2*D2,2026/02/02|商品B|5|2000|=C3*D3"

# 5. フォルダ移動（任意）
gog drive move "$ID" --parent "<フォルダID>"

echo "作成完了: https://docs.google.com/spreadsheets/d/$ID/edit"

Bulk write of large data

gog sheets update "$ID" "シート1!A1:D5" --values-json '[
  ["名前", "部署", "役職", "入社日"],
  ["田中太郎", "営業", "主任", "2024/04/01"],
  ["佐藤花子", "開発", "リーダー", "2023/01/15"],
  ["鈴木一郎", "総務", "担当", "2025/07/01"],
  ["山田次郎", "企画", "マネージャー", "2022/10/01"]
]'

Copy from a template and edit

# 1. コピー
RESULT=$(gog sheets copy "$TEMPLATE_ID" "請求書_202602_山田様" --json)
NEW_ID=$(echo "$RESULT" | jq -r '.id')

# 2. 編集
gog sheets update "$NEW_ID" "請求書!B6" "株式会社山田様"
gog sheets update "$NEW_ID" "請求書!D10" "コンサルティング業務"

echo "作成完了: https://docs.google.com/spreadsheets/d/$NEW_ID/edit"

CellFormat Reference

Background color

{"backgroundColor": {"red": 0.9, "green": 0.9, "blue": 0.9}}

RGB values are 0.0–1.0

Text format

{
  "textFormat": {
    "bold": true,
    "italic": false,
    "strikethrough": false,
    "underline": false,
    "fontSize": 12,
    "foregroundColor": {"red": 0, "green": 0, "blue": 0}
  }
}

Alignment

{
  "horizontalAlignment": "CENTER",
  "verticalAlignment": "MIDDLE"
}

Value: LEFT, CENTER, RIGHT / TOP, MIDDLE, BOTTOM

Borders

{
  "borders": {
    "top": {"style": "SOLID", "color": {"red": 0, "green": 0, "blue": 0}},
    "bottom": {"style": "SOLID"},
    "left": {"style": "SOLID"},
    "right": {"style": "SOLID"}
  }
}

What gog cannot do

The following require the Sheets API batchUpdate:

Adding, deleting, or renaming tabs afterward
Data validation (dropdown)
Conditional formatting
Cell merging
Column width / row height adjustment
Freezing rows/columns
Filters and Charts

Common Mistakes

Mistakes	Correct method
`update "A1" "a" "b" "c"`	`update "A1:C1" "a\|b\|c"`
Numbers containing commas `1,000`	`--values-json` use
Wrong sheet name	`metadata` check with
No range specified	Always specify the range

About this skill