EARLY BETA

Broken Equipment Report — Google Sheet → Hospital DB (daily)

Automated pipeline to ingest reports of broken equipment from a Google Sheet, enrich them with canonical equipment labels (from the Tokenizing broken items repo), and push daily updates into the hospital report database so staff can edit/view reports inside the hospital system while new items continue to be appended each day.

Key goals

• Fast capture of broken items by frontline staff (Google Form → Google Sheet).
• Daily sync to hospital database (row upsert) so the hospital EHR/asset DB has the live list and staff can annotate / close tickets.
• Maintain provenance: each uploaded row contains original timestamp, uploader id, canonical equipment token (from Tokenizing broken items), and audit metadata.

Repo contents

• pipeline/
  • sheet_to_db.py — main daily sync script (Python).
  • config.example.yml — DB + Google API config template.
  • requirements.txt
• tokenizing/ — (external repo) mapping of equipment labels → canonical tokens (pull as submodule or pre-copy).
• powershell/
  • Gen Merged Broken Report.ps1 — merges daily Excel exports (existing artifact).
  • move_old_excels.ps1 — housekeeping script.
• Bot.py — helper to generate form links from a CSV (present in uploads). :contentReference[oaicite:1]{index=1}

High-level flow

1. Frontline staff submit a Google Form → responses stored in Google Sheet.
2. Daily cron / Windows Task Scheduler runs sheet_to_db.py.
3. Script reads new sheet rows since last-run, maps free-text equipment to canonical token using tokenizing/ mapping, then upserts rows into hospital DB (via ODBC or REST API).
4. Hospital staff edit rows in the hospital DB (status, assigned tech, resolution notes). Next daily run preserves those edits and adds new rows.

Requirements

• Python 3.9+
• Packages listed in requirements.txt (gspread, pandas, pyyaml, sqlalchemy / pyodbc or requests)
• Service account JSON for Google Sheets (gspread) or OAuth creds
• DB credentials (ODBC/SQLAlchemy URI or REST API token)
• tokenizing/ data: CSV or DB table mapping raw labels → canonical tokens

Deployment

• Linux: systemd timer or cron job running python pipeline/sheet_to_db.py daily.
• Windows: Task Scheduler calling run_daily.bat which runs the same Python script.

Tutorial

-Coming soon...

Audit & troubleshooting

• Each upsert writes source_row_id, source_timestamp, synced_at, and synced_by to the hospital DB.
• Script writes logs/sheet_to_db_YYYYMMDD.log.

Where Tokenizing broken items fits

• The tokenizing/ repo provides canonical equipment types + regex/alias rules.