Retournierte E-Mails aus Gmail-Zustellfehlern in Google Tabellen kennzeichnen

• •Google-Konto + Gmail API-Anmeldedaten
• •Google Sheets API-Anmeldedaten

{
  "meta": {
    "instanceId": "e2b72466a589dd1250fc94a8e861457e040bf25b07f6b069958c036d3f2bfe77"
  },
  "nodes": [
    {
      "id": "9963c9a2-ace8-4de9-b9ba-45f82713bf4a",
      "name": "Bei Klick auf 'Workflow testen'",
      "type": "n8n-nodes-base.manualTrigger",
      "position": [
        -944,
        -48
      ],
      "parameters": {},
      "typeVersion": 1
    },
    {
      "id": "54d5fbd7-5932-4994-ab52-bbffddbf3de8",
      "name": "geterremail",
      "type": "n8n-nodes-base.code",
      "position": [
        -928,
        208
      ],
      "parameters": {
        "jsCode": "const emailRegex = /[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\\.[a-zA-Z]{2,}/g; // Regex to match email addresses\n\n// Helper function to remove duplicates from an array\nconst removeDuplicates = (array) => [...new Set(array)];\n\n// Loop through all input items\nreturn $input.all().map(item => {\n  const text = item.json.text || \"\"; // Replace 'body' with the actual field containing the text\n  const emails = text.match(emailRegex) || []; // Extract email addresses or return an empty array\n  const uniqueEmails = removeDuplicates(emails); // Remove duplicates\n  \n  return {\n    json: {\n      ...item.json,\n      extractedEmails: uniqueEmails[0] // Add the unique emails to the output\n    }\n  };\n});\n"
      },
      "executeOnce": true,
      "typeVersion": 2
    },
    {
      "id": "3c6df588-89dd-4e85-9ee9-cc2793bd4cf3",
      "name": "listerremail",
      "type": "n8n-nodes-base.set",
      "position": [
        -704,
        208
      ],
      "parameters": {
        "options": {},
        "assignments": {
          "assignments": [
            {
              "id": "93a314f1-b42c-45a9-bbdc-fda0ffec13cb",
              "name": "extractedEmails",
              "type": "string",
              "value": "={{ $json.extractedEmails }}"
            }
          ]
        }
      },
      "typeVersion": 3.4
    },
    {
      "id": "d700ab34-e7da-4336-b6c7-4e4c303ad5ec",
      "name": "readspamfolder",
      "type": "n8n-nodes-base.gmail",
      "position": [
        -496,
        -48
      ],
      "webhookId": "c9790d08-c845-4965-a6ce-6e538aa74279",
      "parameters": {
        "simple": false,
        "filters": {
          "labelIds": [
            "SPAM"
          ]
        },
        "options": {},
        "operation": "getAll",
        "returnAll": true
      },
      "credentials": {
        "gmailOAuth2": {
          "id": "3DsIsALVl78cvnHm",
          "name": "Gmail account"
        }
      },
      "typeVersion": 2.1
    },
    {
      "id": "fd3634df-d31a-450c-ac7a-4db633f569d4",
      "name": "undelivered_failure",
      "type": "n8n-nodes-base.if",
      "position": [
        -272,
        -48
      ],
      "parameters": {
        "conditions": {
          "string": [
            {
              "value1": "={{$json[\"subject\"]}}",
              "value2": "Undelivered",
              "operation": "contains"
            },
            {
              "value1": "={{$json[\"subject\"]}}",
              "value2": "Failure",
              "operation": "contains"
            }
          ]
        },
        "combineOperation": "any"
      },
      "typeVersion": 1
    },
    {
      "id": "34c8eeb3-760a-43cc-8d7d-ccea4689af63",
      "name": "lookupemail",
      "type": "n8n-nodes-base.googleSheets",
      "position": [
        -480,
        208
      ],
      "parameters": {
        "options": {},
        "filtersUI": {
          "values": [
            {
              "lookupValue": "={{ $('geterremail').item.json.extractedEmails }}",
              "lookupColumn": "email"
            }
          ]
        },
        "sheetName": {
          "__rl": true,
          "mode": "name",
          "value": "contacts"
        },
        "documentId": {
          "__rl": true,
          "mode": "id",
          "value": "={{ $('settings').item.json.googlesheetid }}"
        }
      },
      "credentials": {
        "googleSheetsOAuth2Api": {
          "id": "0xG6VARJ6hnHx2T1",
          "name": "Google Sheets account"
        }
      },
      "executeOnce": false,
      "typeVersion": 4.5
    },
    {
      "id": "17af022f-0c5f-47f6-b5ef-bd6df05d7952",
      "name": "update_err",
      "type": "n8n-nodes-base.googleSheets",
      "position": [
        -32,
        208
      ],
      "parameters": {
        "columns": {
          "value": {
            "err": "Y",
            "row_number": "={{ $('lookupemail').item.json.row_number }}"
          },
          "schema": [
            {
              "id": "email",
              "type": "string",
              "display": true,
              "removed": true,
              "required": false,
              "displayName": "email",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "firstname",
              "type": "string",
              "display": true,
              "removed": true,
              "required": false,
              "displayName": "firstname",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "lastname",
              "type": "string",
              "display": true,
              "removed": true,
              "required": false,
              "displayName": "lastname",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "company",
              "type": "string",
              "display": true,
              "removed": true,
              "required": false,
              "displayName": "company",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "process",
              "type": "string",
              "display": true,
              "removed": true,
              "required": false,
              "displayName": "process",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "err",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "err",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "row_number",
              "type": "number",
              "display": true,
              "removed": false,
              "readOnly": true,
              "required": false,
              "displayName": "row_number",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            }
          ],
          "mappingMode": "defineBelow",
          "matchingColumns": [
            "row_number"
          ],
          "attemptToConvertTypes": false,
          "convertFieldsToString": false
        },
        "options": {},
        "operation": "update",
        "sheetName": {
          "__rl": true,
          "mode": "name",
          "value": "contacts"
        },
        "documentId": {
          "__rl": true,
          "mode": "id",
          "value": "={{ $('settings').item.json.googlesheetid }}"
        }
      },
      "credentials": {
        "googleSheetsOAuth2Api": {
          "id": "0xG6VARJ6hnHx2T1",
          "name": "Google Sheets account"
        }
      },
      "typeVersion": 4.5
    },
    {
      "id": "565a47a3-80cc-418f-8862-7ed07c58fdd0",
      "name": "keep_row",
      "type": "n8n-nodes-base.set",
      "position": [
        -256,
        208
      ],
      "parameters": {
        "options": {},
        "assignments": {
          "assignments": [
            {
              "id": "c3ab2e7e-dec9-4c4e-a5a1-7be42975a4ea",
              "name": "row_number",
              "type": "number",
              "value": "={{ $json.row_number }}"
            }
          ]
        }
      },
      "typeVersion": 3.4
    },
    {
      "id": "ef72cddc-e32f-4e46-bf41-c57b1a0c98a8",
      "name": "Haftnotiz7",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -1712,
        -176
      ],
      "parameters": {
        "color": 7,
        "width": 656,
        "height": 1024,
        "content": "# Scanning Email Inbox for Delivery Errors\n\n**Prerequisite:** [Automate Personalized Email Campaigns with Google Docs, Sheets, and SMTP](https://n8n.io/workflows/7065-automate-personalized-email-campaigns-with-google-docs-sheets-and-smtp/).\n\n## How It Works\n\nAfter running your email campaign, some messages may fail to deliver. This workflow scans your email inbox for delivery errors (e.g., bounced messages), flags problematic email addresses in the Google Sheet and ensures future campaigns skip them.\n\n## How to Use\n\n1. **Ensure Prerequisite Workflow:**  \n   - You should have the [Email Campaign Workflow](https://n8n.io/workflows/7065-automate-personalized-email-campaigns-with-google-docs-sheets-and-smtp/) configured and running.\n\n2. **Google Sheet Setup:**  \n   - Use the [Google Sheet Template](https://docs.google.com/spreadsheets/d/1mFKp3wmbV9qp2tpGGsN72zdiC32y8H1nhjdgP885y-U/edit?usp=sharing).  \n   - Identify your document’s ID (the string after `/d/` and before `/edit` in the URL).\n\n3. **Configure Workflow:**  \n   - Enter your Google Sheet ID in the `settings` node.  \n   - Connect your Google credentials to n8n.\n\n4. **Email Inbox:**  \n   - Set up the `readspamfolder` node to search for bounce/error messages in your mail (e.g., in the Spam or Inbox folders—adjust label/folder if emails land elsewhere).  \n\n5. **Google Sheet Update:**  \n   - Configure the `lookupemail` and `update_err` nodes\n\n\n## Requirements\n\n- **Google Credentials** to access Gmail and sheets.\n- **Gmail Account** (bounce/error messages must be accessible here).\n- **n8n Version:** Tested with 1.105.2 (Ubuntu).\n\n## Need Help?\n\nContact me on [LinkedIn](https://www.linkedin.com/in/stephaneheckel/) or ask in the [Forum](https://community.n8n.io/)!\n\n\n"
      },
      "typeVersion": 1
    },
    {
      "id": "8c0840f2-3731-4b02-a92d-e80fe6cf99d3",
      "name": "settings",
      "type": "n8n-nodes-base.set",
      "position": [
        -720,
        -48
      ],
      "parameters": {
        "options": {},
        "assignments": {
          "assignments": [
            {
              "id": "4b74909d-6a40-422f-9d5a-1d72f5577f3f",
              "name": "googlesheetid",
              "type": "string",
              "value": "1mFKp3wmbV9qp2tpGGsN72zdiC32y8H1nhjdgP885y-U"
            }
          ]
        }
      },
      "typeVersion": 3.4
    },
    {
      "id": "62510cba-702f-4e0e-9462-64cd2d22338c",
      "name": "Haftnotiz6",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -768,
        -160
      ],
      "parameters": {
        "color": 7,
        "width": 192,
        "height": 96,
        "content": "Define the Google  Sheet ID used in this [Workflow](https://n8n.io/workflows/7065-automate-personalized-email-campaigns-with-google-docs-sheets-and-smtp/)"
      },
      "typeVersion": 1
    },
    {
      "id": "9ce3155f-1cf5-4457-83a4-e9bab7315aba",
      "name": "Haftnotiz",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -544,
        -160
      ],
      "parameters": {
        "color": 7,
        "width": 192,
        "height": 96,
        "content": "Read the folder (Gmail SPAM here), with Mail Delivery issues"
      },
      "typeVersion": 1
    },
    {
      "id": "3fe9243f-5a5e-484b-8a13-8bdb658282d8",
      "name": "Haftnotiz1",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -320,
        -160
      ],
      "parameters": {
        "color": 7,
        "width": 192,
        "height": 96,
        "content": "Identify Subject containing \"Undelivered\" or \"Failure\""
      },
      "typeVersion": 1
    },
    {
      "id": "1fb775c5-aa90-45d0-9826-a24ba430268f",
      "name": "Haftnotiz2",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -976,
        368
      ],
      "parameters": {
        "color": 7,
        "width": 192,
        "height": 112,
        "content": "Extract the \"email\" from the body message. Unduplication to be sure you get one unique email"
      },
      "typeVersion": 1
    },
    {
      "id": "1f1bec65-c428-4c72-a141-d748a6b11498",
      "name": "Haftnotiz3",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -528,
        368
      ],
      "parameters": {
        "color": 7,
        "width": 192,
        "height": 288,
        "content": "Find the Row Number in the Google  Sheet ID used by this previous [Workflow](https://n8n.io/workflows/7065-automate-personalized-email-campaigns-with-google-docs-sheets-and-smtp/)\n\nThe `email` column need to match `{{ $('geterremail').item.json.extractedEmails }}`\n\n\nDownload the [Google Sheet Template](https://docs.google.com/spreadsheets/d/1mFKp3wmbV9qp2tpGGsN72zdiC32y8H1nhjdgP885y-U/edit?usp=sharing)"
      },
      "typeVersion": 1
    },
    {
      "id": "2e050778-b76f-4497-b42f-dee1d9a55e13",
      "name": "Haftnotiz4",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -752,
        368
      ],
      "parameters": {
        "color": 7,
        "width": 192,
        "height": 112,
        "content": "For debugging. To show the emails in error"
      },
      "typeVersion": 1
    },
    {
      "id": "8d635eba-7666-4c70-96d4-1acf02a42728",
      "name": "Haftnotiz5",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -304,
        368
      ],
      "parameters": {
        "color": 7,
        "width": 192,
        "height": 112,
        "content": "For debugging. To show the \"Row Number\" we are going to update in the Google Sheet\n"
      },
      "typeVersion": 1
    },
    {
      "id": "ecd8fd66-a6b2-4a25-b070-218af8bd0df0",
      "name": "Haftnotiz8",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -64,
        368
      ],
      "parameters": {
        "color": 7,
        "width": 192,
        "height": 224,
        "content": "Update the Google Sheet and set err = \"Y\"\n\n`row_number` (using to match)\n\n`{{ $('lookupemail').item.json.row_number }}`"
      },
      "typeVersion": 1
    }
  ],
  "pinData": {},
  "connections": {
    "565a47a3-80cc-418f-8862-7ed07c58fdd0": {
      "main": [
        [
          {
            "node": "17af022f-0c5f-47f6-b5ef-bd6df05d7952",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "8c0840f2-3731-4b02-a92d-e80fe6cf99d3": {
      "main": [
        [
          {
            "node": "d700ab34-e7da-4336-b6c7-4e4c303ad5ec",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "54d5fbd7-5932-4994-ab52-bbffddbf3de8": {
      "main": [
        [
          {
            "node": "3c6df588-89dd-4e85-9ee9-cc2793bd4cf3",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "34c8eeb3-760a-43cc-8d7d-ccea4689af63": {
      "main": [
        [
          {
            "node": "565a47a3-80cc-418f-8862-7ed07c58fdd0",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "3c6df588-89dd-4e85-9ee9-cc2793bd4cf3": {
      "main": [
        [
          {
            "node": "34c8eeb3-760a-43cc-8d7d-ccea4689af63",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "d700ab34-e7da-4336-b6c7-4e4c303ad5ec": {
      "main": [
        [
          {
            "node": "fd3634df-d31a-450c-ac7a-4db633f569d4",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "fd3634df-d31a-450c-ac7a-4db633f569d4": {
      "main": [
        [
          {
            "node": "54d5fbd7-5932-4994-ab52-bbffddbf3de8",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "9963c9a2-ace8-4de9-b9ba-45f82713bf4a": {
      "main": [
        [
          {
            "node": "8c0840f2-3731-4b02-a92d-e80fe6cf99d3",
            "type": "main",
            "index": 0
          }
        ]
      ]
    }
  }
}