Google Sheets Skill

Interact with Google Sheets using a service account.

Setup (One-time)

1. Google Cloud Console:

   • Create/select a project
   • Enable "Google Sheets API"
   • Create a Service Account (IAM → Service Accounts → Create)
   • Download JSON key

2. 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

3. Share sheets with the service account email (found in JSON key as client_email)

4. 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