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 1. **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. 2. **Automation**: `formautomation.js` injects financial formulas and sends an immediate confirmation email. 3. **Review**: Staff reviews the row. Changing "Ready to Invoice" to "Yes" triggers `Invoice_Logic.js`. 4. **Verification**: The system checks for real-time bus conflicts. If clear, it converts "HOLDs" to "CONFIRMED" calendar events. 5. **Execution**: A PDF is generated, emailed to the client, and booking requests are sent to all relevant tour vendors. 6. **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.