在Google表格中标记来自Gmail投递错误信息的退回邮件
高级
这是一个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": "当点击\"测试工作流\"时",
"type": "n8n-nodes-base.manualTrigger",
"position": [
-944,
-48
],
"parameters": {},
"typeVersion": 1
},
{
"id": "54d5fbd7-5932-4994-ab52-bbffddbf3de8",
"name": "获取错误电子邮件",
"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": "列出错误电子邮件",
"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": "读取垃圾邮件文件夹",
"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": "未送达失败",
"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": "查找电子邮件",
"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": "更新错误",
"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": "保留行",
"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": "# 扫描电子邮件收件箱查找投递错误"
},
"typeVersion": 1
},
{
"id": "8c0840f2-3731-4b02-a92d-e80fe6cf99d3",
"name": "设置",
"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": "定义此[工作流](https://n8n.io/workflows/7065-automate-personalized-email-campaigns-with-google-docs-sheets-and-smtp/)中使用的 Google Sheet ID"
},
"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": "读取文件夹（此处为 Gmail 垃圾邮件），包含邮件投递问题"
},
"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": "识别包含\"未送达\"或\"失败\"的主题"
},
"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": "从正文消息中提取\"电子邮件\"。去重以确保获得唯一电子邮件"
},
"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": "在此先前[工作流](https://n8n.io/workflows/7065-automate-personalized-email-campaigns-with-google-docs-sheets-and-smtp/)使用的 Google Sheet ID 中查找行号"
},
"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": "用于调试。显示错误的电子邮件"
},
"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": "用于调试。显示我们将在 Google Sheet 中更新的\"行号\""
},
"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": "更新 Google Sheet 并设置 err = \"Y\""
},
"typeVersion": 1
}
],
"pinData": {},
"connections": {
"keep_row": {
"main": [
[
{
"node": "update_err",
"type": "main",
"index": 0
}
]
]
},
"settings": {
"main": [
[
{
"node": "readspamfolder",
"type": "main",
"index": 0
}
]
]
},
"geterremail": {
"main": [
[
{
"node": "listerremail",
"type": "main",
"index": 0
}
]
]
},
"lookupemail": {
"main": [
[
{
"node": "keep_row",
"type": "main",
"index": 0
}
]
]
},
"listerremail": {
"main": [
[
{
"node": "lookupemail",
"type": "main",
"index": 0
}
]
]
},
"readspamfolder": {
"main": [
[
{
"node": "undelivered_failure",
"type": "main",
"index": 0
}
]
]
},
"undelivered_failure": {
"main": [
[
{
"node": "geterremail",
"type": "main",
"index": 0
}
]
]
},
"When clicking ‘Test workflow’": {
"main": [
[
{
"node": "settings",
"type": "main",
"index": 0
}
]
]
}
}
}常见问题
如何使用这个工作流？
复制上方的 JSON 配置代码，在您的 n8n 实例中创建新工作流并选择「从 JSON 导入」，粘贴配置后根据需要修改凭证设置即可。
这个工作流适合什么场景？
高级 - 社交媒体, 多模态 AI
需要付费吗？
本工作流完全免费，您可以直接导入使用。但请注意，工作流中使用的第三方服务（如 OpenAI API）可能需要您自行付费。
相关工作流推荐
使用 Browserflow 和 Google Sheets 跟踪自动化 LinkedIn 邀请
使用 Browserflow 和 Google Sheets 跟踪自动化 LinkedIn 邀请
If
Set
Wait
+8
23 节点Stéphane Heckel
客户培育
LinkedIn内容工厂（含OpenAI研究与品牌图像生成）
LinkedIn内容工厂，集成OpenAI研究与Replicate品牌图像生成
If
Set
Code
+10
23 节点Onur
社交媒体
Google Sheets 联系人同步至 SeaTable（更新插入逻辑）
将 Google Sheets 联系人同步至 SeaTable，支持更新/插入逻辑
If
Set
Sea Table
+6
18 节点Stéphane Heckel
客户关系管理
AI-Deepseek-R1t 会议差旅审批与费用授权申请
通过Deepseek AI、Gmail和Google Sheets自动化会议差旅审批
If
Set
Code
+11
24 节点Cheng Siong Chin
文档提取
使用Google Sheets、Forms和Gmail通知自动化多步骤入职流程
使用Google Sheets、Forms和Gmail通知自动化多步骤入职流程
If
Set
Code
+11
31 节点PollupAI
人力资源
基于 YouTube 视频的自主博客发布
使用 ChatGPT、Sheets、Apify、Pexels 和 WordPress 从 YouTube 视频自主发布博客
If
Set
Code
+18
80 节点Oriol Seguí
内容创作
