Google Sheets Skill
Interact with Google Sheets using a service account.
Setup (One-time)
-
Google Cloud Console:
- Create/select a project
- Enable "Google Sheets API"
- Create a Service Account (IAM → Service Accounts → Create)
- Download JSON key
-
Configure credentials (one of these):
- Set env:
GOOGLE_SERVICE_ACCOUNT_KEY=/path/to/key.json - Place
service-account.jsonorcredentials.jsonin the skill directory - Place in
~/.config/google-sheets/credentials.json
- Set env:
-
Share sheets with the service account email (found in JSON key as
client_email) -
Install dependencies:
cd skills/google-sheets && npm install
Usage
node scripts/sheets.js <command> [args]
Commands
Data Operations
| Command | Args | Description |
|---|---|---|
read | <id> <range> | Read cells |
write | <id> <range> <json> | Write data |
append | <id> <range> <json> | Append rows |
clear | <id> <range> | Clear range |
Formatting
| Command | Args | Description |
|---|---|---|
format | <id> <range> <formatJson> | Format cells |
getFormat | <id> <range> | Get cell formats |
borders | <id> <range> [styleJson] | Add borders |
copyFormat | <id> <source> <dest> | Copy format between ranges |
merge | <id> <range> | Merge cells |
unmerge | <id> <range> | Unmerge cells |
Layout
| Command | Args | Description |
|---|---|---|
resize | <id> <sheet> <cols|rows> <start> <end> <px> | Resize columns/rows |
autoResize | <id> <sheet> <startCol> <endCol> | Auto-fit columns |
freeze | <id> <sheet> [rows] [cols] | Freeze rows/columns |
Sheet Management
| Command | Args | Description |
|---|---|---|
create | <title> | Create spreadsheet |
info | <id> | Get metadata |
addSheet | <id> <title> | Add sheet tab |
deleteSheet | <id> <sheetName> | Delete sheet tab |
renameSheet | <id> <oldName> <newName> | Rename sheet tab |
Examples
# Read data
node scripts/sheets.js read "SPREADSHEET_ID" "Sheet1!A1:C10"
# Write data
node scripts/sheets.js write "SPREADSHEET_ID" "Sheet1!A1:B2" '[["Name","Score"],["Alice",95]]'
# Format cells (yellow bg, bold)
node scripts/sheets.js format "SPREADSHEET_ID" "Sheet1!A1:B2" '{"backgroundColor":{"red":255,"green":255,"blue":0},"textFormat":{"bold":true}}'
# Copy format from one range to another
node scripts/sheets.js copyFormat "SPREADSHEET_ID" "Sheet1!A1:C3" "Sheet1!D1:F3"
# Add borders
node scripts/sheets.js borders "SPREADSHEET_ID" "Sheet1!A1:C3"
# Resize columns to 150px
node scripts/sheets.js resize "SPREADSHEET_ID" "Sheet1" cols A C 150
# Auto-fit column widths
node scripts/sheets.js autoResize "SPREADSHEET_ID" "Sheet1" A Z
# Freeze first row and column
node scripts/sheets.js freeze "SPREADSHEET_ID" "Sheet1" 1 1
# Add new sheet tab
node scripts/sheets.js addSheet "SPREADSHEET_ID" "NewSheet"
Format Options
{
"backgroundColor": {"red": 255, "green": 255, "blue": 0},
"textFormat": {
"bold": true,
"italic": false,
"fontSize": 12,
"foregroundColor": {"red": 0, "green": 0, "blue": 0}
},
"horizontalAlignment": "CENTER",
"verticalAlignment": "MIDDLE",
"wrapStrategy": "WRAP"
}
Border Style
{
"style": "SOLID",
"color": {"red": 0, "green": 0, "blue": 0}
}
Border styles: DOTTED, DASHED, SOLID, SOLID_MEDIUM, SOLID_THICK, DOUBLE
Finding Spreadsheet ID
From URL: https://docs.google.com/spreadsheets/d/SPREADSHEET_ID/edit
Troubleshooting
- 403 Forbidden: Sheet not shared with service account email
- 404 Not Found: Wrong spreadsheet ID or sheet name