{
"id": "Lwvu2jjMU2irTyAY",
"meta": {
"instanceId": "fb924c73af8f703905bc09c9ee8076f48c17b596ed05b18c0ff86915ef8a7c4a"
},
"name": "Summarize Google Sheets form feedback via OpenAI's GPT-4",
"tags": [
{
"id": "y9tvM3hISJKT2jeo",
"name": "Ted's Tech Talks",
"createdAt": "2023-08-15T22:12:34.260Z",
"updatedAt": "2023-08-15T22:12:34.260Z"
}
],
"nodes": [
{
"id": "cd80cd2f-a6e1-48eb-ba05-0f8f1a0875e5",
"name": "When clicking \"Test workflow\"",
"type": "n8n-nodes-base.manualTrigger",
"position": [
680,
320
],
"parameters": {},
"typeVersion": 1
},
{
"id": "9f03f1c4-c47e-4eda-bc0a-a598c21e4616",
"name": "Sticky Note",
"type": "n8n-nodes-base.stickyNote",
"position": [
640,
130
],
"parameters": {
"width": 369.1031874662338,
"height": 349,
"content": "### 1. Create a Google Sheet document\n* This tutorial uses Google Sheet document connected to Google Forms, but a standalone Sheet document will work too\n* Adapt initial trigger to your needs: run manually or at some time intervals\n\n[Link to the Google Sheets template](https://docs.google.com/spreadsheets/d/1Kcr1oF_RrfNQJczmJDpwClOSYpvSnwbeX-_pdUo91-I/edit?usp=sharing)"
},
"typeVersion": 1
},
{
"id": "1e478f81-76e7-4fc3-a147-11a92d3f9998",
"name": "Sticky Note1",
"type": "n8n-nodes-base.stickyNote",
"position": [
1040,
160
],
"parameters": {
"width": 394,
"height": 319,
"content": "### 2. Combine all answers into an array\n* Since the main goal is to provide an overall summary, we need to combine all answers for each Google Form question\n* Aggregate Node takes multiple incoming items and produces just a single item which contains arrays of user feedback"
},
"typeVersion": 1
},
{
"id": "1ab06b51-3b9e-4a4c-afba-c98e529a636c",
"name": "Sticky Note2",
"type": "n8n-nodes-base.stickyNote",
"position": [
1480,
160
],
"parameters": {
"width": 432,
"height": 319,
"content": "### 3. Generate a summary report\n* Enter a __system message__ with a overall instructions on how to analyze the feedback form\n* Provide a __user message__ with JSON arrays.\n\n__NB! Consider splitting the form questions for a very long forms or when the number of responses is too high__"
},
"typeVersion": 1
},
{
"id": "ce0118a3-4eaf-4d60-adf0-5bde5d41328a",
"name": "Sticky Note3",
"type": "n8n-nodes-base.stickyNote",
"position": [
1940,
160
],
"parameters": {
"width": 359.1031874662346,
"height": 319,
"content": "### 4. Convert to HTML and send an email\n* GPT is configured to reply in Markdown format. Markdown Node converts such text into HTML\n* Finally, the Gmail node sends a message with HTML report"
},
"typeVersion": 1
},
{
"id": "37bc8ab5-328c-4f50-bbda-f7482bf36522",
"name": "Get Google Sheets records",
"type": "n8n-nodes-base.googleSheets",
"position": [
860,
320
],
"parameters": {
"options": {},
"sheetName": {
"__rl": true,
"mode": "list",
"value": 2035968519,
"cachedResultUrl": "https://docs.google.com/spreadsheets/d/1Kcr1oF_RrfNQJczmJDpwClOSYpvSnwbeX-_pdUo91-I/edit#gid=2035968519",
"cachedResultName": "Form Responses 1"
},
"documentId": {
"__rl": true,
"mode": "list",
"value": "1Kcr1oF_RrfNQJczmJDpwClOSYpvSnwbeX-_pdUo91-I",
"cachedResultUrl": "https://docs.google.com/spreadsheets/d/1Kcr1oF_RrfNQJczmJDpwClOSYpvSnwbeX-_pdUo91-I/edit?usp=drivesdk",
"cachedResultName": "Event feedback form (Responses)"
}
},
"credentials": {
"googleSheetsOAuth2Api": {
"id": "RtRiRezoxiWkzZQt",
"name": "Ted's Tech Talks Google account"
}
},
"typeVersion": 4.2
},
{
"id": "d75b11b1-2cce-40c2-ab5a-d18fdf7f5283",
"name": "Aggregate responses into arrays",
"type": "n8n-nodes-base.aggregate",
"position": [
1200,
320
],
"parameters": {
"options": {},
"fieldsToAggregate": {
"fieldToAggregate": [
{
"fieldToAggregate": "['What went great?']"
},
{
"fieldToAggregate": "['How can we improve?']"
},
{
"fieldToAggregate": "['What is the chance of recommending our event?']"
}
]
}
},
"typeVersion": 1
},
{
"id": "a90f83fe-809b-42db-b65d-43fb11b2979a",
"name": "Summarize via GPT model",
"type": "n8n-nodes-base.openAi",
"position": [
1620,
320
],
"parameters": {
"prompt": {
"messages": [
{
"role": "system",
"content": "Your task is to summarize event feedback form responses. You will receive answers on three questions:\n1. What went great?\n2. How can we improve?\n3. What is the chance of recommending our event?\n\nEach questions has several answers separated by | character.\nAnalyze each question and prepare a summary report. It should contain an overall sentiment regarding the event, followed by the constructive ideas of what to improve.\n\nReply in Markdown formatting"
},
{
"content": "=1. What went great: ```{{ $json['What went great?'].join(' | ') }}```\n2. How can we improve: ```{{ $json['How can we improve?'].join(' | ') }}```\n3. What is the chance of recommending our event: ```{{ $json['What is the chance of recommending our event?'].join(' | ') }}```"
}
]
},
"options": {
"temperature": 0.3
},
"resource": "chat",
"chatModel": "gpt-4-turbo-preview"
},
"credentials": {
"openAiApi": {
"id": "rveqdSfp7pCRON1T",
"name": "Ted's Tech Talks OpenAi"
}
},
"typeVersion": 1.1
},
{
"id": "2c8d4e46-9d3e-4655-952b-37d04f673914",
"name": "Convet from Markdown to HTML",
"type": "n8n-nodes-base.markdown",
"position": [
1980,
320
],
"parameters": {
"mode": "markdownToHtml",
"options": {
"completeHTMLDocument": false
},
"markdown": "={{ $json.message.content }}"
},
"typeVersion": 1
},
{
"id": "a27d8664-dc87-4458-9f12-970b88ab6515",
"name": "Send via Gmail",
"type": "n8n-nodes-base.gmail",
"position": [
2160,
320
],
"parameters": {
"sendTo": "teds.tech.talks@gmail.com",
"message": "={{ $json.data }}",
"options": {
"appendAttribution": false
},
"subject": "Feedback form response"
},
"credentials": {
"gmailOAuth2": {
"id": "UllrXlZsDnkdA3tT",
"name": "Gmail account"
}
},
"typeVersion": 2.1
}
],
"active": false,
"pinData": {},
"settings": {
"executionOrder": "v1"
},
"versionId": "756cdd85-49dd-4f0f-acc7-58f834a3512f",
"connections": {
"Summarize via GPT model": {
"main": [
[
{
"node": "Convet from Markdown to HTML",
"type": "main",
"index": 0
}
]
]
},
"Get Google Sheets records": {
"main": [
[
{
"node": "Aggregate responses into arrays",
"type": "main",
"index": 0
}
]
]
},
"Convet from Markdown to HTML": {
"main": [
[
{
"node": "Send via Gmail",
"type": "main",
"index": 0
}
]
]
},
"When clicking \"Test workflow\"": {
"main": [
[
{
"node": "Get Google Sheets records",
"type": "main",
"index": 0
}
]
]
},
"Aggregate responses into arrays": {
"main": [
[
{
"node": "Summarize via GPT model",
"type": "main",
"index": 0
}
]
]
}
}
}