使用OpenAI在Google表格中审核学生学位进度
中级
这是一个自动化工作流，包含 10 个节点。主要使用 GoogleSheets, ManualTrigger, Agent, LmChatOpenAi, OutputParserStructured 等节点。 使用OpenAI在Google表格中审核学生学位进度
前置要求
- •Google Sheets API 凭证
- •OpenAI API Key
分类
-
工作流预览
可视化展示节点连接关系，支持缩放和平移
导出工作流
复制以下 JSON 配置到 n8n 导入，即可使用此工作流
{
"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) 连接 Google Sheets (OAuth2)"
},
"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) 连接 OpenAI (API 密钥)"
},
"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": "## 🔎 使用 OpenAI 在 Google Sheets 中审核学生学位进度"
},
"typeVersion": 1
},
{
"id": "2694ca9c-f3fb-4dd9-bda4-dc55748d5fba",
"name": "便签5",
"type": "n8n-nodes-base.stickyNote",
"position": [
-2736,
7136
],
"parameters": {
"width": 400,
"height": 1056,
"content": "## 设置（2 个步骤）"
},
"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": {
"Get Student Data1": {
"main": [
[
{
"node": "Degree Audit Agent",
"type": "main",
"index": 0
}
]
]
},
"Degree Audit Agent": {
"main": [
[
{
"node": "Add Student Degree Summary",
"type": "main",
"index": 0
}
]
]
},
"OpenAI Chat Model1": {
"ai_languageModel": [
[
{
"node": "Degree Audit Agent",
"type": "ai_languageModel",
"index": 0
}
]
]
},
"Structured Output Parser1": {
"ai_outputParser": [
[
{
"node": "Degree Audit Agent",
"type": "ai_outputParser",
"index": 0
}
]
]
},
"When clicking ‘Execute workflow’": {
"main": [
[
{
"node": "Get Student Data1",
"type": "main",
"index": 0
}
]
]
}
}
}常见问题
如何使用这个工作流？
复制上方的 JSON 配置代码，在您的 n8n 实例中创建新工作流并选择「从 JSON 导入」，粘贴配置后根据需要修改凭证设置即可。
这个工作流适合什么场景？
中级
需要付费吗？
本工作流完全免费，您可以直接导入使用。但请注意，工作流中使用的第三方服务（如 OpenAI API）可能需要您自行付费。
相关工作流推荐
基于已修先修课程的AI课程排程器（Google Sheets + OpenAI）
基于已修先修课程的AI课程排程器（Google Sheets + OpenAI）
Set
Split Out
Google Sheets
+5
13 节点Robert Breen
AI生成LinkedIn帖子含人工审批
使用GPT-4、GoToHuman和Blotato的AI生成LinkedIn帖子含人工审批
Code
Merge
Filter
+10
19 节点Robert Breen
社交媒体
初学者数据分析：使用 GPT-4o 在 Google Sheets 中合并、筛选和汇总
初学者数据分析：使用 GPT-4o 在 Google Sheets 中合并、筛选和汇总
If
Set
Code
+9
21 节点Robert Breen
文档提取
冷线索重新参与邮件生成器：GPT-4o-mini、Outlook和Sheets
冷线索重新参与邮件生成器：GPT-4o-mini、Outlook和Sheets
Code
Aggregate
Google Sheets
+6
15 节点Robert Breen
客户培育
播客笔记记录器
使用GPT和Gemini结合性能数据创建高转化率Meta广告脚本
Set
Google Sheets
Manual Trigger
+4
12 节点Robert Breen
内容创作
使用Google Sheets、SerpAPI、Apify和GPT-4o提取本地企业联系人
使用Google Sheets、SerpAPI、Apify和GPT-4o提取本地企业联系人
Code
Filter
Summarize
+10
18 节点Robert Breen
潜在客户开发
工作流信息
难度等级
中级
节点数量10
分类-
节点类型6
作者
Robert Breen
@rbreenProfessional services consultant with over 10 years of experience solving complex business problems across industries. I specialize in n8n and process automation—designing custom workflows that integrate tools like Google Calendar, Airtable, GPT, and internal systems. Whether you need to automate scheduling, sync data, or streamline operations, I build solutions that save time and drive results.
外部链接
在 n8n.io 查看 →
分享此工作流