転写テキストを使ってOpenAIとGoogle Sheetsでスプリントレビュー要約を生成

• •Google Sheets API認証情報
• •OpenAI API Key

{
  "meta": {
    "instanceId": "51c6a64ef13fda2e6aa96576c25d72519ca4bc44cea9da20af60a642b594d835",
    "templateCredsSetupCompleted": true
  },
  "nodes": [
    {
      "id": "155c6cd6-3c71-4399-9f1a-3f6c52c3a384",
      "name": "スプリントレビュー入力の収集",
      "type": "n8n-nodes-base.formTrigger",
      "position": [
        -320,
        -64
      ],
      "webhookId": "610911dc-0778-4270-a571-9aa90143f00a",
      "parameters": {
        "options": {
          "buttonLabel": "Create Summary"
        },
        "formTitle": "Sprint Review Summary",
        "formFields": {
          "values": [
            {
              "fieldType": "file",
              "fieldLabel": "Transcript file",
              "multipleFiles": false
            },
            {
              "fieldLabel": "Sprint name"
            },
            {
              "fieldType": "dropdown",
              "fieldLabel": "Domain",
              "fieldOptions": {
                "values": [
                  {
                    "option": "Team 1"
                  },
                  {
                    "option": "Team 2"
                  },
                  {
                    "option": "Team 3"
                  }
                ]
              }
            }
          ]
        }
      },
      "typeVersion": 2.2
    },
    {
      "id": "7f8b7ce6-d1d9-4961-93b6-22edb0e3c31a",
      "name": "文字起こしの解析",
      "type": "n8n-nodes-base.code",
      "position": [
        -16,
        -64
      ],
      "parameters": {
        "jsCode": "const binaryKey = Object.keys(items[0].binary || {})[0];\nif (!binaryKey) {\n  throw new Error('No binary data found. Please upload a transcript file.');\n}\nconst binaryData = items[0].binary[binaryKey];\nconst txtContent = Buffer.from(binaryData.data, 'base64').toString('utf-8');\nconst lines = txtContent.split('\\n');\nlet transcriptWithTimestamps = [];\nif (txtContent.startsWith('WEBVTT')) {\n  let currentTimestamp = null;\n  let currentText = [];\n  for (let i = 0; i < lines.length; i++) {\n    const line = lines[i].trim();\n    const ts = line.match(/^(\\d{2}:\\d{2}:\\d{2}\\.\\d{3}) --> (\\d{2}:\\d{2}:\\d{2}\\.\\d{3})$/);\n    if (ts) {\n      if (currentTimestamp && currentText.length) {\n        transcriptWithTimestamps.push(`[${currentTimestamp}] Unknown: ${currentText.join(' ').trim()}`);\n      }\n      currentTimestamp = ts[1];\n      currentText = [];\n    } else if (line !== '' && !line.startsWith('WEBVTT')) {\n      currentText.push(line);\n    }\n  }\n  if (currentTimestamp && currentText.length) {\n    transcriptWithTimestamps.push(`[${currentTimestamp}] Unknown: ${currentText.join(' ').trim()}`);\n  }\n} else {\n  let currentSpeaker = null;\n  let currentTimestamp = null;\n  let currentText = [];\n  for (let i = 0; i < lines.length; i++) {\n    const m = lines[i].match(/^\\[([^\\]]+)\\]\\s+(\\d{2}:\\d{2}:\\d{2})(.*)$/);\n    if (m) {\n      if (currentSpeaker && currentTimestamp && currentText.length) {\n        transcriptWithTimestamps.push(`[${currentTimestamp}] ${currentSpeaker}: ${currentText.join(' ').trim()}`);\n      }\n      currentSpeaker = m[1];\n      currentTimestamp = m[2];\n      currentText = m[3] ? [m[3].trim()] : [];\n    } else if (lines[i].trim() !== '') {\n      currentText.push(lines[i].trim());\n    }\n  }\n  if (currentSpeaker && currentTimestamp && currentText.length) {\n    transcriptWithTimestamps.push(`[${currentTimestamp}] ${currentSpeaker}: ${currentText.join(' ').trim()}`);\n  }\n}\nitems[0].json.transcriptWithTimestamps = transcriptWithTimestamps.join('\\n');\nreturn items;"
      },
      "typeVersion": 2
    },
    {
      "id": "86aa481e-f7aa-40f1-8f45-1d2ffea43d5a",
      "name": "要約の生成",
      "type": "@n8n/n8n-nodes-langchain.agent",
      "position": [
        272,
        -64
      ],
      "parameters": {
        "text": "=You are an expert Agile coach. Summarize the following Sprint Review transcript related to the domain: {{ $json.Domain }} and sprint: {{ $json['Sprint name'] }}.\n\nRequirements:\n- Output strictly in Markdown.\n- add header title\n- Start with concise summary\n- Then a brief executive summary (3–5 bullets).\n- Then a **Presentation recap** table with columns: Timestamp | Presenter | Topics.\n- Finish with **Action items** as a checklist with owners if recognizable.\n- keep format clean with headers for each sections\n\nTranscript:\n{{ $json.transcriptWithTimestamps }}\n\n\n\n",
        "options": {},
        "promptType": "define"
      },
      "typeVersion": 2.1
    },
    {
      "id": "1fb90118-9077-4488-9ab1-efbfffc23736",
      "name": "要約のプレビュー",
      "type": "n8n-nodes-base.form",
      "position": [
        816,
        -144
      ],
      "webhookId": "c9b09c5d-a618-4ee0-a494-552f853306be",
      "parameters": {
        "options": {
          "customCss": ".card {\n\tposition: relative;\n\tmax-width: 500px;\n\tmargin: 20px auto;\n\tpadding: 20px;\n\tbackground: #fefefe !important;\n\tborder-radius: 12px;\n\tbox-shadow: 0 10px 30px rgba(0, 0, 0, 0.1);\n\tfont-family: 'Courier New', monospace;\n\tcolor: #1f2937;\n\tfont-size: 16px;\n\tline-height: 1.6;\n\twhite-space: pre-wrap;\n}\n\n.header p { text-align: left; }\n\n@media (max-width: 768px) {\n\t.card { margin: 10px !important; padding: 15px !important; font-size: 14px; }\n}\n"
        },
        "operation": "completion",
        "completionTitle": "Summary",
        "completionMessage": "={{ $json.output }}"
      },
      "typeVersion": 1
    },
    {
      "id": "70b492a1-5674-4a79-9ae1-6a773b494ec9",
      "name": "OpenAI LLM",
      "type": "@n8n/n8n-nodes-langchain.lmChatOpenAi",
      "position": [
        272,
        160
      ],
      "parameters": {
        "model": {
          "mode": "list",
          "value": "gpt-5-mini-2025-08-07"
        },
        "options": {}
      },
      "credentials": {
        "openAiApi": {
          "id": "7CgAvVK5FzY7W0vB",
          "name": "OpenAi account"
        }
      },
      "typeVersion": 1.2
    },
    {
      "id": "adb94eee-700f-48af-b4c5-3298e87bfeb4",
      "name": "Google スプレッドシートに保存",
      "type": "n8n-nodes-base.googleSheets",
      "position": [
        816,
        48
      ],
      "parameters": {
        "columns": {
          "value": {
            "Date": "={{ $('Parse Transcript').item.json.submittedAt }}",
            "Domain": "={{ $('Parse Transcript').item.json.Domain }}",
            "Content": "={{ $json.output }}",
            "VTT file": "={{ $('Parse Transcript').item.json['Transcript file'].filename }}",
            "Transcript": "={{ $('Parse Transcript').item.json.transcriptWithTimestamps }}",
            "Sprint name": "={{ $('Parse Transcript').item.json['Sprint name'] }}"
          },
          "schema": [
            {
              "id": "Date",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "Date",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "Domain",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "Domain",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "Sprint name",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "Sprint name",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "Content",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "Content",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "VTT file",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "VTT file",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "Transcript",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "Transcript",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "output",
              "type": "string",
              "display": true,
              "removed": true,
              "required": false,
              "displayName": "output",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            }
          ],
          "mappingMode": "defineBelow",
          "matchingColumns": [
            "Date"
          ],
          "attemptToConvertTypes": false,
          "convertFieldsToString": false
        },
        "options": {},
        "operation": "appendOrUpdate",
        "sheetName": {
          "__rl": true,
          "mode": "list",
          "value": "gid=0",
          "cachedResultUrl": "https://docs.google.com/your sheetlink",
          "cachedResultName": "Sheet1"
        },
        "documentId": {
          "__rl": true,
          "mode": "list",
          "value": "1rNGViGRvVgIol0mJzsShXF7h21TC6L3eKt4R8i21AG4",
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/1rNGViGRvVgIol0mJzsShXF7h21TC6L3eKt4R8i21AG4/edit?usp=drivesdk",
          "cachedResultName": "Sprint Review Summary"
        }
      },
      "credentials": {
        "googleSheetsOAuth2Api": {
          "id": "2wOj5Yn1ya1ZE298",
          "name": "Google Sheets Personal"
        }
      },
      "typeVersion": 4.6
    },
    {
      "id": "d884f161-0977-46a2-8b0b-f59c9a97b942",
      "name": "付箋: 概要1",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -464,
        -336
      ],
      "parameters": {
        "width": 256,
        "height": 240,
        "content": "## Overview\nSprint Review Transcript → AI Markdown Summary → Google Sheets\nInputs: File (VTT/text), Sprint name, Domain.\nOutputs: Preview in UI + archived row in Sheet."
      },
      "typeVersion": 1
    },
    {
      "id": "2886f243-599a-4328-a889-cf57d1b171b7",
      "name": "付箋: 入力フォーム1",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -464,
        112
      ],
      "parameters": {
        "content": "## Input Form\nCollects transcript file + sprint + domain."
      },
      "typeVersion": 1
    },
    {
      "id": "d201c8f4-cbf7-475e-80ce-329d020862eb",
      "name": "付箋: パーサー1",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -80,
        -336
      ],
      "parameters": {
        "height": 192,
        "content": "## Transcript Parser\nNormalizes to `[HH:MM:SS] Speaker: text`.\nSupports VTT and simple speaker/timestamp lines."
      },
      "typeVersion": 1
    },
    {
      "id": "1f1c40ee-cd8f-4899-b0eb-9bef6e59e1e3",
      "name": "付箋: AI要約1",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        256,
        -320
      ],
      "parameters": {
        "content": "## AI Summary\nCreates executive bullets, recap table, and action items in Markdown."
      },
      "typeVersion": 1
    },
    {
      "id": "946883d1-348d-4f49-9caa-ffad446422d2",
      "name": "付箋: プレビュー1",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        784,
        -272
      ],
      "parameters": {
        "height": 256,
        "content": "## Preview\nShows the generated Markdown with custom CSS."
      },
      "typeVersion": 1
    },
    {
      "id": "147f0d86-0272-41a7-a447-9cb8d1a9c0f4",
      "name": "付箋: スプレッドシート1",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        784,
        224
      ],
      "parameters": {
        "content": "## Archive to Sheets\nSaves summary + transcript + metadata (date/domain/sprint/file)."
      },
      "typeVersion": 1
    },
    {
      "id": "e38cebcc-793b-48cb-a1ee-3ae85d8ac289",
      "name": "付箋: LLM1",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        192,
        288
      ],
      "parameters": {
        "content": "## LLM Backend\nOpenAI Chat Model used by AI Agent."
      },
      "typeVersion": 1
    }
  ],
  "pinData": {},
  "connections": {
    "70b492a1-5674-4a79-9ae1-6a773b494ec9": {
      "ai_languageModel": [
        [
          {
            "node": "86aa481e-f7aa-40f1-8f45-1d2ffea43d5a",
            "type": "ai_languageModel",
            "index": 0
          }
        ]
      ]
    },
    "86aa481e-f7aa-40f1-8f45-1d2ffea43d5a": {
      "main": [
        [
          {
            "node": "1fb90118-9077-4488-9ab1-efbfffc23736",
            "type": "main",
            "index": 0
          },
          {
            "node": "adb94eee-700f-48af-b4c5-3298e87bfeb4",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "7f8b7ce6-d1d9-4961-93b6-22edb0e3c31a": {
      "main": [
        [
          {
            "node": "86aa481e-f7aa-40f1-8f45-1d2ffea43d5a",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "155c6cd6-3c71-4399-9f1a-3f6c52c3a384": {
      "main": [
        [
          {
            "node": "7f8b7ce6-d1d9-4961-93b6-22edb0e3c31a",
            "type": "main",
            "index": 0
          }
        ]
      ]
    }
  }
}