Overview
Automated weather monitoring and early warning system for a disaster-prone area. The bot monitors a Google Spreadsheet for approved observations and delivers standardized alert messages to a public Telegram channel serving the local community.
Challenge
- Manual alert formatting took 10-15 minutes per message during extreme weather
- Alert messages had inconsistent structure, with critical fields often missing
- No tracking of which observations were communicated to the public
- No approval mechanism to prevent accidental broadcasts of unreviewed data
Solution
Alert Pipeline
- Polls spreadsheet every 10 minutes for approved records
- Generates 4-level severity warnings: NORMAL, WASPADA, SIAGA, AWAS
- Auto-updates record status to ‘Terkirim’ after delivery
Approval Workflow
- 4-status flow: Pending → Kirim → Terkirim (or Skip)
- Officers retain full broadcast control via dropdown
- Prevents accidental mass notifications from incomplete data
Resilience & Data Management
- Exponential backoff retries with connection pooling and credential auto-refresh
- Only fetches last 100 rows per cycle for API efficiency
- Monthly archival script keeps spreadsheet lean long-term
Results
- Alert delivery: 10-15 min manual → under 30 seconds
- Format consistency: 100% standardized compliance
- Uptime: Continuous operation with automatic self-recovery
- Operator effort: Single-click approval replaces manual formatting
