使用 Google Gemini 自动提取 Jotform 回复的主题和情感

• •Google Sheets API 凭证
• •Google Gemini API Key

{
  "id": "s9tAZpHcsRFJjK3A",
  "meta": {
    "instanceId": "885b4fb4a6a9c2cb5621429a7b972df0d05bb724c20ac7dac7171b62f1c7ef40",
    "templateCredsSetupCompleted": true
  },
  "name": "使用 Google Gemini 自动提取 Jotform 回复的主题和情感",
  "tags": [
    {
      "id": "ddPkw7Hg5dZhQu2w",
      "name": "AI",
      "createdAt": "2025-04-13T05:38:08.053Z",
      "updatedAt": "2025-04-13T05:38:08.053Z"
    },
    {
      "id": "ZOwtAMLepQaGW76t",
      "name": "Building Blocks",
      "createdAt": "2025-04-13T15:23:40.462Z",
      "updatedAt": "2025-04-13T15:23:40.462Z"
    },
    {
      "id": "Kujft2FOjmOVQAmJ",
      "name": "Engineering",
      "createdAt": "2025-04-09T01:31:00.558Z",
      "updatedAt": "2025-04-09T01:31:00.558Z"
    }
  ],
  "nodes": [
    {
      "id": "36ae4c6f-049d-475c-af85-9cca6affc769",
      "name": "Google Gemini聊天模型",
      "type": "@n8n/n8n-nodes-langchain.lmChatGoogleGemini",
      "position": [
        -32,
        320
      ],
      "parameters": {
        "options": {},
        "modelName": "models/gemini-2.0-flash-exp"
      },
      "credentials": {
        "googlePalmApi": {
          "id": "YeO7dHZnuGBVQKVZ",
          "name": "Google Gemini(PaLM) Api account"
        }
      },
      "typeVersion": 1
    },
    {
      "id": "8c3ecf36-96e7-4dfc-9ec2-0cf2d0425139",
      "name": "情感分析器",
      "type": "@n8n/n8n-nodes-langchain.chainLlm",
      "position": [
        0,
        104
      ],
      "parameters": {
        "text": "=Perform sentiment analysis of the following {{ $json.body }} \n",
        "batching": {},
        "messages": {
          "messageValues": [
            {
              "message": "You are an expert sentiment analyzer"
            }
          ]
        },
        "promptType": "define",
        "hasOutputParser": true
      },
      "typeVersion": 1.7
    },
    {
      "id": "8fdec511-d900-41a4-bf79-295a620f0b4e",
      "name": "结构化输出解析器",
      "type": "@n8n/n8n-nodes-langchain.outputParserStructured",
      "position": [
        208,
        320
      ],
      "parameters": {
        "schemaType": "manual",
        "inputSchema": "\n{\n  \"$schema\": \"http://json-schema.org/draft-07/schema#\",\n  \"title\": \"Feedback Sentiment Output Schema\",\n  \"description\": \"Schema for parsing AI sentiment analysis results from customer feedback submissions.\",\n  \"type\": \"object\",\n  \"properties\": {\n    \"customer_name\": {\n      \"type\": \"string\",\n      \"description\": \"Name of the customer submitting feedback.\"\n    },\n    \"customer_email\": {\n      \"type\": \"string\",\n      \"description\": \"Email of the customer submitting feedback.\"\n    },\n    \"feedback_text\": {\n      \"type\": \"string\",\n      \"description\": \"Raw feedback text provided by the customer.\"\n    },\n    \"sentiment\": {\n      \"type\": \"string\",\n      \"enum\": [\"positive\", \"neutral\", \"negative\"],\n      \"description\": \"Predicted sentiment label of the feedback.\"\n    },\n    \"confidence_score\": {\n      \"type\": \"number\",\n      \"minimum\": 0,\n      \"maximum\": 1,\n      \"description\": \"Model confidence score for the sentiment classification (0-1).\"\n    },\n    \"key_phrases\": {\n      \"type\": \"array\",\n      \"items\": { \"type\": \"string\" },\n      \"description\": \"Important keywords or topics extracted from the feedback.\"\n    },\n    \"summary\": {\n      \"type\": \"string\",\n      \"description\": \"A short AI-generated summary of the feedback.\"\n    },\n    \"alert_priority\": {\n      \"type\": \"string\",\n      \"enum\": [\"high\", \"medium\", \"low\"],\n      \"description\": \"Priority level for team alerting based on sentiment and urgency.\"\n    },\n    \"timestamp\": {\n      \"type\": \"string\",\n      \"format\": \"date-time\",\n      \"description\": \"Time when the feedback was received.\"\n    }\n  },\n  \"additionalProperties\": false\n}"
      },
      "typeVersion": 1.3
    },
    {
      "id": "ec24ab03-ffdd-412b-a66f-4b3d6bb04edd",
      "name": "在表格中追加或更新行",
      "type": "n8n-nodes-base.googleSheets",
      "position": [
        880,
        -112
      ],
      "parameters": {
        "columns": {
          "value": {
            "topics_keywords": "={{ $json.output[0].toJsonString() }}",
            "feedback_analysis": "={{ $json.output.toJsonString() }}"
          },
          "schema": [
            {
              "id": "feedback_analysis",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "feedback_analysis",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "topics_keywords",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "topics_keywords",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            }
          ],
          "mappingMode": "defineBelow",
          "matchingColumns": [
            "feedback_analysis"
          ],
          "attemptToConvertTypes": false,
          "convertFieldsToString": false
        },
        "options": {},
        "operation": "appendOrUpdate",
        "sheetName": {
          "__rl": true,
          "mode": "list",
          "value": "gid=0",
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/13iJ2sOSaCEekzRNrhkUirZX2llBnpwGi3gdhI4PWIFM/edit#gid=0",
          "cachedResultName": "Sheet1"
        },
        "documentId": {
          "__rl": true,
          "mode": "list",
          "value": "13iJ2sOSaCEekzRNrhkUirZX2llBnpwGi3gdhI4PWIFM",
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/13iJ2sOSaCEekzRNrhkUirZX2llBnpwGi3gdhI4PWIFM/edit?usp=drivesdk",
          "cachedResultName": "Jotform Feedback"
        }
      },
      "credentials": {
        "googleSheetsOAuth2Api": {
          "id": "Zjoxh2BUZ6VXGQhA",
          "name": "Google Sheets account"
        }
      },
      "typeVersion": 4.7
    },
    {
      "id": "b3dbc129-bd64-420b-969b-9bec6a11f4d1",
      "name": "便签",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -608,
        -416
      ],
      "parameters": {
        "color": 7,
        "width": 352,
        "height": 272,
        "content": "![Logo](https://www.jotform.com/resources/assets/logo-nb/min/jotform-logo-white-400x200.png)"
      },
      "typeVersion": 1
    },
    {
      "id": "83b6913b-ff18-46c0-ba3e-b2c0d5fddedf",
      "name": "格式化表单数据",
      "type": "n8n-nodes-base.code",
      "position": [
        -352,
        -96
      ],
      "parameters": {
        "jsCode": "const outputString = JSON.stringify($input.first().json, null, 2);\nreturn [\n    {\n      json: {\n        body: outputString\n      }\n    }\n  ];"
      },
      "typeVersion": 2
    },
    {
      "id": "78ffe47a-7836-45ed-a3b1-73f9ccff41d6",
      "name": "主题与关键词",
      "type": "@n8n/n8n-nodes-langchain.chainLlm",
      "position": [
        0,
        -400
      ],
      "parameters": {
        "text": "=Perform sentiment analysis of the following {{ $json.body }} \n",
        "batching": {},
        "messages": {
          "messageValues": [
            {
              "message": "You are an expert sentiment analyzer"
            }
          ]
        },
        "promptType": "define",
        "hasOutputParser": true
      },
      "typeVersion": 1.7
    },
    {
      "id": "41b0cfcb-fa81-4c52-96a7-e6581bb057be",
      "name": "用于主题和关键词的 Google Gemini 聊天模型",
      "type": "@n8n/n8n-nodes-langchain.lmChatGoogleGemini",
      "position": [
        -16,
        -176
      ],
      "parameters": {
        "options": {},
        "modelName": "models/gemini-2.0-flash-exp"
      },
      "credentials": {
        "googlePalmApi": {
          "id": "YeO7dHZnuGBVQKVZ",
          "name": "Google Gemini(PaLM) Api account"
        }
      },
      "typeVersion": 1
    },
    {
      "id": "5f5c238d-9705-4017-bdb8-0f6b1adbba15",
      "name": "主题与关键词的结构化输出解析器",
      "type": "@n8n/n8n-nodes-langchain.outputParserStructured",
      "position": [
        176,
        -176
      ],
      "parameters": {
        "schemaType": "manual",
        "inputSchema": "{\n  \"$schema\": \"https://json-schema.org/draft/2020-12/schema\",\n  \"title\": \"SurveyTopicKeywordExtraction\",\n  \"description\": \"Schema for extracting topics, keywords, and sentiment insights from Jotform survey responses.\",\n  \"type\": \"object\",\n  \"properties\": {\n    \"topics\": {\n      \"type\": \"array\",\n      \"description\": \"High-level themes or categories extracted from the survey answers.\",\n      \"items\": {\n        \"type\": \"object\",\n        \"properties\": {\n          \"topic\": {\n            \"type\": \"string\",\n            \"description\": \"Descriptive name of the topic, e.g., 'Customer Support Experience'.\"\n          },\n          \"summary\": {\n            \"type\": \"string\",\n            \"description\": \"Short summary describing this topic based on the responses.\"\n          },\n          \"keywords\": {\n            \"type\": \"array\",\n            \"description\": \"List of key terms or phrases related to this topic.\",\n            \"items\": {\n              \"type\": \"string\"\n            }\n          },\n          \"sentiment\": {\n            \"type\": \"string\",\n            \"enum\": [\"positive\", \"negative\", \"neutral\", \"mixed\"],\n            \"description\": \"Overall sentiment associated with this topic.\"\n          },\n          \"importance_score\": {\n            \"type\": \"number\",\n            \"minimum\": 0,\n            \"maximum\": 1,\n            \"description\": \"Relative importance or weight of this topic (0-1 scale).\"\n          }\n        },\n        \"required\": [\"topic\", \"keywords\"]\n      }\n    },\n    \"global_keywords\": {\n      \"type\": \"array\",\n      \"description\": \"Top-level keywords or phrases representing the overall survey themes.\",\n      \"items\": {\n        \"type\": \"string\"\n      }\n    },\n    \"insights\": {\n      \"type\": \"array\",\n      \"description\": \"Key takeaways or AI-generated insights derived from the data mining.\",\n      \"items\": {\n        \"type\": \"string\"\n      }\n    },\n    \"generated_at\": {\n      \"type\": \"string\",\n      \"format\": \"date-time\",\n      \"description\": \"Timestamp of when this analysis was generated.\"\n    }\n  }\n}\n"
      },
      "typeVersion": 1.3
    },
    {
      "id": "041a9363-5f09-46ed-9cd4-4cc17dbe60c8",
      "name": "JotForm 触发器",
      "type": "n8n-nodes-base.jotFormTrigger",
      "position": [
        -576,
        -96
      ],
      "webhookId": "bddebb80-2e71-43e6-941e-4d599f70d0e6",
      "parameters": {
        "form": "252797914459475"
      },
      "credentials": {
        "jotFormApi": {
          "id": "IcptK658rWIj1G45",
          "name": "JotForm account"
        }
      },
      "typeVersion": 1
    },
    {
      "id": "4981c2c9-a760-43d4-ad81-dc60f545bffb",
      "name": "合并",
      "type": "n8n-nodes-base.merge",
      "position": [
        464,
        -112
      ],
      "parameters": {},
      "typeVersion": 3.2
    },
    {
      "id": "e6ec2964-d484-4d8f-9c92-515ad9904cd0",
      "name": "聚合",
      "type": "n8n-nodes-base.aggregate",
      "position": [
        672,
        -112
      ],
      "parameters": {
        "options": {},
        "fieldsToAggregate": {
          "fieldToAggregate": [
            {
              "fieldToAggregate": "output"
            }
          ]
        }
      },
      "typeVersion": 1
    },
    {
      "id": "95125c3d-1fc8-4339-a91c-f4f7527fb2de",
      "name": "便签1",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -144,
        -464
      ],
      "parameters": {
        "color": 4,
        "width": 528,
        "height": 432,
        "content": "## 使用 Google Gemini 进行主题和关键词提取"
      },
      "typeVersion": 1
    },
    {
      "id": "95fc87ef-b095-41d0-9ce1-9a7ef11facc6",
      "name": "便签2",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -144,
        0
      ],
      "parameters": {
        "color": 4,
        "width": 528,
        "height": 464,
        "content": "## 使用 Google Gemini 进行情感分析"
      },
      "typeVersion": 1
    }
  ],
  "active": false,
  "pinData": {},
  "settings": {
    "executionOrder": "v1"
  },
  "versionId": "6f6e2249-0884-47a8-af5e-bcc4bac7a61c",
  "connections": {
    "Merge": {
      "main": [
        [
          {
            "node": "Aggregate",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Aggregate": {
      "main": [
        [
          {
            "node": "Append or update row in sheet",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "JotForm Trigger": {
      "main": [
        [
          {
            "node": "Format the Form Data",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Topics & Keywords": {
      "main": [
        [
          {
            "node": "Merge",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Sentiment Analyzer": {
      "main": [
        [
          {
            "node": "Merge",
            "type": "main",
            "index": 1
          }
        ]
      ]
    },
    "Format the Form Data": {
      "main": [
        [
          {
            "node": "Sentiment Analyzer",
            "type": "main",
            "index": 0
          },
          {
            "node": "Topics & Keywords",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Google Gemini Chat Model": {
      "ai_languageModel": [
        [
          {
            "node": "Sentiment Analyzer",
            "type": "ai_languageModel",
            "index": 0
          }
        ]
      ]
    },
    "Structured Output Parser": {
      "ai_outputParser": [
        [
          {
            "node": "Sentiment Analyzer",
            "type": "ai_outputParser",
            "index": 0
          }
        ]
      ]
    },
    "Append or update row in sheet": {
      "main": [
        []
      ]
    },
    "Structured Output Parser for Topics & Keywords": {
      "ai_outputParser": [
        [
          {
            "node": "Topics & Keywords",
            "type": "ai_outputParser",
            "index": 0
          }
        ]
      ]
    },
    "Google Gemini Chat Model for Topics and Keywords": {
      "ai_languageModel": [
        [
          {
            "node": "Topics & Keywords",
            "type": "ai_languageModel",
            "index": 0
          }
        ]
      ]
    }
  }
}