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.json
or credentials.json
in the skill directory
- Place in
~/.config/google-sheets/credentials.json
-
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
