Gmail への配信エラーメールを Google テーブルでハンドルを立てる
上級
これはSocial Media, Multimodal AI分野の自動化ワークフローで、18個のノードを含みます。主にIf, Set, Code, Gmail, GoogleSheetsなどのノードを使用。 Gmail の配信エラーを基に Google スプレッドシートにバウンスメールをマーク
前提条件
- •Googleアカウント + Gmail API認証情報
- •Google Sheets API認証情報
ワークフロープレビュー
ノード接続関係を可視化、ズームとパンをサポート
ワークフローをエクスポート
以下のJSON設定をn8nにインポートして、このワークフローを使用できます
{
"meta": {
"instanceId": "e2b72466a589dd1250fc94a8e861457e040bf25b07f6b069958c036d3f2bfe77"
},
"nodes": [
{
"id": "9963c9a2-ace8-4de9-b9ba-45f82713bf4a",
"name": "「Test workflow」クリック時",
"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": "付箋7",
"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": "付箋6",
"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": "付箋",
"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": "付箋1",
"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": "付箋2",
"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": "付箋3",
"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": "付箋4",
"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": "付箋5",
"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": "付箋8",
"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
}
]
]
}
}
}よくある質問
このワークフローの使い方は？
上記のJSON設定コードをコピーし、n8nインスタンスで新しいワークフローを作成して「JSONからインポート」を選択、設定を貼り付けて認証情報を必要に応じて変更してください。
このワークフローはどんな場面に適していますか？
上級 - ソーシャルメディア, マルチモーダルAI
有料ですか？
このワークフローは完全無料です。ただし、ワークフローで使用するサードパーティサービス（OpenAI APIなど）は別途料金が発生する場合があります。
関連ワークフロー
Browserflow と Google Sheets で LinkedIn へのリクエストを自動追跡
Browserflow と Google Sheets を使って LinkedIn の自動招待を追跡
If
Set
Wait
+
If
Set
Wait
23 ノードStéphane Heckel
リードナーチャリング
LinkedInコンテンツファクトリー（OpenAI研究とブランド画像生成機能付き）
LinkedInコンテンツファクトリー、OpenAIリサーチとReplicateのブランド画像生成を統合
If
Set
Code
+
If
Set
Code
23 ノードOnur
ソーシャルメディア
AI-Deepseek-R1t を用いた会議交通費精算申請と旅費承認
Deepseek AI、Gmail、Google Sheetsを使った会議出張承認の自動化
If
Set
Code
+
If
Set
Code
24 ノードCheng Siong Chin
文書抽出
Google Sheets、Forms、Gmailを使った多段階入社フローの自動化
Google Sheets、Forms、Gmailを使って多段階の入社プロセスを自動化
If
Set
Code
+
If
Set
Code
31 ノードPollupAI
人事
YouTube 動画に基づく自律ブログ公開
YouTube 動画から ChatGPT、Sheets、Apify、Pexels、WordPress を使用してブログの自主公開
If
Set
Code
+
If
Set
Code
80 ノードOriol Seguí
コンテンツ作成
私の冷蔵郵送メールジェネレーター
Anthropic、GPT-4、そしてGoogleシートを使ってパーソナライズされたアイスのメールを生成する
If
Set
Code
+
If
Set
Code
24 ノードBhuvanesh R
リードナーチャリング
