Détection de rebond et d'adresse invalide (déclencheur Gmail)

• •Compte Google et informations d'identification Gmail API
• •Token Bot Slack ou URL Webhook
• •Informations d'identification Google Sheets API

{
  "id": "BwfB2WsAGCX5rTFB",
  "meta": {
    "instanceId": "8443f10082278c46aa5cf3acf8ff0f70061a2c58bce76efac814b16290845177",
    "templateCredsSetupCompleted": true
  },
  "name": "Bounce & Invalid Detection (Gmail Trigger)",
  "tags": [],
  "nodes": [
    {
      "id": "bff28463-72c1-415a-aac0-8d19808addcf",
      "name": "Lors du clic sur 'Exécuter le workflow'",
      "type": "n8n-nodes-base.manualTrigger",
      "position": [
        384,
        1104
      ],
      "parameters": {},
      "typeVersion": 1
    },
    {
      "id": "3b5dabd0-e0ad-4b27-86d3-fc326edc2842",
      "name": "Analyser les adresses email en rebond",
      "type": "n8n-nodes-base.function",
      "position": [
        1184,
        1120
      ],
      "parameters": {
        "functionCode": "// Input: items array (from Gmail node output)\n// Output: one item per failed email address\n\nreturn items\n  .map(item => {\n    const snippet = item.json.snippet || \"\";\n    const match = snippet.match(/wasn't delivered to\\s+([\\w.-]+@[\\w.-]+\\.\\w+)/i);\n    if (match) {\n      return {\n        json: {\n          failedEmail: match[1],\n          subject: item.json.Subject || \"\",\n          id: item.json.id,\n          snippet: snippet\n        }\n      };\n    }\n    return null;\n  })\n  .filter(item => item !== null);\n"
      },
      "typeVersion": 1
    },
    {
      "id": "bde866db-a655-4cb3-bba1-81dbda49e09c",
      "name": "Récupérer tous les contacts email",
      "type": "n8n-nodes-base.googleSheets",
      "position": [
        736,
        1296
      ],
      "parameters": {
        "options": {},
        "sheetName": {
          "__rl": true,
          "mode": "list",
          "value": "gid=0",
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/1-4TaBE0cOTO1iwrrah5y822s8KXrWWkxFmJYiGCfWx4/edit#gid=0",
          "cachedResultName": "Sheet1"
        },
        "documentId": {
          "__rl": true,
          "mode": "list",
          "value": "1-4TaBE0cOTO1iwrrah5y822s8KXrWWkxFmJYiGCfWx4",
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/1-4TaBE0cOTO1iwrrah5y822s8KXrWWkxFmJYiGCfWx4/edit?usp=drivesdk",
          "cachedResultName": "Fraud Email"
        }
      },
      "credentials": {
        "googleSheetsOAuth2Api": {
          "id": "s4dP1fNuVZ2gWvs3",
          "name": "Google Sheets account"
        }
      },
      "typeVersion": 3
    },
    {
      "id": "ac79fbd2-381e-4de2-b5e2-e7502752978d",
      "name": "Écrire le statut dans la feuille",
      "type": "n8n-nodes-base.googleSheets",
      "position": [
        1904,
        1232
      ],
      "parameters": {
        "options": {},
        "fieldsUi": {
          "values": [
            {
              "column": "Status",
              "fieldValue": "={{ $json.Status }}"
            },
            {
              "column": "Last Updated",
              "fieldValue": "={{ $json[\"Last Updated\"] }}"
            }
          ]
        },
        "operation": "update",
        "sheetName": {
          "__rl": true,
          "mode": "list",
          "value": "gid=0",
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/1-4TaBE0cOTO1iwrrah5y822s8KXrWWkxFmJYiGCfWx4/edit#gid=0",
          "cachedResultName": "Sheet1"
        },
        "documentId": {
          "__rl": true,
          "mode": "list",
          "value": "1-4TaBE0cOTO1iwrrah5y822s8KXrWWkxFmJYiGCfWx4",
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/1-4TaBE0cOTO1iwrrah5y822s8KXrWWkxFmJYiGCfWx4/edit?usp=drivesdk",
          "cachedResultName": "Fraud Email"
        },
        "valueToMatchOn": "={{ $json.Name }}",
        "columnToMatchOn": "Name"
      },
      "credentials": {
        "googleSheetsOAuth2Api": {
          "id": "s4dP1fNuVZ2gWvs3",
          "name": "Google Sheets account"
        }
      },
      "typeVersion": 3
    },
    {
      "id": "7f7c1971-5591-4f6c-a3dd-06c3e3e20bb6",
      "name": "Déclencheur du rapport quotidien 19h",
      "type": "n8n-nodes-base.cron",
      "position": [
        304,
        1904
      ],
      "parameters": {
        "triggerTimes": {
          "item": [
            {
              "hour": 19
            }
          ]
        }
      },
      "typeVersion": 1
    },
    {
      "id": "36ffd609-d7c2-461f-82c1-c7303f3e5f46",
      "name": "Lire les données mises à jour de la feuille",
      "type": "n8n-nodes-base.googleSheets",
      "position": [
        608,
        1904
      ],
      "parameters": {
        "options": {},
        "sheetName": {
          "__rl": true,
          "mode": "list",
          "value": "gid=0",
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/1-4TaBE0cOTO1iwrrah5y822s8KXrWWkxFmJYiGCfWx4/edit#gid=0",
          "cachedResultName": "Sheet1"
        },
        "documentId": {
          "__rl": true,
          "mode": "list",
          "value": "1-4TaBE0cOTO1iwrrah5y822s8KXrWWkxFmJYiGCfWx4",
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/1-4TaBE0cOTO1iwrrah5y822s8KXrWWkxFmJYiGCfWx4/edit?usp=drivesdk",
          "cachedResultName": "Fraud Email"
        }
      },
      "credentials": {
        "googleSheetsOAuth2Api": {
          "id": "s4dP1fNuVZ2gWvs3",
          "name": "Google Sheets account"
        }
      },
      "typeVersion": 3
    },
    {
      "id": "4ad00c44-5e60-452a-b4ab-89cc23b38c7c",
      "name": "Envoyer le résumé quotidien Slack",
      "type": "n8n-nodes-base.slack",
      "position": [
        1120,
        1904
      ],
      "parameters": {
        "text": "=📢 *Daily Bounce Cleanup Report*  \n📧 Invalid Marked: {{$json[\"Invalid emails\"]}}  \n📭 No Action Marked: {{$json[\"No activity\"]}}  \n✅ Keep your lists healthy 💪\n",
        "channel": "#email-cleanup",
        "attachments": [],
        "otherOptions": {}
      },
      "credentials": {
        "slackApi": {
          "id": "rNqvWj9TfChPVRYY",
          "name": "Slack account vivek"
        }
      },
      "typeVersion": 1
    },
    {
      "id": "c3984c6c-4861-46fe-8b6b-68bf842f3fa4",
      "name": "Récupérer les notifications de rebond",
      "type": "n8n-nodes-base.gmail",
      "position": [
        672,
        1120
      ],
      "webhookId": "c4ef7681-fe33-4c55-952d-db9916b6ee6e",
      "parameters": {
        "filters": {
          "sender": "mailer-daemon@googlemail.com"
        },
        "operation": "getAll",
        "returnAll": true
      },
      "credentials": {
        "gmailOAuth2": {
          "id": "RchiXdmY8WaQhOSJ",
          "name": "Gmail account"
        }
      },
      "typeVersion": 2.1
    },
    {
      "id": "ca28e3bf-d30f-4e54-affc-5b36be6b928e",
      "name": "Obtenir les 5 derniers rebonds",
      "type": "n8n-nodes-base.code",
      "position": [
        880,
        1120
      ],
      "parameters": {
        "jsCode": "// Get all Gmail messages\nconst emails = items.map(item => item.json);\n\n// Sort by internalDate (descending → newest first)\nemails.sort((a, b) => Number(b.internalDate) - Number(a.internalDate));\n\n// Slice the top 5 messages\nconst latestFive = emails.slice(0, 5);\n\n// Return each of the top 5 as separate items\nreturn latestFive.map(email => ({ json: email }));\n"
      },
      "typeVersion": 2
    },
    {
      "id": "a7f0f64f-1af0-4ad9-814d-49a1cff65699",
      "name": "Combiner les données de rebond et de contacts",
      "type": "n8n-nodes-base.merge",
      "position": [
        1424,
        1232
      ],
      "parameters": {},
      "typeVersion": 3.2
    },
    {
      "id": "84ff56ce-1a6f-472f-b4ab-bf50705d87f9",
      "name": "Correspondre et mettre à jour le statut des contacts",
      "type": "n8n-nodes-base.code",
      "position": [
        1616,
        1232
      ],
      "parameters": {
        "jsCode": "// Separate bounce reports and contact rows\nconst failedEmails = items\n  .filter(i => i.json.failedEmail)\n  .map(i => i.json.failedEmail);\n\nconst contacts = items.filter(i => i.json.Email);\n\n// Current timestamp\nconst now = new Date().toISOString();\n\n// Update contacts based on whether they appear in failedEmails\nconst updatedContacts = contacts.map(item => {\n  const email = item.json.Email;\n  const isFailed = failedEmails.includes(email);\n\n  return {\n    json: {\n      row_number: item.json.row_number,\n      Name: item.json.Name,\n      Email: email,\n      Status: isFailed ? \"Not Found\" : \"Not Sent\",\n      \"Last Updated\": now\n    }\n  };\n});\n\nreturn updatedContacts;\n"
      },
      "typeVersion": 2
    },
    {
      "id": "d020cba9-9eaa-406c-9bc6-d6d3c20ba1db",
      "name": "Calculer les statistiques récapitulatives",
      "type": "n8n-nodes-base.code",
      "position": [
        848,
        1904
      ],
      "parameters": {
        "jsCode": "// Extract all contact rows\nconst contacts = items.map(item => item.json);\n\n// Count based on status\nlet invalidCount = 0;\nlet noActivityCount = 0;\n\nfor (const contact of contacts) {\n  const status = (contact.Status || \"\").toLowerCase();\n  if (status === \"not found\") invalidCount++;\n  if (status === \"not sent\") noActivityCount++;\n}\n\n// Return clean summary as one item\nreturn [\n  {\n    json: {\n      \"Invalid emails\": invalidCount,\n      \"No activity\": noActivityCount\n    }\n  }\n];\n"
      },
      "typeVersion": 2
    },
    {
      "id": "5ae11ced-5c43-4a24-a8b2-2a49757f7e4e",
      "name": "Note adhésive 1",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -320,
        960
      ],
      "parameters": {
        "width": 446,
        "height": 456,
        "content": "## 🎯 MAIN WORKFLOW - Email Bounce Detection\n\n**Purpose:** Detects bounced emails from Gmail and updates Google Sheets\n\n**Flow:**\n1. Manually trigger or schedule to run\n2. Fetch bounce notifications from Gmail (mailer-daemon)\n3. Get latest 5 bounces to process\n4. Parse email addresses from bounce messages\n5. Fetch all contacts from Google Sheet\n6. Merge bounce data with contact list\n7. Match emails and update status (\"Not Found\" if bounced)\n8. Write updated status back to sheet\n\n**Sheet Columns Updated:**\n- Status: \"Not Found\" (bounced) or \"Not Sent\" (no bounce)\n- Last Updated: Current timestamp"
      },
      "typeVersion": 1
    },
    {
      "id": "1615d0f0-4afd-4da2-bb0d-9bd41db897cc",
      "name": "Note adhésive 2",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -352,
        1728
      ],
      "parameters": {
        "width": 446,
        "height": 317,
        "content": "## 📊 DAILY REPORTING WORKFLOW\n\n**Purpose:** Sends daily summary to Slack at 7 PM\n\n**Flow:**\n1. Cron trigger fires daily at 19:00 (7 PM)\n2. Read all data from Google Sheet\n3. Calculate statistics:\n   - Count \"Not Found\" status (invalid emails)\n   - Count \"Not Sent\" status (no activity)\n4. Format and send summary to Slack channel\n\n**Slack Channel:** #email-cleanup"
      },
      "typeVersion": 1
    },
    {
      "id": "d795adbb-b531-4b9c-8e0d-eda50037f4e1",
      "name": "Note adhésive 3",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        224,
        768
      ],
      "parameters": {
        "width": 237,
        "height": 300,
        "content": "## 🚀 START HERE\n\n**Two Ways to Run:**\n\n1. **Manual:** Click \"Execute workflow\" button for testing\n\n2. **Automatic:** Set up a schedule trigger (replace manual trigger)\n\n**Note:** This triggers the main bounce detection workflow"
      },
      "typeVersion": 1
    },
    {
      "id": "32e515c9-4c0a-40c9-a97f-e06aea785971",
      "name": "Note adhésive 4",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        560,
        736
      ],
      "parameters": {
        "width": 237,
        "height": 348,
        "content": "## 📬 FETCH BOUNCES\n\n**What it does:**\nRetrieves ALL bounce notification emails from Gmail\n\n**Filter:**\n- Sender: mailer-daemon@googlemail.com\n\n**Output:**\nList of bounce emails with snippets containing failed addresses"
      },
      "typeVersion": 1
    },
    {
      "id": "5afaf827-064a-4149-a855-61cb71c9d5d3",
      "name": "Note adhésive 5",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        896,
        704
      ],
      "parameters": {
        "width": 237,
        "height": 380,
        "content": "## 🔢 LIMIT PROCESSING\n\n**What it does:**\nSorts bounces by date and keeps only the 5 newest\n\n**Why:**\n- Prevents overwhelming the workflow\n- Focuses on recent bounces\n- Improves performance\n\n**Sorting:** Newest first (descending by internalDate)"
      },
      "typeVersion": 1
    },
    {
      "id": "fa7f3745-4b40-4b7c-be33-5ebeceb7352b",
      "name": "Note adhésive 6",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        1264,
        704
      ],
      "parameters": {
        "width": 237,
        "height": 380,
        "content": "## 🔍 EXTRACT EMAILS\n\n**What it does:**\nParses bounce message snippets to extract failed email addresses\n\n**Regex Pattern:**\n`wasn't delivered to [email]`\n\n**Output Fields:**\n- failedEmail\n- subject\n- id\n- snippet"
      },
      "typeVersion": 1
    },
    {
      "id": "1c0510ae-68e7-4c42-ac92-5523fe4b7978",
      "name": "Note adhésive 7",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        896,
        1360
      ],
      "parameters": {
        "width": 237,
        "height": 412,
        "content": "## 📋 GET CONTACTS\n\n**What it does:**\nReads ALL rows from Google Sheet\n\n**Sheet:** Fraud Email\n**Tab:** Sheet1\n\n**Expected Columns:**\n- Name\n- Email\n- Status\n- Last Updated\n\n**Note:** Runs in parallel with bounce fetch"
      },
      "typeVersion": 1
    },
    {
      "id": "62be1676-694b-4328-b126-1849078b9b91",
      "name": "Note adhésive 8",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        1296,
        1408
      ],
      "parameters": {
        "width": 237,
        "height": 284,
        "content": "## 🔗 MERGE DATA\n\n**What it does:**\nCombines two data streams:\n1. Bounced email addresses\n2. All contacts from sheet\n\n**Result:**\nSingle data stream containing both bounce info and contact list for comparison"
      },
      "typeVersion": 1
    },
    {
      "id": "bbb6ffaa-74da-47f7-853f-5d1969fa0214",
      "name": "Note adhésive 9",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        1664,
        1408
      ],
      "parameters": {
        "width": 237,
        "height": 412,
        "content": "## ✅ UPDATE STATUS\n\n**What it does:**\nMatches emails and sets status:\n\n- **\"Not Found\"** if email appears in bounce list\n- **\"Not Sent\"** if email NOT in bounce list\n\n**Also Updates:**\n- Last Updated: Current timestamp\n\n**Output:** Prepared data for sheet update"
      },
      "typeVersion": 1
    },
    {
      "id": "db166829-b496-43af-a332-79ddf6de2528",
      "name": "Note adhésive 10",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        2176,
        1200
      ],
      "parameters": {
        "width": 236.93062200956936,
        "height": 187.9942396313364,
        "content": "## 💾 SAVE TO SHEET\n\n**What it does:**\nWrites updated status back to Google Sheet\n\n**Update Method:**\n- Match on: Name column\n- Update: Status & Last Updated columns\n\n**Result:**\nSheet now reflects which emails bounced"
      },
      "typeVersion": 1
    },
    {
      "id": "a89cbaf1-3665-4e51-97a6-29d55123ce1f",
      "name": "Note adhésive 11",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        176,
        2048
      ],
      "parameters": {
        "width": 237,
        "height": 300,
        "content": "## ⏰ DAILY SCHEDULE\n\n**What it does:**\nAutomatically triggers daily report\n\n**Schedule:**\nEvery day at 19:00 (7 PM)\n\n**Triggers:** Daily summary workflow to send Slack notification"
      },
      "typeVersion": 1
    },
    {
      "id": "b653e1c7-0247-4d6f-a732-eec9050795ce",
      "name": "Note adhésive 12",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        448,
        1584
      ],
      "parameters": {
        "width": 237,
        "height": 300,
        "content": "## 📖 READ SHEET\n\n**What it does:**\nFetches current data from sheet for reporting\n\n**Reads:**\nAll rows with current status values\n\n**Purpose:**\nProvide data for statistics calculation"
      },
      "typeVersion": 1
    },
    {
      "id": "350dc62c-4f26-4b8d-8914-9821b38b3fb5",
      "name": "Note adhésive 13",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        768,
        2080
      ],
      "parameters": {
        "width": 237,
        "height": 332,
        "content": "## 🧮 CALCULATE STATS\n\n**What it does:**\nCounts contacts by status:\n\n- **Invalid emails:** Count of \"Not Found\" status\n- **No activity:** Count of \"Not Sent\" status\n\n**Output:**\nSingle item with formatted statistics"
      },
      "typeVersion": 1
    },
    {
      "id": "f2fd8aa0-3e40-4df0-85fa-3a22e5f019d5",
      "name": "Note adhésive 14",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        1312,
        1792
      ],
      "parameters": {
        "width": 237,
        "height": 316,
        "content": "## 💬 SEND TO SLACK\n\n**What it does:**\nPosts formatted summary to Slack\n\n**Channel:** #email-cleanup\n\n**Message Includes:**\n- 📧 Invalid email count\n- 📭 No activity count\n- Timestamp of report"
      },
      "typeVersion": 1
    }
  ],
  "active": false,
  "pinData": {},
  "settings": {
    "executionOrder": "v1"
  },
  "versionId": "1ad9a2ea-ab34-4d93-bc86-e5cc5e4453f3",
  "connections": {
    "ca28e3bf-d30f-4e54-affc-5b36be6b928e": {
      "main": [
        [
          {
            "node": "3b5dabd0-e0ad-4b27-86d3-fc326edc2842",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "36ffd609-d7c2-461f-82c1-c7303f3e5f46": {
      "main": [
        [
          {
            "node": "d020cba9-9eaa-406c-9bc6-d6d3c20ba1db",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "7f7c1971-5591-4f6c-a3dd-06c3e3e20bb6": {
      "main": [
        [
          {
            "node": "36ffd609-d7c2-461f-82c1-c7303f3e5f46",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "bde866db-a655-4cb3-bba1-81dbda49e09c": {
      "main": [
        [
          {
            "node": "a7f0f64f-1af0-4ad9-814d-49a1cff65699",
            "type": "main",
            "index": 1
          }
        ]
      ]
    },
    "c3984c6c-4861-46fe-8b6b-68bf842f3fa4": {
      "main": [
        [
          {
            "node": "ca28e3bf-d30f-4e54-affc-5b36be6b928e",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "d020cba9-9eaa-406c-9bc6-d6d3c20ba1db": {
      "main": [
        [
          {
            "node": "4ad00c44-5e60-452a-b4ab-89cc23b38c7c",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "a7f0f64f-1af0-4ad9-814d-49a1cff65699": {
      "main": [
        [
          {
            "node": "84ff56ce-1a6f-472f-b4ab-bf50705d87f9",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "84ff56ce-1a6f-472f-b4ab-bf50705d87f9": {
      "main": [
        [
          {
            "node": "ac79fbd2-381e-4de2-b5e2-e7502752978d",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "3b5dabd0-e0ad-4b27-86d3-fc326edc2842": {
      "main": [
        [
          {
            "node": "a7f0f64f-1af0-4ad9-814d-49a1cff65699",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "bff28463-72c1-415a-aac0-8d19808addcf": {
      "main": [
        [
          {
            "node": "c3984c6c-4861-46fe-8b6b-68bf842f3fa4",
            "type": "main",
            "index": 0
          },
          {
            "node": "bde866db-a655-4cb3-bba1-81dbda49e09c",
            "type": "main",
            "index": 0
          }
        ]
      ]
    }
  }
}