Generate student course schedules based on prerequisites with GPT and Google Sheets

Create a Fall 2025 course schedule for each student based on what they’ve already completed, catalog prerequisites, and term availability (Fall/Both). Reads students from Google Sheets → asks an AI agent to select exactly 5 courses (target 15–17 credits, no duplicates, prereqs enforced) → appends each student’s schedule to a schedule tab. --- 🧠 Summary - Trigger: Manual — “When clicking ‘Execute workflow’” - I/O: Google Sheets in → OpenAI decisioning → Google Sheets out - Ideal for: Registrars, advisors, degree-planning prototypes --- ✅ What this template does - Reads: StudentID, Name, Program, Year, CompletedCourses (pipe-separated CourseIDs) from Sheet1 - Decides: AI Scheduling Agent chooses 5 courses per student following catalog rules and prerequisites - Writes: Appends StudentID + Schedule strings to schedule worksheet - Credits target: 15–17 total per term - Catalog rules (enforced in the agent prompt): - Use Fall or Both courses for Fall 2025 - Enforce AND prereqs (e.g., CS-102|CS-103 means both) - Priority: Major Core → Major Elective → Gen Ed (include Gen Ed if needed) - No duplicates or already-completed courses - Prefer 200-level progression when prereqs allow --- ⚙️ Setup (only 2 steps) 1) Connect Google Sheets (OAuth2) - In n8n → Credentials → New → Google Sheets (OAuth2), sign in and grant access - In the Google Sheets nodes, select your spreadsheet and these tabs: - Sheet1 (input students) - schedule (output) > Example spreadsheet (replace with your own): > - Input: .../editgid=0 > - Output: .../editgid=572766543 2) Connect OpenAI (API Key) - In n8n → Credentials → New → OpenAI API, paste your key - In the OpenAI Chat Model node, select that credential and a chat model (e.g., gpt-4o) --- 📥 Required input (Sheet1) - Columns: StudentID, Name, Program, Year, CompletedCourses - CompletedCourses: pipe-separated IDs (e.g., GEN-101|GEN-103|CS-101) - Program names should match those referenced in the embedded catalog (e.g., Computer Science BS, Business Administration BBA, etc.) --- 📤 Output (schedule tab) - Columns: - StudentID - Schedule → a selected course string (written one row per course after splitting) --- 🧩 Nodes in this template - Manual Trigger → Get Student Data (Google Sheets) → Scheduling Agent (OpenAI) → Split Schedule → Set Fields → Clear sheet → Append Schedule (Google Sheets) --- 🛠 Configuration tips - If you rename tabs, update both Get Student Data and Append Schedule nodes - Keep CompletedCourses consistent (use | as the delimiter) - To store rationale as well, add a column to the output and map it from the agent’s JSON --- 🧪 Test quickly 1) Add 2–3 sample student rows with realistic CompletedCourses 2) Run the workflow and verify: - 5 course rows per student in schedule - Course IDs respect prereqs & Fall/Both availability - Credits sum ~15–17 --- 🧯 Troubleshooting - Sheets OAuth error: Reconnect “Google Sheets (OAuth2)” and re-select the spreadsheet & tabs - Empty schedules: Ensure CompletedCourses uses | and that programs/courses align with the provided catalog names - Prereq violations: Check that students actually have all AND-prereqs in CompletedCourses - OpenAI errors (401/429): Verify API key, billing, and rate-limit → retry with lower concurrency --- 🔒 Privacy & data handling - Student rows are sent to OpenAI for decisioning. Remove or mask any fields you don’t want shared. - Google Sheets retains input/output. Use spreadsheet sharing controls to limit access. --- 💸 Cost & performance - OpenAI: Billed per token; cost scales with student count and prompt size - Google Sheets: Free within normal usage limits - Runtime: Typically seconds to a minute depending on rows and rate limits --- 🧱 Limitations & assumptions - Works for Fall 2025 only (as written). For Spring, update availability rules in the agent prompt - Assumes catalog in the agent system message is your source of truth - Assumes Program names match catalog variants (case/spacing matter for clarity) --- 🧩 Customiz