Google Forms、Sheets、GoHighLevel CRM を使ったプロスペクト登録自動化と重複除去

• •ターゲットAPIの認証情報が必要な場合あり
• •Google Sheets API認証情報

{
  "meta": {
    "instanceId": "8443f10082278c46aa5cf3acf8ff0f70061a2c58bce76efac814b16290845177",
    "templateCredsSetupCompleted": true
  },
  "nodes": [
    {
      "id": "f1e3a076-6607-4018-9bcc-ef0008d7156d",
      "name": "Google スプレッドシート Trigger1",
      "type": "n8n-nodes-base.googleSheetsTrigger",
      "notes": "Monitors Google Forms responses sheet for new submissions every minute. Triggers when new lead data is added.",
      "position": [
        -976,
        256
      ],
      "parameters": {
        "options": {},
        "pollTimes": {
          "item": [
            {
              "mode": "everyMinute"
            }
          ]
        },
        "sheetName": {
          "__rl": true,
          "mode": "url",
          "value": "https://docs.google.com/spreadsheets/d/1NxuC7u6ycvn18f-vyUjvIiYmQ_DJiH7q6YwLndIlLi8/edit?resourcekey=&gid=2142376877#gid=2142376877"
        },
        "documentId": {
          "__rl": true,
          "mode": "url",
          "value": "https://docs.google.com/spreadsheets/d/1NxuC7u6ycvn18f-vyUjvIiYmQ_DJiH7q6YwLndIlLi8/edit?resourcekey=&gid=2142376877#gid=2142376877"
        }
      },
      "credentials": {
        "googleSheetsTriggerOAuth2Api": {
          "id": "tjY8nawuuRO1rxAE",
          "name": "Google Sheets Trigger account"
        }
      },
      "description": "Triggers the workflow when a new lead submits the Google Form",
      "typeVersion": 1
    },
    {
      "id": "6a0428e1-327b-403f-9319-5cb078a1758c",
      "name": "Lookup Lead1",
      "type": "n8n-nodes-base.googleSheets",
      "notes": "Searches the leads database spreadsheet to check if an email already exists. This helps identify duplicate leads.",
      "position": [
        -752,
        256
      ],
      "parameters": {
        "options": {},
        "sheetName": {
          "__rl": true,
          "mode": "list",
          "value": "gid=0",
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/1QMm8xd7-xK-0rwfx9-98hWoKBiNsXljwah4F9qSao1s/edit#gid=0",
          "cachedResultName": "Sheet1"
        },
        "documentId": {
          "__rl": true,
          "mode": "list",
          "value": "1QMm8xd7-xK-0rwfx9-98hWoKBiNsXljwah4F9qSao1s",
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/1QMm8xd7-xK-0rwfx9-98hWoKBiNsXljwah4F9qSao1s/edit?usp=drivesdk",
          "cachedResultName": "Lead Database"
        }
      },
      "credentials": {
        "googleSheetsOAuth2Api": {
          "id": "s4dP1fNuVZ2gWvs3",
          "name": "Google Sheets account"
        }
      },
      "description": "Checks if the lead's email already exists in the database to prevent duplicates",
      "typeVersion": 3
    },
    {
      "id": "c7af40f1-cb01-43d5-ab50-b3874c427e4d",
      "name": "Update Contact (GHL)1",
      "type": "n8n-nodes-base.httpRequest",
      "notes": "Updates existing contact in GoHighLevel with latest information from the form submission. Uses contact ID from the lookup result.",
      "position": [
        -304,
        352
      ],
      "parameters": {
        "url": "=https://rest.gohighlevel.com/v1/contacts?email={{ $json[\"Email address\"] }}\n",
        "options": {},
        "authentication": "genericCredentialType",
        "genericAuthType": "httpHeaderAuth"
      },
      "credentials": {
        "goHighLevelApi": {
          "id": "7QfYKr1ndNWKTj36",
          "name": "GoHighLevel API"
        },
        "httpHeaderAuth": {
          "id": "7QfYKr1ndNWKTj36",
          "name": "Header Auth account"
        }
      },
      "description": "Updates existing duplicate contact in GoHighLevel CRM with new information",
      "typeVersion": 4.2
    },
    {
      "id": "3125090e-0913-454a-83e6-3a2ee0288797",
      "name": "Log New Lead1",
      "type": "n8n-nodes-base.googleSheets",
      "notes": "Records the new lead information in the leads database spreadsheet along with the GoHighLevel contact ID for future reference.",
      "position": [
        144,
        160
      ],
      "parameters": {
        "options": {},
        "operation": "append",
        "sheetName": {
          "__rl": true,
          "mode": "list",
          "value": "gid=0",
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/1QMm8xd7-xK-0rwfx9-98hWoKBiNsXljwah4F9qSao1s/edit#gid=0",
          "cachedResultName": "Sheet1"
        },
        "documentId": {
          "__rl": true,
          "mode": "list",
          "value": "1QMm8xd7-xK-0rwfx9-98hWoKBiNsXljwah4F9qSao1s",
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/1QMm8xd7-xK-0rwfx9-98hWoKBiNsXljwah4F9qSao1s/edit?usp=drivesdk",
          "cachedResultName": "Lead Database"
        }
      },
      "credentials": {
        "googleSheetsOAuth2Api": {
          "id": "s4dP1fNuVZ2gWvs3",
          "name": "Google Sheets account"
        }
      },
      "description": "Adds new lead to the database spreadsheet with GHL contact ID for tracking",
      "typeVersion": 3
    },
    {
      "id": "061dc960-0221-4318-a2a8-47422224e7e5",
      "name": "Log Duplicate Lead1",
      "type": "n8n-nodes-base.googleSheets",
      "notes": "Updates the existing lead record in the database with new submission timestamp and increments the update counter.",
      "position": [
        144,
        352
      ],
      "parameters": {
        "options": {},
        "dataMode": "autoMapInputData",
        "operation": "update",
        "sheetName": {
          "__rl": true,
          "mode": "list",
          "value": "gid=0",
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/1ZbE_X3ctoS5HRUNGO5vn3Srj2ahG_Tt3uArQttk91hU/edit#gid=0",
          "cachedResultName": "Sheet1"
        },
        "documentId": {
          "__rl": true,
          "mode": "list",
          "value": "1ZbE_X3ctoS5HRUNGO5vn3Srj2ahG_Tt3uArQttk91hU",
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/1ZbE_X3ctoS5HRUNGO5vn3Srj2ahG_Tt3uArQttk91hU/edit?usp=drivesdk",
          "cachedResultName": "Leads duplicate"
        }
      },
      "credentials": {
        "googleSheetsOAuth2Api": {
          "id": "s4dP1fNuVZ2gWvs3",
          "name": "Google Sheets account"
        }
      },
      "description": "Updates existing lead record in database to track duplicate submissions",
      "typeVersion": 3
    },
    {
      "id": "c05d19ef-4172-4476-a5d7-0fd9eaa6f347",
      "name": "Create Contact (GHL)1",
      "type": "n8n-nodes-base.httpRequest",
      "notes": "Creates a new contact in GoHighLevel CRM with all the form data. Includes custom fields and tags for better organization.",
      "position": [
        -80,
        160
      ],
      "parameters": {
        "url": "https://rest.gohighlevel.com/v1/contacts/",
        "options": {},
        "jsonBody": "={\n  \"firstName\": \"{{ $('Google Sheets Trigger1').item.json['First Name'] }}\",\n  \"lastName\": \"{{ $('Google Sheets Trigger1').item.json['Last Name'] }}\",\n  \"email\": \"{{ $('Google Sheets Trigger1').item.json.Email }}\",\n  \"phone\": \"{{ $('Google Sheets Trigger1').item.json.Phone }}\",\n  \"source\": \"{{ $('Google Sheets Trigger1').item.json.Source || 'Google Form' }}\",\n  \"customFields\": {\n    \"lead_score\": \"{{ $('Google Sheets Trigger1').item.json['Lead Score'] || 'Cold' }}\",\n    \"form_submission_date\": \"{{ $now }}\"\n  },\n  \"tags\": [\"New Lead\", \"Google Form\"]\n}",
        "sendBody": true,
        "specifyBody": "json",
        "authentication": "predefinedCredentialType",
        "nodeCredentialType": "goHighLevelApi"
      },
      "credentials": {
        "goHighLevelApi": {
          "id": "7QfYKr1ndNWKTj36",
          "name": "GoHighLevel API"
        }
      },
      "description": "Creates new contact in GoHighLevel CRM with form submission data",
      "typeVersion": 4.2
    },
    {
      "id": "545f1688-1b7f-41f8-9b32-efbabcfcca98",
      "name": "Check the duplication",
      "type": "n8n-nodes-base.filter",
      "position": [
        -304,
        160
      ],
      "parameters": {
        "options": {},
        "conditions": {
          "options": {
            "version": 2,
            "leftValue": "",
            "caseSensitive": true,
            "typeValidation": "strict"
          },
          "combinator": "and",
          "conditions": [
            {
              "id": "374d6f04-de54-439a-b49e-4b7df42a390e",
              "operator": {
                "type": "string",
                "operation": "notEquals"
              },
              "leftValue": "={{ $json[\"Email address\"] }}",
              "rightValue": "={{ $('Lookup Lead1').item.json[\"Email address\"] }}"
            }
          ]
        }
      },
      "typeVersion": 2.2
    },
    {
      "id": "bb3ef342-9acc-40cb-a1aa-b56a846f6e01",
      "name": "Check from the data base?1",
      "type": "n8n-nodes-base.if",
      "notes": "Decision point: If lookup found an email (duplicate), go to 'true' path. If no email found (new lead), go to 'false' path.",
      "position": [
        -528,
        256
      ],
      "parameters": {
        "options": {},
        "conditions": {
          "options": {
            "version": 1,
            "leftValue": "",
            "caseSensitive": true,
            "typeValidation": "strict"
          },
          "combinator": "and",
          "conditions": [
            {
              "id": "41cc0f5c-5131-49c7-bd6c-1514c5b23a20",
              "operator": {
                "type": "string",
                "operation": "notEquals"
              },
              "leftValue": "={{ $('Google Sheets Trigger1').item.json['Email address'] }}",
              "rightValue": "={{ $json[\"Email address\"] }}"
            }
          ]
        }
      },
      "description": "Routes the workflow based on whether the lead is new or a duplicate",
      "typeVersion": 2
    },
    {
      "id": "b92e1f44-4326-421e-b25f-52862e616945",
      "name": "付箋",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -1152,
        -144
      ],
      "parameters": {
        "height": 384,
        "content": "## Node 1: Google Sheets Trigger1\n**Type**: Trigger Node\n**Purpose**: Lead Form Monitor\n**Description**: This node continuously monitors a spreadsheet for new lead form submissions from various sources. It polls the data source every minute and automatically triggers the workflow when new lead data is detected. The node captures comprehensive lead information including names, email addresses, phone numbers, and other form data that will be used for duplicate detection and CRM integration."
      },
      "typeVersion": 1
    },
    {
      "id": "26d72008-7869-486c-8c49-8c1e90fb54a0",
      "name": "付箋1",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -832,
        -160
      ],
      "parameters": {
        "height": 400,
        "content": "## Node 2: Lookup Lead1\n**Type**: Data Query Node\n**Purpose**: Duplicate Detection Scanner\n**Description**: This node searches an existing lead database to check if the incoming lead's email address already exists in the system. It performs a lookup operation against a master lead database spreadsheet to identify potential duplicates based on email matching. The node returns existing lead data if found, which will be used to determine the processing path."
      },
      "typeVersion": 1
    },
    {
      "id": "39c14e1e-8ef1-4c3e-98ff-495428e90e46",
      "name": "付箋2",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -640,
        416
      ],
      "parameters": {
        "height": 400,
        "content": "## Node 3: Check from the data base?1\n**Type**: Conditional Logic Node\n**Purpose**: Duplicate Decision Router\n**Description**: This node evaluates whether the lead lookup found a matching email address in the database. It creates two distinct execution paths: one for new leads (no match found) and another for duplicate leads (match found). The conditional logic compares email addresses to determine if this is a first-time submission or a repeat contact."
      },
      "typeVersion": 1
    },
    {
      "id": "45de1ad7-accb-41d3-a9bc-85e6ab60d377",
      "name": "付箋3",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -432,
        -192
      ],
      "parameters": {
        "height": 336,
        "content": "## Node 4: Check the duplication\n**Type**: Filter Node\n**Purpose**: New Lead Validator\n**Description**: This node performs an additional validation step to ensure leads are truly new before proceeding with CRM creation. It applies filtering logic to verify that the email address is not already present in the system, providing an extra layer of duplicate prevention before creating new contacts in the CRM system."
      },
      "typeVersion": 1
    },
    {
      "id": "d51e3ad8-642f-4ee7-a5ee-b5983ea962db",
      "name": "付箋4",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -112,
        -208
      ],
      "parameters": {
        "height": 352,
        "content": "## Node 5: Create Contact (GHL)1\n**Type**: CRM Integration Node\n**Purpose**: New Contact Creator\n**Description**: This node creates a new contact record in the CRM system for validated new leads. It formats the lead data into the required structure, including names, contact information, source tracking, custom fields, and appropriate tags. The node sends a structured API request to create a complete contact profile with all submitted information."
      },
      "typeVersion": 1
    },
    {
      "id": "b00943a6-0039-404a-8643-4b55d0ca4fe3",
      "name": "付箋5",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        272,
        -160
      ],
      "parameters": {
        "height": 352,
        "content": "## Node 6: Log New Lead1\n**Type**: Data Storage Node\n**Purpose**: New Lead Database Writer\n**Description**: This node records new lead information in the master lead database spreadsheet along with the CRM contact ID returned from the contact creation process. It appends a new row with all lead details and the generated CRM identifier, creating a permanent record for future duplicate detection and lead tracking."
      },
      "typeVersion": 1
    },
    {
      "id": "0f6fdc51-5af2-4bae-81b9-7896b96b232a",
      "name": "付箋6",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -320,
        496
      ],
      "parameters": {
        "height": 352,
        "content": "## Node 7: Update Contact (GHL)1\n**Type**: CRM Update Node\n**Purpose**: Duplicate Contact Updater\n**Description**: This node updates existing contact records in the CRM system when duplicate leads are detected. It sends updated information to refresh the existing contact profile with any new data from the recent submission, ensuring the CRM maintains the most current information while avoiding duplicate contact creation."
      },
      "typeVersion": 1
    },
    {
      "id": "62b84233-71da-4b5d-b468-b86e334e9b57",
      "name": "付箋7",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -32,
        496
      ],
      "parameters": {
        "height": 336,
        "content": "## Node 8: Code\n**Type**: Data Processing Node\n**Purpose**: Contact Data Processor\n**Description**: This node processes the response from CRM operations to extract and format contact information for further workflow steps. It handles the returned contact data, filters relevant information, and prepares it for the duplicate logging process. The node ensures proper data structure for downstream operations."
      },
      "typeVersion": 1
    },
    {
      "id": "e6395ad9-7865-4eb9-8f05-5139f122180c",
      "name": "付箋8",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        336,
        384
      ],
      "parameters": {
        "height": 352,
        "content": "## Node 9: Log Duplicate Lead1\n**Type**: Duplicate Tracking Node\n**Purpose**: Duplicate Activity Logger\n**Description**: This node records duplicate submission activity in a dedicated tracking spreadsheet. It updates records to show when duplicate submissions occur, tracks submission timestamps, and maintains a count of repeat contacts. This provides valuable analytics about lead behavior and form submission patterns."
      },
      "typeVersion": 1
    },
    {
      "id": "aed3b9c1-5603-4037-adb8-8e0d4cb22f02",
      "name": "Field extraction",
      "type": "n8n-nodes-base.code",
      "position": [
        -80,
        352
      ],
      "parameters": {
        "jsCode": "// Grab contacts from the previous node\nconst contacts = items[0].json.contacts || [];\n\n// Set your target email (hardcode for testing, or read dynamically)\nconst targetEmail = $json.email || \"huw@talentism.com\";\n\nif (!targetEmail) {\n  return [{ json: { error: \"No targetEmail provided\" } }];\n}\n\n// Filter only those contacts that actually have an email\nconst match = contacts.filter(c => \n  c.email && c.email.toLowerCase() === targetEmail.toLowerCase()\n);\n\n// Return results or a clear message if not found\nif (match.length === 0) {\n  return [{ json: { message: \"No contact found for \" + targetEmail } }];\n}\n\nreturn match.map(c => ({ json: c }));\n"
      },
      "typeVersion": 2
    }
  ],
  "pinData": {},
  "connections": {
    "6a0428e1-327b-403f-9319-5cb078a1758c": {
      "main": [
        [
          {
            "node": "bb3ef342-9acc-40cb-a1aa-b56a846f6e01",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "aed3b9c1-5603-4037-adb8-8e0d4cb22f02": {
      "main": [
        [
          {
            "node": "061dc960-0221-4318-a2a8-47422224e7e5",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "545f1688-1b7f-41f8-9b32-efbabcfcca98": {
      "main": [
        [
          {
            "node": "c05d19ef-4172-4476-a5d7-0fd9eaa6f347",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "c05d19ef-4172-4476-a5d7-0fd9eaa6f347": {
      "main": [
        [
          {
            "node": "3125090e-0913-454a-83e6-3a2ee0288797",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "c7af40f1-cb01-43d5-ab50-b3874c427e4d": {
      "main": [
        [
          {
            "node": "aed3b9c1-5603-4037-adb8-8e0d4cb22f02",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Google Sheets Trigger1": {
      "main": [
        [
          {
            "node": "6a0428e1-327b-403f-9319-5cb078a1758c",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "bb3ef342-9acc-40cb-a1aa-b56a846f6e01": {
      "main": [
        [
          {
            "node": "545f1688-1b7f-41f8-9b32-efbabcfcca98",
            "type": "main",
            "index": 0
          }
        ],
        [
          {
            "node": "c7af40f1-cb01-43d5-ab50-b3874c427e4d",
            "type": "main",
            "index": 0
          }
        ]
      ]
    }
  }
}