Generator für Schüler-Fortschrittsberichte

• •Google-Konto + Gmail API-Anmeldedaten
• •Möglicherweise sind Ziel-API-Anmeldedaten erforderlich
• •Google Sheets API-Anmeldedaten

{
  "id": "rumCZInCSooKZgBp",
  "meta": {
    "instanceId": "dd69efaf8212c74ad206700d104739d3329588a6f3f8381a46a481f34c9cc281",
    "templateCredsSetupCompleted": true
  },
  "name": "Student Academic Progress Report Generator",
  "tags": [],
  "nodes": [
    {
      "id": "3df9207a-f7cb-421d-ba09-c8933669c09d",
      "name": "Schülerliste abrufen",
      "type": "n8n-nodes-base.googleSheets",
      "position": [
        -240,
        -160
      ],
      "parameters": {
        "options": {},
        "sheetName": {
          "__rl": true,
          "mode": "list",
          "value": "Students",
          "cachedResultName": "Students"
        },
        "documentId": {
          "__rl": true,
          "mode": "list",
          "value": "YOUR_STUDENT_SHEET_ID",
          "cachedResultName": "Student Database"
        },
        "authentication": "serviceAccount"
      },
      "credentials": {
        "googleApi": {
          "id": "ScSS2KxGQULuPtdy",
          "name": "Google Sheets- test"
        }
      },
      "typeVersion": 4.6
    },
    {
      "id": "b48f5de6-7a90-4a40-8f93-4bd3c2ab4d15",
      "name": "Schüler zur Verarbeitung aufteilen",
      "type": "n8n-nodes-base.splitInBatches",
      "position": [
        -20,
        -160
      ],
      "parameters": {
        "options": {},
        "batchSize": 5
      },
      "typeVersion": 3
    },
    {
      "id": "441a7ac4-ef43-4692-b5b2-eaf3bf2ebac7",
      "name": "LMS-Lerndaten abfragen",
      "type": "n8n-nodes-base.httpRequest",
      "position": [
        200,
        -160
      ],
      "parameters": {
        "url": "={{ $credentials.lmsApi.baseUrl }}/api/students/{{ $json.student_id }}/grades",
        "options": {},
        "sendQuery": true,
        "sendHeaders": true,
        "queryParameters": {
          "parameters": [
            {
              "name": "period",
              "value": "current_week"
            },
            {
              "name": "include_assignments",
              "value": "true"
            }
          ]
        },
        "headerParameters": {
          "parameters": [
            {
              "name": "Authorization",
              "value": "Bearer {{ $credentials.lmsApi.apiToken }}"
            },
            {
              "name": "Content-Type",
              "value": "application/json"
            }
          ]
        }
      },
      "typeVersion": 4.2
    },
    {
      "id": "7237f988-66a8-4e45-b6ac-2746485e7282",
      "name": "Lerndaten verarbeiten",
      "type": "n8n-nodes-base.code",
      "position": [
        420,
        -160
      ],
      "parameters": {
        "jsCode": "const studentData = $input.first().json;\nconst lmsData = $input.last().json;\n\nconst grades = lmsData.grades || [];\nconst totalGrade = grades.reduce((sum, grade) => sum + parseFloat(grade.score || 0), 0);\nconst averageGrade = grades.length > 0 ? (totalGrade / grades.length).toFixed(2) : 'N/A';\n\nconst assignments = lmsData.assignments || [];\nconst completedAssignments = assignments.filter(a => a.status === 'completed').length;\nconst completionRate = assignments.length > 0 ? ((completedAssignments / assignments.length) * 100).toFixed(1) : '0';\n\nconst recentGrades = grades.slice(-3).map(g => parseFloat(g.score || 0));\nlet trend = 'Stable';\nif (recentGrades.length >= 2) {\n  const firstHalf = recentGrades.slice(0, Math.floor(recentGrades.length/2));\n  const secondHalf = recentGrades.slice(Math.floor(recentGrades.length/2));\n  const firstAvg = firstHalf.reduce((a,b) => a+b, 0) / firstHalf.length;\n  const secondAvg = secondHalf.reduce((a,b) => a+b, 0) / secondHalf.length;\n  \n  if (secondAvg > firstAvg + 5) trend = 'Improving';\n  else if (secondAvg < firstAvg - 5) trend = 'Declining';\n}\n\nconst attendanceRate = lmsData.attendance ? ((lmsData.attendance.present / lmsData.attendance.total) * 100).toFixed(1) : 'N/A';\n\nconst subjectsNeedingAttention = grades.filter(grade => parseFloat(grade.score || 0) < 70).map(grade => grade.subject).slice(0, 3);\n\nreturn [{\n  json: {\n    student_name: studentData.student_name,\n    student_id: studentData.student_id,\n    parent_email: studentData.parent_email,\n    grade_level: studentData.grade_level,\n    report_date: new Date().toISOString().split('T')[0],\n    overall_grade: averageGrade,\n    assignment_completion_rate: completionRate + '%',\n    attendance_rate: attendanceRate + '%',\n    performance_trend: trend,\n    subjects_needing_attention: subjectsNeedingAttention.join(', ') || 'None',\n    total_assignments: assignments.length,\n    completed_assignments: completedAssignments,\n    recent_grades: recentGrades.join(', '),\n    teacher_comments: lmsData.teacher_comments || 'No recent comments'\n  }\n}];"
      },
      "typeVersion": 2
    },
    {
      "id": "3035a22d-352e-47ed-9628-cf72675f344f",
      "name": "HTML-Bericht generieren",
      "type": "n8n-nodes-base.code",
      "position": [
        640,
        -160
      ],
      "parameters": {
        "jsCode": "const data = $input.first().json;\n\nconst htmlTemplate = `<!DOCTYPE html>\n<html>\n<head>\n    <style>\n        body { font-family: Arial, sans-serif; line-height: 1.6; color: #333; }\n        .container { max-width: 600px; margin: 0 auto; padding: 20px; }\n        .header { background-color: #4CAF50; color: white; padding: 20px; text-align: center; border-radius: 5px; }\n        .content { background-color: #f9f9f9; padding: 20px; margin: 20px 0; border-radius: 5px; }\n        .metric { display: inline-block; margin: 10px; padding: 15px; background-color: white; border-radius: 5px; text-align: center; min-width: 120px; }\n        .metric-value { font-size: 24px; font-weight: bold; color: #4CAF50; }\n        .metric-label { font-size: 12px; color: #666; }\n        .alert { background-color: #fff3cd; border: 1px solid #ffeaa7; padding: 10px; border-radius: 5px; margin: 10px 0; }\n        .footer { text-align: center; font-size: 12px; color: #666; margin-top: 20px; }\n    </style>\n</head>\n<body>\n    <div class=\"container\">\n        <div class=\"header\">\n            <h1>📚 Weekly Academic Progress Report</h1>\n            <p>Student: ${data.student_name} | Grade: ${data.grade_level}</p>\n            <p>Report Date: ${data.report_date}</p>\n        </div>\n        <div class=\"content\">\n            <h2>📊 Performance Overview</h2>\n            <div style=\"text-align: center;\">\n                <div class=\"metric\">\n                    <div class=\"metric-value\">${data.overall_grade}</div>\n                    <div class=\"metric-label\">Overall Grade</div>\n                </div>\n                <div class=\"metric\">\n                    <div class=\"metric-value\">${data.assignment_completion_rate}</div>\n                    <div class=\"metric-label\">Assignments Completed</div>\n                </div>\n                <div class=\"metric\">\n                    <div class=\"metric-value\">${data.attendance_rate}</div>\n                    <div class=\"metric-label\">Attendance Rate</div>\n                </div>\n            </div>\n        </div>\n        <div class=\"content\">\n            <h2>📈 Performance Trend</h2>\n            <p>Current trend: <strong>${data.performance_trend}</strong></p>\n            <p><strong>Recent Grades:</strong> ${data.recent_grades || 'No recent grades available'}</p>\n        </div>\n        <div class=\"content\">\n            <h2>📝 Assignment Status</h2>\n            <p><strong>Total Assignments:</strong> ${data.total_assignments}</p>\n            <p><strong>Completed:</strong> ${data.completed_assignments}</p>\n            <p><strong>Completion Rate:</strong> ${data.assignment_completion_rate}</p>\n        </div>\n        ${data.subjects_needing_attention && data.subjects_needing_attention !== 'None' ? `<div class=\"alert\"><h3>⚠️ Areas for Improvement</h3><p><strong>Subjects needing attention:</strong> ${data.subjects_needing_attention}</p></div>` : ''}\n        <div class=\"content\">\n            <h2>💬 Teacher Comments</h2>\n            <p>${data.teacher_comments}</p>\n        </div>\n        <div class=\"footer\">\n            <p>This report was automatically generated by the Academic Progress System.</p>\n            <p>For questions, please contact your child's teacher.</p>\n        </div>\n    </div>\n</body>\n</html>`;\n\nreturn [{\n  json: {\n    ...data,\n    html_report: htmlTemplate,\n    email_subject: `📚 Weekly Progress Report - ${data.student_name} (${data.report_date})`\n  }\n}];"
      },
      "typeVersion": 2
    },
    {
      "id": "167244cb-3eb0-45ae-bfd3-a4a70e3573a9",
      "name": "E-Mail an Eltern senden",
      "type": "n8n-nodes-base.gmail",
      "position": [
        860,
        -160
      ],
      "webhookId": "0ea94587-98b6-4e38-9898-fe27ecbebc27",
      "parameters": {
        "sendTo": "={{ $json.parent_email }}",
        "message": "={{ $json.html_report }}",
        "options": {
          "ccList": "teacher@school.edu"
        },
        "subject": "={{ $json.email_subject }}"
      },
      "credentials": {
        "gmailOAuth2": {
          "id": "PcTqvGU9uCunfltE",
          "name": "Gmail account - test"
        }
      },
      "typeVersion": 2.1
    },
    {
      "id": "ad762e3e-5680-47d8-98e2-b6ec9dffa028",
      "name": "Berichtsversand protokollieren",
      "type": "n8n-nodes-base.googleSheets",
      "position": [
        1080,
        -160
      ],
      "parameters": {
        "columns": {
          "value": {
            "timestamp": "={{ $now.toISO() }}",
            "student_id": "={{ $json.student_id }}",
            "parent_email": "={{ $json.parent_email }}",
            "student_name": "={{ $json.student_name }}",
            "overall_grade": "={{ $json.overall_grade }}",
            "delivery_status": "Sent Successfully"
          },
          "schema": [
            {
              "id": "timestamp",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "Timestamp",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "student_id",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "Student ID",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "student_name",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "Student Name",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "parent_email",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "Parent Email",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "overall_grade",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "Overall Grade",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "delivery_status",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "Delivery Status",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            }
          ],
          "mappingMode": "defineBelow"
        },
        "options": {},
        "operation": "append",
        "sheetName": {
          "__rl": true,
          "mode": "list",
          "value": "Delivery_Log",
          "cachedResultName": "Delivery Log"
        },
        "documentId": {
          "__rl": true,
          "mode": "list",
          "value": "YOUR_LOG_SHEET_ID",
          "cachedResultName": "Report Logs"
        },
        "authentication": "serviceAccount"
      },
      "credentials": {
        "googleApi": {
          "id": "ScSS2KxGQULuPtdy",
          "name": "Google Sheets- test"
        }
      },
      "typeVersion": 4.6
    },
    {
      "id": "d5956d08-fcc2-49ee-91c2-00350e228f39",
      "name": "Admin-Zusammenfassung senden",
      "type": "n8n-nodes-base.gmail",
      "position": [
        1300,
        -160
      ],
      "webhookId": "2b979d48-c8e7-4811-9db4-ac8f2edbc28b",
      "parameters": {
        "sendTo": "admin@school.edu",
        "message": "=<h2>📚 Student Progress Report Summary</h2><br><strong>Date:</strong> {{ $now.format('MMMM DD, YYYY') }}<br><strong>Status:</strong> ✅ All weekly progress reports sent successfully<br><br><em>Automated summary from Student Progress Report Generator</em>",
        "options": {},
        "subject": "📊 Weekly Student Progress Reports Complete"
      },
      "credentials": {
        "gmailOAuth2": {
          "id": "PcTqvGU9uCunfltE",
          "name": "Gmail account - test"
        }
      },
      "typeVersion": 2.1
    },
    {
      "id": "516a6491-f5fe-4800-aba5-06312a441716",
      "name": "Workflow-Info",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -660,
        -640
      ],
      "parameters": {
        "width": 600,
        "height": 300,
        "content": "## 📚 Student Academic Progress Report Generator\n\n### Features:\n• Weekly automated reports\n• LMS data integration\n• Parent email notifications\n• Performance trend analysis\n• HTML formatted reports\n• Admin summaries\n\n### Schedule: Every Monday at 9 AM"
      },
      "typeVersion": 1
    },
    {
      "id": "8fb08929-6149-49d9-bc60-5b0713074df7",
      "name": "Einrichtung erforderlich",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        20,
        -720
      ],
      "parameters": {
        "color": 3,
        "width": 420,
        "height": 200,
        "content": "## ⚙️ Setup Required\n\n1. Replace YOUR_STUDENT_SHEET_ID\n2. Replace YOUR_LOG_SHEET_ID\n3. Configure LMS API credentials\n4. Set up Gmail credentials\n5. Update email addresses"
      },
      "typeVersion": 1
    },
    {
      "id": "b41641c9-aef5-49a8-96ff-dc4ba3a10591",
      "name": "Wöchentlicher Zeitplan-Trigger",
      "type": "n8n-nodes-base.scheduleTrigger",
      "position": [
        -480,
        -160
      ],
      "parameters": {
        "rule": {
          "interval": [
            {
              "field": "weeks",
              "triggerAtDay": [
                1
              ],
              "triggerAtHour": 9
            }
          ]
        }
      },
      "typeVersion": 1.2
    }
  ],
  "active": false,
  "pinData": {},
  "settings": {
    "executionOrder": "v1"
  },
  "versionId": "f2deb505-6c16-43dd-9322-c18de89c5684",
  "connections": {
    "3df9207a-f7cb-421d-ba09-c8933669c09d": {
      "main": [
        [
          {
            "node": "b48f5de6-7a90-4a40-8f93-4bd3c2ab4d15",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "ad762e3e-5680-47d8-98e2-b6ec9dffa028": {
      "main": [
        [
          {
            "node": "d5956d08-fcc2-49ee-91c2-00350e228f39",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "3035a22d-352e-47ed-9628-cf72675f344f": {
      "main": [
        [
          {
            "node": "167244cb-3eb0-45ae-bfd3-a4a70e3573a9",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "7237f988-66a8-4e45-b6ac-2746485e7282": {
      "main": [
        [
          {
            "node": "3035a22d-352e-47ed-9628-cf72675f344f",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "167244cb-3eb0-45ae-bfd3-a4a70e3573a9": {
      "main": [
        [
          {
            "node": "ad762e3e-5680-47d8-98e2-b6ec9dffa028",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "441a7ac4-ef43-4692-b5b2-eaf3bf2ebac7": {
      "main": [
        [
          {
            "node": "7237f988-66a8-4e45-b6ac-2746485e7282",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "b41641c9-aef5-49a8-96ff-dc4ba3a10591": {
      "main": [
        [
          {
            "node": "3df9207a-f7cb-421d-ba09-c8933669c09d",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "b48f5de6-7a90-4a40-8f93-4bd3c2ab4d15": {
      "main": [
        [
          {
            "node": "441a7ac4-ef43-4692-b5b2-eaf3bf2ebac7",
            "type": "main",
            "index": 0
          }
        ]
      ]
    }
  }
}