Compliance-Berichtssammler

• •MySQL-Datenbankverbindungsdaten
• •Google Drive API-Anmeldedaten
• •Google Sheets API-Anmeldedaten

{
  "id": "Un9KXLeARM23XTIQ",
  "meta": {
    "instanceId": "14e4c77104722ab186539dfea5182e419aecc83d85963fe13f6de862c875ebfa",
    "templateCredsSetupCompleted": true
  },
  "name": "Compliance Report Collector",
  "tags": [],
  "nodes": [
    {
      "id": "c1d13027-17a3-485a-b43b-e7fece0612c6",
      "name": "Google Sheets Form-Trigger",
      "type": "n8n-nodes-base.googleSheetsTrigger",
      "position": [
        -440,
        60
      ],
      "parameters": {
        "event": "rowAdded",
        "options": {},
        "pollTimes": {
          "item": [
            {
              "mode": "everyMinute"
            }
          ]
        },
        "sheetName": {
          "__rl": true,
          "mode": "url",
          "value": ""
        },
        "documentId": {
          "__rl": true,
          "mode": "list",
          "value": "1U4MfFW06VMW0gW3fAkGFDvDnomb1j_0E-0lbEkPlli0",
          "cachedResultUrl": "",
          "cachedResultName": "compliance_report_sheet(Responses)"
        }
      },
      "credentials": {
        "googleSheetsTriggerOAuth2Api": {
          "id": "WF8FtUtPeBSlrlAC",
          "name": "Google Sheets Trigger account 2"
        }
      },
      "typeVersion": 1
    },
    {
      "id": "038c1dae-f38c-4a98-ae1d-5376d81e3332",
      "name": "In MySQL protokollieren",
      "type": "n8n-nodes-base.mySql",
      "position": [
        660,
        60
      ],
      "parameters": {
        "table": {
          "__rl": true,
          "mode": "name",
          "value": "report_logs"
        },
        "options": {}
      },
      "credentials": {
        "mySql": {
          "id": "YqKBp66liP3vXdbi",
          "name": "MySQL account 4"
        }
      },
      "typeVersion": 2.2
    },
    {
      "id": "d644afd6-ad2e-4931-ba17-42c1d635c3fc",
      "name": "Felder umbenennen",
      "type": "n8n-nodes-base.set",
      "position": [
        440,
        60
      ],
      "parameters": {
        "mode": "raw",
        "options": {},
        "jsonOutput": "=\n{\n  \"reporter\": \"{{$json.reporter}}\",\n  \"category\": \"{{$json.category}}\",\n  \"timestamp\": \"{{$json.timestamp}}\",\n  \"folder_id\": \"{{$json.folder_id}}\",\n  \"file_name\": \"{{$binary.data.fileName}}\",\n  \"mime_type\": \"{{$binary.data.mimeType}}\",\n  \"email\": \"{{$json.email}}\",\n  \"description\": \"{{$json.description}}\"\n}"
      },
      "typeVersion": 3.4
    },
    {
      "id": "ba6a2d4c-f548-446f-ab24-20c518e06f86",
      "name": "Google Sheets-Daten extrahieren",
      "type": "n8n-nodes-base.function",
      "notes": "Extract metadata for logging",
      "position": [
        -220,
        60
      ],
      "parameters": {
        "functionCode": "// Input: rows from Google Sheets Trigger\nconst rows = $input.all();\nconst out = [];\n\nfor (const item of rows) {\n  const j = item.json;\n\n  const url = j['Upload Report File'] || '';\n  // supports both ?id=... and /d/.../view URLs\n  const m =\n    url.match(/[?&]id=([^&]+)/) ||\n    url.match(/\\/d\\/([a-zA-Z0-9_-]+)/);\n\n  out.push({\n    json: {\n      reporter: j['Reporter Name'] || null,\n      category: j['Report Category'] || null,\n      timestamp: new Date(j['Timestamp']).toISOString(),\n      folder_id: m ? m[1] : null,\n      email: j['Email Address'] || null,\n      description: j['Description'] || null,\n    },\n  });\n}\n\nreturn out;\n"
      },
      "typeVersion": 1
    },
    {
      "id": "efa9cce4-db44-4296-8c76-9bf12ca68d52",
      "name": "Metadaten hochgeladener Datei abrufen",
      "type": "n8n-nodes-base.googleDrive",
      "position": [
        0,
        60
      ],
      "parameters": {
        "fileId": {
          "__rl": true,
          "mode": "id",
          "value": "={{ $json.folder_id }}"
        },
        "options": {},
        "operation": "download"
      },
      "credentials": {
        "googleDriveOAuth2Api": {
          "id": "dkusrOXTExpMxEAe",
          "name": "Google Drive account 7"
        }
      },
      "typeVersion": 3
    },
    {
      "id": "6abc9f56-0b24-4e47-840f-72ac8421d90c",
      "name": "Tabellen- und Dateidaten zusammenführen",
      "type": "n8n-nodes-base.merge",
      "position": [
        220,
        60
      ],
      "parameters": {
        "mode": "combine",
        "options": {
          "includeUnpaired": true
        },
        "combineBy": "combineByPosition"
      },
      "typeVersion": 3.2,
      "alwaysOutputData": true
    },
    {
      "id": "779477a4-ecac-4cd5-8b5f-e385da6c64fc",
      "name": "Haftnotiz",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -540,
        -100
      ],
      "parameters": {
        "width": 1400,
        "height": 420,
        "content": "## Compliance Report Collector (Google Form → Drive + MySQL)\n\n\n"
      },
      "typeVersion": 1
    },
    {
      "id": "071841f7-f553-4637-806a-0db0d27e8de4",
      "name": "Haftnotiz1",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -540,
        380
      ],
      "parameters": {
        "width": 1420,
        "height": 1240,
        "content": "## Description\n\n### 1. Google Sheets form Trigger\n- Watches the linked Google Sheet for new rows (submitted via Google Form). When a new row is added, it triggers the workflow and passes the form data to the next node.\n\n---\n\n### 2. Extract googlesheet data\n- Parses the Google Sheet row data.  \n- Extracts the Google Drive file ID from the **\"Upload Report File\"** URL.  \n- Keeps key form fields:\n `reporter`\n `category`\n `timestamp`\n `email`\n `description`\n `folder_id`\n\n---\n\n### 3. Get file uploaded metadata\n- Uses the Google Drive node to fetch metadata for the uploaded file by its `folder_id`.  \n- Downloads the binary temporarily so we can extract:\n `fileName`\n `mimeType`\n\n---\n\n### 4. Merge sheet & file data\nCombines:\n- Google Sheet form data\n- Google Drive file metadata  \ninto a single JSON object per submission.  \nEnsures both sets of information are available for insertion into MySQL.\n\n---\n\n### 5. Rename Fields\nNormalizes and renames merged fields to match MySQL column names:\n- `fileName` → `file_name`\n- `mimeType` → `mime_type`\n\nRemoves unnecessary fields so only required columns remain.\n\n---\n\n### 6. Log to MySQL\nInserts the cleaned and normalized data into the `report_logs` table in MySQL, storing:\n- Form submission details\n- Uploaded file’s name\n- MIME type\n\n---\n"
      },
      "typeVersion": 1
    }
  ],
  "active": false,
  "pinData": {},
  "settings": {
    "executionOrder": "v1"
  },
  "versionId": "7f30bbf3-aa3d-462b-b69e-7bf403e3c880",
  "connections": {
    "d644afd6-ad2e-4931-ba17-42c1d635c3fc": {
      "main": [
        [
          {
            "node": "038c1dae-f38c-4a98-ae1d-5376d81e3332",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "6abc9f56-0b24-4e47-840f-72ac8421d90c": {
      "main": [
        [
          {
            "node": "d644afd6-ad2e-4931-ba17-42c1d635c3fc",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "ba6a2d4c-f548-446f-ab24-20c518e06f86": {
      "main": [
        [
          {
            "node": "efa9cce4-db44-4296-8c76-9bf12ca68d52",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "efa9cce4-db44-4296-8c76-9bf12ca68d52": {
      "main": [
        [
          {
            "node": "6abc9f56-0b24-4e47-840f-72ac8421d90c",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "c1d13027-17a3-485a-b43b-e7fece0612c6": {
      "main": [
        [
          {
            "node": "ba6a2d4c-f548-446f-ab24-20c518e06f86",
            "type": "main",
            "index": 0
          }
        ]
      ]
    }
  }
}