Menu
Use when working with Google Sheets data output — writing arrest records, managing tabs, handling schema, debugging API errors.
Install with Codex or Claude Copy this prompt, paste it into Codex, Claude, or another assistant, and let it review the skill page and install it for you.
Based on SOC occupation classification
| name | google-sheets-integration |
| description | Use when working with Google Sheets data output — writing arrest records, managing tabs, handling schema, debugging API errors. |
| source | Adapted from googleworkspace/cli GWS skills |
Google Sheets is our primary arrest data store. Each county gets its own tab.
The core/writers/sheets_writer.py module handles all writes using the
Google Sheets API v4 via a service account.
Solver → returns List[dict] → Runner → SheetsWriter.append_rows()
→ Google Sheets API v4
→ Sheet: "121z5R...IV_0E"
→ Tab: "{County}" (auto-created)
Our standard HEADER_ROW (defined in sheets_writer.py):
HEADER_ROW = [
'Booking_Number', 'County', 'State', 'Facility',
'Full_Name', 'First_Name', 'Last_Name', 'Middle_Name',
'DOB', 'Sex', 'Race', 'Height', 'Weight',
'Hair_Color', 'Eye_Color',
'Address', 'City', 'Zipcode',
'Booking_Date', 'Release_Date', 'Status',
'Charges', 'Bond_Amount', 'Bond_Type', 'Bond_Status',
'Agency', 'Case_Number',
'Court_Date', 'Court_Type', 'Court_Location',
'Mugshot_URL', 'Detail_URL', 'Scrape_Timestamp',
'Person_ID', 'Notes', 'Phone', 'Email',
'Emergency_Contact', 'Occupation'
]
Every write checks for existing Booking_Number + County combination:
# The writer handles dedup internally
writer = SheetsWriter(sheet_id, service_account_path)
new_count = writer.append_rows(county_tab, records)
# new_count = number of genuinely new records appended
Solvers return dicts — the writer maps them to columns:
record = {
'Booking_Number': '2026-04140123',
'County': 'Collier',
'State': 'FL',
'Full_Name': 'SMITH, JOHN A',
'First_Name': 'JOHN',
'Last_Name': 'SMITH',
'DOB': '01/15/1990',
'Charges': 'Battery | Disorderly Conduct',
'Bond_Amount': '5000.00',
'Booking_Date': '04/14/2026',
'Scrape_Timestamp': '2026-04-14 15:30:00',
}
# Missing fields default to empty string
GOOGLE_SERVICE_ACCOUNT_KEYcreds/service-account-key.json| Issue | Cause | Fix |
|---|---|---|
HttpError 403: insufficient permissions | Sheet not shared with SA | Share sheet with SA email |
HttpError 429: Rate limit exceeded | Too many API calls | Add retry with exponential backoff |
| Tab not found | County tab doesn't exist yet | Writer auto-creates tabs |
| Wrong column count | Schema mismatch | Verify HEADER_ROW matches |
| Duplicate records | Dedup check failed | Verify Booking_Number is populated |
Booking_Number — it's the dedup keyCounty — second part of dedup keyMM/DD/YYYY or YYYY-MM-DD (both work)Charge 1 | Charge 2 | Charge 3"5000.00" not 5000.00 (avoids float issues)datetime.now().strftime('%Y-%m-%d %H:%M:%S')Use this skill when expanding the scraper network to new Florida counties. Contains the 67-county expansion roadmap, prioritization framework, site reconnaissance procedure, and lead analysis strategy. The goal is total Florida coverage for maximum bail bond lead generation.
Use this skill when creating a new county arrest scraper, modifying an existing solver, or adding a new county to the scraping pipeline. Contains the canonical architecture, naming conventions, and integration checklist.
Non-negotiable rules, safe refactoring patterns, shared code modification guidelines, secrets management, config hierarchy, and schema change process for the swfl-arrest-scrapers repo. Read this before modifying any core infrastructure.
Testing patterns, fixture guidelines, parser test templates, smoke tests, and red-green TDD methodology for the scraper pipeline. Use when writing tests for new counties, verifying core/ changes, or debugging test failures.
Google Sheets: Read and write spreadsheets via the Sheets API v4.
Systematically strengthen scraper pipelines against network failures, anti-bot measures, data edge cases, and real-world brittleness that breaks idealized scraper designs.