Skip to content

Instantly share code, notes, and snippets.

@parafeu
Last active August 22, 2025 14:58
Show Gist options
  • Select an option

  • Save parafeu/0838be4b086aec18f26de3465fb17eea to your computer and use it in GitHub Desktop.

Select an option

Save parafeu/0838be4b086aec18f26de3465fb17eea to your computer and use it in GitHub Desktop.
atlas_opco_import.json
{
"nodes": [
{
"parameters": {
"resource": "workflow",
"operation": "dispatchAndWait",
"owner": {
"__rl": true,
"value": "global-exam",
"mode": "list",
"cachedResultName": "global-exam",
"cachedResultUrl": "https://github.com/global-exam"
},
"repository": {
"__rl": true,
"value": "globalexam",
"mode": "list",
"cachedResultName": "globalexam",
"cachedResultUrl": "https://github.com/global-exam/globalexam"
},
"workflowId": {
"__rl": true,
"value": 179018555,
"mode": "list",
"cachedResultName": "[🤖 Automation] Atlas OPCO Complete List Workflow"
},
"ref": {
"__rl": true,
"value": "main",
"mode": "name"
},
"inputs": "={ \"fileUrl\": \"{{ $('Upload du ficher dans Google Drive').item.json.webContentLink }}\" }"
},
"type": "n8n-nodes-base.github",
"typeVersion": 1.1,
"position": [
1616,
208
],
"id": "f7d63906-7165-461a-9946-109b70a51d52",
"name": "Déclenchement Workflow Github et attente de fin",
"webhookId": "2622d4cd-25ad-4ff0-98dd-faf9dbbc36f7",
"credentials": {
"githubApi": {
"id": "clZqjJ57O9QBZonr",
"name": "[GlobalExam System] Github Actions Runner"
}
}
},
{
"parameters": {
"aggregate": "aggregateAllItemData",
"options": {}
},
"type": "n8n-nodes-base.aggregate",
"typeVersion": 1,
"position": [
720,
208
],
"id": "7518a26f-0a06-43fd-bdcb-20fd3b3501c5",
"name": "Agrégation des données"
},
{
"parameters": {
"conditions": {
"options": {
"caseSensitive": true,
"leftValue": "",
"typeValidation": "strict",
"version": 2
},
"conditions": [
{
"id": "e56c8ea9-e401-48f7-b57a-332f7c4b0fd7",
"leftValue": "={{ $('Déclenchement Workflow Github et attente de fin').error }}",
"rightValue": "",
"operator": {
"type": "string",
"operation": "notExists",
"singleValue": true
}
}
],
"combinator": "and"
},
"options": {}
},
"type": "n8n-nodes-base.if",
"typeVersion": 2.2,
"position": [
2064,
208
],
"id": "098832f0-3557-44a0-b9cc-9f88bce850ab",
"name": "Vérification absence d'erreur Workflow",
"executeOnce": false
},
{
"parameters": {
"errorMessage": "=Error during Github Workflow : {{ $json.error }}"
},
"type": "n8n-nodes-base.stopAndError",
"typeVersion": 1,
"position": [
2288,
400
],
"id": "f1718c16-5c50-40c7-badb-3d80c3ba7aa8",
"name": "Arrêt si erreur Workflow"
},
{
"parameters": {
"formTitle": "Import Atlas",
"formFields": {
"values": [
{
"fieldLabel": "Google Sheet Url",
"requiredField": true
}
]
},
"options": {}
},
"type": "n8n-nodes-base.formTrigger",
"typeVersion": 2.2,
"position": [
-624,
208
],
"id": "f2c10a8b-b6e0-4cdc-a2cf-cc4114fb932d",
"name": "Déclencheur formulaire Import Atlas",
"webhookId": "2e81a994-06e6-4734-bfc4-600fff861828"
},
{
"parameters": {
"jsCode": "const regex = /https:\\/\\/docs\\.google\\.com\\/spreadsheets\\/d\\/(?<documentId>[a-zA-Z0-9-_]+)(?:\\/.*?#gid=(?<sheetId>\\d+))?/;\n\nconst matches = $input.first().json['Google Sheet Url'].match(regex)\nconst groups = matches.groups;\n\nreturn [groups]"
},
"type": "n8n-nodes-base.code",
"typeVersion": 2,
"position": [
-400,
304
],
"id": "c7db3b6a-0d48-4827-ad80-329174956a9c",
"name": "Extraction IDs Google Sheet"
},
{
"parameters": {
"documentId": {
"__rl": true,
"mode": "list",
"value": ""
},
"sheetName": {
"__rl": true,
"value": "",
"mode": "list",
"cachedResultName": "",
"cachedResultUrl": ""
}
},
"type": "n8n-nodes-base.googleSheets",
"typeVersion": 4.6,
"position": [
-176,
112
],
"id": "3acc94ee-7948-4a1a-8154-e2e8edd38ee7",
"name": "Lecture données feuille maître",
"credentials": {
"googleSheetsOAuth2Api": {
"id": "V0siOVbJrKAdjrny",
"name": "[GlobalExam System] Google Sheet"
}
}
},
{
"parameters": {
"mode": "combine",
"advanced": true,
"mergeByFields": {
"values": [
{
"field1": "Numero de dossier",
"field2": "Numéro du dossier"
}
]
},
"joinMode": "keepNonMatches",
"outputDataFrom": "input2",
"options": {
"fuzzyCompare": true
}
},
"type": "n8n-nodes-base.merge",
"typeVersion": 3.2,
"position": [
48,
208
],
"id": "e89c8d83-ae6b-4542-96af-eb08ea326456",
"name": "Fusion données maître et import"
},
{
"parameters": {
"documentId": {
"__rl": true,
"value": "={{ $json.documentId }}",
"mode": "id"
},
"sheetName": {
"__rl": true,
"value": "={{ $json.sheetId }}",
"mode": "id"
},
"options": {}
},
"type": "n8n-nodes-base.googleSheets",
"typeVersion": 4.6,
"position": [
-176,
304
],
"id": "fc44fb52-0c83-4c75-9996-801fe584f19a",
"name": "Lecture données feuille importée",
"credentials": {
"googleSheetsOAuth2Api": {
"id": "V0siOVbJrKAdjrny",
"name": "[GlobalExam System] Google Sheet"
}
}
},
{
"parameters": {
"operation": "append",
"documentId": {
"__rl": true,
"mode": "list",
"value": ""
},
"sheetName": {
"__rl": true,
"value": "",
"mode": "list",
"cachedResultName": "",
"cachedResultUrl": ""
}
},
"type": "n8n-nodes-base.googleSheets",
"typeVersion": 4.6,
"position": [
2512,
16
],
"id": "c5510a65-81e9-473a-8def-1c8620f66e2d",
"name": "Ajout données fusionnées dans feuille maître",
"credentials": {
"googleSheetsOAuth2Api": {
"id": "V0siOVbJrKAdjrny",
"name": "[GlobalExam System] Google Sheet"
}
}
},
{
"parameters": {
"content": "### ⚠️ Attention\nModifier le fichier de destination avant la première utilisation",
"height": 320,
"color": 3
},
"type": "n8n-nodes-base.stickyNote",
"position": [
-256,
-48
],
"typeVersion": 1,
"id": "8c14f7a9-fe12-4216-9cfc-1ba683dfe363",
"name": "Sticky Note"
},
{
"parameters": {
"content": "### ⚠️ Attention\nModifier le fichier de destination avant la première utilisation",
"height": 320,
"color": 3
},
"type": "n8n-nodes-base.stickyNote",
"position": [
2448,
-144
],
"typeVersion": 1,
"id": "f3844767-4d88-4c39-a20a-d97291adfbe9",
"name": "Sticky Note1"
},
{
"parameters": {
"keys": {
"key": [
{
"currentKey": "Date de naissance du stagiaire 1",
"newKey": "Prénom et nom du stagiaire 1"
},
{
"currentKey": "Nombre de modules",
"newKey": "Date de naissance du stagiaire 1"
},
{
"currentKey": "Date de fin de formation module 1",
"newKey": "Date de début de formation module 1"
},
{
"currentKey": "Organisme de formation module 1",
"newKey": "Date de fin de formation module 1"
}
]
},
"additionalOptions": {}
},
"type": "n8n-nodes-base.renameKeys",
"typeVersion": 1,
"position": [
496,
144
],
"id": "860b8223-06ce-4edb-a942-5935b26c3477",
"name": "Nomage des clefs pour éviter le décalage"
},
{
"parameters": {
"conditions": {
"options": {
"caseSensitive": true,
"leftValue": "",
"typeValidation": "strict",
"version": 2
},
"conditions": [
{
"id": "a42ad028-ec2b-4895-871e-088a4e65b835",
"leftValue": "={{ Number.isNaN(parseInt(`${$json['Prénom et nom du stagiaire 1']}`)) }}",
"rightValue": "",
"operator": {
"type": "boolean",
"operation": "false",
"singleValue": true
}
}
],
"combinator": "and"
},
"options": {}
},
"type": "n8n-nodes-base.if",
"typeVersion": 2.2,
"position": [
272,
208
],
"id": "4df63e72-3568-4a0e-90de-d3e3b0a556b4",
"name": "Vérification décalage colonnes"
},
{
"parameters": {
"jsCode": "const workflowResult = $('Déclenchement Workflow Github et attente de fin').all()\nconst originalList = $('Fusion données maître et import').all()\n\nconst filteredResult = originalList.filter(\n (original) => !workflowResult.find(\n (item) => item.json['Numéro du dossier'] === original.json['Numéro du dossier']\n )\n)\n\nreturn [{\n json: {\n missed: filteredResult,\n added: workflowResult.length\n }\n}]\n"
},
"type": "n8n-nodes-base.code",
"typeVersion": 2,
"position": [
2288,
208
],
"id": "448ffc33-48cd-4bf8-938e-9de9285cfd4a",
"name": "Préparation des données rapport Email"
},
{
"parameters": {
"url": "https://gmail.googleapis.com/gmail/v1/users/me/profile",
"authentication": "predefinedCredentialType",
"nodeCredentialType": "gmailOAuth2",
"options": {}
},
"type": "n8n-nodes-base.httpRequest",
"typeVersion": 4.2,
"position": [
2512,
208
],
"id": "7d8c906d-22fc-489c-b102-87f0e32fdd24",
"name": "Récupération de l'email de l'utilisateur Gmail",
"credentials": {
"gmailOAuth2": {
"id": "YcllN5Vsu6tVE46r",
"name": "[GlobalExam System] Gmail"
}
}
},
{
"parameters": {
"sendTo": "={{ $json.emailAddress }}",
"subject": "=Rapport d'import ATLAS OPCO - {{ $now.format('dd/MM/y') }}",
"message": "=<!DOCTYPE html>\n<html lang=\"fr\">\n<head>\n <meta charset=\"utf-8\" />\n <meta name=\"x-apple-disable-message-reformatting\">\n <meta name=\"viewport\" content=\"width=device-width,initial-scale=1\" />\n <title>Rapport d'import ATLAS OPCO</title>\n <style>\n /* Styles de base compatibles emails */\n .container{max-width:720px;margin:0 auto;padding:24px;font-family:system-ui,-apple-system,Segoe UI,Roboto,Arial,sans-serif;color:#111;background:#f6f7f9}\n .card{background:#fff;border:1px solid #e5e7eb;border-radius:12px;padding:20px}\n .title{margin:0 0 8px 0;font-size:20px;line-height:1.3}\n .subtitle{margin:0 0 16px 0;color:#4b5563;font-size:14px}\n .kpi{display:inline-block;padding:10px 14px;border-radius:999px;background:#eef2ff;color:#3730a3;font-weight:600;font-size:14px;margin:8px 8px 16px 0}\n .hr{height:1px;background:#e5e7eb;margin:16px 0}\n .badge-ok{display:inline-block;padding:8px 12px;border-radius:8px;background:#ecfdf5;color:#065f46;font-weight:600;font-size:13px}\n .badge-warn{display:inline-block;padding:8px 12px;border-radius:8px;background:#fff7ed;color:#9a3412;font-weight:600;font-size:13px}\n\n /* Tableau */\n table{width:100%;border-collapse:collapse;margin-top:12px}\n th,td{border:1px solid #e5e7eb;padding:8px 10px;text-align:left;vertical-align:top;font-size:13px;line-height:1.35}\n th{background:#f3f4f6;font-weight:700}\n tbody tr:nth-child(even){background:#fafafa}\n code{font-family:ui-monospace,SFMono-Regular,Menlo,Consolas,monospace;font-size:12px;background:#f3f4f6;padding:2px 4px;border-radius:4px}\n .footer{color:#6b7280;font-size:12px;margin-top:16px}\n </style>\n</head>\n<body>\n <div class=\"container\">\n <div class=\"card\">\n <h1 class=\"title\">Rapport d'import ATLAS OPCO</h1>\n <p class=\"subtitle\">Résumé de l’exécution et détails des lignes non importées.</p>\n\n <!-- KPIs -->\n <div class=\"kpi\">\n Ajoutés : {{ $('Préparation des données rapport Email').item.json.added ?? 0 }}\n </div>\n <div class=\"kpi\">\n Lignes manquées : {{ ($('Préparation des données rapport Email').item.json.missed || []).length }}\n </div>\n\n <div class=\"hr\"></div>\n\n <!-- Bloc si aucune ligne manquée -->\n {{ \n (($('Préparation des données rapport Email').item.json.missed || []).length === 0) \n ? '<span class=\"badge-ok\">✅ Aucune ligne manquée</span>' \n : '' \n }}\n\n <!-- Bloc tableau des lignes manquées -->\n {{\n (() => {\n const missed = $('Préparation des données rapport Email').item.json.missed || [];\n if (!missed.length) return '';\n\n const rows = missed.map(obj => {\n return `<tr>\n <td>${obj.json['Numéro du dossier']}</td>\n <td>${obj.json['Prénom et nom du stagiaire 1']}</td>\n <td>${obj.json['Nom de l’établissement']}</td>\n </tr>`;\n }).join('');\n\n return `\n <span class=\"badge-warn\">⚠️ Détails des lignes non importées</span>\n <table role=\"table\" aria-label=\"Lignes non importées\">\n <thead>\n <tr>\n <th>Numéro de dossier</th>\n <th>Stagiaire</th>\n <th>Etablissement</th>\n </tr>\n </thead>\n <tbody>${rows}</tbody>\n </table>\n `;\n })()\n }}\n </div>\n\n <p class=\"footer\">\n Email généré automatiquement — ATLAS OPCO • n8n → Gmail.\n </p>\n </div>\n</body>\n</html>\n",
"options": {}
},
"type": "n8n-nodes-base.gmail",
"typeVersion": 2.1,
"position": [
2736,
208
],
"id": "36894321-bdca-41bc-a3dd-1306a8b6e7a4",
"name": "Envoi du rapport d'import",
"webhookId": "345b8b38-6c4f-42ff-a57c-1c69dfaa21be",
"credentials": {
"gmailOAuth2": {
"id": "YcllN5Vsu6tVE46r",
"name": "[GlobalExam System] Gmail"
}
}
},
{
"parameters": {
"operation": "toJson",
"options": {}
},
"type": "n8n-nodes-base.convertToFile",
"typeVersion": 1.1,
"position": [
944,
208
],
"id": "dafbf48f-1090-471e-838d-b5149dca718a",
"name": "Création du fichier JSON"
},
{
"parameters": {
"name": "={{ $('Extraction IDs Google Sheet').item.json.documentId }}.json",
"driveId": {
"__rl": true,
"mode": "list",
"value": "My Drive"
},
"folderId": {
"__rl": true,
"mode": "list",
"value": "root",
"cachedResultName": "/ (Root folder)"
},
"options": {}
},
"type": "n8n-nodes-base.googleDrive",
"typeVersion": 3,
"position": [
1168,
208
],
"id": "7c4aa236-bdfe-442a-a002-08da4e407bb4",
"name": "Upload du ficher dans Google Drive",
"credentials": {
"googleDriveOAuth2Api": {
"id": "jLxoYRPcaU94kulH",
"name": "[GlobalExam System] Google Drive"
}
}
},
{
"parameters": {
"operation": "share",
"fileId": {
"__rl": true,
"value": "={{ $json.id }}",
"mode": "id"
},
"permissionsUi": {
"permissionsValues": {
"role": "reader",
"type": "anyone"
}
},
"options": {}
},
"type": "n8n-nodes-base.googleDrive",
"typeVersion": 3,
"position": [
1392,
208
],
"id": "762fe99b-9074-4619-9ed5-796b7db091af",
"name": "Mise à jour des droits du fichier Google Drive",
"credentials": {
"googleDriveOAuth2Api": {
"id": "jLxoYRPcaU94kulH",
"name": "[GlobalExam System] Google Drive"
}
}
},
{
"parameters": {
"operation": "deleteFile",
"fileId": {
"__rl": true,
"value": "={{ $('Upload du ficher dans Google Drive').item.json.id }}",
"mode": "id"
},
"options": {
"deletePermanently": true
}
},
"type": "n8n-nodes-base.googleDrive",
"typeVersion": 3,
"position": [
1840,
208
],
"id": "76d961e8-9e09-4dcb-a0fd-08745715ccf4",
"name": "Suppression du fichier Google Drive",
"executeOnce": true,
"credentials": {
"googleDriveOAuth2Api": {
"id": "jLxoYRPcaU94kulH",
"name": "[GlobalExam System] Google Drive"
}
},
"onError": "continueErrorOutput"
},
{
"parameters": {
"jsCode": "return $('Déclenchement Workflow Github et attente de fin').all()"
},
"type": "n8n-nodes-base.code",
"typeVersion": 2,
"position": [
2288,
16
],
"id": "5f7b35bc-8b97-45cb-bb14-b3732d6fe402",
"name": "Récupération des items du workflow github"
}
],
"connections": {
"Déclenchement Workflow Github et attente de fin": {
"main": [
[
{
"node": "Suppression du fichier Google Drive",
"type": "main",
"index": 0
}
]
]
},
"Agrégation des données": {
"main": [
[
{
"node": "Création du fichier JSON",
"type": "main",
"index": 0
}
]
]
},
"Vérification absence d'erreur Workflow": {
"main": [
[
{
"node": "Préparation des données rapport Email",
"type": "main",
"index": 0
},
{
"node": "Récupération des items du workflow github",
"type": "main",
"index": 0
}
],
[
{
"node": "Arrêt si erreur Workflow",
"type": "main",
"index": 0
}
]
]
},
"Déclencheur formulaire Import Atlas": {
"main": [
[
{
"node": "Extraction IDs Google Sheet",
"type": "main",
"index": 0
},
{
"node": "Lecture données feuille maître",
"type": "main",
"index": 0
}
]
]
},
"Extraction IDs Google Sheet": {
"main": [
[
{
"node": "Lecture données feuille importée",
"type": "main",
"index": 0
}
]
]
},
"Lecture données feuille maître": {
"main": [
[
{
"node": "Fusion données maître et import",
"type": "main",
"index": 0
}
]
]
},
"Fusion données maître et import": {
"main": [
[
{
"node": "Vérification décalage colonnes",
"type": "main",
"index": 0
}
]
]
},
"Lecture données feuille importée": {
"main": [
[
{
"node": "Fusion données maître et import",
"type": "main",
"index": 1
}
]
]
},
"Nomage des clefs pour éviter le décalage": {
"main": [
[
{
"node": "Agrégation des données",
"type": "main",
"index": 0
}
]
]
},
"Vérification décalage colonnes": {
"main": [
[
{
"node": "Nomage des clefs pour éviter le décalage",
"type": "main",
"index": 0
}
],
[
{
"node": "Agrégation des données",
"type": "main",
"index": 0
}
]
]
},
"Préparation des données rapport Email": {
"main": [
[
{
"node": "Récupération de l'email de l'utilisateur Gmail",
"type": "main",
"index": 0
}
]
]
},
"Récupération de l'email de l'utilisateur Gmail": {
"main": [
[
{
"node": "Envoi du rapport d'import",
"type": "main",
"index": 0
}
]
]
},
"Création du fichier JSON": {
"main": [
[
{
"node": "Upload du ficher dans Google Drive",
"type": "main",
"index": 0
}
]
]
},
"Upload du ficher dans Google Drive": {
"main": [
[
{
"node": "Mise à jour des droits du fichier Google Drive",
"type": "main",
"index": 0
}
]
]
},
"Mise à jour des droits du fichier Google Drive": {
"main": [
[
{
"node": "Déclenchement Workflow Github et attente de fin",
"type": "main",
"index": 0
}
]
]
},
"Suppression du fichier Google Drive": {
"main": [
[
{
"node": "Vérification absence d'erreur Workflow",
"type": "main",
"index": 0
}
],
[
{
"node": "Vérification absence d'erreur Workflow",
"type": "main",
"index": 0
}
]
]
},
"Récupération des items du workflow github": {
"main": [
[
{
"node": "Ajout données fusionnées dans feuille maître",
"type": "main",
"index": 0
}
]
]
}
},
"pinData": {},
"meta": {
"templateCredsSetupCompleted": true,
"instanceId": "310010309e2c7c2bdb8a6e81cb13e7ef5d333843e3bf71b9d54d681abcc12f89"
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment