Agent skill

google-docs-automation

Use when automating Google Workspace (Docs, Sheets, Slides) operations - covers authentication, common patterns, and best practices for gspread and googleapiclient

View SKILL.md on GitHub Repository
Stars 11
Forks 2

Install this agent skill to your Project

npx add-skill https://github.com/dbmcco/claude-agent-toolkit/tree/main/skills/google-docs-automation

SKILL.md

Google Docs/Sheets/Slides Automation

When to Use This Skill

Invoke this skill when you need to:

  • Automate Google Sheets operations (read, write, format, chart creation)
  • Generate or update Google Docs programmatically
  • Create or modify Google Slides presentations
  • Set up service account authentication for Google APIs
  • Build financial models, dashboards, or reports in Google Workspace
  • Integrate Google Workspace data with other systems

Core Principles

  1. Service Account Authentication: Always use service accounts for automation (never OAuth user credentials)
  2. Environment Variables: Store credentials and document IDs in .env files (never commit)
  3. Scope Management: Request only the minimum required scopes
  4. Error Handling: Wrap all API calls in try/except with specific error messages
  5. Rate Limits: Be aware of API quotas and implement backoff strategies for production use

Authentication Setup

Step 1: Service Account Credentials

python
from google.oauth2.service_account import Credentials
from dotenv import load_dotenv
import os

load_dotenv('.env')

# For Google Sheets (using gspread)
scope = [
    'https://spreadsheets.google.com/feeds',
    'https://www.googleapis.com/auth/drive'
]

creds_file = os.getenv('GOOGLE_SERVICE_ACCOUNT_FILE')
credentials = Credentials.from_service_account_file(creds_file, scopes=scope)

Step 2: Environment Configuration

Create .env file:

bash
GOOGLE_SERVICE_ACCOUNT_FILE=path/to/credentials.json
GOOGLE_SHEET_ID=your_sheet_id_here
GOOGLE_DOC_ID=your_doc_id_here

CRITICAL: Add .env and *.json credential files to .gitignore

Google Sheets Automation

Pattern: Read and Write Cells

python
import gspread

# Authorize and open spreadsheet
gc = gspread.authorize(credentials)
spreadsheet = gc.open_by_key(os.getenv('GOOGLE_SHEET_ID'))
worksheet = spreadsheet.worksheet('Sheet1')

# Read operations
value = worksheet.acell('A1').value
row_data = worksheet.row_values(1)
col_data = worksheet.col_values(1)
all_data = worksheet.get_all_values()

# Write operations
worksheet.update_acell('A1', 'New Value')
worksheet.update('A1:C3', [
    ['Name', 'Age', 'City'],
    ['Alice', 30, 'NYC'],
    ['Bob', 25, 'SF']
])

Pattern: Formatting and Styling

python
# Format cells
worksheet.format('A1:C1', {
    'backgroundColor': {'red': 0.2, 'green': 0.2, 'blue': 0.2},
    'textFormat': {'bold': True, 'foregroundColor': {'red': 1, 'green': 1, 'blue': 1}}
})

# Set column width
worksheet.set_column_width(1, 200)

# Merge cells
worksheet.merge_cells('A1:C1')

Pattern: Adding Charts

python
from googleapiclient.discovery import build

sheets_service = build('sheets', 'v4', credentials=credentials)

chart_request = {
    'addChart': {
        'chart': {
            'spec': {
                'title': 'Revenue vs Expenses',
                'basicChart': {
                    'chartType': 'LINE',
                    'legendPosition': 'RIGHT_LEGEND',
                    'axis': [
                        {'position': 'BOTTOM_AXIS', 'title': 'Month'},
                        {'position': 'LEFT_AXIS', 'title': 'Amount'}
                    ],
                    'series': [
                        {'series': {'sourceRange': {'sources': [{'sheetId': 0, 'startRowIndex': 0, 'endRowIndex': 12, 'startColumnIndex': 0, 'endColumnIndex': 1}]}}},
                        {'series': {'sourceRange': {'sources': [{'sheetId': 0, 'startRowIndex': 0, 'endRowIndex': 12, 'startColumnIndex': 1, 'endColumnIndex': 2}]}}}
                    ]
                }
            },
            'position': {'overlayPosition': {'anchorCell': {'sheetId': 0, 'rowIndex': 2, 'columnIndex': 5}}}
        }
    }
}

sheets_service.spreadsheets().batchUpdate(
    spreadsheetId=os.getenv('GOOGLE_SHEET_ID'),
    body={'requests': [chart_request]}
).execute()

Google Docs Automation

Pattern: Read Document with Comments

python
from googleapiclient.discovery import build

docs_service = build('docs', 'v1', credentials=credentials)
drive_service = build('drive', 'v3', credentials=credentials)

doc_id = os.getenv('GOOGLE_DOC_ID')

# Get document content
document = docs_service.documents().get(documentId=doc_id).execute()
title = document.get('title')
content = document.get('body', {}).get('content', [])

# Get comments
comments = drive_service.comments().list(
    fileId=doc_id,
    fields='comments(content,quotedFileContent,author,createdTime,resolved)'
).execute().get('comments', [])

for comment in comments:
    author = comment.get('author', {}).get('displayName')
    text = comment.get('content')
    quoted = comment.get('quotedFileContent', {}).get('value')
    print(f"{author}: {text} on '{quoted}'")

Pattern: Write/Update Document

python
# Insert text at beginning
requests = [{
    'insertText': {
        'location': {'index': 1},
        'text': 'New paragraph at start\n'
    }
}]

docs_service.documents().batchUpdate(
    documentId=doc_id,
    body={'requests': requests}
).execute()

# Format text
format_request = [{
    'updateTextStyle': {
        'range': {'startIndex': 1, 'endIndex': 20},
        'textStyle': {
            'bold': True,
            'fontSize': {'magnitude': 14, 'unit': 'PT'}
        },
        'fields': 'bold,fontSize'
    }
}]

docs_service.documents().batchUpdate(
    documentId=doc_id,
    body={'requests': format_request}
).execute()

Google Slides Automation

Pattern: Create Presentation

python
slides_service = build('slides', 'v1', credentials=credentials)

# Create new presentation
presentation = slides_service.presentations().create(
    body={'title': 'My Presentation'}
).execute()

presentation_id = presentation.get('presentationId')

# Add slide
requests = [{
    'createSlide': {
        'slideLayoutReference': {'predefinedLayout': 'TITLE_AND_BODY'}
    }
}]

response = slides_service.presentations().batchUpdate(
    presentationId=presentation_id,
    body={'requests': requests}
).execute()

Pattern: Update Slide Content

python
slide_id = response.get('replies')[0].get('createSlide').get('objectId')

# Add text to slide
text_requests = [{
    'insertText': {
        'objectId': slide_id,
        'insertionIndex': 0,
        'text': 'Slide Title'
    }
}]

slides_service.presentations().batchUpdate(
    presentationId=presentation_id,
    body={'requests': text_requests}
).execute()

Common Patterns and Best Practices

Pattern: List Worksheets

python
def list_worksheets(spreadsheet_id):
    """List all worksheets in a spreadsheet"""
    gc = gspread.authorize(credentials)
    spreadsheet = gc.open_by_key(spreadsheet_id)

    for worksheet in spreadsheet.worksheets():
        print(f"- {worksheet.title} (ID: {worksheet.id})")

Pattern: Error Handling

python
from gspread.exceptions import SpreadsheetNotFound, WorksheetNotFound
from googleapiclient.errors import HttpError

try:
    worksheet = spreadsheet.worksheet('Sheet1')
    value = worksheet.acell('A1').value
except SpreadsheetNotFound:
    print("Spreadsheet not found - check ID and sharing permissions")
except WorksheetNotFound:
    print("Worksheet 'Sheet1' does not exist")
except HttpError as e:
    print(f"API error: {e.resp.status} - {e.content}")
except Exception as e:
    print(f"Unexpected error: {e}")

Pattern: Batch Operations for Performance

python
# BAD: Multiple API calls in loop
for row in data:
    worksheet.update_acell(f'A{i}', row[0])  # Slow!

# GOOD: Single batch update
cell_list = worksheet.range('A1:A100')
for i, cell in enumerate(cell_list):
    cell.value = data[i][0]
worksheet.update_cells(cell_list)  # One API call

Security Checklist

  • Service account credentials stored in .env file
  • .env and *.json files in .gitignore
  • Minimum required scopes requested
  • Service account has appropriate sharing permissions on documents
  • No hardcoded document IDs in scripts (use environment variables)
  • Error handling includes specific error messages
  • Rate limiting considered for production use

Common Use Cases

Financial Modeling

  • Read current financial data from master spreadsheet
  • Update pro forma models with scenario planning
  • Generate formatted P&L statements
  • Create revenue vs expense charts

Dashboard Creation

  • Aggregate data from multiple sources
  • Build dynamic charts with real-time data
  • Format dashboards for executive presentation
  • Export dashboards to PDF via Drive API

Document Generation

  • Create customer-specific one-pagers
  • Update templated proposals with current data
  • Generate reports from spreadsheet data
  • Manage document comments and feedback

Presentation Automation

  • Build pitch decks from templates
  • Update slides with current financial data
  • Create standardized slide layouts
  • Export presentations for client delivery

References

See comprehensive examples in:

  • /path/to/projects/work/synth/google-automation/
  • Sheets: sheets/financial/, sheets/dashboards/
  • Docs: docs/onepagers/, docs/general/
  • Slides: presentations/

API Documentation

Recommended Agent Skills

Expand your agent's capabilities with these related and highly-rated skills.

dbmcco/claude-agent-toolkit

gemini-cli-extension-porting

Use when porting beads or superpowers workflows into Gemini CLI extensions or designing Gemini CLI command prompts that emulate multi-step agent workflows - covers extension layout, GEMINI.md context, command TOML patterns, and enforceable guardrails (tests/CI/pre-commit)

11 2
Explore
dbmcco/claude-agent-toolkit

rifts

11 2
Explore
dbmcco/claude-agent-toolkit

obsidian-vault-intelligence

Use when working with Obsidian vault via MCP - covers effective search patterns, note operations, intelligent queries, and cross-reference strategies for knowledge extraction

11 2
Explore
dbmcco/claude-agent-toolkit

keynote-slides

Build Keynote-style single-file HTML slide decks with brand-ready templates, minimal navigation, and Gemini nano banana media generation. Includes Narrative Engine integration for framework-driven deck creation with 17 proven storytelling structures and 5-agent review panel. Use when creating or editing slide decks, transforming content into presentations, or generating slide visuals.

11 2
Explore
dbmcco/claude-agent-toolkit

linear

Use when managing Linear issues, creating tasks from conversations, tracking work items, or querying project status - provides bash scripts to interact with Linear's GraphQL API without writing queries directly (project, gitignored)

11 2
Explore
dbmcco/claude-agent-toolkit

workspace-cleanup

Intelligent workspace cleanup using multi-signal detection (similarity, timestamps, references) to identify and archive clutter with two-stage safety review

11 2
Explore

Didn't find tool you were looking for?

Be as detailed as possible for better results