AI-Powered HighLevel → Google Sheets Sync & Daily HTML Report Workflow (GPT-4o + Gmail)

{
  "id": "XDPNH4sW5lnVmzdE",
  "meta": {
    "instanceId": "8443f10082278c46aa5cf3acf8ff0f70061a2c58bce76efac814b16290845177",
    "templateCredsSetupCompleted": true
  },
  "name": "AI-Powered HighLevel → Google Sheets Sync & Daily HTML Report Workflow (GPT-4o + Gmail)",
  "tags": [],
  "nodes": [
    {
      "id": "f28f8aca-d06c-4fd1-9783-42614b274efa",
      "name": "When clicking ‘Execute workflow’",
      "type": "n8n-nodes-base.manualTrigger",
      "position": [
        -896,
        0
      ],
      "parameters": {},
      "typeVersion": 1
    },
    {
      "id": "7b33aaf1-c1a5-45d2-a0b5-401f59c03bff",
      "name": "Configure GPT-4o Model",
      "type": "@n8n/n8n-nodes-langchain.lmChatAzureOpenAi",
      "position": [
        992,
        512
      ],
      "parameters": {
        "model": "gpt-4o",
        "options": {}
      },
      "credentials": {
        "azureOpenAiApi": {
          "id": "C3WzT18XqF8OdVM6",
          "name": "Azure Open AI account"
        }
      },
      "typeVersion": 1
    },
    {
      "id": "c36b8ef9-44fd-4758-a548-46a5714845b4",
      "name": "Fetch Opportunities from HighLevel CRM",
      "type": "n8n-nodes-base.highLevel",
      "position": [
        -640,
        0
      ],
      "parameters": {
        "limit": 5,
        "filters": {},
        "resource": "opportunity",
        "operation": "getAll",
        "requestOptions": {}
      },
      "credentials": {
        "highLevelOAuth2Api": {
          "id": "5QWHSi134dLIBEsC",
          "name": "HighLevel account"
        }
      },
      "typeVersion": 2
    },
    {
      "id": "8ff4a19e-7a48-4fb9-b9db-ab71e40d0453",
      "name": "Validate Opportunity Data Payload",
      "type": "n8n-nodes-base.if",
      "position": [
        -304,
        0
      ],
      "parameters": {
        "options": {},
        "conditions": {
          "options": {
            "version": 2,
            "leftValue": "",
            "caseSensitive": true,
            "typeValidation": "strict"
          },
          "combinator": "and",
          "conditions": [
            {
              "id": "e2adb005-2b3c-4d1e-8445-442df1fe925a",
              "operator": {
                "type": "string",
                "operation": "notEmpty",
                "singleValue": true
              },
              "leftValue": "={{ $json.id }}",
              "rightValue": ""
            }
          ]
        }
      },
      "typeVersion": 2.2
    },
    {
      "id": "2e5c99f5-43e0-4e6c-bc22-716c0e66dab9",
      "name": "Log Invalid Opportunities to Google Sheets",
      "type": "n8n-nodes-base.googleSheets",
      "position": [
        48,
        496
      ],
      "parameters": {
        "operation": "append",
        "sheetName": {
          "__rl": true,
          "mode": "list",
          "value": "",
          "cachedResultUrl": "",
          "cachedResultName": ""
        },
        "documentId": {
          "__rl": true,
          "mode": "url",
          "value": ""
        }
      },
      "credentials": {
        "googleSheetsOAuth2Api": {
          "id": "kpPEOLCGn963qpoh",
          "name": "[email protected]"
        }
      },
      "typeVersion": 4.6
    },
    {
      "id": "f778a6a3-977b-434a-88c1-ee88d5ea1ac4",
      "name": "Extract Key Fields from HighLevel Data",
      "type": "n8n-nodes-base.code",
      "position": [
        48,
        0
      ],
      "parameters": {
        "jsCode": "return items.map(item => {\n  const o = item.json;\n\n  return {\n    json: {\n      id: o.id,\n      name: o.name,\n      company: o.contact?.companyName || null,\n      email: o.contact?.email || null,\n      phone: o.contact?.phone || null,\n      source: o.source || null,\n      assignedTo: o.assignedTo || null,\n      pipelineId: o.pipelineId,\n      stageId: o.pipelineStageId,\n      tags: o.contact?.tags?.join(\", \") || null,\n      monetaryValue: o.monetaryValue || 0,\n      createdAt: o.createdAt,\n      updatedAt: o.updatedAt,\n      lastStageChangeAt: o.lastStageChangeAt\n    }\n  };\n});\n"
      },
      "typeVersion": 2
    },
    {
      "id": "afd30f6f-9eac-43d1-9e07-0788c7b4206e",
      "name": "Normalize Opportunity Structure",
      "type": "n8n-nodes-base.code",
      "position": [
        368,
        0
      ],
      "parameters": {
        "mode": "runOnceForEachItem",
        "jsCode": "const op = item.json;\n\nreturn {\n  json: {\n    id: op.id,\n    name: op.name,\n    company: op.company || op.contact?.companyName || null,\n    email: op.email || op.contact?.email || null,\n    phone: op.phone || op.contact?.phone || null,\n    source: op.source,\n    assignedTo: op.assignedTo,\n    pipelineId: op.pipelineId,\n    stageId: op.stageId || op.pipelineStageId,\n    tags: op.tags,\n    monetaryValue: op.monetaryValue,\n    createdAt: op.createdAt,\n    updatedAt: op.updatedAt,\n    lastStageChangeAt: op.lastStageChangeAt\n  }\n};\n"
      },
      "typeVersion": 2,
      "alwaysOutputData": false
    },
    {
      "id": "8d3c0c7b-6399-46ff-ac75-4f449da38428",
      "name": "Update Opportunity Records in Google Sheets",
      "type": "n8n-nodes-base.googleSheets",
      "position": [
        752,
        -624
      ],
      "parameters": {
        "columns": {
          "value": {
            "id": "={{ $json.id }}",
            "name": "={{ $json.name }}",
            "email": "={{ $json.email }}",
            "phone": "={{ $json.phone }}",
            "source": "={{ $json.source }}",
            "company": "={{ $json.company }}",
            "stageId": "={{ $json.stageId }}",
            "createdAt": "={{ $json.createdAt }}",
            "updatedAt": "={{ $json.updatedAt }}",
            "assignedTo": "={{ $json.assignedTo }}",
            "pipelineId": "={{ $json.pipelineId }}",
            "monetaryValue": "={{ $json.monetaryValue }}",
            "lastStageChangeAt": "={{ $json.lastStageChangeAt }}"
          },
          "schema": [
            {
              "id": "id",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "id",
              "defaultMatch": true,
              "canBeUsedToMatch": true
            },
            {
              "id": "name",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "name",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "company",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "company",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "email",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "email",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "phone",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "phone",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "source",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "source",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "assignedTo",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "assignedTo",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "pipelineId",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "pipelineId",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "stageId",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "stageId",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "monetaryValue",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "monetaryValue",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "createdAt",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "createdAt",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "updatedAt",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "updatedAt",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "lastStageChangeAt",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "lastStageChangeAt",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            }
          ],
          "mappingMode": "defineBelow",
          "matchingColumns": [
            "id"
          ],
          "attemptToConvertTypes": false,
          "convertFieldsToString": false
        },
        "options": {},
        "operation": "appendOrUpdate",
        "sheetName": {
          "__rl": true,
          "mode": "list",
          "value": 1423466972,
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/17rcNd_ZpUQLm0uWEVbD-NY6GyFUkrD4BglvawlyBygM/edit#gid=1423466972",
          "cachedResultName": "ghl database"
        },
        "documentId": {
          "__rl": true,
          "mode": "list",
          "value": "17rcNd_ZpUQLm0uWEVbD-NY6GyFUkrD4BglvawlyBygM",
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/17rcNd_ZpUQLm0uWEVbD-NY6GyFUkrD4BglvawlyBygM/edit?usp=drivesdk",
          "cachedResultName": "sample_leads_50"
        }
      },
      "credentials": {
        "googleSheetsOAuth2Api": {
          "id": "kpPEOLCGn963qpoh",
          "name": "[email protected]"
        }
      },
      "typeVersion": 4.6
    },
    {
      "id": "5ed73d10-d43b-448e-bca6-5b48dfb4c1d5",
      "name": "Merge All Opportunities into Single JSON Array",
      "type": "n8n-nodes-base.code",
      "position": [
        736,
        0
      ],
      "parameters": {
        "jsCode": "// All items come as array: items = [{json: {...}}, {json: {...}}, ...]\n\n// Extract only JSON data into a single array\nconst merged = items.map(i => i.json);\n\n// Return ONE item containing the whole array\nreturn [\n  {\n    json: {\n      opportunities: merged\n    }\n  }\n];\n"
      },
      "typeVersion": 2
    },
    {
      "id": "6dc96226-3f51-42fc-9f72-7f8bd2c06dd2",
      "name": "Send Daily Opportunity Summary via Gmail",
      "type": "n8n-nodes-base.gmail",
      "position": [
        1456,
        0
      ],
      "webhookId": "0c82c299-6938-42ed-bda6-5007d79af34f",
      "parameters": {
        "sendTo": "[email protected]",
        "message": "={{ $json.output }}",
        "options": {},
        "subject": "=Daily Opportunity Report – Summary of New Leads"
      },
      "credentials": {
        "gmailOAuth2": {
          "id": "RchiXdmY8WaQhOSJ",
          "name": "Gmail account"
        }
      },
      "typeVersion": 2.1
    },
    {
      "id": "e47b8d9d-6bac-47bd-b0fa-eba96ce7ec52",
      "name": "Generate Daily Opportunity Summary Report",
      "type": "@n8n/n8n-nodes-langchain.agent",
      "position": [
        992,
        0
      ],
      "parameters": {
        "text": "=You are given an array of opportunities in JSON format below.\n\nUse ONLY this data. \nDo NOT guess, invent, fill missing fields, or add any extra information.\nIf a field is null, replace it with \"-\".\n\nInput JSON:\n{{ JSON.stringify($json[\"opportunities\"], null, 2) }}\n\nYour task:\nGenerate a clean HTML report summarizing these opportunities.\n\nSTRICT RULES:\n- Output ONLY VALID HTML (no markdown, no backticks).\n- Use a simple, clean, Gmail-friendly table.\n- Include ALL rows exactly as they appear.\n- Column order must be:\n  Name, Company, Email, Phone, Source, Pipeline ID, Stage ID, Value, Created At\n\nHTML requirements:\n- <h2>Daily Opportunity Summary</h2>\n- A small description paragraph\n- A bordered table with header background (#f5f5f5)\n- Padding 8px in cells\n- Full width table\n- Use “-” for null values\n",
        "options": {
          "systemMessage": "=You must not invent or guess data. All values must come only from the user-provided JSON. Output only valid HTML with no markdown or code blocks.\n"
        },
        "promptType": "define"
      },
      "typeVersion": 2.1
    },
    {
      "id": "ba5645ff-e058-44f1-8c0a-120ba75c5441",
      "name": "Sticky Note",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -1536,
        -704
      ],
      "parameters": {
        "width": 656,
        "height": 496,
        "content": "## 📊 AI-Powered HighLevel → Google Sheets Sync & Daily HTML Report Workflow (GPT-4o + Gmail)  \nThis workflow syncs CRM opportunities from HighLevel into Google Sheets and automatically creates a daily HTML summary email using GPT-4o.\n\n### 🔹 Workflow Overview\n1️⃣ Fetch opportunities from HighLevel CRM  \n2️⃣ Validate records for completeness  \n3️⃣ Extract & normalize key fields  \n4️⃣ Update opportunities in Google Sheets  \n5️⃣ Merge all data into a single array  \n6️⃣ Use GPT-4o to create an HTML sales summary table  \n7️⃣ Email the final report to the sales team  \n\n### 🔹 Tools & Integrations\n- HighLevel CRM → Opportunity data source  \n- Google Sheets → Data storage  \n- Azure OpenAI (GPT-4o) → AI report generator  \n- Gmail → Automated daily summary delivery  \n- JavaScript Nodes → Data cleaning and transformation  \n"
      },
      "typeVersion": 1
    },
    {
      "id": "c5053949-91d9-499b-b9fa-09c4ad533c0c",
      "name": "Sticky Note1",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -704,
        -304
      ],
      "parameters": {
        "color": 7,
        "height": 544,
        "content": "## 🔗 Fetch Opportunities from HighLevel CRM  \nPulls recent opportunity data (limit: 5 by default) from your HighLevel CRM account.  \nIncludes contact details, company, source, and stage info.  \nThis is the main data entry point.\n"
      },
      "typeVersion": 1
    },
    {
      "id": "023f1cfd-864e-4038-b968-2923840da53c",
      "name": "Sticky Note2",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -384,
        -272
      ],
      "parameters": {
        "color": 7,
        "height": 480,
        "content": "## 🧩 Validate Opportunity Data Payload  \nChecks whether each opportunity record has a valid “id” field.  \n✅ Valid → moves to extraction  \n❌ Invalid → logged into Google Sheets for review.\n"
      },
      "typeVersion": 1
    },
    {
      "id": "8a4b16a5-cb5e-40af-9641-b8f3f6633927",
      "name": "Sticky Note3",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -32,
        304
      ],
      "parameters": {
        "color": 7,
        "height": 432,
        "content": "## ⚠️ Log Invalid Opportunities  \nStores invalid or incomplete opportunities in a dedicated Google Sheet.  \nUseful for cleaning CRM data and tracking integration issues.\n"
      },
      "typeVersion": 1
    },
    {
      "id": "1b3035c5-1c4f-4bee-8466-e22e20ea2a6b",
      "name": "Sticky Note4",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -16,
        -384
      ],
      "parameters": {
        "color": 7,
        "height": 576,
        "content": "## 🧾 Extract Key Fields from HighLevel Data  \nExtracts only the essential details from CRM opportunities, including:  \n- id, name, company, email, phone  \n- source, assignedTo, pipelineId, stageId  \n- tags, monetary value, timestamps  \nSimplifies data for consistency before sheet update.\n"
      },
      "typeVersion": 1
    },
    {
      "id": "b73f368f-a4fa-470b-8b0a-9b765db17460",
      "name": "Sticky Note5",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        304,
        -256
      ],
      "parameters": {
        "color": 7,
        "height": 416,
        "content": "## ⚙️ Normalize Opportunity Structure  \nEnsures every opportunity has standardized field names.  \nFills missing contact info, adjusts IDs, and prepares for sheet update and reporting.\n"
      },
      "typeVersion": 1
    },
    {
      "id": "effc05a4-472f-4621-adc2-d32c9f43548a",
      "name": "Sticky Note6",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        656,
        -240
      ],
      "parameters": {
        "color": 7,
        "height": 416,
        "content": "## 🧠 Merge All Opportunities  \nCombines all normalized records into a single JSON array under the field `opportunities`.  \nPrepares data for AI processing and HTML report generation.\n"
      },
      "typeVersion": 1
    },
    {
      "id": "6d55cf17-fb56-488c-9fc6-09a5bcea2ec9",
      "name": "Sticky Note7",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        672,
        -976
      ],
      "parameters": {
        "color": 7,
        "width": 256,
        "height": 544,
        "content": "## 📋 Update Opportunity Records in Google Sheets  \nUpserts (append or update) opportunity data into the “ghl database” tab inside your master sheet (`sample_leads_50`).  \nMatching key: `id`.  \nKeeps CRM and sheet data fully synced.\n"
      },
      "typeVersion": 1
    },
    {
      "id": "a29730a2-550a-4292-8662-0b61eab146ed",
      "name": "Sticky Note8",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        944,
        -368
      ],
      "parameters": {
        "color": 7,
        "width": 352,
        "height": 576,
        "content": "## 🧠 Generate Daily Opportunity Summary Report  \nGPT-4o analyzes the merged opportunities JSON and generates a clean HTML report.  \nStructure:\n- <h2> title: “Daily Opportunity Summary”  \n- Description paragraph  \n- Styled table with:  \n  Name, Company, Email, Phone, Source, Pipeline ID, Stage ID, Value, Created At  \n- Nulls replaced with “-”  \n- Gmail-friendly design (bordered table, padded cells)\n"
      },
      "typeVersion": 1
    },
    {
      "id": "6447a496-aa1c-4c7b-8dc6-6a0a392b744c",
      "name": "Sticky Note9",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        1376,
        -320
      ],
      "parameters": {
        "color": 7,
        "height": 496,
        "content": "## 📧 Send Daily Opportunity Summary via Gmail  \nSends the AI-generated HTML report to the sales inbox.  \nSubject: “Daily Opportunity Report – Summary of New Leads.”  \nAutomatically formatted for Gmail rendering.\n"
      },
      "typeVersion": 1
    },
    {
      "id": "93ef3968-0b83-48d9-b108-d14ba2997201",
      "name": "Sticky Note10",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        912,
        288
      ],
      "parameters": {
        "color": 7,
        "height": 368,
        "content": "## ⚙️ Configure GPT-4o Model  \nConnects to the Azure OpenAI GPT-4o model.  \nUsed as the AI engine for generating HTML reports and summaries.\n"
      },
      "typeVersion": 1
    }
  ],
  "active": false,
  "pinData": {},
  "settings": {
    "executionOrder": "v1"
  },
  "versionId": "34b619f9-f2f5-489d-986e-66c86661fb25",
  "connections": {
    "Configure GPT-4o Model": {
      "ai_languageModel": [
        [
          {
            "node": "Generate Daily Opportunity Summary Report",
            "type": "ai_languageModel",
            "index": 0
          }
        ]
      ]
    },
    "Normalize Opportunity Structure": {
      "main": [
        [
          {
            "node": "Update Opportunity Records in Google Sheets",
            "type": "main",
            "index": 0
          },
          {
            "node": "Merge All Opportunities into Single JSON Array",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Validate Opportunity Data Payload": {
      "main": [
        [
          {
            "node": "Extract Key Fields from HighLevel Data",
            "type": "main",
            "index": 0
          }
        ],
        [
          {
            "node": "Log Invalid Opportunities to Google Sheets",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "When clicking ‘Execute workflow’": {
      "main": [
        [
          {
            "node": "Fetch Opportunities from HighLevel CRM",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Extract Key Fields from HighLevel Data": {
      "main": [
        [
          {
            "node": "Normalize Opportunity Structure",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Fetch Opportunities from HighLevel CRM": {
      "main": [
        [
          {
            "node": "Validate Opportunity Data Payload",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Generate Daily Opportunity Summary Report": {
      "main": [
        [
          {
            "node": "Send Daily Opportunity Summary via Gmail",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Merge All Opportunities into Single JSON Array": {
      "main": [
        [
          {
            "node": "Generate Daily Opportunity Summary Report",
            "type": "main",
            "index": 0
          }
        ]
      ]
    }
  }
}