Googleスプレッドシート内の商業リードのメールアドレスを enrichment し抽出
上級
これはLead Generation分野の自動化ワークフローで、26個のノードを含みます。主にIf, Set, Code, Wait, HttpRequestなどのノードを使用。 Serper.devとScrapingBeeを使用してGoogleスプレッドシートからビジネスメールアドレスを抽出
前提条件
- •ターゲットAPIの認証情報が必要な場合あり
- •Google Sheets API認証情報
カテゴリー
ワークフロープレビュー
ノード接続関係を可視化、ズームとパンをサポート
ワークフローをエクスポート
以下のJSON設定をn8nにインポートして、このワークフローを使用できます
{
"id": "8uSz0nrsQS7mofQd",
"meta": {
"instanceId": "6bad1eba185697a960de5bc9203d6bff2fd56440a0b5303c382e0b1537a2a568"
},
"name": "Enrich and extract emails from business leads in Google Sheets",
"tags": [],
"nodes": [
{
"id": "f6d286f2-7d8e-48ce-a846-060948b5adfc",
"name": "アイテムをループ処理",
"type": "n8n-nodes-base.splitInBatches",
"position": [
2460,
720
],
"parameters": {
"options": {}
},
"typeVersion": 3,
"alwaysOutputData": false
},
{
"id": "77536dd3-7863-4dfd-a531-48c45734546f",
"name": "条件分岐",
"type": "n8n-nodes-base.if",
"position": [
3100,
740
],
"parameters": {
"options": {},
"conditions": {
"options": {
"version": 2,
"leftValue": "",
"caseSensitive": true,
"typeValidation": "strict"
},
"combinator": "and",
"conditions": [
{
"id": "faa41c08-7cc6-4fd1-8447-884a00754fba",
"operator": {
"type": "string",
"operation": "empty",
"singleValue": true
},
"leftValue": "={{ $json.error.message }}",
"rightValue": ""
}
]
}
},
"typeVersion": 2.2
},
{
"id": "f2651a42-0c4a-45d2-afba-c9478e0d45ac",
"name": "Scraping Bee",
"type": "n8n-nodes-base.httpRequest",
"onError": "continueRegularOutput",
"position": [
3320,
740
],
"parameters": {
"url": "=https://app.scrapingbee.com/api/v1/?api_key=YOUR_SCRAPING_BEE_API_KEY={{ $('Website Options').item.json.Website }}&render_js=true",
"options": {
"batching": {
"batch": {
"batchSize": 5
}
}
}
},
"typeVersion": 4.2
},
{
"id": "8df36dea-5646-4900-a50f-995601085c49",
"name": "ウェブサイトオプション",
"type": "n8n-nodes-base.code",
"position": [
2220,
720
],
"parameters": {
"jsCode": "// Function to extract the base URL from a website string\nfunction getBaseUrl(website) {\n // Match the protocol and domain (e.g., https://example.com)\n const match = website.match(/^(https?:\\/\\/[^\\/]+)/);\n // Return the matched base URL or the original website if no match\n return match ? match[1] : website;\n}\n\n// Process all input items and generate multiple URL variants for each company\nreturn $input.all().reduce((acc, item) => {\n // Extract fields from the current item\n const company = item.json[\"Company\"];\n const website = item.json[\"website\"];\n const client = item.json[\"Client\"];\n const city = item.json[\"City\"];\n const state = item.json[\"State\"]\n \n // Get the base URL from the website\n const baseUrl = getBaseUrl(website);\n\n // List of contact and support page paths to append to the base URL\n const paths = [\n \"/\", // home\n \"/contacto/\",\n \"/contact/\",\n \"/contactanos/\",\n \"/comunicate/\",\n \"/contact/\",\n \"/contact-us/\",\n \"/support/\",\n \"/help/\",\n \"/customer-service/\",\n \"/help-center/\",\n \"/contactus/\",\n \"/customer-support/\",\n \"/feedback/\",\n \"/get-in-touch/\"\n ];\n\n // Create an array of variants, each with a different path appended to the base URL\n const variants = paths.map(path => {\n return {\n json: {\n company: company,\n Website: `${baseUrl}${path}`,\n client: client,\n city: city,\n state: state\n }\n };\n });\n\n // Concatenate the variants for this item to the accumulator array\n return acc.concat(variants);\n}, []);\n"
},
"typeVersion": 2
},
{
"id": "ffe396f7-dc1c-4ae0-ab47-fedd20c3bace",
"name": "Google Sheets Trigger",
"type": "n8n-nodes-base.googleSheetsTrigger",
"position": [
860,
720
],
"parameters": {
"event": "rowUpdate",
"options": {
"columnsToWatch": [
"Activate"
]
},
"pollTimes": {
"item": [
{
"mode": "everyMinute"
}
]
},
"sheetName": {
"__rl": true,
"mode": "list",
"value": "",
"cachedResultUrl": "",
"cachedResultName": ""
},
"documentId": {
"__rl": true,
"mode": "id",
"value": ""
}
},
"credentials": {
"googleSheetsTriggerOAuth2Api": {
"id": "pduM8gW77O9qv08M",
"name": "Google Sheets Trigger account"
}
},
"typeVersion": 1
},
{
"id": "85a56d75-5c7c-4a99-8b80-3ba1b09763f2",
"name": "条件分岐1",
"type": "n8n-nodes-base.if",
"position": [
3480,
740
],
"parameters": {
"options": {},
"conditions": {
"options": {
"version": 2,
"leftValue": "",
"caseSensitive": true,
"typeValidation": "strict"
},
"combinator": "and",
"conditions": [
{
"id": "faa41c08-7cc6-4fd1-8447-884a00754fba",
"operator": {
"type": "string",
"operation": "empty",
"singleValue": true
},
"leftValue": "={{ $json.error.message }}",
"rightValue": ""
}
]
}
},
"typeVersion": 2.2
},
{
"id": "32910d3a-079b-4e56-be68-9968a00f8966",
"name": "条件分岐2",
"type": "n8n-nodes-base.if",
"position": [
3840,
740
],
"parameters": {
"options": {},
"conditions": {
"options": {
"version": 2,
"leftValue": "",
"caseSensitive": true,
"typeValidation": "strict"
},
"combinator": "and",
"conditions": [
{
"id": "74bfe580-4c7b-4859-85da-02e846bb2d68",
"operator": {
"type": "string",
"operation": "notEmpty",
"singleValue": true
},
"leftValue": "={{ $('Email Extractor').item.json.email }}",
"rightValue": ""
}
]
}
},
"typeVersion": 2.2
},
{
"id": "ee67ab82-b78b-4487-97a7-d27e33bddf3a",
"name": "待機",
"type": "n8n-nodes-base.wait",
"position": [
4340,
780
],
"webhookId": "a7f40dd8-a7d1-4b15-9c7b-fa017f0a6f94",
"parameters": {
"unit": "minutes",
"amount": 1
},
"typeVersion": 1.1
},
{
"id": "823f2cfb-c1a7-4c16-a95a-100aad15426a",
"name": "条件分岐3",
"type": "n8n-nodes-base.if",
"position": [
1020,
720
],
"parameters": {
"options": {},
"conditions": {
"options": {
"version": 2,
"leftValue": "",
"caseSensitive": true,
"typeValidation": "strict"
},
"combinator": "or",
"conditions": [
{
"id": "1f2515cd-fc6f-422d-a364-760a473aace3",
"operator": {
"type": "string",
"operation": "notEmpty",
"singleValue": true
},
"leftValue": "={{ $json.Client }}",
"rightValue": ""
},
{
"id": "3fdcf4ff-79d1-45bc-a3d0-f9a53be69158",
"operator": {
"type": "string",
"operation": "notEmpty",
"singleValue": true
},
"leftValue": "={{ $json.City }}",
"rightValue": ""
},
{
"id": "6d59f840-f110-4c57-8d10-cc1e6976772c",
"operator": {
"type": "string",
"operation": "notEmpty",
"singleValue": true
},
"leftValue": "={{ $json.State }}",
"rightValue": ""
}
]
}
},
"typeVersion": 2.2
},
{
"id": "9e4d96df-7be4-4a3b-b8ec-71f40698a8e8",
"name": "情報を設定",
"type": "n8n-nodes-base.set",
"position": [
1380,
720
],
"parameters": {
"values": {
"number": [
{
"name": "result_count",
"value": 10
}
],
"string": [
{
"name": "state",
"value": "={{ $('Google Sheets Trigger').item.json.State }}"
},
{
"name": "city",
"value": "={{ $('Google Sheets Trigger').item.json.City }}"
},
{
"name": "client",
"value": "={{ $('Google Sheets Trigger').item.json.Client }}"
},
{
"name": "business_type",
"value": "={{ $node[\"Google Sheets Trigger\"].json[\"Business Type\"] }}"
},
{
"name": "country",
"value": "Argentina"
},
{
"name": "country_code",
"value": "AR"
},
{
"name": "language",
"value": "es-419"
}
]
},
"options": {},
"keepOnlySet": true
},
"typeVersion": 2
},
{
"id": "293eabf8-c7c5-485f-9631-60a6b3b746d0",
"name": "企業検索 (Serper.dev)",
"type": "n8n-nodes-base.httpRequest",
"position": [
1580,
720
],
"parameters": {
"url": "https://google.serper.dev/search",
"options": {},
"requestMethod": "POST",
"authentication": "genericCredentialType",
"jsonParameters": true,
"genericAuthType": "httpHeaderAuth",
"bodyParametersJson": "={\n \"q\": \"{{ $json.business_type }} in {{ $json.city }}, {{ $json.state }}, {{ $json.country }}\",\n \"num\": {{ $json.result_count }},\n \"gl\": \"{{ $json.country_code }}\",\n \"hl\": \"{{ $json.language }}\"\n}"
},
"credentials": {
"httpHeaderAuth": {
"id": "ICxPswiAFqOcpFSl",
"name": "Header Auth account"
}
},
"typeVersion": 2
},
{
"id": "9944d54a-7a52-4b4d-94d3-4b635839b284",
"name": "企業とウェブサイトを抽出",
"type": "n8n-nodes-base.code",
"position": [
1780,
720
],
"parameters": {
"jsCode": "// Get the organic search results or an empty array if undefined\nconst results = $json.organic ?? [];\n\n// Define a blacklist of terms to filter out unwanted results\nconst blacklist = [\n \"telecom\", \"teco\", \"claro\", \"telmex\", \"telefonica\",\n \"cirion\", \"silica\", \"level3\", \"movistar\", \"personal\", \"orbith\", \"expereo\", \"linkup\", \"telecentro\", \"cabase\", \"argentina\", \"fibertel\", \"insat\", \"sawerin\", \"iplan\", \"metrotel\", \"sion\", \"starlink\", \"selectra\", \"directvla\", \"buenosaires\", \"lanacion\", \"infobae\", \"internexa\", \"ifxnetworks\", \"centurylink\", \"arnet\", \"fibertel\", \"cablevision\", \"arnet\", \"arsat\", \"instagram\", \"facebook\", \"tiktok\", \"wikipedia\", \"twitter\"\n];\n\n// Filter out results that contain any term from the blacklist in the title or link\nconst filtrados = results.filter(r => {\n const lowerTitle = (r.title || \"\").toLowerCase();\n const lowerLink = (r.link || \"\").toLowerCase();\n return !blacklist.some(term => lowerTitle.includes(term) || lowerLink.includes(term));\n});\n\n// Map the filtered results to a new object structure, adding additional info from 'Set Information'\nreturn filtrados.map(r => ({\n json: {\n company: r.title || \"\",\n Website: r.link || \"\",\n client: $('Set Information').first().json.client, // Get client info from 'Set Information'\n state: $('Set Information').first().json.state, // Get state info from 'Set Information'\n city: $('Set Information').first().json.city, // Get city info from 'Set Information'\n }\n}));\n"
},
"typeVersion": 2
},
{
"id": "eb3b27a5-dcf0-48c0-ab2d-3947ed1e95da",
"name": "実行ステータスを更新",
"type": "n8n-nodes-base.googleSheets",
"position": [
1200,
640
],
"parameters": {
"columns": {
"value": {
"Client": "={{ $json.Client }}",
"Status": "Running"
},
"schema": [
{
"id": "Client",
"type": "string",
"display": true,
"removed": false,
"required": false,
"displayName": "Client",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "Business Type.",
"type": "string",
"display": true,
"removed": false,
"required": false,
"displayName": "Business Type.",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "City",
"type": "string",
"display": true,
"removed": false,
"required": false,
"displayName": "City",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "State",
"type": "string",
"display": true,
"removed": false,
"required": false,
"displayName": "State",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "Activate",
"type": "string",
"display": true,
"removed": false,
"required": false,
"displayName": "Activate",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "Status",
"type": "string",
"display": true,
"required": false,
"displayName": "Status",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "row_number",
"type": "string",
"display": true,
"removed": true,
"readOnly": true,
"required": false,
"displayName": "row_number",
"defaultMatch": false,
"canBeUsedToMatch": true
}
],
"mappingMode": "defineBelow",
"matchingColumns": [
"Client"
],
"attemptToConvertTypes": false,
"convertFieldsToString": false
},
"options": {},
"operation": "update",
"sheetName": {
"__rl": true,
"mode": "list",
"value": 127380953,
"cachedResultUrl": "https://docs.google.com/spreadsheets/d/1UZbZVmWLin1An4IoNq6zAb7Z6Sa0epZI3Xd5PJlDl1E/edit#gid=127380953",
"cachedResultName": "Input"
},
"documentId": {
"__rl": true,
"mode": "list",
"value": "1UZbZVmWLin1An4IoNq6zAb7Z6Sa0epZI3Xd5PJlDl1E",
"cachedResultUrl": "https://docs.google.com/spreadsheets/d/1UZbZVmWLin1An4IoNq6zAb7Z6Sa0epZI3Xd5PJlDl1E/edit?usp=drivesdk",
"cachedResultName": "ISP Search"
}
},
"credentials": {
"googleSheetsOAuth2Api": {
"id": "VRohINn3IW42gCgi",
"name": "Google Sheets account"
}
},
"typeVersion": 4.6
},
{
"id": "ac6e7f24-34ae-4811-8a50-bb0ca5e14eea",
"name": "不足情報ステータスを更新",
"type": "n8n-nodes-base.googleSheets",
"position": [
1200,
840
],
"parameters": {
"columns": {
"value": {
"Client": "={{ $json.Client }}",
"Status": "Missing data"
},
"schema": [
{
"id": "Client",
"type": "string",
"display": true,
"removed": false,
"required": false,
"displayName": "Client",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "Business Type.",
"type": "string",
"display": true,
"removed": false,
"required": false,
"displayName": "Business Type.",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "City",
"type": "string",
"display": true,
"removed": false,
"required": false,
"displayName": "City",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "State",
"type": "string",
"display": true,
"removed": false,
"required": false,
"displayName": "State",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "Activate",
"type": "string",
"display": true,
"removed": false,
"required": false,
"displayName": "Activate",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "Status",
"type": "string",
"display": true,
"required": false,
"displayName": "Status",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "row_number",
"type": "string",
"display": true,
"removed": true,
"readOnly": true,
"required": false,
"displayName": "row_number",
"defaultMatch": false,
"canBeUsedToMatch": true
}
],
"mappingMode": "defineBelow",
"matchingColumns": [
"Client"
],
"attemptToConvertTypes": false,
"convertFieldsToString": false
},
"options": {},
"operation": "update",
"sheetName": {
"__rl": true,
"mode": "list",
"value": 127380953,
"cachedResultUrl": "https://docs.google.com/spreadsheets/d/1UZbZVmWLin1An4IoNq6zAb7Z6Sa0epZI3Xd5PJlDl1E/edit#gid=127380953",
"cachedResultName": "Input"
},
"documentId": {
"__rl": true,
"mode": "list",
"value": "1UZbZVmWLin1An4IoNq6zAb7Z6Sa0epZI3Xd5PJlDl1E",
"cachedResultUrl": "https://docs.google.com/spreadsheets/d/1UZbZVmWLin1An4IoNq6zAb7Z6Sa0epZI3Xd5PJlDl1E/edit?usp=drivesdk",
"cachedResultName": "ISP Search"
}
},
"credentials": {
"googleSheetsOAuth2Api": {
"id": "VRohINn3IW42gCgi",
"name": "Google Sheets account"
}
},
"typeVersion": 4.6
},
{
"id": "3109c8db-837b-48f2-b1ff-9f86d9c8d79e",
"name": "調査結果を追加",
"type": "n8n-nodes-base.googleSheets",
"position": [
2000,
720
],
"parameters": {
"columns": {
"value": {
"City": "={{ $('Set Information').item.json.city }}",
"State": "={{ $('Set Information').item.json.state }}",
"Client": "={{ $json.client }}",
"Company": "={{ $json.company }}",
"website": "={{ $json.Website }}"
},
"schema": [
{
"id": "Client",
"type": "string",
"display": true,
"removed": false,
"required": false,
"displayName": "Client",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "City",
"type": "string",
"display": true,
"removed": false,
"required": false,
"displayName": "City",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "State",
"type": "string",
"display": true,
"removed": false,
"required": false,
"displayName": "State",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "Company",
"type": "string",
"display": true,
"removed": false,
"required": false,
"displayName": "Company",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "website",
"type": "string",
"display": true,
"required": false,
"displayName": "website",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "emails",
"type": "string",
"display": true,
"required": false,
"displayName": "emails",
"defaultMatch": false,
"canBeUsedToMatch": true
}
],
"mappingMode": "defineBelow",
"matchingColumns": [],
"attemptToConvertTypes": false,
"convertFieldsToString": false
},
"options": {},
"operation": "append",
"sheetName": {
"__rl": true,
"mode": "list",
"value": "gid=0",
"cachedResultUrl": "https://docs.google.com/spreadsheets/d/1UZbZVmWLin1An4IoNq6zAb7Z6Sa0epZI3Xd5PJlDl1E/edit#gid=0",
"cachedResultName": "Data"
},
"documentId": {
"__rl": true,
"mode": "list",
"value": "1UZbZVmWLin1An4IoNq6zAb7Z6Sa0epZI3Xd5PJlDl1E",
"cachedResultUrl": "https://docs.google.com/spreadsheets/d/1UZbZVmWLin1An4IoNq6zAb7Z6Sa0epZI3Xd5PJlDl1E/edit?usp=drivesdk",
"cachedResultName": "ISP Search"
}
},
"typeVersion": 4.6
},
{
"id": "382f7581-ac23-4ba6-928c-9a6e4a990230",
"name": "テストページ",
"type": "n8n-nodes-base.httpRequest",
"onError": "continueRegularOutput",
"position": [
2900,
740
],
"parameters": {
"url": "={{ $('Website Options').item.json.Website }}",
"options": {}
},
"typeVersion": 4.2
},
{
"id": "bbda5b3d-8293-436e-b7ad-cfc0d719d92c",
"name": "完了ステータスを更新",
"type": "n8n-nodes-base.googleSheets",
"position": [
2920,
280
],
"parameters": {
"columns": {
"value": {
"Client": "={{ $('Website Options').item.json.client }}",
"Status": "Finished"
},
"schema": [
{
"id": "Client",
"type": "string",
"display": true,
"removed": false,
"required": false,
"displayName": "Client",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "Business Type.",
"type": "string",
"display": true,
"removed": false,
"required": false,
"displayName": "Business Type.",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "City",
"type": "string",
"display": true,
"removed": false,
"required": false,
"displayName": "City",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "State",
"type": "string",
"display": true,
"removed": false,
"required": false,
"displayName": "State",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "Activate",
"type": "string",
"display": true,
"removed": false,
"required": false,
"displayName": "Activate",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "Status",
"type": "string",
"display": true,
"required": false,
"displayName": "Status",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "row_number",
"type": "string",
"display": true,
"removed": true,
"readOnly": true,
"required": false,
"displayName": "row_number",
"defaultMatch": false,
"canBeUsedToMatch": true
}
],
"mappingMode": "defineBelow",
"matchingColumns": [
"Client"
],
"attemptToConvertTypes": false,
"convertFieldsToString": false
},
"options": {},
"operation": "update",
"sheetName": {
"__rl": true,
"mode": "list",
"value": 127380953,
"cachedResultUrl": "https://docs.google.com/spreadsheets/d/1UZbZVmWLin1An4IoNq6zAb7Z6Sa0epZI3Xd5PJlDl1E/edit#gid=127380953",
"cachedResultName": "Input"
},
"documentId": {
"__rl": true,
"mode": "list",
"value": "1UZbZVmWLin1An4IoNq6zAb7Z6Sa0epZI3Xd5PJlDl1E",
"cachedResultUrl": "https://docs.google.com/spreadsheets/d/1UZbZVmWLin1An4IoNq6zAb7Z6Sa0epZI3Xd5PJlDl1E/edit?usp=drivesdk",
"cachedResultName": "ISP Search"
}
},
"typeVersion": 4.6
},
{
"id": "652ea686-3d10-495d-a136-a956ff6445be",
"name": "メール抽出ツール",
"type": "n8n-nodes-base.code",
"position": [
3660,
740
],
"parameters": {
"jsCode": "// Get the HTML content from the current item's \"data\" field\nconst html = $json[\"data\"];\n\n// Regular expression to match email addresses with specific domains\nconst emailRegex = /[a-zA-Z0-9_.+-]+@[a-zA-Z0-9-]+(?:\\.[a-zA-Z0-9-]+)*\\.[a-zA-Z]{2,}/g;\n\n// Find all emails in the HTML content (returns an array or empty array if none found)\nconst emailsFound = html.match(emailRegex) || [];\n\n// Remove duplicate emails by converting the array to a Set and back to an array\nconst uniqueEmails = [...new Set(emailsFound)];\n\n// Return an object with client, company, website, and all unique emails joined by comma\nreturn {\n client: $('Website Options').item.json.client, // Client name from the \"Website Options\" node\n company: $('Website Options').item.json.company, // Company name from the \"Website Options\" node\n website: $('Website Options').item.json.Website, // Website URL from the \"Website Options\" node\n email: uniqueEmails.join(\", \"), // Unique emails found, separated by comma\n};"
},
"typeVersion": 2
},
{
"id": "3ac582d2-d295-4ebf-a898-c7c1d0bb75e1",
"name": "メールを取得",
"type": "n8n-nodes-base.googleSheets",
"position": [
4020,
740
],
"parameters": {
"options": {},
"filtersUI": {
"values": [
{
"lookupValue": "={{ $json.company }}",
"lookupColumn": "Company"
}
]
},
"sheetName": {
"__rl": true,
"mode": "list",
"value": "gid=0",
"cachedResultUrl": "https://docs.google.com/spreadsheets/d/1UZbZVmWLin1An4IoNq6zAb7Z6Sa0epZI3Xd5PJlDl1E/edit#gid=0",
"cachedResultName": "Data"
},
"documentId": {
"__rl": true,
"mode": "list",
"value": "1UZbZVmWLin1An4IoNq6zAb7Z6Sa0epZI3Xd5PJlDl1E",
"cachedResultUrl": "https://docs.google.com/spreadsheets/d/1UZbZVmWLin1An4IoNq6zAb7Z6Sa0epZI3Xd5PJlDl1E/edit?usp=drivesdk",
"cachedResultName": "ISP Search"
}
},
"typeVersion": 4.6
},
{
"id": "b629c2b5-75df-4731-af2a-4c4c42258bc0",
"name": "メールを追加",
"type": "n8n-nodes-base.googleSheets",
"position": [
4180,
740
],
"parameters": {
"columns": {
"value": {
"emails": "={{ $json.emails ? $json.emails + \", \" + $('Email Extractor').item.json.email : $('Email Extractor').item.json.email }}",
"Company": "={{ $('Email Extractor').item.json.company }}"
},
"schema": [
{
"id": "Client",
"type": "string",
"display": true,
"removed": false,
"required": false,
"displayName": "Client",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "City",
"type": "string",
"display": true,
"removed": false,
"required": false,
"displayName": "City",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "State",
"type": "string",
"display": true,
"removed": false,
"required": false,
"displayName": "State",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "Company",
"type": "string",
"display": true,
"removed": false,
"required": false,
"displayName": "Company",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "website",
"type": "string",
"display": true,
"required": false,
"displayName": "website",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "emails",
"type": "string",
"display": true,
"required": false,
"displayName": "emails",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "row_number",
"type": "string",
"display": true,
"removed": true,
"readOnly": true,
"required": false,
"displayName": "row_number",
"defaultMatch": false,
"canBeUsedToMatch": true
}
],
"mappingMode": "defineBelow",
"matchingColumns": [
"Company"
],
"attemptToConvertTypes": false,
"convertFieldsToString": false
},
"options": {},
"operation": "update",
"sheetName": {
"__rl": true,
"mode": "list",
"value": "gid=0",
"cachedResultUrl": "https://docs.google.com/spreadsheets/d/1UZbZVmWLin1An4IoNq6zAb7Z6Sa0epZI3Xd5PJlDl1E/edit#gid=0",
"cachedResultName": "Data"
},
"documentId": {
"__rl": true,
"mode": "list",
"value": "1UZbZVmWLin1An4IoNq6zAb7Z6Sa0epZI3Xd5PJlDl1E",
"cachedResultUrl": "https://docs.google.com/spreadsheets/d/1UZbZVmWLin1An4IoNq6zAb7Z6Sa0epZI3Xd5PJlDl1E/edit?usp=drivesdk",
"cachedResultName": "ISP Search"
}
},
"typeVersion": 4.6
},
{
"id": "a7d639f1-ae76-498c-b04c-2ca32da39906",
"name": "付箋メモ",
"type": "n8n-nodes-base.stickyNote",
"position": [
840,
0
],
"parameters": {
"color": 5,
"width": 640,
"height": 1020,
"content": "# Sheet Trigger and Set Information\n\n## 1. Prepare your Sheet\n* Make sure your sheet includes these columns:\n **business type**, **city**, **state**, **activate**\n* You can copy the template from:\n [Sheet Template](https://docs.google.com/spreadsheets/d/1222TvBxE2UBb1MK2xDMoQSd5WHQ7mA5Ew-W6vBgfCJs/edit?usp=sharing)\n* Make sure you have your Google Sheets API credentials from Google Cloud\n---\n## 2. Activate the Trigger\n* Change the value in the `activate` column for the row you want to process.\n---\n## 3. Automatic Validation\n* The flow checks if all required fields are filled.\n * If any information is missing, the status will be:\n `Missing information`\n * If everything is complete, the status will be:\n `Running`\n---\n## 4. Fill the `Set Information` Node\nEnter these fields:\n* `country`\n Main country for your searches\n* `country_code`\n Example: `AR` or `US`\n* `language`\n Example: `en` or `es-419`\n* `result_count`\n Number of results per search\n"
},
"typeVersion": 1
},
{
"id": "15601f84-9ed8-4871-89cb-86ea2a9f0e7a",
"name": "付箋メモ1",
"type": "n8n-nodes-base.stickyNote",
"position": [
1520,
200
],
"parameters": {
"color": 3,
"width": 820,
"height": 700,
"content": "# Serper.dev Integration\n## 1. Get your Serper.dev API Key\n* Sign up at [Serper.dev](https://serper.dev/) to get your API key.\n* There is a free tier for initial use.\n* The search uses the information from **Set Information** (city, state, country, etc).\n---\n## 2. Search and Filter\n* Perform a business search using the given parameters.\n* Use a code filter to remove companies you don’t want (blacklist by company names).\n* Organize the remaining results for clarity.\n---\n## 3. Update the Sheet\n* Write the filtered business names and their URLs back into your sheet.\n* At this point, you have your first batch of search results.\n---\n## 4. Generate Possible Email Pages\n* For each company URL, generate possible alternative pages where an email might be found, such as:\n * `/contact`\n * (Add more paths as needed, e.g. `/about`, `/info`)\n"
},
"typeVersion": 1
},
{
"id": "6cf2731f-4d3b-4acf-bf9c-2e446543bbaa",
"name": "付箋メモ2",
"type": "n8n-nodes-base.stickyNote",
"position": [
2840,
500
],
"parameters": {
"width": 420,
"height": 400,
"content": "# Check Generated URLs\n\n* Test each generated URL (e.g. `/contact`)\n* Keep only URLs that exist\n* Avoid unnecessary API calls\n`Example:`\n* `https://company.com/contact`\n"
},
"typeVersion": 1
},
{
"id": "e9228783-bc86-4343-8f09-f06b3e513b4e",
"name": "付箋メモ3",
"type": "n8n-nodes-base.stickyNote",
"position": [
3320,
260
],
"parameters": {
"color": 3,
"width": 980,
"height": 640,
"content": "# ScrapingBee Integration\n## 1. Get your ScrapingBee API Key\n* Sign up at [ScrapingBee](https://www.scrapingbee.com/) to get your API key.\n* Free tier available for initial use.\n---\n## 2. Scrape Company Pages\n* Extract all information from each valid company URL.\n---\n## 3. Filter Successful Results\n* Keep only results where the page was scraped successfully.\n---\n## 4. Extract Emails\n* Use a code node to extract emails from each page.\n* Combine emails with the rest of the data.\n---\n## 5. Update the Sheet\n* Add all found emails to the sheet.\n* If there are several emails, add them all (separated by commas)."
},
"typeVersion": 1
},
{
"id": "651374c5-5493-413e-9746-35609e4cadeb",
"name": "付箋メモ4",
"type": "n8n-nodes-base.stickyNote",
"position": [
2860,
60
],
"parameters": {
"width": 280,
"height": 380,
"content": "# Update Status to Finished\n* Once all steps are complete, update the status in the sheet to `Finished`.\n* This marks the row as fully processed.\n"
},
"typeVersion": 1
},
{
"id": "a8091f1f-c6de-45d9-8b93-f68878315f97",
"name": "付箋メモ5",
"type": "n8n-nodes-base.stickyNote",
"position": [
0,
0
],
"parameters": {
"width": 820,
"height": 1080,
"content": "# Template Summary: Lead Enrichment & Email Discovery from Google Sheets\n\n## What this workflow does\nThis template automates the enrichment of business leads from a Google Sheet by:\n* Triggering when a row is activated\n* Searching for company information with Serper.dev\n* Generating and validating potential contact pages\n* Scraping company pages with ScrapingBee\n* Extracting emails and updating the sheet\n* Marking rows as finished\n---\n## Prerequisites\n* Google Sheet with columns: `business type`, `city`, `state`, `activate`\n* **Copy the ready-to-use template:**\n [Sheet Template](https://docs.google.com/spreadsheets/d/1222TvBxE2UBb1MK2xDMoQSd5WHQ7mA5Ew-W6vBgfCJs/edit?usp=sharing)\n* Google Sheets API credentials (from Google Cloud)\n* Serper.dev API key (free tier available)\n* ScrapingBee API key (free tier available)\n---\n## Inputs\n* **Google Sheet row:**\n Must include `business type`, `city`, `state`, `activate`\n* **Set Information Node:**\n `country`, `country_code`, `language`, `result_count` (can also be provided via columns in the sheet)\n---\n## Outputs\n* **Google Sheet update:**\n Company names, URLs, found email addresses (comma-separated if multiple), and status updates (`Running`, `Missing information`, `Finished`)\n---\n## Configuration Required\n* Connect Google Sheets node with your Google Cloud credentials\n* Add your Serper.dev API key to the HTTP Request node\n* Add your ScrapingBee API key to the scraping node\n* Adjust search and filtering options as needed\n---\n## How to customize the workflow\n* **Send `country`, `country_code`, and `result_count` from the sheet:**\n Add these as columns in your sheet and update the workflow to read their values dynamically, making your search fully configurable per row.\n* **Add more blacklist terms:**\n Update the code node with additional company names or keywords you want to exclude from the search results.\n* **Extract more contact details:**\n Modify the email extraction code to find other contact info (like phone numbers or social profiles) if needed.\n"
},
"typeVersion": 1
}
],
"active": false,
"pinData": {},
"settings": {
"executionOrder": "v1"
},
"versionId": "ea0dbcd3-d077-4097-a2bd-01469f5e8544",
"connections": {
"77536dd3-7863-4dfd-a531-48c45734546f": {
"main": [
[
{
"node": "f2651a42-0c4a-45d2-afba-c9478e0d45ac",
"type": "main",
"index": 0
}
],
[
{
"node": "f6d286f2-7d8e-48ce-a846-060948b5adfc",
"type": "main",
"index": 0
}
]
]
},
"85a56d75-5c7c-4a99-8b80-3ba1b09763f2": {
"main": [
[
{
"node": "652ea686-3d10-495d-a136-a956ff6445be",
"type": "main",
"index": 0
}
],
[
{
"node": "f6d286f2-7d8e-48ce-a846-060948b5adfc",
"type": "main",
"index": 0
}
]
]
},
"32910d3a-079b-4e56-be68-9968a00f8966": {
"main": [
[
{
"node": "3ac582d2-d295-4ebf-a898-c7c1d0bb75e1",
"type": "main",
"index": 0
}
],
[
{
"node": "f6d286f2-7d8e-48ce-a846-060948b5adfc",
"type": "main",
"index": 0
}
]
]
},
"823f2cfb-c1a7-4c16-a95a-100aad15426a": {
"main": [
[
{
"node": "eb3b27a5-dcf0-48c0-ab2d-3947ed1e95da",
"type": "main",
"index": 0
}
],
[
{
"node": "ac6e7f24-34ae-4811-8a50-bb0ca5e14eea",
"type": "main",
"index": 0
}
]
]
},
"ee67ab82-b78b-4487-97a7-d27e33bddf3a": {
"main": [
[
{
"node": "f6d286f2-7d8e-48ce-a846-060948b5adfc",
"type": "main",
"index": 0
}
]
]
},
"b629c2b5-75df-4731-af2a-4c4c42258bc0": {
"main": [
[
{
"node": "ee67ab82-b78b-4487-97a7-d27e33bddf3a",
"type": "main",
"index": 0
}
]
]
},
"3ac582d2-d295-4ebf-a898-c7c1d0bb75e1": {
"main": [
[
{
"node": "b629c2b5-75df-4731-af2a-4c4c42258bc0",
"type": "main",
"index": 0
}
]
]
},
"382f7581-ac23-4ba6-928c-9a6e4a990230": {
"main": [
[
{
"node": "77536dd3-7863-4dfd-a531-48c45734546f",
"type": "main",
"index": 0
}
]
]
},
"f2651a42-0c4a-45d2-afba-c9478e0d45ac": {
"main": [
[
{
"node": "85a56d75-5c7c-4a99-8b80-3ba1b09763f2",
"type": "main",
"index": 0
}
]
]
},
"652ea686-3d10-495d-a136-a956ff6445be": {
"main": [
[
{
"node": "32910d3a-079b-4e56-be68-9968a00f8966",
"type": "main",
"index": 0
}
]
]
},
"f6d286f2-7d8e-48ce-a846-060948b5adfc": {
"main": [
[
{
"node": "bbda5b3d-8293-436e-b7ad-cfc0d719d92c",
"type": "main",
"index": 0
}
],
[
{
"node": "382f7581-ac23-4ba6-928c-9a6e4a990230",
"type": "main",
"index": 0
}
]
]
},
"9e4d96df-7be4-4a3b-b8ec-71f40698a8e8": {
"main": [
[
{
"node": "293eabf8-c7c5-485f-9631-60a6b3b746d0",
"type": "main",
"index": 0
}
]
]
},
"8df36dea-5646-4900-a50f-995601085c49": {
"main": [
[
{
"node": "f6d286f2-7d8e-48ce-a846-060948b5adfc",
"type": "main",
"index": 0
}
]
]
},
"3109c8db-837b-48f2-b1ff-9f86d9c8d79e": {
"main": [
[
{
"node": "8df36dea-5646-4900-a50f-995601085c49",
"type": "main",
"index": 0
}
]
]
},
"ffe396f7-dc1c-4ae0-ab47-fedd20c3bace": {
"main": [
[
{
"node": "823f2cfb-c1a7-4c16-a95a-100aad15426a",
"type": "main",
"index": 0
}
]
]
},
"eb3b27a5-dcf0-48c0-ab2d-3947ed1e95da": {
"main": [
[
{
"node": "9e4d96df-7be4-4a3b-b8ec-71f40698a8e8",
"type": "main",
"index": 0
}
]
]
},
"9944d54a-7a52-4b4d-94d3-4b635839b284": {
"main": [
[
{
"node": "3109c8db-837b-48f2-b1ff-9f86d9c8d79e",
"type": "main",
"index": 0
}
]
]
},
"293eabf8-c7c5-485f-9631-60a6b3b746d0": {
"main": [
[
{
"node": "9944d54a-7a52-4b4d-94d3-4b635839b284",
"type": "main",
"index": 0
}
]
]
}
}
}よくある質問
このワークフローの使い方は？
上記のJSON設定コードをコピーし、n8nインスタンスで新しいワークフローを作成して「JSONからインポート」を選択、設定を貼り付けて認証情報を必要に応じて変更してください。
このワークフローはどんな場面に適していますか？
上級 - リード獲得
有料ですか？
このワークフローは完全無料です。ただし、ワークフローで使用するサードパーティサービス（OpenAI APIなど）は別途料金が発生する場合があります。
関連ワークフロー
OpenAIのgpt-image-1とAIキャプションを使ってInstagramカルーセルを作成し、公開する
Google SheetsからGPT-Image-1とAIキャプションを使用してInstagramカルーセルを自動生成
If
Set
Code
+
If
Set
Code
32 ノードJorge Martínez
マルチモーダルAI
潜在顧客開掘とメールワーキングフロー
Google Maps、SendGrid、AIを使用してB2Bリード獲得とメールマーケティングを自動化
If
Set
Code
+
If
Set
Code
141 ノードEzema Kingsley Chibuzo
リード獲得
LeadfeederとApollo.ioのデータ収集を自動化
リードリッチパイプライン：LeadfeederからApollo、そしてGoogleスプレッドシートへ
If
Set
Code
+
If
Set
Code
23 ノードKhairul Muhtadin
リード獲得
GPT-4o、WordPress、LinkedInを使ってRSSから自動でブログ記事を投稿
GPT-4o、WordPress、LinkedInを使ってRSSコンテンツを自動のにブログ記事へ公開
If
Set
Code
+
If
Set
Code
40 ノードImmanuel
人工知能
n8nノードの探索（可視化リファレンスライブラリ内）
n8nノードを可視化リファレンスライブラリで探索
If
Ftp
Set
+
If
Ftp
Set
113 ノードI versus AI
その他
AI駆動のGoogle Mapsビジネスデータスクレイピングとスプレッドシート出力
AIを基盤としたGoogle Mapsビジネスデータの収集、データの充実化、スプレッドシートへのエクスポートをサポート
If
Code
Wait
+
If
Code
Wait
25 ノードMsaid Mohamed el hadi
リード獲得
ワークフロー情報
難易度
上級
ノード数26
カテゴリー1
ノードタイプ9
作成者
Jorge Martínez
@jorgemartinezjamAI Engineer & Full Stack Developer skilled in designing intelligent agents, workflow automation, and NLP solutions. Experienced with LLMs, Relevance AI, make, zapier, and n8n. Proficient in integrating AI and chatbots into web apps, optimizing processes, and leveraging automation for real-time productivity. Strong background in backend and frontend development, REST APIs, and database management.
外部リンク
n8n.ioで表示 →
このワークフローを共有