自动化学生课程安排与日历提醒

• •无特殊前置要求，导入即可使用

{
  "id": "JPdXXuF2DVCMU818",
  "meta": {
    "instanceId": "dd69efaf8212c74ad206700d104739d3329588a6f3f8381a46a481f34c9cc281",
    "templateCredsSetupCompleted": true
  },
  "name": "自动化学生课程安排与日历提醒",
  "tags": [],
  "nodes": [
    {
      "id": "7465b9dc-b93d-486c-bffc-f58cbbb95096",
      "name": "每日课表检查",
      "type": "n8n-nodes-base.cron",
      "position": [
        -460,
        20
      ],
      "parameters": {},
      "typeVersion": 1
    },
    {
      "id": "06ad7a4a-a202-4741-9631-0431071d8a7e",
      "name": "读取课程安排",
      "type": "n8n-nodes-base.microsoftExcel",
      "position": [
        -240,
        20
      ],
      "parameters": {
        "filters": {}
      },
      "credentials": {
        "microsoftExcelOAuth2Api": {
          "id": "jevPChvDpEJk6W9v",
          "name": "Microsoft Excel account - test"
        }
      },
      "typeVersion": 2
    },
    {
      "id": "e9a81260-0a7a-4682-a197-008ec53df730",
      "name": "筛选今日课程",
      "type": "n8n-nodes-base.code",
      "position": [
        -20,
        20
      ],
      "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 schedule data\nconst scheduleData = $input.all();\n\n// Filter classes for today and upcoming reminders\nconst todayClasses = scheduleData.filter(item => {\n  const classDate = item.json['Class Date'];\n  const classTime = item.json['Class Time'];\n  const reminderTime = item.json['Reminder Time (Hours)'] || 1;\n  \n  // Check if class is today\n  if (classDate === today) {\n    // Parse class time\n    const [hours, minutes] = classTime.split(':');\n    const classHour = parseInt(hours);\n    \n    // Check if we need to send reminder\n    const hoursUntilClass = classHour - currentHour;\n    \n    // Send reminder if within reminder window\n    if (hoursUntilClass > 0 && hoursUntilClass <= reminderTime) {\n      return true;\n    }\n  }\n  \n  return false;\n});\n\n// Also get tomorrow's classes for evening prep\nconst tomorrow = new Date(now);\ntomorrow.setDate(tomorrow.getDate() + 1);\nconst tomorrowDate = tomorrow.toISOString().split('T')[0];\n\nconst tomorrowClasses = scheduleData.filter(item => {\n  return item.json['Class Date'] === tomorrowDate && currentHour >= 18; // After 6 PM\n});\n\n// Combine today's reminders and tomorrow's prep\nconst classesToProcess = [...todayClasses, ...tomorrowClasses];\n\nreturn classesToProcess.map(item => ({\n  json: {\n    ...item.json,\n    reminderType: todayClasses.includes(item) ? 'today' : 'tomorrow',\n    currentTime: now.toISOString()\n  }\n}));"
      },
      "typeVersion": 2
    },
    {
      "id": "a7fe4b25-abcc-42e9-9da2-5692a9a302b5",
      "name": "今日有课吗？",
      "type": "n8n-nodes-base.if",
      "position": [
        200,
        -80
      ],
      "parameters": {
        "conditions": {
          "string": [
            {
              "value1": "={{$json['Class Name']}}",
              "operation": "isNotEmpty"
            }
          ]
        }
      },
      "typeVersion": 1
    },
    {
      "id": "0fcf7fb2-d8e4-4db3-9ef8-b4990792c483",
      "name": "读取学生联系方式",
      "type": "n8n-nodes-base.microsoftExcel",
      "position": [
        420,
        -80
      ],
      "parameters": {
        "filters": {}
      },
      "credentials": {
        "microsoftExcelOAuth2Api": {
          "id": "jevPChvDpEJk6W9v",
          "name": "Microsoft Excel account - test"
        }
      },
      "typeVersion": 2
    },
    {
      "id": "10b43784-8d58-4fbb-b964-0f6b4da16527",
      "name": "创建学生提醒",
      "type": "n8n-nodes-base.code",
      "position": [
        640,
        20
      ],
      "parameters": {
        "jsCode": "// Get class data and student contacts\nconst classData = $('Filter Today\\'s Classes').all();\nconst studentContacts = $input.all();\n\n// Create reminder messages for each class\nconst reminders = [];\n\nclassData.forEach(classItem => {\n  const classInfo = classItem.json;\n  \n  // Find students enrolled in this class\n  const enrolledStudents = studentContacts.filter(student => {\n    const studentClasses = student.json['Enrolled Classes'] || '';\n    return studentClasses.includes(classInfo['Class Name']);\n  });\n  \n  // Create reminder for each enrolled student\n  enrolledStudents.forEach(student => {\n    const reminderMessage = classInfo.reminderType === 'today' \n      ? `🔔 Class Reminder: \"${classInfo['Class Name']}\" starts at ${classInfo['Class Time']} today!\\n\\n📍 Location: ${classInfo['Location'] || 'TBD'}\\n👨‍🏫 Instructor: ${classInfo['Instructor'] || 'TBD'}\\n📚 Topic: ${classInfo['Topic'] || 'Regular class'}\\n\\n⚠️ Please arrive 5 minutes early. Don't forget your materials!`\n      : `📅 Tomorrow's Schedule: You have \"${classInfo['Class Name']}\" at ${classInfo['Class Time']}\\n\\n📍 Location: ${classInfo['Location'] || 'TBD'}\\n👨‍🏫 Instructor: ${classInfo['Instructor'] || 'TBD'}\\n📚 Topic: ${classInfo['Topic'] || 'Regular class'}\\n\\n💡 Tip: Prepare your materials tonight!`;\n    \n    reminders.push({\n      json: {\n        studentName: `${student.json['First Name']} ${student.json['Last Name']}`,\n        studentEmail: student.json['Email'],\n        studentPhone: student.json['Phone'],\n        className: classInfo['Class Name'],\n        classTime: classInfo['Class Time'],\n        classDate: classInfo['Class Date'],\n        location: classInfo['Location'],\n        instructor: classInfo['Instructor'],\n        topic: classInfo['Topic'],\n        reminderType: classInfo.reminderType,\n        message: reminderMessage,\n        preferredContact: student.json['Preferred Contact'] || 'email'\n      }\n    });\n  });\n});\n\nreturn reminders;"
      },
      "typeVersion": 2
    },
    {
      "id": "73d5961e-b4a1-45cf-b9fd-4c4c717eb152",
      "name": "拆分为批次",
      "type": "n8n-nodes-base.splitInBatches",
      "position": [
        860,
        20
      ],
      "parameters": {
        "options": {},
        "batchSize": 10
      },
      "typeVersion": 3
    },
    {
      "id": "0b07fae3-e6c1-4ba8-b168-f5c937d091f3",
      "name": "邮件还是短信？",
      "type": "n8n-nodes-base.if",
      "position": [
        1080,
        20
      ],
      "parameters": {
        "conditions": {
          "string": [
            {
              "value1": "={{$json.preferredContact}}",
              "value2": "email"
            }
          ]
        }
      },
      "typeVersion": 1
    },
    {
      "id": "05312b9a-6dc1-408f-9f55-7dbb6aded4d8",
      "name": "准备邮件提醒",
      "type": "n8n-nodes-base.code",
      "position": [
        1300,
        -80
      ],
      "parameters": {
        "jsCode": "// Prepare email data\nconst reminderData = $input.all();\n\nconst emailsToSend = reminderData.map(item => {\n  const data = item.json;\n  \n  const subject = data.reminderType === 'today' \n    ? `🔔 Class Reminder: ${data.className} Today`\n    : `📅 Tomorrow's Class: ${data.className}`;\n  \n  const htmlBody = `\n    <!DOCTYPE html>\n    <html>\n    <head>\n        <style>\n            body { font-family: Arial, sans-serif; color: #333; margin: 0; padding: 20px; }\n            .container { max-width: 600px; margin: 0 auto; }\n            .header { background: linear-gradient(135deg, #667eea 0%, #764ba2 100%); color: white; padding: 20px; border-radius: 8px 8px 0 0; text-align: center; }\n            .content { background: #f9f9f9; padding: 25px; border-radius: 0 0 8px 8px; }\n            .class-info { background: white; padding: 20px; border-radius: 8px; margin: 15px 0; border-left: 4px solid #667eea; }\n            .info-row { margin: 10px 0; }\n            .info-label { font-weight: bold; color: #667eea; }\n            .footer { text-align: center; margin-top: 20px; color: #666; font-size: 12px; }\n            .emoji { font-size: 18px; }\n        </style>\n    </head>\n    <body>\n        <div class=\"container\">\n            <div class=\"header\">\n                <h1><span class=\"emoji\">${data.reminderType === 'today' ? '🔔' : '📅'}</span> Class ${data.reminderType === 'today' ? 'Reminder' : 'Preview'}</h1>\n            </div>\n            <div class=\"content\">\n                <p>Hello ${data.studentName},</p>\n                <p>${data.reminderType === 'today' ? 'Your class is starting soon!' : 'Here\\'s your schedule for tomorrow:'}</p>\n                \n                <div class=\"class-info\">\n                    <div class=\"info-row\"><span class=\"info-label\">📚 Class:</span> ${data.className}</div>\n                    <div class=\"info-row\"><span class=\"info-label\">🕐 Time:</span> ${data.classTime}</div>\n                    <div class=\"info-row\"><span class=\"info-label\">📅 Date:</span> ${data.classDate}</div>\n                    <div class=\"info-row\"><span class=\"info-label\">📍 Location:</span> ${data.location || 'TBD'}</div>\n                    <div class=\"info-row\"><span class=\"info-label\">👨‍🏫 Instructor:</span> ${data.instructor || 'TBD'}</div>\n                    <div class=\"info-row\"><span class=\"info-label\">📖 Topic:</span> ${data.topic || 'Regular class session'}</div>\n                </div>\n                \n                ${data.reminderType === 'today' \n                  ? '<p><strong>⚠️ Please arrive 5 minutes early and don\\'t forget your materials!</strong></p>'\n                  : '<p><strong>💡 Tip: Prepare your materials tonight for a smooth start tomorrow!</strong></p>'\n                }\n                \n                <p>Have a great class!</p>\n            </div>\n            <div class=\"footer\">\n                <p>This is an automated reminder from your class management system.</p>\n            </div>\n        </div>\n    </body>\n    </html>\n  `;\n  \n  return {\n    json: {\n      to: data.studentEmail,\n      subject: subject,\n      body: data.message,\n      html: htmlBody,\n      studentName: data.studentName,\n      className: data.className\n    }\n  };\n});\n\nreturn emailsToSend;"
      },
      "typeVersion": 2
    },
    {
      "id": "cece98bb-8af5-4a9c-b1e1-9ff2a421576b",
      "name": "准备短信提醒",
      "type": "n8n-nodes-base.code",
      "position": [
        1300,
        120
      ],
      "parameters": {
        "jsCode": "// Prepare SMS data\nconst reminderData = $input.all();\n\nconst smsToSend = reminderData.map(item => {\n  const data = item.json;\n  \n  const smsMessage = data.reminderType === 'today'\n    ? `🔔 Class Reminder: \"${data.className}\" starts at ${data.classTime} today at ${data.location || 'TBD'}. Arrive 5 min early!`\n    : `📅 Tomorrow: \"${data.className}\" at ${data.classTime} (${data.location || 'TBD'}). Prepare tonight!`;\n  \n  return {\n    json: {\n      to: data.studentPhone,\n      message: smsMessage,\n      studentName: data.studentName,\n      className: data.className\n    }\n  };\n});\n\nreturn smsToSend;"
      },
      "typeVersion": 2
    },
    {
      "id": "9771a2d3-1ad7-47d9-8951-58f9ef7e66d7",
      "name": "同步到 Google 日历",
      "type": "n8n-nodes-base.googleCalendar",
      "position": [
        420,
        120
      ],
      "parameters": {
        "end": "={{$json['Class Date']}}T{{(parseInt($json['Class Time'].split(':')[0]) + 1).toString().padStart(2, '0')}}:{{$json['Class Time'].split(':')[1].padStart(2, '0')}}:00",
        "start": "={{$json['Class Date']}}T{{$json['Class Time'].split(':')[0].padStart(2, '0')}}:{{$json['Class 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": "366cc8cf-d94c-4685-b4bb-fad0cf8f948b",
      "name": "读取提醒日志",
      "type": "n8n-nodes-base.microsoftExcel",
      "position": [
        1520,
        20
      ],
      "parameters": {
        "filters": {}
      },
      "credentials": {
        "microsoftExcelOAuth2Api": {
          "id": "jevPChvDpEJk6W9v",
          "name": "Microsoft Excel account - test"
        }
      },
      "typeVersion": 2
    },
    {
      "id": "d28bca9d-3a43-4c04-abe4-7ecc8200e23d",
      "name": "更新提醒日志",
      "type": "n8n-nodes-base.code",
      "position": [
        1740,
        20
      ],
      "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    'Student Name': email.json.studentName,\n    'Class Name': email.json.className,\n    'Contact Method': 'Email',\n    'Contact Info': email.json.to,\n    'Status': 'Sent',\n    'Reminder Type': 'Class 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    'Student Name': sms.json.studentName,\n    'Class Name': sms.json.className,\n    'Contact Method': 'SMS',\n    'Contact Info': sms.json.to,\n    'Status': 'Sent',\n    'Reminder Type': 'Class 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": "af36b846-ccb8-4ab3-8e3e-0c8599efb69a",
      "name": "保存提醒日志",
      "type": "n8n-nodes-base.microsoftExcel",
      "position": [
        1960,
        20
      ],
      "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": "2d5a26d5-e112-42b4-b4a1-3cdb98df6240",
      "name": "便签",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -340,
        -440
      ],
      "parameters": {
        "width": 600,
        "height": 220,
        "content": "### **工作流流程**"
      },
      "typeVersion": 1
    }
  ],
  "active": false,
  "pinData": {},
  "settings": {
    "executionOrder": "v1"
  },
  "versionId": "a4519a70-de1e-4df5-9dd4-0118ed25fc16",
  "connections": {
    "Email or SMS?": {
      "main": [
        [
          {
            "node": "Prepare Email Reminders",
            "type": "main",
            "index": 0
          }
        ],
        [
          {
            "node": "Prepare SMS Reminders",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Read Reminder Log": {
      "main": [
        [
          {
            "node": "Update Reminder Log",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Has Classes Today?": {
      "main": [
        [
          {
            "node": "Read Student Contacts",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Split Into Batches": {
      "main": [
        [
          {
            "node": "Email or SMS?",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Read Class Schedule": {
      "main": [
        [
          {
            "node": "Filter Today's Classes",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Update Reminder Log": {
      "main": [
        [
          {
            "node": "Save Reminder Log",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Daily Schedule Check": {
      "main": [
        [
          {
            "node": "Read Class Schedule",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Prepare SMS Reminders": {
      "main": [
        [
          {
            "node": "Read Reminder Log",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Read Student Contacts": {
      "main": [
        [
          {
            "node": "Create Student Reminders",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Filter Today's Classes": {
      "main": [
        [
          {
            "node": "Has Classes Today?",
            "type": "main",
            "index": 0
          },
          {
            "node": "Sync to Google Calendar",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Prepare Email Reminders": {
      "main": [
        [
          {
            "node": "Read Reminder Log",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Sync to Google Calendar": {
      "main": [
        [
          {
            "node": "Create Student Reminders",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Create Student Reminders": {
      "main": [
        [
          {
            "node": "Split Into Batches",
            "type": "main",
            "index": 0
          }
        ]
      ]
    }
  }
}