Detección de rebotes y direcciones no válidas (Activador de Gmail)

• •Cuenta de Google y credenciales de API de Gmail
• •Bot Token de Slack o URL de Webhook
• •Credenciales de API de Google Sheets

{
  "id": "BwfB2WsAGCX5rTFB",
  "meta": {
    "instanceId": "8443f10082278c46aa5cf3acf8ff0f70061a2c58bce76efac814b16290845177",
    "templateCredsSetupCompleted": true
  },
  "name": "Bounce & Invalid Detection (Gmail Trigger)",
  "tags": [],
  "nodes": [
    {
      "id": "bff28463-72c1-415a-aac0-8d19808addcf",
      "name": "Al hacer clic en 'Ejecutar flujo de trabajo'",
      "type": "n8n-nodes-base.manualTrigger",
      "position": [
        384,
        1104
      ],
      "parameters": {},
      "typeVersion": 1
    },
    {
      "id": "3b5dabd0-e0ad-4b27-86d3-fc326edc2842",
      "name": "Analizar Direcciones de Correo Rebotadas",
      "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": "Obtener Todos los Contactos de Correo",
      "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": "Escribir Estado de Vuelta en la Hoja",
      "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": "Disparador de Reporte Diario 7PM",
      "type": "n8n-nodes-base.cron",
      "position": [
        304,
        1904
      ],
      "parameters": {
        "triggerTimes": {
          "item": [
            {
              "hour": 19
            }
          ]
        }
      },
      "typeVersion": 1
    },
    {
      "id": "36ffd609-d7c2-461f-82c1-c7303f3e5f46",
      "name": "Leer Datos Actualizados de la Hoja",
      "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": "Enviar Resumen Diario 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": "Obtener Notificaciones de Rebote",
      "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": "Obtener Últimos 5 Rebotes",
      "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": "Combinar Datos de Rebote y Contactos",
      "type": "n8n-nodes-base.merge",
      "position": [
        1424,
        1232
      ],
      "parameters": {},
      "typeVersion": 3.2
    },
    {
      "id": "84ff56ce-1a6f-472f-b4ab-bf50705d87f9",
      "name": "Emparejar y Actualizar Estado de Contacto",
      "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": "Calcular Estadísticas de Resumen",
      "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": "Nota Adhesiva 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": "Nota Adhesiva 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": "Nota Adhesiva 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": "Nota Adhesiva 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": "Nota Adhesiva 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": "Nota Adhesiva 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": "Nota Adhesiva 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": "Nota Adhesiva 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": "Nota Adhesiva 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": "Nota Adhesiva 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": "Nota Adhesiva 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": "Nota Adhesiva 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": "Nota Adhesiva 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": "Nota Adhesiva 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
          }
        ]
      ]
    }
  }
}