基于Google Sheets使用OpenAI和Gmail生成个性化潜在客户回复

• •Google 账号和 Gmail API 凭证
• •可能需要目标 API 的认证凭证
• •Google Sheets API 凭证
• •OpenAI API Key

{
  "meta": {
    "instanceId": "0a25db4fd0692af64b58f35e01611f2ef0b5bb9d6654c57ef3741021e7d8cc11",
    "templateId": "7163"
  },
  "nodes": [
    {
      "id": "af621613-d8c3-411e-8f5b-26e08eb27133",
      "name": "获取表格中的行",
      "type": "n8n-nodes-base.googleSheets",
      "position": [
        816,
        1168
      ],
      "parameters": {
        "options": {},
        "sheetName": {
          "__rl": true,
          "mode": "list",
          "value": "{YOUR_SHEET_ID}",
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/{YOUR_GOOGLE_SHEET_ID}/edit#gid=0",
          "cachedResultName": "{YOUR_SHEET_NAME}"
        },
        "documentId": {
          "__rl": true,
          "mode": "list",
          "value": "{YOUR_GOOGLE_DOCUMENT_ID}",
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/{YOUR_GOOGLE_DOCUMENT_ID}/edit?usp=drivesdk",
          "cachedResultName": "n8nEmail"
        }
      },
      "credentials": {
        "googleSheetsOAuth2Api": {
          "id": "cjPjcwa3RdiMAu79",
          "name": "Google Sheets account"
        }
      },
      "typeVersion": 4.6
    },
    {
      "id": "cf8bc124-04fd-4930-9040-6fc6ee6f25c4",
      "name": "当点击\"执行工作流\"时",
      "type": "n8n-nodes-base.manualTrigger",
      "position": [
        592,
        1168
      ],
      "parameters": {},
      "typeVersion": 1
    },
    {
      "id": "613fd1d4-ec8a-40be-9f31-ef64483acf87",
      "name": "Send Personalized emails",
      "type": "n8n-nodes-base.gmail",
      "position": [
        1616,
        1168
      ],
      "webhookId": "{YOUR_WEBHOOK_ID}",
      "parameters": {
        "sendTo": "={{ $('Get row(s) in sheet').item.json['Email ID'] }}",
        "message": "={{ $json.message.content }}",
        "options": {
          "appendAttribution": false
        },
        "subject": "=Re:{{ $('Get row(s) in sheet').item.json.Intent }}"
      },
      "credentials": {
        "gmailOAuth2": {
          "id": "vdI9VLsEmuPzPxxo",
          "name": "Gmail account"
        }
      },
      "typeVersion": 2.1
    },
    {
      "id": "cfb4be86-d24c-4eb5-ab7e-649c851bbadc",
      "name": "向模型发送消息",
      "type": "@n8n/n8n-nodes-langchain.openAi",
      "position": [
        1264,
        1168
      ],
      "parameters": {
        "modelId": {
          "__rl": true,
          "mode": "list",
          "value": "{YOUR_OPENAI_MODEL}",
          "cachedResultName": "{YOUR_OPENAI_MODEL_NAME}"
        },
        "options": {},
        "messages": {
          "values": [
            {
              "content": "=Write a professional and friendly email reply to {{ $('Get row(s) in sheet').item.json['First Name'] }} . Their intent is \"{{ $('Get row(s) in sheet').item.json.Intent }}\". They wrote: \"{{ $('Get row(s) in sheet').item.json['Why They Sent Email'] }}\". Make the response specific to their message and helpful.No need to add the subject line. Generate in HTML formatting.\nThe footer signature should be of the following format\nThanks,\n{{ $json.sendAs[0].displayName }}\n"
            }
          ]
        }
      },
      "credentials": {
        "openAiApi": {
          "id": "Kzhpo6yW8AwY3uMm",
          "name": "OpenAI account"
        }
      },
      "typeVersion": 1.8
    },
    {
      "id": "e2b7d857-9ebb-4bff-914c-c7c8c4c87de2",
      "name": "HTTP 请求",
      "type": "n8n-nodes-base.httpRequest",
      "position": [
        1040,
        1168
      ],
      "parameters": {
        "url": "https://gmail.googleapis.com/gmail/v1/users/me/settings/sendAs",
        "options": {},
        "authentication": "predefinedCredentialType",
        "nodeCredentialType": "gmailOAuth2"
      },
      "credentials": {
        "gmailOAuth2": {
          "id": "vdI9VLsEmuPzPxxo",
          "name": "Gmail account"
        }
      },
      "typeVersion": 4.2,
      "alwaysOutputData": true
    },
    {
      "id": "fd1f0cb8-1711-442d-b6be-2ebff70cee29",
      "name": "便签",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        640,
        848
      ],
      "parameters": {
        "color": 4,
        "width": 276,
        "height": 294,
        "content": "## STEP 1 · Lead Intake (Google Sheets)\nReads rows from your sheet.\n\nFields used:\n• Email ID → recipient\n• First Name / Intent / Why They Sent Email → prompt context\n\nTip: Replace the placeholder Document/Sheet IDs before running."
      },
      "typeVersion": 1
    },
    {
      "id": "8fd52a43-0143-460f-bd93-74ddbf123544",
      "name": "便签1",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        960,
        880
      ],
      "parameters": {
        "color": 5,
        "width": 212,
        "height": 252,
        "content": "## STEP 2 · Sender Identity (Gmail sendAs)\nFetches Gmail sendAs to get your display name for the signature.\n\nOutput used in LLM prompt:\n• sendAs[0].displayName"
      },
      "typeVersion": 1
    },
    {
      "id": "45c4dee3-ff27-4577-a5dc-d2ecb97d9db9",
      "name": "便签2",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        1216,
        912
      ],
      "parameters": {
        "color": 6,
        "width": 392,
        "height": 192,
        "content": "## STEP 3 · Draft Generation (LLM)\nCreates a personalized HTML reply.\n\nPrompt inputs:\n• First Name, Intent, Why They Sent Email\n• Signature uses Gmail displayName\n\nNote: Model should output **HTML** (no subject)."
      },
      "typeVersion": 1
    },
    {
      "id": "3d8c5212-fcf4-4262-aeb7-047a9c6e58e7",
      "name": "便签3",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        1664,
        912
      ],
      "parameters": {
        "color": 7,
        "width": 324,
        "height": 214,
        "content": "## STEP 4 · Send Email (Gmail)\nTo: Email ID from sheet\nSubject: \"Re:\" + Intent\nBody: LLM HTML\n\n⚠️ Set Gmail node **emailType = html** so the formatting renders."
      },
      "typeVersion": 1
    }
  ],
  "pinData": {},
  "connections": {
    "HTTP Request": {
      "main": [
        [
          {
            "node": "Message a model",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Message a model": {
      "main": [
        [
          {
            "node": "Send Personalized emails",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Get row(s) in sheet": {
      "main": [
        [
          {
            "node": "HTTP Request",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "When clicking ‘Execute workflow’": {
      "main": [
        [
          {
            "node": "Get row(s) in sheet",
            "type": "main",
            "index": 0
          }
        ]
      ]
    }
  }
}