craftos-dev 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