Summarize Google Sheets form feedback via OpenAI's GPT-4

{"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": "", "name": "[Your googleSheetsOAuth2Api]"}}, "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": "", "name": "[Your openAiApi]"}}, "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": "", "name": "[Your gmailOAuth2]"}}, "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}]]}}}