Fairbanks KOA: Automated Caravan Reservation System

Fairbanks KOA: Automated Caravan Reservation & Operations System 📋 Project Overview The Fairbanks KOA Caravan Reservation & Automation System is a sophisticated, end-to-end business automation platform built on Google Workspace. It manages the entire lifecycle of high-volume caravan and group bookings for the Fairbanks / North Pole KOA campsite. The system transforms a complex manual process involving multiple vendors, bus scheduling, and financial tracking into a streamlined, error-resistant digital workflow. 🚀 Key Features 1. Intelligent Data Ingestion Sequential ID Generation: Uses a thread-safe LockService to generate unique, sequential Caravan IDs (e.g., AK-2026-042) even during simultaneous submissions. Dynamic Formula Injection: Automatically injects 30+ R1C1 spreadsheet formulas into new rows, allowing for real-time financial updates if staff manually adjust guest counts or dates. Real-Time Availability: The public-facing web form queries the live calendar to show AM/PM bus slot availability before a user even submits. 2. Advanced Scheduling & Conflict Management Multi-State Calendar Engine: Implements a "HOLD" vs "CONFIRMED" state system. New bookings create holds that prevent double-booking while staff review the request. Bus-Tour Mapping: Handles complex logic where multiple tours (e.g., Riverboat & Gold Dredge) can share a single bus rental slot. Bi-Directional Sync: Periodically synchronizes the Google Calendar with the internal spreadsheet database to ensure the system is aware of events added manually by staff. 3. Automated Financial & Document Pipeline PDF Invoice Generation: Merges live spreadsheet data into a Google Doc template, converts it to PDF, and stores it in a structured Drive folder. Dynamic Tag Replacement: Supports nested data and calculated totals through a custom regex-based merge tag engine. File Lifecycle Management: Automatically trashes obsolete invoices before regenerating new ones to prevent clutter. 4. Full-Lifecycle Communication Sequencer A daily automated "engine" manages 10+ distinct email milestones: Instant: Submission Confirmation. Staff Action: PDF Invoice & Vendor Booking Requests. 14 Days Post-Invoice: Deposit Reminder (if unpaid). 30 Days Pre-Arrival: Check-In Update Form link. 14 Days Pre-Arrival: Final Payment & Guest Count Verification. 7 Days Pre-Arrival: "Almost Here" Follow-up. 5 Days Pre-Arrival: Digital Arrival Packet (Bus schedule, vouchers, map). 2 Days Pre-Tour: Automated Vendor Re-Confirmation. 🛠️ Tech Stack Component Technology Core Logic Google Apps Script (JavaScript/V8) Database Google Sheets (Multi-sheet relational structure) User Interface HTML5 / CSS3 / JavaScript (Client-side Web App) File Handling Google Drive API & Google Docs API Communication Gmail API (HTML Templates & PDF Attachments) Scheduling Google Calendar API Infrastructure CLASP (Command Line Apps Script Projects) 🔄 Technical Workflow Ingestion: Wagon Master submits the Index.html web form. Code.js generates a unique ID, writes the data, and places "HOLD" markers on the bus calendar. Automation: formautomation.js injects financial formulas and sends an immediate confirmation email. Review: Staff reviews the row. Changing "Ready to Invoice" to "Yes" triggers Invoice_Logic.js. Verification: The system checks for real-time bus conflicts. If clear, it converts "HOLDs" to "CONFIRMED" calendar events. Execution: A PDF is generated, emailed to the client, and booking requests are sent to all relevant tour vendors. Maintenance: A 24/7 daily trigger (Reminder.js) scans the database and sends milestone-based emails and final vendor confirmations. 🏆 Project Outcomes Administrative Efficiency: Automated ~90% of repetitive customer correspondence and vendor booking tasks. Data Integrity: Eliminated manual calculation errors through script-injected formulas and automated ID management. Operational Security: Prevented over-booking of campsite resources (bus/staff) via the AM/PM conflict detection engine. Professional Branding: Provided customers with high-fidelity PDF invoices and consistent, timely communication sequences. Scalability: The system comfortably handles dozens of simultaneous caravans without increasing staff workload. ⚠️ Challenges Overcome Concurrency & Race Conditions: Solved through the implementation of LockService to ensure that sequential ID generation and spreadsheet writes remain atomic during peak booking times. Complex Dependencies: Developed a "Tour Mapping" logic to handle groups where one bus rental covers multiple activity stops, ensuring the calendar reflects actual resource usage. Platform Limits: Optimized the Daily Reminder engine to handle large row counts within the 6-minute Google Apps Script execution timeout. Dynamic Data Accuracy: Implemented a "Sync then Check" strategy for invoicing—ensuring the system pulls fresh calendar data immediately before performing a conflict check. User Error Resiliency: Created automated header cleanup and data validation tools to prevent stray whitespace or typos from breaking the automation pipeline.