Utiliser OpenAI pour auditer la progression des diplômes des étudiants dans Google Sheets

• •Informations d'identification Google Sheets API
• •Clé API OpenAI

{
  "meta": {
    "instanceId": "ad0113c344ee237399e44e9f11798b05baeb83a6196d514a9ae9d2ad71c3b5c9",
    "templateCredsSetupCompleted": true
  },
  "nodes": [
    {
      "id": "5432b2ed-adde-4021-b6b3-e75e43bd102c",
      "name": "当点击‘执行工作流’时",
      "type": "n8n-nodes-base.manualTrigger",
      "position": [
        -2048,
        7776
      ],
      "parameters": {},
      "typeVersion": 1
    },
    {
      "id": "86b73ee2-7e25-4ab2-b781-f677032e7fb2",
      "name": "便签68",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -1840,
        7248
      ],
      "parameters": {
        "color": 3,
        "width": 224,
        "height": 656,
        "content": "### 1) Connect Google Sheets (OAuth2)\n1. In **n8n → Credentials → New → Google Sheets (OAuth2)**  \n2. Sign in with your Google account and grant access  \n3. In each Google Sheets node, select your **Spreadsheet** and the appropriate **Worksheet**:  \n   - **data** (raw spend)  \n   - **Lookup** (channel reference table)  \n   - **render pivot** (output tab)  \n\nhttps://docs.google.com/spreadsheets/d/10IMnD8JhiR4lTlNFQTG_Auopg8haAiEt3_G9EKWTqLw/edit?gid=1466231493#gid=1466231493\n"
      },
      "typeVersion": 1
    },
    {
      "id": "01782593-fe8a-46fe-82ba-23c1a13c604e",
      "name": "OpenAI聊天模型1",
      "type": "@n8n/n8n-nodes-langchain.lmChatOpenAi",
      "position": [
        -1344,
        8048
      ],
      "parameters": {
        "model": {
          "__rl": true,
          "mode": "list",
          "value": "gpt-5",
          "cachedResultName": "gpt-5"
        },
        "options": {}
      },
      "credentials": {
        "openAiApi": {
          "id": "4l6TDfLZVFS24g3X",
          "name": "OpenAi account 4"
        }
      },
      "typeVersion": 1.2
    },
    {
      "id": "24d787bc-36ca-4808-851d-bcdcbfdc681c",
      "name": "结构化输出解析器1",
      "type": "@n8n/n8n-nodes-langchain.outputParserStructured",
      "position": [
        -1120,
        8032
      ],
      "parameters": {
        "jsonSchemaExample": "{\n  \"StudentID\": \"S001\",\n  \"Program\": \"Example Program\",\n  \"Missing\": [\n    \"CS-201 | Computer Systems | 3\",\n    \"CS-220 | Databases | 3\",\n    \"GEN-107 | Introduction to Sociology | 3\"\n  ],\n  \"Summary\": \"Student still needs two core Computer Science courses and one general education requirement. Should prioritize 200-level major courses next term.\"\n}\n"
      },
      "typeVersion": 1.3
    },
    {
      "id": "602cbc87-b96e-4ee6-8925-6e0213ca4a41",
      "name": "便签69",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -1424,
        7744
      ],
      "parameters": {
        "color": 3,
        "width": 224,
        "height": 400,
        "content": "### 3) Connect OpenAI (API Key)\n1. In **n8n → Credentials → New → OpenAI API**  \n2. Paste your **OpenAI API key**  \n3. In **OpenAI Chat Model**, select your credential and a **vision-capable** chat model (e.g., `gpt-4o-mini`, `gpt-4o`, or your configured vision model)\n"
      },
      "typeVersion": 1
    },
    {
      "id": "8ae8ffe0-e377-4fd5-b134-5b3ecd61aacf",
      "name": "便签57",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -2304,
        7136
      ],
      "parameters": {
        "color": 7,
        "width": 2144,
        "height": 1056,
        "content": "## 🔎 Audit student degree progress in Google Sheets using OpenAI\n\nRun an **AI-powered degree audit** for each senior student. This workflow reads rows from a Google Sheet, checks their completed courses against hard-coded program requirements, and writes back a summary of what’s still missing — core classes, electives, general education, and upper-division credits.\n"
      },
      "typeVersion": 1
    },
    {
      "id": "2694ca9c-f3fb-4dd9-bda4-dc55748d5fba",
      "name": "便签5",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -2736,
        7136
      ],
      "parameters": {
        "width": 400,
        "height": 1056,
        "content": "## Setup (2 steps)\n\n### 1) Connect Google Sheets (OAuth2)\nIn n8n → Credentials → New → Google Sheets (OAuth2) and sign in.\n\nIn the Google Sheets nodes, select your spreadsheet and the Senior_data tab.\n\nEnsure your input sheet has at least: StudentID, Name, Program, Year, CompletedCourses.\n\n### 2) Connect OpenAI (API Key)\nIn n8n → Credentials → New → OpenAI API, paste your key.\n\nIn the OpenAI Chat Model node, select that credential and a model (e.g., gpt-4o or gpt-5).\n\n\n\n- 📧 **rbreen@ynteractive.com**  \n- 🔗 **Robert Breen** — https://www.linkedin.com/in/robert-breen-29429625/  \n- 🌐 **ynteractive.com** — https://ynteractive.com\n\n"
      },
      "typeVersion": 1
    },
    {
      "id": "05c50b84-4ed3-4327-b17a-8262cac58305",
      "name": "学位审核代理",
      "type": "@n8n/n8n-nodes-langchain.agent",
      "position": [
        -1120,
        7552
      ],
      "parameters": {
        "text": "=student:  {{ $json.StudentID }}, Name: {{ $json.Name }}, Program: {{ $json.Program }} Year: {{ $json.Year }} Completed Courses: {{ $json.CompletedCourses }}",
        "options": {
          "systemMessage": "You are a university degree-audit assistant. Your job is to analyze each student’s completed courses against the hard-coded program requirements and catalog below, then output only what they are still missing.\n\nPROGRAM REQUIREMENTS (HARD-CODED)\n\nComputer Science BS\n\nTotal Credits Required: 120\n\nGen Ed Credits Required: 36\n\nMajor Core Courses: CS-101, CS-102, CS-103, CS-201, CS-220\n\nMajor Elective Credits Required: 12 (any CS course with “Major Elective”)\n\nUpper-Division Credits Required: 30 (courses level ≥ 200)\n\nBusiness Administration BBA\n\nTotal Credits Required: 120\n\nGen Ed Credits Required: 36\n\nMajor Core Courses: BUS-101, BUS-102, BUS-103, BUS-201, BUS-202\n\nMajor Elective Credits Required: 12 (BUS electives level ≥ 200)\n\nUpper-Division Credits Required: 30\n\nPsychology BA\n\nTotal Credits Required: 120\n\nGen Ed Credits Required: 42\n\nMajor Core Courses: PSY-101, PSY-201\n\nMajor Elective Credits Required: 18 (PSY electives level ≥ 200)\n\nUpper-Division Credits Required: 27\n\nMechanical Engineering BS\n\nTotal Credits Required: 128\n\nGen Ed Credits Required: 30\n\nMajor Core Courses: ENGR-101, ENGR-102, MTH-121, MTH-122, PHY-151, ME-201\n\nMajor Elective Credits Required: 12 (ME electives level ≥ 200)\n\nUpper-Division Credits Required: 36\n\nBiology BS (Pre-Med)\n\nTotal Credits Required: 124\n\nGen Ed Credits Required: 36\n\nMajor Core Courses: BIO-101, BIO-102, CHM-101, CHM-102, BIO-201\n\nMajor Elective Credits Required: 9 (BIO electives level ≥ 200)\n\nUpper-Division Credits Required: 30\n\nEnglish Literature BA\n\nTotal Credits Required: 120\n\nGen Ed Credits Required: 42\n\nMajor Core Courses: ENG-101, ENG-201\n\nMajor Elective Credits Required: 18 (ENG electives level ≥ 200)\n\nUpper-Division Credits Required: 27\n\nData Science BS\n\nTotal Credits Required: 120\n\nGen Ed Credits Required: 36\n\nMajor Core Courses: CS-101, CS-102, DS-101, DS-201\n\nMajor Elective Credits Required: 12 (DS electives level ≥ 200)\n\nUpper-Division Credits Required: 30\n\nNursing BSN\n\nTotal Credits Required: 124\n\nGen Ed Credits Required: 36\n\nMajor Core Courses: BIO-101, CHM-101, NUR-101, NUR-201\n\nMajor Elective Credits Required: 9 (NUR electives level ≥ 200)\n\nUpper-Division Credits Required: 30\n\nEconomics BA\n\nTotal Credits Required: 120\n\nGen Ed Credits Required: 36\n\nMajor Core Courses: ECON-101, ECON-102, ECON-201\n\nMajor Elective Credits Required: 12 (ECON electives level ≥ 200)\n\nUpper-Division Credits Required: 27\n\nGraphic Design BFA\n\nTotal Credits Required: 120\n\nGen Ed Credits Required: 30\n\nMajor Core Courses: ART-101, ART-102, ART-201\n\nMajor Elective Credits Required: 18 (ART electives level ≥ 200)\n\nUpper-Division Credits Required: 30\n\nOUTPUT RULES\n\nFor each student, output valid JSON only.\n\nOutput must be a JSON array of objects.\n\nEach object must include:\n\n\"StudentID\"\n\n\"Program\"\n\n\"Missing\" → array of strings, each formatted \"COURSEID | Title | Credits\" (list the missing Major Core, plus indicate Gen Ed/Elective requirements if still needed)\n\n\"Summary\" → 1–2 sentences explaining what they’re missing and what to prioritize next (mention 200/300-level if upper-division is short).\n\nWould you like me to now fill in a JSON example for one program (e.g., Computer Science BS with Ava Thompson’s courses) so you see exactly how the Missing list and Summary would look?\n\nOutput like this. \n\n{\n  \"StudentID\": \"S001\",\n  \"Program\": \"Example Program\",\n  \"Missing\": [\n    \"CS-201 | Computer Systems | 3\",\n    \"CS-220 | Databases | 3\",\n    \"GEN-107 | Introduction to Sociology | 3\"\n  ],\n  \"Summary\": \"Student still needs two core Computer Science courses and one general education requirement. Should prioritize 200-level major courses next term.\"\n}\n"
        },
        "promptType": "define",
        "hasOutputParser": true
      },
      "executeOnce": false,
      "typeVersion": 2.2,
      "alwaysOutputData": false
    },
    {
      "id": "e719b531-b1c0-431a-b2ba-9bc9b1c71d1a",
      "name": "添加学生学位摘要",
      "type": "n8n-nodes-base.googleSheets",
      "position": [
        -720,
        7568
      ],
      "parameters": {
        "columns": {
          "value": {
            "StudentID": "={{ $json.output.StudentID }}",
            "AI Degree Summary": "={{ $json.output.Summary }}"
          },
          "schema": [
            {
              "id": "StudentID",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "StudentID",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "Name",
              "type": "string",
              "display": true,
              "removed": true,
              "required": false,
              "displayName": "Name",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "Program",
              "type": "string",
              "display": true,
              "removed": true,
              "required": false,
              "displayName": "Program",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "Year",
              "type": "string",
              "display": true,
              "removed": true,
              "required": false,
              "displayName": "Year",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "CompletedCourses",
              "type": "string",
              "display": true,
              "removed": true,
              "required": false,
              "displayName": "CompletedCourses",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "Degree Summary",
              "type": "string",
              "display": true,
              "removed": true,
              "required": false,
              "displayName": "Degree Summary",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "AI Degree Summary",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "AI Degree Summary",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            }
          ],
          "mappingMode": "defineBelow",
          "matchingColumns": [
            "StudentID"
          ],
          "attemptToConvertTypes": false,
          "convertFieldsToString": false
        },
        "options": {},
        "operation": "appendOrUpdate",
        "sheetName": {
          "__rl": true,
          "mode": "list",
          "value": 1466231493,
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/10IMnD8JhiR4lTlNFQTG_Auopg8haAiEt3_G9EKWTqLw/edit#gid=1466231493",
          "cachedResultName": "Senior_data"
        },
        "documentId": {
          "__rl": true,
          "mode": "list",
          "value": "10IMnD8JhiR4lTlNFQTG_Auopg8haAiEt3_G9EKWTqLw",
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/10IMnD8JhiR4lTlNFQTG_Auopg8haAiEt3_G9EKWTqLw/edit?usp=drivesdk",
          "cachedResultName": "Student Details"
        }
      },
      "credentials": {
        "googleSheetsOAuth2Api": {
          "id": "HlBW2puZbuCCq8jJ",
          "name": "Google Sheets account 3"
        }
      },
      "typeVersion": 4.7
    },
    {
      "id": "22a77dea-d57c-487c-9d55-382e8121f711",
      "name": "获取学生数据1",
      "type": "n8n-nodes-base.googleSheets",
      "position": [
        -1760,
        7776
      ],
      "parameters": {
        "options": {},
        "sheetName": {
          "__rl": true,
          "mode": "list",
          "value": 1466231493,
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/10IMnD8JhiR4lTlNFQTG_Auopg8haAiEt3_G9EKWTqLw/edit#gid=1466231493",
          "cachedResultName": "Senior_data"
        },
        "documentId": {
          "__rl": true,
          "mode": "list",
          "value": "10IMnD8JhiR4lTlNFQTG_Auopg8haAiEt3_G9EKWTqLw",
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/10IMnD8JhiR4lTlNFQTG_Auopg8haAiEt3_G9EKWTqLw/edit?usp=drivesdk",
          "cachedResultName": "Student Details"
        }
      },
      "credentials": {
        "googleSheetsOAuth2Api": {
          "id": "HlBW2puZbuCCq8jJ",
          "name": "Google Sheets account 3"
        }
      },
      "typeVersion": 4.7
    }
  ],
  "pinData": {
    "Degree Audit Agent": [
      {
        "output": {
          "Missing": [
            "GEN-REMAIN | General Education credits remaining | 6",
            "CS-EL-REM | CS Major Electives (200+ level) | 6",
            "UPPER-DIV | Additional Upper-Division (200+ level) credits needed | 18",
            "FREE-EL | Free Electives to reach 120 total credits | 54"
          ],
          "Program": "Computer Science BS",
          "Summary": "All core CS courses are complete. You still need 6 Gen Ed credits, 6 CS major elective credits, and additional electives to reach 120 credits (66 total remaining), including at least 18 more upper-division credits—prioritize 200/300-level CS electives and other upper-division courses.",
          "StudentID": "S001"
        }
      },
      {
        "output": {
          "Missing": [
            "BUS-ELECTIVE-200+ | Major Elective Credits Remaining | 9",
            "GEN-ED | General Education Credits Remaining | 6",
            "UPPER-DIV | Upper-Division Credits Remaining | 21",
            "TOTAL | Total Credits Remaining | 72"
          ],
          "Program": "Business Administration BBA",
          "Summary": "All core BUS courses are complete. Student still needs 9 credits of 200+ BUS electives, 6 Gen Ed credits, and 21 more upper-division credits (72 total credits remaining overall); prioritize 200/300-level BUS electives next term to satisfy both major elective and upper-division requirements.",
          "StudentID": "S002"
        }
      },
      {
        "output": {
          "Missing": [
            "PSY-EL-200+ | Psychology Major Electives (≥200-level) | 15",
            "GEN-ED | General Education Credits | 12",
            "UPPER-200+ | Upper-Division Credits (≥200-level, any subject) | 21",
            "TOTAL | Overall Credits to reach 120 | 81"
          ],
          "Program": "Psychology BA",
          "Summary": "Core is complete; you still need 15 credits of 200+ PSY electives, 12 gen ed credits, and 21 upper-division credits, with 81 total credits remaining to reach 120. Prioritize 200/300-level PSY electives to satisfy both the major elective and upper-division requirements while finishing remaining gen eds.",
          "StudentID": "S003"
        }
      },
      {
        "output": {
          "Missing": [
            "ME-ELECTIVE | Mechanical Engineering Electives (200+) | 9",
            "UPPER-DIV | Upper-Division Credits (200+) | 30",
            "TOTAL | Additional Credits to reach 128 | 77"
          ],
          "Program": "Mechanical Engineering BS",
          "Summary": "All major core and Gen Ed are complete. Student still needs 9 credits of ME electives and 30 upper-division credits; 77 total credits remain to reach 128, so prioritize 300/400-level ME and technical electives.",
          "StudentID": "S004"
        }
      },
      {
        "output": {
          "Missing": [
            "BIO-EL | Biology Major Electives (200+ level) | 9",
            "GEN-ED | General Education Credits Remaining | 6",
            "UPPER-DIV | Upper-Division Credits Remaining | 27"
          ],
          "Program": "Biology BS (Pre-Med)",
          "Summary": "Isabella has completed all Biology (Pre-Med) core courses but still needs 9 credits of 200+ BIO major electives, 6 more General Education credits, and 27 additional upper-division credits overall. Prioritize 200/300-level BIO electives and other upper-division courses to close the upper-division gap.",
          "StudentID": "S005"
        }
      },
      {
        "output": {
          "Missing": [
            "ENG-ELECTIVE-200+ | English Electives (≥200-level) | 18",
            "GEN-ED | General Education Credits | 12",
            "UPPER-DIV | Upper-Division Credits (≥200) | 24",
            "TOTAL | Overall Credits to Reach 120 | 84"
          ],
          "Program": "English Literature BA",
          "Summary": "Major core (ENG-101, ENG-201) is complete. You still need 18 credits of 200/300-level ENG electives, 12 additional Gen Ed credits, and 24 more upper-division credits; prioritize 200/300-level ENG electives to satisfy both major elective and upper-division requirements while finishing the remaining Gen Eds.",
          "StudentID": "S006"
        }
      },
      {
        "output": {
          "Missing": [
            "GEN-ED | General Education Credits | 6",
            "DS-ELECT-200+ | Data Science Major Electives (200-level or above) | 12",
            "UPPER-DIV | Upper-Division Credits (≥200) | 27"
          ],
          "Program": "Data Science BS",
          "Summary": "Sophia has completed all Data Science core courses. She still needs 12 credits of 200+ DS electives, 6 Gen Ed credits, and 27 more upper-division credits to reach 30 total; prioritize 200/300-level DS electives next term.",
          "StudentID": "S007"
        }
      },
      {
        "output": {
          "Missing": [
            "GEN-ED-REQ | General Education credits remaining | 6",
            "NUR-ELECTIVES-REQ | Nursing Major Electives (≥200-level) remaining | 9",
            "UPPER-DIV-REQ | Upper-Division credits remaining (≥200-level) | 27",
            "TOTAL-REQ | Overall credits remaining to reach 124 | 82"
          ],
          "Program": "Nursing BSN",
          "Summary": "All Nursing core courses are complete. You still need 6 Gen Ed credits, 9 credits of 200-level NUR electives, and 27 more upper-division credits (82 credits overall to reach 124); prioritize 200+ nursing electives and other upper-division courses while finishing your remaining Gen Eds.",
          "StudentID": "S008"
        }
      },
      {
        "output": {
          "Missing": [
            "ECON-EL-200+ | Economics Major Electives (200+ level) | 12",
            "GEN-ED | General Education Credits Remaining | 6",
            "UPPER-DIV | Upper-Division Credits Remaining | 24"
          ],
          "Program": "Economics BA",
          "Summary": "All Economics core courses are complete. Still needs 12 credits of 200+ ECON electives, 6 Gen Ed credits, and 24 additional upper-division credits—prioritize 200/300-level ECON electives and other 200+ courses to build upper-division hours.",
          "StudentID": "S009"
        }
      },
      {
        "output": {
          "Missing": [
            "ART-EL-200+ | ART Major Electives (200+ level) | 12",
            "UPPER-DIV | Upper-Division Credits (200+ level) | 21"
          ],
          "Program": "Graphic Design BFA",
          "Summary": "Mason has completed all core ART courses and general education, but still needs 12 credits of ART electives at the 200+ level and 21 additional upper-division credits overall. Prioritize 300-level ART electives to satisfy both the major elective and upper-division requirements.",
          "StudentID": "S010"
        }
      }
    ]
  },
  "connections": {
    "22a77dea-d57c-487c-9d55-382e8121f711": {
      "main": [
        [
          {
            "node": "05c50b84-4ed3-4327-b17a-8262cac58305",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "05c50b84-4ed3-4327-b17a-8262cac58305": {
      "main": [
        [
          {
            "node": "e719b531-b1c0-431a-b2ba-9bc9b1c71d1a",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "01782593-fe8a-46fe-82ba-23c1a13c604e": {
      "ai_languageModel": [
        [
          {
            "node": "05c50b84-4ed3-4327-b17a-8262cac58305",
            "type": "ai_languageModel",
            "index": 0
          }
        ]
      ]
    },
    "24d787bc-36ca-4808-851d-bcdcbfdc681c": {
      "ai_outputParser": [
        [
          {
            "node": "05c50b84-4ed3-4327-b17a-8262cac58305",
            "type": "ai_outputParser",
            "index": 0
          }
        ]
      ]
    },
    "5432b2ed-adde-4021-b6b3-e75e43bd102c": {
      "main": [
        [
          {
            "node": "22a77dea-d57c-487c-9d55-382e8121f711",
            "type": "main",
            "index": 0
          }
        ]
      ]
    }
  }
}