{
  "id": "HdSUjNHdVNGiveKd",
  "meta": {
    "instanceId": "dd69efaf8212c74ad206700d104739d3329588a6f3f8381a46a481f34c9cc281"
  },
  "name": "Automated Journey Scheduler & SMS/Email Alerts",
  "tags": [],
  "nodes": [
    {
      "id": "2c9cace5-a7fa-4a0f-975d-c10c7f4405ad",
      "name": "Daily Travel Check",
      "type": "n8n-nodes-base.cron",
      "position": [
        -448,
        240
      ],
      "parameters": {},
      "typeVersion": 1
    },
    {
      "id": "5e304a56-4b9c-42dc-84d9-b63f336a5a99",
      "name": "Read Travel Itinerary",
      "type": "n8n-nodes-base.microsoftExcel",
      "position": [
        -224,
        240
      ],
      "parameters": {
        "filters": {}
      },
      "credentials": {
        "microsoftExcelOAuth2Api": {
          "id": "jevPChvDpEJk6W9v",
          "name": "Microsoft Excel account - test"
        }
      },
      "typeVersion": 2
    },
    {
      "id": "e41bf01b-ddc7-47cb-b0b8-027016b22fca",
      "name": "Filter Today's Trips",
      "type": "n8n-nodes-base.code",
      "position": [
        0,
        240
      ],
      "parameters": {
        "jsCode": "// Get current date and time\nconst now = new Date();\nconst today = now.toISOString().split('T')[0];\nconst currentHour = now.getHours();\n\n// Get all itinerary data\nconst itineraryData = $input.all();\n\n// Filter trips for today and upcoming reminders\nconst todayTrips = itineraryData.filter(item => {\n  const tripDate = item.json['Trip Date'];\n  const departureTime = item.json['Departure Time'];\n  const reminderHours = item.json['Reminder Hours'] || 24;\n  \n  // Check if trip is today\n  if (tripDate === today) {\n    // Parse departure time\n    const [hours, minutes] = departureTime.split(':');\n    const departureHour = parseInt(hours);\n    \n    // Check if we need to send reminder\n    const hoursUntilTrip = departureHour - currentHour;\n    \n    // Send reminder if within reminder window\n    if (hoursUntilTrip > 0 && hoursUntilTrip <= reminderHours) {\n      return true;\n    }\n  }\n  \n  return false;\n});\n\n// Also get tomorrow's trips for evening prep\nconst tomorrow = new Date(now);\ntomorrow.setDate(tomorrow.getDate() + 1);\nconst tomorrowDate = tomorrow.toISOString().split('T')[0];\n\nconst tomorrowTrips = itineraryData.filter(item => {\n  return item.json['Trip Date'] === tomorrowDate && currentHour >= 18; // After 6 PM\n});\n\n// Combine today's reminders and tomorrow's prep\nconst tripsToProcess = [...todayTrips, ...tomorrowTrips];\n\nreturn tripsToProcess.map(item => ({\n  json: {\n    ...item.json,\n    reminderType: todayTrips.includes(item) ? 'today' : 'tomorrow',\n    currentTime: now.toISOString()\n  }\n}));"
      },
      "typeVersion": 2
    },
    {
      "id": "a978bf0f-96e8-4257-abd0-a10b19dc3143",
      "name": "Has Trips Today?",
      "type": "n8n-nodes-base.if",
      "position": [
        224,
        128
      ],
      "parameters": {
        "conditions": {
          "string": [
            {
              "value1": "={{$json['Trip Name']}}",
              "operation": "isNotEmpty"
            }
          ]
        }
      },
      "typeVersion": 1
    },
    {
      "id": "bcd8b022-52db-4095-8f69-6c95f8b1135f",
      "name": "Read Traveler Contacts",
      "type": "n8n-nodes-base.microsoftExcel",
      "position": [
        448,
        128
      ],
      "parameters": {
        "filters": {}
      },
      "credentials": {
        "microsoftExcelOAuth2Api": {
          "id": "jevPChvDpEJk6W9v",
          "name": "Microsoft Excel account - test"
        }
      },
      "typeVersion": 2
    },
    {
      "id": "586c2dcb-e203-4817-874b-c52480abe596",
      "name": "Create Traveler Reminders",
      "type": "n8n-nodes-base.code",
      "position": [
        656,
        240
      ],
      "parameters": {
        "jsCode": "// Get trip data and traveler contacts\nconst tripData = $('Filter Today\\'s Trips').all();\nconst travelerContacts = $input.all();\n\n// Create reminder messages for each trip\nconst reminders = [];\n\ntripData.forEach(tripItem => {\n  const tripInfo = tripItem.json;\n  \n  // Find travelers assigned to this trip\n  const assignedTravelers = travelerContacts.filter(traveler => {\n    const travelerTrips = traveler.json['Assigned Trips'] || '';\n    return travelerTrips.includes(tripInfo['Trip Name']);\n  });\n  \n  // Create reminder for each assigned traveler\n  assignedTravelers.forEach(traveler => {\n    const reminderMessage = tripInfo.reminderType === 'today' \n      ? `✈️ Travel Reminder: Your \"${tripInfo['Trip Name']}\" trip departs at ${tripInfo['Departure Time']} today!\\n\\n📍 From: ${tripInfo['Departure Location'] || 'TBD'}\\n📍 To: ${tripInfo['Destination'] || 'TBD'}\\n✈️ Flight/Transport: ${tripInfo['Flight Number'] || 'TBD'}\\n🏨 Accommodation: ${tripInfo['Hotel'] || 'TBD'}\\n\\n⚠️ Please arrive at the departure point 2 hours early. Don't forget your passport and documents!`\n      : `📅 Tomorrow's Trip: \"${tripInfo['Trip Name']}\" departs at ${tripInfo['Departure Time']}\\n\\n📍 From: ${tripInfo['Departure Location'] || 'TBD'}\\n📍 To: ${tripInfo['Destination'] || 'TBD'}\\n✈️ Flight/Transport: ${tripInfo['Flight Number'] || 'TBD'}\\n🏨 Accommodation: ${tripInfo['Hotel'] || 'TBD'}\\n\\n💡 Tip: Pack tonight and prepare your documents!`;\n    \n    reminders.push({\n      json: {\n        travelerName: `${traveler.json['First Name']} ${traveler.json['Last Name']}`,\n        travelerEmail: traveler.json['Email'],\n        travelerPhone: traveler.json['Phone'],\n        tripName: tripInfo['Trip Name'],\n        departureTime: tripInfo['Departure Time'],\n        tripDate: tripInfo['Trip Date'],\n        departureLocation: tripInfo['Departure Location'],\n        destination: tripInfo['Destination'],\n        flightNumber: tripInfo['Flight Number'],\n        hotel: tripInfo['Hotel'],\n        tripDuration: tripInfo['Duration (Days)'],\n        reminderType: tripInfo.reminderType,\n        message: reminderMessage,\n        preferredContact: traveler.json['Preferred Contact'] || 'email'\n      }\n    });\n  });\n});\n\nreturn reminders;"
      },
      "typeVersion": 2
    },
    {
      "id": "7773868f-847a-4e2c-b3f8-d9c46342235b",
      "name": "Split Into Batches",
      "type": "n8n-nodes-base.splitInBatches",
      "position": [
        1088,
        240
      ],
      "parameters": {
        "options": {},
        "batchSize": 10
      },
      "typeVersion": 3
    },
    {
      "id": "4b4296c2-4710-49e0-9272-c5dc39e4e7d2",
      "name": "Email or SMS?",
      "type": "n8n-nodes-base.if",
      "position": [
        1312,
        240
      ],
      "parameters": {
        "conditions": {
          "string": [
            {
              "value1": "={{$json.preferredContact}}",
              "value2": "email"
            }
          ]
        }
      },
      "typeVersion": 1
    },
    {
      "id": "0c27ad59-6f7b-49ea-93a9-93529773438f",
      "name": "Prepare Email Reminders",
      "type": "n8n-nodes-base.code",
      "position": [
        1536,
        128
      ],
      "parameters": {
        "jsCode": "// Prepare email data for trip reminders\nconst reminderData = $input.all();\n\nconst emailsToSend = reminderData.map(item => {\n  const data = item.json;\n  \n  const subject = data.reminderType === 'today' \n    ? `✈️ Travel Reminder: ${data.tripName} Today`\n    : `📅 Tomorrow's Trip: ${data.tripName}`;\n  \n  const htmlBody = `\n    \n    \n    \n        \n    \n    \n        
container\">\n            
\n                
✈️ Travel ${data.reminderType === 'today' ? 'Reminder' : 'Preview'}
\n            
\n            
\n                
Hello ${data.travelerName},
\n                
${data.reminderType === 'today' ? 'Your trip is happening today!' : 'Get ready for tomorrow\\'s adventure!'}
\n                \n                
\n                    
✈️ Trip: ${data.tripName}
\n                    
🕐 Departure: ${data.departureTime}
\n                    
📅 Date: ${data.tripDate}
\n                    
📍 From: ${data.departureLocation || 'TBD'}
\n                    
📍 To: ${data.destination || 'TBD'}
\n                    
🛫 Flight/Transport: ${data.flightNumber || 'TBD'}
\n                    
🏨 Hotel: ${data.hotel || 'TBD'}
\n                    
📆 Duration: ${data.tripDuration || 'N/A'} days
\n                
\n                \n                
\n                    📋 Pre-Travel Checklist:\n                    
\n                        
Passport and travel documents
\n                        
Travel insurance documents
\n                        
Hotel confirmations and bookings
\n                        
Medications and toiletries
\n                        
Weather-appropriate clothing
\n                        
Phone charger and adapters
\n                    
\n                
\n                \n                ${data.reminderType === 'today' \n                  ? '
⚠️ Please arrive at the departure point 2 hours early!
'\n                  : '
💡 Tip: Pack and prepare your documents tonight for a stress-free departure tomorrow!
'\n                }\n                \n                
Have a wonderful trip!
\n            
\n            
\n                
This is an automated reminder from your travel management system.
\n            
\n        
\n    \n    \n  `;\n  \n  return {\n    json: {\n      to: data.travelerEmail,\n      subject: subject,\n      body: data.message,\n      html: htmlBody,\n      travelerName: data.travelerName,\n      tripName: data.tripName\n    }\n  };\n});\n\nreturn emailsToSend;"
      },
      "typeVersion": 2
    },
    {
      "id": "71d0ce98-bcb5-4b70-9be8-a3bc7264c8a7",
      "name": "Prepare SMS Reminders",
      "type": "n8n-nodes-base.code",
      "position": [
        1536,
        336
      ],
      "parameters": {
        "jsCode": "// Prepare SMS data for travel reminders\nconst reminderData = $input.all();\n\nconst smsToSend = reminderData.map(item => {\n  const data = item.json;\n  \n  const smsMessage = data.reminderType === 'today'\n    ? `✈️ Travel Reminder: \"${data.tripName}\" departs at ${data.departureTime} today from ${data.departureLocation || 'TBD'}. Arrive 2 hours early! Flight: ${data.flightNumber || 'N/A'}`\n    : `📅 Tomorrow: \"${data.tripName}\" departs at ${data.departureTime} from ${data.departureLocation || 'TBD'}. Pack tonight! (${data.tripDuration || '?'} days)`;\n  \n  return {\n    json: {\n      to: data.travelerPhone,\n      message: smsMessage,\n      travelerName: data.travelerName,\n      tripName: data.tripName\n    }\n  };\n});\n\nreturn smsToSend;"
      },
      "typeVersion": 2
    },
    {
      "id": "bd8a65eb-1712-42ce-82de-f9b6525a2a00",
      "name": "Sync to Google Calendar",
      "type": "n8n-nodes-base.googleCalendar",
      "position": [
        448,
        336
      ],
      "parameters": {
        "end": "={{$json['Trip Date']}}T{{(parseInt($json['Departure Time'].split(':')[0]) + 12).toString().padStart(2, '0')}}:{{$json['Departure Time'].split(':')[1].padStart(2, '0')}}:00",
        "start": "={{$json['Trip Date']}}T{{$json['Departure Time'].split(':')[0].padStart(2, '0')}}:{{$json['Departure Time'].split(':')[1].padStart(2, '0')}}:00",
        "calendar": {
          "__rl": true,
          "mode": "id",
          "value": "abc@google.com"
        },
        "additionalFields": {}
      },
      "credentials": {
        "googleCalendarOAuth2Api": {
          "id": "6ldLmzzYtaqng4pw",
          "name": "Google Calendar account - test"
        }
      },
      "typeVersion": 1
    },
    {
      "id": "7cd24c85-ebc9-488a-ba09-0d038aed0600",
      "name": "Read Reminder Log",
      "type": "n8n-nodes-base.microsoftExcel",
      "position": [
        1744,
        240
      ],
      "parameters": {
        "filters": {}
      },
      "credentials": {
        "microsoftExcelOAuth2Api": {
          "id": "jevPChvDpEJk6W9v",
          "name": "Microsoft Excel account - test"
        }
      },
      "typeVersion": 2
    },
    {
      "id": "fec45952-9b13-4611-89da-5dda16dc9895",
      "name": "Update Reminder Log",
      "type": "n8n-nodes-base.code",
      "position": [
        1968,
        240
      ],
      "parameters": {
        "jsCode": "// Get existing log data\nconst existingLogs = $input.all();\n\n// Get sent reminders data\nconst sentEmails = $('Prepare Email Reminders').all() || [];\nconst sentSMS = $('Prepare SMS Reminders').all() || [];\n\n// Create log entries for sent reminders\nconst newLogEntries = [];\n\n// Log email reminders\nsentEmails.forEach(email => {\n  newLogEntries.push({\n    'Log ID': 'LOG-' + Date.now() + '-' + Math.random().toString(36).substr(2, 9),\n    'Timestamp': new Date().toISOString(),\n    'Traveler Name': email.json.travelerName,\n    'Trip Name': email.json.tripName,\n    'Contact Method': 'Email',\n    'Contact Info': email.json.to,\n    'Status': 'Sent',\n    'Reminder Type': 'Travel Reminder',\n    'Message Preview': email.json.subject\n  });\n});\n\n// Log SMS reminders\nsentSMS.forEach(sms => {\n  newLogEntries.push({\n    'Log ID': 'LOG-' + Date.now() + '-' + Math.random().toString(36).substr(2, 9),\n    'Timestamp': new Date().toISOString(),\n    'Traveler Name': sms.json.travelerName,\n    'Trip Name': sms.json.tripName,\n    'Contact Method': 'SMS',\n    'Contact Info': sms.json.to,\n    'Status': 'Sent',\n    'Reminder Type': 'Travel Reminder',\n    'Message Preview': sms.json.message.substring(0, 50) + '...'\n  });\n});\n\n// Combine existing logs with new entries\nconst allLogs = [...existingLogs, ...newLogEntries];\n\nreturn allLogs.map(item => ({ json: item }));"
      },
      "typeVersion": 2
    },
    {
      "id": "3b8caca5-4c45-484b-860f-17ef4ea2566b",
      "name": "Save Reminder Log",
      "type": "n8n-nodes-base.microsoftExcel",
      "position": [
        2192,
        240
      ],
      "parameters": {
        "options": {},
        "resource": "worksheet",
        "workbook": {
          "__rl": true,
          "mode": "id",
          "value": "3456yuhh"
        },
        "operation": "append",
        "worksheet": {
          "__rl": true,
          "mode": "id",
          "value": "=23456yuytrewerfgn"
        }
      },
      "credentials": {
        "microsoftExcelOAuth2Api": {
          "id": "jevPChvDpEJk6W9v",
          "name": "Microsoft Excel account - test"
        }
      },
      "typeVersion": 2
    },
    {
      "id": "aed8c63f-1c9d-43a3-b514-8b971303a377",
      "name": "Wait For Data",
      "type": "n8n-nodes-base.wait",
      "position": [
        864,
        240
      ],
      "webhookId": "fbd474eb-c62b-4953-bf0f-a745253bfcb0",
      "parameters": {
        "amount": 15
      },
      "typeVersion": 1.1
    },
    {
      "id": "c32a8171-99f7-4a2f-9b13-c70de6b93e02",
      "name": "Sticky Note",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        288,
        -256
      ],
      "parameters": {
        "color": 3,
        "width": 624,
        "height": 272,
        "content": "## What This Workflow Does\n\n* Automatically checks travel itineraries every day\n* Identifies today's trips and upcoming departures\n* Syncs trip information to Google Calendar\n* Sends personalized reminders to assigned travelers\n* Tracks reminder delivery status and logs activities\n* Handles both email and SMS notification preferences\n* Provides pre-travel checklists and booking confirmations\n* Manages multi-day trip schedules and activities"
      },
      "typeVersion": 1
    },
    {
      "id": "8bb24600-34ec-4f10-a221-4d900ad777b4",
      "name": "Sticky Note1",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        336,
        -576
      ],
      "parameters": {
        "width": 560,
        "height": 240,
        "content": "## Essential Prerequisites\n\n* Travel itinerary database/Excel file with trip assignments\n* Traveler contact database with email and phone numbers\n* Google Calendar API access and credentials\n* SMTP server for email notifications\n* SMS service provider (Twilio, Nexmo, etc.) for text reminders\n* Reminder log file for tracking sent notifications\n* Booking confirmation system (flight, hotel, transport)"
      },
      "typeVersion": 1
    },
    {
      "id": "24480f08-45ae-418e-a086-aaea9b8648a3",
      "name": "Sticky Note2",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        960,
        -672
      ],
      "parameters": {
        "color": 4,
        "width": 512,
        "height": 816,
        "content": "## Required Data Files\n\n### trip_itinerary.xlsx:\n* Trip ID | Trip Name | Date | Departure Time | Duration\n* Departure Location | Destination | Hotel | Flight Number\n* Assigned Travelers | Status | Booking Reference | Cost\n\n### traveler_contacts.xlsx:\n* Traveler ID | First Name | Last Name | Email | Phone\n* Preferred Contact | Assigned Trips | Passport Number | Emergency Contact\n\n### reminder_log.xlsx:\n* Log ID | Date | Traveler ID | Trip ID | Contact Method\n* Status | Sent Time | Message Preview | Confirmation\n\n## Key Features\n\n* ⏰ **Daily Automation**: Runs automatically every day at scheduled times\n* 📅 **Calendar Sync**: Syncs trips to Google Calendar for easy viewing\n* 📧 **Smart Reminders**: Sends email or SMS based on traveler preference\n* 👥 **Batch Processing**: Handles multiple travelers efficiently\n* 📊 **Activity Logging**: Tracks all reminder activities and delivery status\n* 🔄 **Duplicate Prevention**: Avoids sending multiple reminders\n* 📱 **Multi-Channel**: Supports both email and SMS notifications\n* ✈️ **Travel-Specific**: Includes flight numbers, locations, accommodation details\n* 📋 **Pre-Travel Checklist**: Provides comprehensive packing and document reminders\n* 🌍 **Multi-Destination**: Manages complex multi-stop itineraries\n"
      },
      "typeVersion": 1
    },
    {
      "id": "f3b7fd2c-3fd6-4fc1-aff2-94241fb42a28",
      "name": "Sticky Note3",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -464,
        -528
      ],
      "parameters": {
        "color": 5,
        "width": 640,
        "height": 416,
        "content": "## Main Components\n\n* **Daily Travel Check** - Triggers daily to check travel itineraries\n* **Read Travel Itinerary** - Retrieves today's trips and bookings from database/Excel\n* **Filter Today's Trips** - Identifies trips departing today and upcoming activities\n* **Has Trips Today?** - Checks if there are any trips scheduled\n* **Read Traveler Contacts** - Gets traveler contact information for assigned trips\n* **Sync to Google Calendar** - Creates/updates trip events in Google Calendar\n* **Create Traveler Reminders** - Generates personalized reminder messages with travel details\n* **Split Into Batches** - Processes reminders in manageable batches\n* **Email or SMS?** - Routes based on traveler communication preferences\n* **Prepare Email Reminders** - Creates detailed email reminder content with checklists\n* **Prepare SMS Reminders** - Creates SMS reminder content optimized for text\n* **Read Reminder Log** - Checks previous reminder history\n* **Update Reminder Log** - Records sent reminders with timestamps\n* **Save Reminder Log** - Saves updated log data for audit trail"
      },
      "typeVersion": 1
    }
  ],
  "active": false,
  "pinData": {},
  "settings": {
    "executionOrder": "v1"
  },
  "versionId": "c42adac1-72fc-44c6-bcc9-b314a49fa442",
  "connections": {
    "Email or SMS?": {
      "main": [
        [
          {
            "node": "Prepare Email Reminders",
            "type": "main",
            "index": 0
          }
        ],
        [
          {
            "node": "Prepare SMS Reminders",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Wait For Data": {
      "main": [
        [
          {
            "node": "Split Into Batches",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Has Trips Today?": {
      "main": [
        [
          {
            "node": "Read Traveler Contacts",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Read Reminder Log": {
      "main": [
        [
          {
            "node": "Update Reminder Log",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Daily Travel Check": {
      "main": [
        [
          {
            "node": "Read Travel Itinerary",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Split Into Batches": {
      "main": [
        [
          {
            "node": "Email or SMS?",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Update Reminder Log": {
      "main": [
        [
          {
            "node": "Save Reminder Log",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Filter Today's Trips": {
      "main": [
        [
          {
            "node": "Has Trips Today?",
            "type": "main",
            "index": 0
          },
          {
            "node": "Sync to Google Calendar",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Prepare SMS Reminders": {
      "main": [
        [
          {
            "node": "Read Reminder Log",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Read Travel Itinerary": {
      "main": [
        [
          {
            "node": "Filter Today's Trips",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Read Traveler Contacts": {
      "main": [
        [
          {
            "node": "Create Traveler Reminders",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Prepare Email Reminders": {
      "main": [
        [
          {
            "node": "Read Reminder Log",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Sync to Google Calendar": {
      "main": [
        [
          {
            "node": "Create Traveler Reminders",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Create Traveler Reminders": {
      "main": [
        [
          {
            "node": "Wait For Data",
            "type": "main",
            "index": 0
          }
        ]
      ]
    }
  }
}