E-Mail-Adressen von Geschäftskontakten in Google Sheets anreichern und extrahieren

• •Möglicherweise sind Ziel-API-Anmeldedaten erforderlich
• •Google Sheets API-Anmeldedaten

{
  "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": "Über Elemente schleifen",
      "type": "n8n-nodes-base.splitInBatches",
      "position": [
        2460,
        720
      ],
      "parameters": {
        "options": {}
      },
      "typeVersion": 3,
      "alwaysOutputData": false
    },
    {
      "id": "77536dd3-7863-4dfd-a531-48c45734546f",
      "name": "If",
      "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": "Website Options",
      "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 Tabellen 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": "If1",
      "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": "If2",
      "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": "Warten",
      "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": "If3",
      "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": "Setzen Information",
      "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": "Search Companies (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": "Extract Company & Website",
      "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": "Update Running Status",
      "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": "Update Missing Information Status",
      "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": "Add research Results",
      "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": "Test pages",
      "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": "Update Finished Status",
      "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": "E-Mail Extractor",
      "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": "Get E-Mails",
      "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": "Add E-Mails",
      "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": "Haftnotiz",
      "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": "Haftnotiz1",
      "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": "Haftnotiz2",
      "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": "Haftnotiz3",
      "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": "Haftnotiz4",
      "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": "Haftnotiz5",
      "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": "Loop Over Items",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "85a56d75-5c7c-4a99-8b80-3ba1b09763f2": {
      "main": [
        [
          {
            "node": "Email Extractor",
            "type": "main",
            "index": 0
          }
        ],
        [
          {
            "node": "Loop Over Items",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "32910d3a-079b-4e56-be68-9968a00f8966": {
      "main": [
        [
          {
            "node": "Get Emails",
            "type": "main",
            "index": 0
          }
        ],
        [
          {
            "node": "Loop Over Items",
            "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
          }
        ]
      ]
    },
    "Wait": {
      "main": [
        [
          {
            "node": "Loop Over Items",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Add Emails": {
      "main": [
        [
          {
            "node": "Wait",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Get Emails": {
      "main": [
        [
          {
            "node": "Add Emails",
            "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
          }
        ]
      ]
    },
    "Email Extractor": {
      "main": [
        [
          {
            "node": "32910d3a-079b-4e56-be68-9968a00f8966",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Loop Over Items": {
      "main": [
        [
          {
            "node": "bbda5b3d-8293-436e-b7ad-cfc0d719d92c",
            "type": "main",
            "index": 0
          }
        ],
        [
          {
            "node": "382f7581-ac23-4ba6-928c-9a6e4a990230",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Set Information": {
      "main": [
        [
          {
            "node": "293eabf8-c7c5-485f-9631-60a6b3b746d0",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "8df36dea-5646-4900-a50f-995601085c49": {
      "main": [
        [
          {
            "node": "Loop Over Items",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "3109c8db-837b-48f2-b1ff-9f86d9c8d79e": {
      "main": [
        [
          {
            "node": "8df36dea-5646-4900-a50f-995601085c49",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Google Sheets Trigger": {
      "main": [
        [
          {
            "node": "823f2cfb-c1a7-4c16-a95a-100aad15426a",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "eb3b27a5-dcf0-48c0-ab2d-3947ed1e95da": {
      "main": [
        [
          {
            "node": "Set Information",
            "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
          }
        ]
      ]
    }
  }
}