Generate Student Course Schedules Based on Prerequisites with GPT and Google Sheets

Nodes

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

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: .../edit#gid=0

Output: .../edit#gid=572766543

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)

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.)

Columns:
- StudentID
- Schedule → a selected course string (written one row per course after splitting)

Manual Trigger → Get Student Data (Google Sheets) → Scheduling Agent (OpenAI)
→ Split Schedule → Set Fields → Clear sheet → Append Schedule (Google Sheets)

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

Add 2–3 sample student rows with realistic CompletedCourses
Run the workflow and verify:
- 5 course rows per student in schedule
- Course IDs respect prereqs & Fall/Both availability
- Credits sum ~15–17

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

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.

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)

Add a Max Credits column to cap term credits per student
Include Rationale in the sheet for advisor review
Add a “Gen Ed needed?” flag per student to force at least one Gen Ed selection
Export to PDF or email the schedules to advisors/students

n8n version: Tested on recent n8n Cloud builds (2025)
Community nodes: Not required
Maintenance: Update the embedded catalog and offerings each term; keep prerequisites accurate

Category: Education / Student Information Systems
Tags: scheduling, registrar, google-sheets, openai, prerequisites, degree-planning, catalog, fall-term