Overview
This is an automated weather monitoring and early warning system built for a disaster-prone area in Indonesia. The bot watches a Google Spreadsheet for approved weather observations and delivers standardized alert messages to a public Telegram channel that serves the local community.
The problem it solves is simple but critical: during extreme weather, officers were spending 10 to 15 minutes manually formatting each alert message. That’s time they don’t have when conditions are deteriorating fast. The bot reduces that to under 30 seconds from approval to delivery.
I built this as a Python automation that runs continuously, polling for new approved records and handling the entire alert pipeline with zero manual formatting required.
Challenge
Weather early warning systems depend on speed and accuracy. The existing manual process failed on both counts:
- Formatting a single alert message took 10 to 15 minutes during extreme weather, when speed matters most
- Alert messages had inconsistent structure. Critical fields were sometimes missing, and the format changed depending on who wrote it
- There was no tracking of which observations had been communicated to the public. Officers couldn’t tell at a glance what had been sent
- No approval mechanism existed to prevent accidental broadcasts of unreviewed or incomplete data
Solution
Alert Pipeline
The core loop is straightforward: poll the spreadsheet every 10 minutes, find records marked as approved, format and send them.
- Generates 4-level severity warnings: NORMAL, WASPADA, SIAGA, AWAS
- Each alert follows a standardized template with all required fields, so nothing gets left out
- Auto-updates the record status to “Terkirim” (sent) after successful delivery, creating a clear audit trail
Approval Workflow
Officers stay in control. Nothing goes out without explicit approval through a simple dropdown in the spreadsheet.
- 4-status flow: Pending, Kirim (send), Terkirim (sent), or Skip
- This prevents accidental mass notifications from incomplete data while keeping the approval process as lightweight as possible
Resilience and Data Management
The bot runs continuously in a disaster-response context, so reliability isn’t optional.
- Exponential backoff retries with connection pooling and automatic credential refresh handle network interruptions gracefully
- Only the last 100 rows are fetched per cycle for API efficiency
- A monthly archival script keeps the spreadsheet lean long-term, preventing the slow creep of accumulated data
Results
- Alert delivery → Under 30 seconds from approval to public broadcast, down from 10-15 minutes of manual formatting
- Format consistency → 100% standardized compliance. Every alert follows the same template with all required fields
- Uptime → Continuous operation with automatic self-recovery from network and credential failures
- Operator effort → Single-click approval in a spreadsheet dropdown replaces manual message composition
