Automatisierte Quiz-Zustellung und Statusverfolgung von Google Sheets zu Telegram

• •Telegram Bot Token
• •Möglicherweise sind Ziel-API-Anmeldedaten erforderlich
• •Google Sheets API-Anmeldedaten

{
  "meta": {
    "instanceId": "c5e931c455fcffaf668d434579b8144a682ba36c13be12948f5ddd388753d54c"
  },
  "nodes": [
    {
      "id": "56689bfc-a856-463e-bf85-53dde3ddd928",
      "name": "Quizdaten lesen",
      "type": "n8n-nodes-base.googleSheets",
      "notes": "Read all quiz rows from Google Sheets",
      "position": [
        -768,
        240
      ],
      "parameters": {
        "options": {},
        "sheetName": "Sheet1",
        "documentId": "<<<YOUR_SHEET_ID>>>"
      },
      "typeVersion": 4
    },
    {
      "id": "dec9e4a5-2815-4a95-a148-94d22f00cfea",
      "name": "Ausstehende Quizs filtern",
      "type": "n8n-nodes-base.code",
      "notes": "Keep only the earliest pending quiz (🟨). If none, pass a marker item.",
      "position": [
        -448,
        240
      ],
      "parameters": {
        "jsCode": "// Filter for pending quizzes (🟨) and pick the lowest quiz_number\nconst items = $input.all();\nconst pending = items.filter(i => i.json.status && i.json.status === '🟨');\nif (pending.length) {\n  pending.sort((a,b) => parseInt(a.json.quiz_number) - parseInt(b.json.quiz_number));\n  return [pending[0]];\n}\nreturn [{ json: { message: 'not exist' } }];\n"
      },
      "typeVersion": 2
    },
    {
      "id": "807289e3-a0e7-4750-b3f2-9d39fe20fee3",
      "name": "Quiz-Existenz prüfen",
      "type": "n8n-nodes-base.if",
      "notes": "If status exists (i.e., a real quiz row), send the poll; else send a refill notice.",
      "position": [
        -192,
        240
      ],
      "parameters": {
        "options": {},
        "conditions": {
          "options": {
            "leftValue": "",
            "caseSensitive": true,
            "typeValidation": "strict"
          },
          "combinator": "and",
          "conditions": [
            {
              "id": "dfdcc097-994d-4e6d-ac8b-9ef226a37ee1",
              "operator": {
                "type": "string",
                "operation": "exists",
                "singleValue": true
              },
              "leftValue": "={{ $json.status }}"
            }
          ]
        }
      },
      "typeVersion": 2
    },
    {
      "id": "3eadd4a7-7658-4f4e-89dc-3722d2e37c5d",
      "name": "Telegram-Umfrage senden",
      "type": "n8n-nodes-base.httpRequest",
      "notes": "Calls Telegram sendPoll. Set TELEGRAM_BOT_TOKEN and TELEGRAM_CHAT_ID as env vars.",
      "position": [
        112,
        112
      ],
      "parameters": {
        "url": "https://api.telegram.org/bot{{ $env.TELEGRAM_BOT_TOKEN }}/sendPoll",
        "method": "POST",
        "options": {},
        "sendQuery": true,
        "queryParameters": {
          "parameters": [
            {
              "name": "chat_id",
              "value": "={{ $env.TELEGRAM_CHAT_ID }}"
            },
            {
              "name": "question",
              "value": "={{ $json.question }}"
            },
            {
              "name": "options",
              "value": "=[\"{{ $json.option_a }}\", \"{{ $json.option_b }}\", \"{{ $json.option_c }}\", \"{{ $json.option_d }}\"]"
            }
          ]
        }
      },
      "typeVersion": 4.1
    },
    {
      "id": "c48ba649-d2ea-41ff-8574-4d02039f21f7",
      "name": "Statusaktualisierung vorbereiten",
      "type": "n8n-nodes-base.set",
      "notes": "Set the new status and include the quiz_number for matching.",
      "position": [
        352,
        0
      ],
      "parameters": {
        "fields": {
          "values": [
            {
              "name": "status",
              "stringValue": "✅"
            },
            {
              "name": "quiz_number",
              "stringValue": "={{ $('Read Quiz Data').item.json.quiz_number }}"
            }
          ]
        },
        "include": "selected",
        "options": {}
      },
      "typeVersion": 3.2
    },
    {
      "id": "ce67a494-ff14-4b9c-9f9f-8b047d58c8ee",
      "name": "Fehlendes Quiz benachrichtigen (Telegram)",
      "type": "n8n-nodes-base.telegram",
      "notes": "Sends a message if no pending quiz exists. Provide TELEGRAM_NOTIFY_CHAT_ID.",
      "position": [
        352,
        320
      ],
      "webhookId": "1b8bcd70-16eb-4e81-a0dd-aee8f847bd9e",
      "parameters": {
        "text": "⚠️ No pending quiz found in Google Sheet. Please refill.",
        "chatId": "={{ $env.TELEGRAM_NOTIFY_CHAT_ID }}",
        "additionalFields": {
          "appendAttribution": false
        }
      },
      "typeVersion": 1.1
    },
    {
      "id": "f949b91d-aa8b-4453-bc49-5cb21befc1b3",
      "name": "Quizstatus aktualisieren1",
      "type": "n8n-nodes-base.googleSheets",
      "notes": "Update the row that matches quiz_number, setting status to ✅.",
      "position": [
        560,
        64
      ],
      "parameters": {
        "columns": {
          "mappingMode": "autoMapInputData",
          "matchingColumns": [
            "quiz_number"
          ]
        },
        "options": {},
        "operation": "update",
        "sheetName": "Sheet1",
        "documentId": "<<<YOUR_SHEET_ID>>>"
      },
      "typeVersion": 4
    },
    {
      "id": "7175b83d-7483-484e-8ddd-6e6d63b409f5",
      "name": "Haftnotiz1",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -912,
        48
      ],
      "parameters": {
        "color": 7,
        "width": 368,
        "height": 432,
        "content": "## Note\n**Create the sheet with this headers**. \nquiz_number,\tquestion,\toption_a,\toption_b,\toption_c,\toption_d,\nstatus,"
      },
      "typeVersion": 1
    },
    {
      "id": "19b16b1c-1bdd-495d-acfa-fbb25fcd025f",
      "name": "Haftnotiz",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -528,
        48
      ],
      "parameters": {
        "color": 2,
        "width": 288,
        "height": 432,
        "content": "Filters rows marked 🟨 and selects the earliest quiz by number."
      },
      "typeVersion": 1
    },
    {
      "id": "6efd2557-da1d-4b98-a15e-111fb61a6561",
      "name": "Haftnotiz2",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -224,
        48
      ],
      "parameters": {
        "color": 4,
        "width": 288,
        "height": 432,
        "content": "Branches depending on whether a pending quiz was found."
      },
      "typeVersion": 1
    },
    {
      "id": "26069e47-cfb6-4947-878e-2f6a0a927ec0",
      "name": "Haftnotiz3",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        80,
        -16
      ],
      "parameters": {
        "color": 5,
        "width": 192,
        "height": 496,
        "content": "Sends the quiz as a poll to the group via Telegram Bot API."
      },
      "typeVersion": 1
    },
    {
      "id": "8b424c6d-6733-467f-93f5-f6380381dce8",
      "name": "Haftnotiz4",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        528,
        -16
      ],
      "parameters": {
        "color": 7,
        "width": 192,
        "height": 496,
        "content": "Marks the quiz row as ✅ to prevent re-sending."
      },
      "typeVersion": 1
    },
    {
      "id": "c1057db4-4926-41b7-a0e0-d36aaf3cbc61",
      "name": "Haftnotiz5",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        304,
        176
      ],
      "parameters": {
        "color": 5,
        "width": 192,
        "height": 304,
        "content": "Alerts admins if no pending quiz is available."
      },
      "typeVersion": 1
    }
  ],
  "pinData": {},
  "connections": {
    "56689bfc-a856-463e-bf85-53dde3ddd928": {
      "main": [
        [
          {
            "node": "dec9e4a5-2815-4a95-a148-94d22f00cfea",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "807289e3-a0e7-4750-b3f2-9d39fe20fee3": {
      "main": [
        [
          {
            "node": "3eadd4a7-7658-4f4e-89dc-3722d2e37c5d",
            "type": "main",
            "index": 0
          }
        ],
        [
          {
            "node": "ce67a494-ff14-4b9c-9f9f-8b047d58c8ee",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "3eadd4a7-7658-4f4e-89dc-3722d2e37c5d": {
      "main": [
        [
          {
            "node": "c48ba649-d2ea-41ff-8574-4d02039f21f7",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "dec9e4a5-2815-4a95-a148-94d22f00cfea": {
      "main": [
        [
          {
            "node": "807289e3-a0e7-4750-b3f2-9d39fe20fee3",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "c48ba649-d2ea-41ff-8574-4d02039f21f7": {
      "main": [
        [
          {
            "node": "f949b91d-aa8b-4453-bc49-5cb21befc1b3",
            "type": "main",
            "index": 0
          }
        ]
      ]
    }
  }
}