Google Sheets' 60 Delivery Deadline Trackers, Now Auto-Alerts Triggered by Bot

Before: Monday Morning's 'Delivery Date Anxiety'\n\nAs non-developers running a B2B business, we manage delivery schedules across multiple trading partners. Our Google Sheet always had roughly 60 active order items, but every Monday morning someone had to manually check each one.\n\nThe problem was clear:\n\n> Are there items past their scheduled delivery date with no confirmation email received?\n> Is this week's shipment stuck anywhere?\n> Which trading partner is responding slowly?\n\nAnswering these questions meant scrolling through the sheet, opening a calculator, and digging through email inboxes. It took about 1 hour and items were often missed.\n\n## After: Claude Code Bot's 'Auto Surveillance'\n\nWe wrote a simple automation script using Claude Code.\n\n```plaintext\nTask executed every morning at 7 AM\n1. Read all order items from Google Sheet\n2. Compare today's date against "Scheduled Delivery Date" column\n3. Filter items not completed within 3 days\n4. Send alerts via Slack (or email)\n5. Log status updates to a separate sheet\n```\n\nThe results were remarkable:\n\n- **Detection Speed**: 60 items scanned in 3 seconds\n- **Error Reduction**: Items previously missed are now auto-flagged\n- **Response Time**: Alerts arrive 3 hours before potential delays occur\n- **Team Peace of Mind**: Eliminates the "did we miss something?" anxiety\n\n### Rules the Bot Detects\n\n1. Delivery Progress Tracking\n - 3 days before deadline: Light orange warning\n - Deadline day: Red urgent alert\n - Past deadline: Multiple notifications\n\n2. Status-Based Filtering\n - "Pending" status longer than 2 weeks triggers verification request\n - "Approved" status without shipper contact triggers tracking\n\n3. Partner Performance Logging\n - Monthly average delay calculation\n - Auto-tag reliable trading partners\n\n## Unexpected Discovery\n\nIn week 2 of automation, we found an interesting pattern.\n\nOne trading partner consistently had contact delays on Thursdays. We realized their manager doesn't check email before leaving on Thursdays. Now we reach out Wednesday mornings instead.\n\nAnother finding: The bot's "urgent alert" accuracy hit 98%, meaning it only flagged items that actually became problems.\n\n## Why Non-Developers Could Build This\n\nThree reasons made this automation possible for us:\n\n1. **Claude Code's 「Question-to-Code」 Model**: We just showed the sheet structure and asked 「Can you find items delayed within 3 days?」 and got working code.\n\n2. **Seamless Google Sheet Integration**: API authentication was explained step-by-step by Claude.\n\n3. **Incremental Improvement**: We started simple (delay alerts only) and added one feature every one-two weeks (priority sorting, partner tracking).\n\n## Where We Are Now\n\nWhen that Monday morning Slack ping arrives, we're already prepared because the bot watched all night.\n\nThis is the power of 「preventive automation」 that non-developers can actually build.