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

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:

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

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

# 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

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

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

# 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

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

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

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

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

# 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

• gspread: https://docs.gspread.org/
• Google Sheets API: https://developers.google.com/sheets/api
• Google Docs API: https://developers.google.com/docs/api
• Google Slides API: https://developers.google.com/slides/api