Process UX Interview Transcripts and Generate Summaries with OpenAI to Google Sheets

{
  "id": "e5SP66m2pNYbV2cg",
  "meta": {
    "instanceId": "eb6cdf70dd526a1c97d7dcb6b9292757001af53236f7ca64a2582483540c6ee4",
    "templateCredsSetupCompleted": true
  },
  "name": "Process UX Interview Transcripts and Generate Summaries with OpenAI to Google Sheets",
  "tags": [],
  "nodes": [
    {
      "id": "c42709ac-6960-4e3b-ba1c-573b491a9ecf",
      "name": "When clicking ‘Execute workflow’",
      "type": "n8n-nodes-base.manualTrigger",
      "position": [
        -1424,
        -368
      ],
      "parameters": {},
      "typeVersion": 1
    },
    {
      "id": "f49de87d-c098-4bed-8b24-993159b1d8ca",
      "name": "Structured Output Parser",
      "type": "@n8n/n8n-nodes-langchain.outputParserStructured",
      "position": [
        16,
        -160
      ],
      "parameters": {
        "jsonSchemaExample": "[\n  {\n    \"Persona\": \"Person 1\",\n    \"User needs\": \"Wants an intuitive mobile experience\",\n    \"Pain points\": \"Finds sign-up process too long\",\n    \"New feature request\": \"Wants biometric login\"\n  },\n  {\n    \"Persona\": \"Person 2\",\n    \"User needs\": \"Needs better calendar integration\",\n    \"Pain points\": \"Misses reminders frequently\",\n    \"New feature request\": \"Requests smart reminders with email sync\"\n  }\n]"
      },
      "typeVersion": 1.3
    },
    {
      "id": "61d2c83e-ff72-49ea-b84d-56b6bc509c44",
      "name": "Search Google Drive for interview files",
      "type": "n8n-nodes-base.googleDrive",
      "position": [
        -1184,
        -368
      ],
      "parameters": {
        "filter": {
          "folderId": {
            "__rl": true,
            "mode": "list",
            "value": "1_HlRPRZeTx48RE95HYTpaW3YVm-Tk1EG",
            "cachedResultUrl": "https://drive.google.com/drive/folders/1_HlRPRZeTx48RE95HYTpaW3YVm-Tk1EG",
            "cachedResultName": "User testing result"
          },
          "whatToSearch": "files"
        },
        "options": {},
        "resource": "fileFolder"
      },
      "credentials": {
        "googleDriveOAuth2Api": {
          "id": "1gZqLrXY9yCNTtEG",
          "name": "Google Drive account"
        }
      },
      "typeVersion": 3
    },
    {
      "id": "1173f8ad-9220-45ce-a308-a823b3c723ec",
      "name": "Filter by .mp3",
      "type": "n8n-nodes-base.filter",
      "position": [
        -944,
        -368
      ],
      "parameters": {
        "options": {
          "ignoreCase": true
        },
        "conditions": {
          "options": {
            "version": 2,
            "leftValue": "",
            "caseSensitive": false,
            "typeValidation": "loose"
          },
          "combinator": "and",
          "conditions": [
            {
              "id": "420e1a9c-2145-4845-b4b0-31a82855a78c",
              "operator": {
                "type": "string",
                "operation": "endsWith"
              },
              "leftValue": "={{ $json.name }}",
              "rightValue": ".mp3"
            }
          ]
        },
        "looseTypeValidation": true
      },
      "typeVersion": 2.2
    },
    {
      "id": "2a966bdb-7469-461c-9d13-a8d94bfcdca5",
      "name": "Download audio file",
      "type": "n8n-nodes-base.googleDrive",
      "position": [
        -688,
        -368
      ],
      "parameters": {
        "fileId": {
          "__rl": true,
          "mode": "id",
          "value": "={{ $json.id }}"
        },
        "options": {},
        "operation": "download"
      },
      "credentials": {
        "googleDriveOAuth2Api": {
          "id": "1gZqLrXY9yCNTtEG",
          "name": "Google Drive account"
        }
      },
      "typeVersion": 3
    },
    {
      "id": "d264aea8-4dc1-4c0f-b2ad-7e2226cab42c",
      "name": "Transcribe a recording",
      "type": "@n8n/n8n-nodes-langchain.openAi",
      "position": [
        -432,
        -368
      ],
      "parameters": {
        "options": {},
        "resource": "audio",
        "operation": "transcribe"
      },
      "credentials": {
        "openAiApi": {
          "id": "s9Zb7s8XMwxB2DpE",
          "name": "OpenAi account"
        }
      },
      "typeVersion": 1.8
    },
    {
      "id": "0b7f1399-f98b-4742-880d-913d560017c6",
      "name": "AI Agent for creating transcript",
      "type": "@n8n/n8n-nodes-langchain.agent",
      "position": [
        -176,
        -368
      ],
      "parameters": {
        "text": "=You are an expert UX researcher assistant.\n\nI will provide you with a transcript from a user interview. Analyze the transcript and return a summary for each person as a JSON array. Each object must have these keys:\n- \"Persona\"\n- \"User needs\"\n- \"Pain points\"\n- \"New feature request\"\n\nFollow this exact format:\n[\n  {\n    \"Persona\": \"Person 1\",\n    \"User needs\": \"Describe the main needs here\",\n    \"Pain points\": \"Describe main pain points here\",\n    \"New feature request\": \"Describe new feature requests here\"\n  },\n  {\n    \"Persona\": \"Person 2\",\n    \"User needs\": \" ... \",\n    \"Pain points\": \" ... \",\n    \"New feature request\": \" ... \"\n  }\n]\n\nRules:\n- Only return valid JSON.\n- No extra text, explanations, or comments.\n- Use concise summaries.\n\nHere is the transcript:\n{{ $json.text }}",
        "options": {},
        "promptType": "define",
        "hasOutputParser": true
      },
      "typeVersion": 2.1,
      "alwaysOutputData": false
    },
    {
      "id": "eef35c0f-f319-4c97-880f-549f4e02dd51",
      "name": "OpenAI Chat Model",
      "type": "@n8n/n8n-nodes-langchain.lmChatOpenAi",
      "position": [
        -176,
        -160
      ],
      "parameters": {
        "model": {
          "__rl": true,
          "mode": "list",
          "value": "gpt-4.1-mini"
        },
        "options": {}
      },
      "credentials": {
        "openAiApi": {
          "id": "s9Zb7s8XMwxB2DpE",
          "name": "OpenAi account"
        }
      },
      "typeVersion": 1.2
    },
    {
      "id": "59b334cc-3d90-451d-a48e-0ce144daecef",
      "name": "Split Out results",
      "type": "n8n-nodes-base.splitOut",
      "position": [
        256,
        -368
      ],
      "parameters": {
        "options": {},
        "fieldToSplitOut": "output"
      },
      "typeVersion": 1
    },
    {
      "id": "23effa45-8a46-4876-8d32-98b07b6cedf2",
      "name": "Insert results to Google Sheets",
      "type": "n8n-nodes-base.googleSheets",
      "position": [
        528,
        -368
      ],
      "parameters": {
        "columns": {
          "value": {
            "Persona": "={{ $json.Persona }}",
            "User need": "={{ $json[\"User needs\"] }}",
            "Pain points": "={{ $json[\"Pain points\"] }}",
            "New feature request": "={{ $json[\"New feature request\"] }}"
          },
          "schema": [
            {
              "id": "Persona",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "Persona",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "User need",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "User need",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "Pain points",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "Pain points",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "New feature request",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "New feature request",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            }
          ],
          "mappingMode": "defineBelow",
          "matchingColumns": [],
          "attemptToConvertTypes": false,
          "convertFieldsToString": false
        },
        "options": {},
        "operation": "append",
        "sheetName": {
          "__rl": true,
          "mode": "name",
          "value": "Summary"
        },
        "documentId": {
          "__rl": true,
          "mode": "list",
          "value": "13tkgJh2tYslsLLKeAI_i-xFLWcFO5UzINB-g6TszzIE",
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/13tkgJh2tYslsLLKeAI_i-xFLWcFO5UzINB-g6TszzIE/edit?usp=drivesdk",
          "cachedResultName": "User transcript v"
        }
      },
      "credentials": {
        "googleSheetsOAuth2Api": {
          "id": "tfmuynHdek0Dkf2l",
          "name": "Google Sheets account"
        }
      },
      "typeVersion": 4.6
    },
    {
      "id": "d25d0972-0afe-4380-bf27-c6906de17233",
      "name": "Sticky Note1",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -2240,
        -624
      ],
      "parameters": {
        "color": 5,
        "width": 520,
        "height": 808,
        "content": "***UX Interview Analysis with OpenAI: Transcipt, Summarize, and Export to Google Sheets!***\n\n## About\nEasily analyze and summarize UX interviews. Just upload your files to Google Drive and get the insights directly in Google Sheets.\n\n## How It Works\n- The workflow is triggered manually with a click\n- Upload the interview recordings in MP3 format to Google Drive (or modify the node “Filter by MP3” to support other formats)\n- OpenAI transcribes the audio\n- An AI agent generates a summary and stores the results in Google Sheets\n\n## How To Use\n- Import the package into your n8n interface\n- Set up credentials for each node to access the required tools\n- Upload your interview files to Google Drive\n- Create a Google Sheet with the following columns:\n\t•\tPersona\n\t•\tUser Needs\n\t•\tPain Points\n\t•\tNew Feature Requests\n- Connect the Google Sheets node titled “Insert results to Google Sheets” to your created document\n- Start the workflow\n\n\n## Requirements\n- OpenAI for transcription and summarization (you can replace it with Gemini if preferred)\n\n\n\n"
      },
      "typeVersion": 1
    },
    {
      "id": "82eb2906-9b36-4f38-a5e7-b3a6b3c6a889",
      "name": "Sticky Note",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -1216,
        -464
      ],
      "parameters": {
        "color": 5,
        "width": 408,
        "height": 392,
        "content": "Upload your MP3 files to Google Drive and connect Google Drive to the workflow."
      },
      "typeVersion": 1
    },
    {
      "id": "456e3b63-946c-482e-ae58-f19ff964a13c",
      "name": "Sticky Note2",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -272,
        -464
      ],
      "parameters": {
        "color": 3,
        "width": 456,
        "height": 504,
        "content": "OpenAI for summarization (you can replace it with Gemini, Claude, or any other LLM). Modify the summary requirements if needed."
      },
      "typeVersion": 1
    },
    {
      "id": "31c97015-3c07-4c89-b051-918b81b2eacc",
      "name": "Sticky Note3",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        432,
        -544
      ],
      "parameters": {
        "color": 4,
        "width": 392,
        "height": 584,
        "content": "Connect Google Sheets to this node. Prior create columns as:\n•\tPersona\n\t•\tUser Needs\n\t•\tPain Points\n\t•\tNew Feature Requests"
      },
      "typeVersion": 1
    }
  ],
  "active": false,
  "pinData": {},
  "settings": {
    "executionOrder": "v1"
  },
  "versionId": "10a06953-0799-48d2-9864-6b94f403be48",
  "connections": {
    "Filter by .mp3": {
      "main": [
        [
          {
            "node": "Download audio file",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "OpenAI Chat Model": {
      "ai_languageModel": [
        [
          {
            "node": "AI Agent for creating transcript",
            "type": "ai_languageModel",
            "index": 0
          }
        ]
      ]
    },
    "Split Out results": {
      "main": [
        [
          {
            "node": "Insert results to Google Sheets",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Download audio file": {
      "main": [
        [
          {
            "node": "Transcribe a recording",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Transcribe a recording": {
      "main": [
        [
          {
            "node": "AI Agent for creating transcript",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Structured Output Parser": {
      "ai_outputParser": [
        [
          {
            "node": "AI Agent for creating transcript",
            "type": "ai_outputParser",
            "index": 0
          }
        ]
      ]
    },
    "AI Agent for creating transcript": {
      "main": [
        [
          {
            "node": "Split Out results",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "When clicking ‘Execute workflow’": {
      "main": [
        [
          {
            "node": "Search Google Drive for interview files",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Search Google Drive for interview files": {
      "main": [
        [
          {
            "node": "Filter by .mp3",
            "type": "main",
            "index": 0
          }
        ]
      ]
    }
  }
}