Gmail に基づく Jira 自動スプロントレポートの生成

• •Googleアカウント + Gmail API認証情報

{
  "id": "4b7zzdwKKLNoIiMy",
  "meta": {
    "instanceId": "0430772da25f7bca29bf5ef2b251086a85fb4096503a6f781526d32befd038d6",
    "templateCredsSetupCompleted": true
  },
  "name": "Generate Jira automatic sprint report for Gmail",
  "tags": [
    {
      "id": "3T7uxjPBNibzqJlE",
      "name": "Google",
      "createdAt": "2025-08-18T07:15:32.460Z",
      "updatedAt": "2025-08-18T07:15:32.460Z"
    },
    {
      "id": "7awkAmV9dEBwVz7l",
      "name": "sprint report",
      "createdAt": "2025-09-25T16:00:40.741Z",
      "updatedAt": "2025-09-25T16:00:40.741Z"
    },
    {
      "id": "EYNbAs3Q9sg9NksG",
      "name": "Jira",
      "createdAt": "2025-09-10T10:47:53.750Z",
      "updatedAt": "2025-09-10T10:47:53.750Z"
    },
    {
      "id": "eAzNESqLUWwWMrE9",
      "name": "gmail",
      "createdAt": "2025-08-18T07:16:22.709Z",
      "updatedAt": "2025-08-18T07:16:22.709Z"
    }
  ],
  "nodes": [
    {
      "id": "e63fae5a-8ccf-4812-bbc8-05e30cd99eb1",
      "name": "メール通知",
      "type": "n8n-nodes-base.gmail",
      "position": [
        1312,
        0
      ],
      "webhookId": "40883792-d8eb-4d1e-89a4-97ede636627f",
      "parameters": {
        "sendTo": "youremail@gmail.com",
        "message": "=Hello, \nThis is your current sprint report\n\n{{$json.html}}\n<p><a href=\"https://yourdomain.atlassian.net/jira/software/c/projects/TES/boards/3\">See the sprint in Jira</a></p>\n",
        "options": {},
        "subject": "=Your Sprint Report"
      },
      "credentials": {
        "gmailOAuth2": {
          "id": "M9FwThxXdSZG2WNo",
          "name": "Gmail account"
        }
      },
      "typeVersion": 2.1,
      "alwaysOutputData": false
    },
    {
      "id": "21fed4c4-f686-4fc1-8255-32c76382b377",
      "name": "課題一覧取得",
      "type": "n8n-nodes-base.jira",
      "position": [
        416,
        0
      ],
      "parameters": {
        "options": {
          "jql": "project = your project AND sprint in openSprints()",
          "fields": "summary,status,assignee,priority,issuetype,labels,created,updated,customfield_10016,customfield_10020"
        },
        "operation": "getAll",
        "returnAll": true
      },
      "credentials": {
        "jiraSoftwareCloudApi": {
          "id": "WL78MSYPg2KS2Y5X",
          "name": "Jira SW Cloud account 3"
        }
      },
      "typeVersion": 1
    },
    {
      "id": "c46a19f2-7c08-44a4-9e91-60d32b9c31ae",
      "name": "付箋1",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -336,
        -464
      ],
      "parameters": {
        "color": 3,
        "width": 304,
        "height": 176,
        "content": "## Required\n\n• Jira Cloud project (API email + API token)\n• Gmail credential"
      },
      "typeVersion": 1
    },
    {
      "id": "a4db27d8-d430-4798-8ffc-c8e71d414897",
      "name": "付箋",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -16,
        -464
      ],
      "parameters": {
        "width": 640,
        "height": 704,
        "content": "## 1) Trigger and Normalize \n\nTrigger: Scheduled run (e.g., every Friday at 17:00)\n\nData Processing:\n- Fetch issues from Jira with JQL: project = <KEY> AND sprint in openSprints()\n- Select relevant fields: summary, status, assignee, priority, story points, sprint, created, updated\n- Validate presence of critical fields (story points, sprint info)\n- Normalize empty values (e.g., “Unassigned”, “Not defined”)\n\nBest Practices:\nPerform validation and normalization here so later metrics and reports don’t break if a field is missing."
      },
      "typeVersion": 1
    },
    {
      "id": "3c26641b-e1e7-46d9-97f4-03d44934ec43",
      "name": "付箋2",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        640,
        -464
      ],
      "parameters": {
        "color": 5,
        "width": 352,
        "height": 704,
        "content": "## 2) Calculate Metrics\n\nAction: Aggregate data into KPIs\n\nKey Metrics:\n- Issues by status (To Do / In Progress / Done)\n- Story points: completed vs total\n- Blockers count (priority = High / label = blocker)\n- Sprint timeline (start / end date)\n\nBest Practices:\nKeep calculations in a dedicated node to make debugging easier and allow quick adjustments (e.g., adding new KPIs)."
      },
      "typeVersion": 1
    },
    {
      "id": "5ddc63f8-f59c-4b08-a5f4-c3b7a35e7d45",
      "name": "付箋3",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        1008,
        -464
      ],
      "parameters": {
        "color": 4,
        "width": 464,
        "height": 704,
        "content": "## 3) Generate Report & Send Notification\n\nAction: Build an HTML report\n\nContents:\n- Sprint name + dates\n- Metrics overview (done vs total issues, SP, blockers)\n- Table of all issues (key, summary, status, assignee, priority, SP)\n- Links to Jira tickets\n\nBest Practices:\nUse a clean HTML template with inline CSS for email compatibility.\n\n \n\nAction: Send reporting email\n\nRecipients: Person who needs it\n\nInformation: include all information relevant to the sprint status\n"
      },
      "typeVersion": 1
    },
    {
      "id": "7d410e39-680b-483c-af58-43a86eb0e3dd",
      "name": "週次トリガー",
      "type": "n8n-nodes-base.scheduleTrigger",
      "position": [
        0,
        0
      ],
      "parameters": {
        "rule": {
          "interval": [
            {
              "field": "weeks",
              "triggerAtDay": [
                5
              ],
              "triggerAtHour": 17
            }
          ]
        }
      },
      "typeVersion": 1.2
    },
    {
      "id": "cc836891-9da3-45eb-805e-2e1875332ac6",
      "name": "Jira & メール設定",
      "type": "n8n-nodes-base.set",
      "position": [
        208,
        0
      ],
      "parameters": {
        "mode": "raw",
        "options": {},
        "jsonOutput": "{\n  \"values\": {\n    \"jiraBaseUrl\": \"https://yourdomain.atlassian.net\",\n    \"jiraEmail\": \"youremail@gmail.com\",\n    \"jiraApiToken\": \"ATATT3xFfGF0p65qwDvypM5e-...\",\n    \"projectKey\": \"yourkey\",\n    \"emailRecipients\": \"yourrecipient@gmail.com\"\n  }\n}\n"
      },
      "typeVersion": 3.4
    },
    {
      "id": "6ace04d2-c0dd-494e-8c6a-6c96fca7490c",
      "name": "検証 & エラー処理",
      "type": "n8n-nodes-base.code",
      "position": [
        672,
        0
      ],
      "parameters": {
        "jsCode": "const SP = 'customfield_10016';\nconst SPRINT = 'customfield_10020';\n\nconst items = $input.all();\n\nconst out = items.map(i => {\n  const it = i.json || {};\n  const f = it.fields || {};\n  const sprintObj = Array.isArray(f[SPRINT]) ? f[SPRINT][0] : f[SPRINT]; // souvent tableau\n\n  return {\n    key: it.key,\n    summary: f.summary || '',\n    status: f.status?.name || '—',\n    statusCategory: f.status?.statusCategory?.name || '—',\n    assignee: f.assignee?.displayName || 'Unassigned',\n    priority: f.priority?.name || 'Undefined',\n    sp: Number(f[SP] || 0),\n\n    sprint: sprintObj?.name || '—',\n    sprintStart: sprintObj?.startDate || null,\n    sprintEnd: sprintObj?.endDate || null,\n\n    created: f.created || null,\n    updated: f.updated || null,\n    labels: f.labels || [],\n  };\n});\n\nreturn out.map(x => ({ json: x }));\n"
      },
      "typeVersion": 2
    },
    {
      "id": "b86c2c98-b12c-410a-a837-46c6d974d3ba",
      "name": "メトリクス計算",
      "type": "n8n-nodes-base.code",
      "position": [
        880,
        0
      ],
      "parameters": {
        "jsCode": "const rows = $input.all().map(i => i.json);\nif (!rows.length) return [{ json: { isEmpty: true, message: 'Aucun ticket' } }];\n\nconst low = s => (s||'').toLowerCase();\n\nconst total = rows.length;\nconst done = rows.filter(r => low(r.status) === 'done').length;\nconst inProgress = rows.filter(r => low(r.status) === 'in progress').length;\nconst toDo = rows.filter(r => low(r.status) === 'to do').length;\n\nconst spTotal = rows.reduce((a,r)=>a+(r.sp||0),0);\nconst spDone  = rows.filter(r=>low(r.status)==='done').reduce((a,r)=>a+(r.sp||0),0);\n\nconst blockers = rows.filter(r =>\n  (r.priority||'').toLowerCase()==='highest' ||\n  /blocked|impediment/i.test(r.status) ||\n  (r.labels||[]).map(low).includes('blocked')\n).length;\n\nconst firstWithSprint = rows.find(r => r.sprint && r.sprint !== '—');\nconst sprintName = firstWithSprint?.sprint || 'Sprint en cours';\nconst sprintEnd = firstWithSprint?.sprintEnd || null;\n\nreturn [{\n  json: {\n    sprintName, sprintEnd,\n    counts: { total, done, inProgress, toDo },\n    storyPoints: { total: spTotal, done: spDone, rate: spTotal ? Math.round(100*spDone/spTotal) : 0 },\n    blockers,\n    issues: rows,\n    reportDate: new Date().toLocaleDateString('fr-FR'),\n    reportTime: new Date().toLocaleTimeString('fr-FR'),\n  }\n}];\n"
      },
      "typeVersion": 2
    },
    {
      "id": "579c77db-15fb-4ae3-a523-0df994024737",
      "name": "HTMLレポート生成",
      "type": "n8n-nodes-base.code",
      "position": [
        1088,
        0
      ],
      "parameters": {
        "jsCode": "const d = $json;\n\nfunction row(t){\n  return `<tr>\n    <td><a href=\"https:/yourdomain.atlassian.net/browse/${t.key}\">${t.key}</a></td>\n    <td>${t.summary || ''}</td>\n    <td>${t.status || ''}</td>\n    <td>${t.assignee || 'Unassigned'}</td>\n    <td>${t.priority || 'Undefined'}</td>\n    <td style=\"text-align:right\">${t.sp || 0}</td>\n  </tr>`;\n}\n\nconst table = `<table border=\"1\" cellspacing=\"0\" cellpadding=\"6\" style=\"border-collapse:collapse;width:100%\">\n  <tr>\n    <th>Key</th>\n    <th>Summary</th>\n    <th>Status</th>\n    <th>Assignee</th>\n    <th>Priority</th>\n    <th>SP</th>\n  </tr>\n  ${(d.issues || []).map(row).join('')}\n</table>`;\n\nconst header = `<p><b>Tickets</b>: ${d.counts.done}/${d.counts.total}\n — <b>Story Points</b>: ${d.storyPoints.done}/${d.storyPoints.total} (${d.storyPoints.rate}%)\n — <b>Blockers</b>: ${d.blockers}</p>`;\n\nconst meta = `<p style=\"color:#6b7280\">\nGenerated on ${d.reportDate} ${d.reportTime}${\n  d.sprintEnd ? ` — Planned end: ${new Date(d.sprintEnd).toLocaleDateString('en-GB')}` : ''\n}</p>`;\n\nreturn [{ json: { html: `<h2>Sprint Report — ${d.sprintName}</h2>${meta}${header}${table}` } }];\n"
      },
      "typeVersion": 2
    }
  ],
  "active": false,
  "pinData": {},
  "settings": {
    "executionOrder": "v1"
  },
  "versionId": "7e8eaceb-500f-431c-92dc-bcdf5e39c926",
  "connections": {
    "7d410e39-680b-483c-af58-43a86eb0e3dd": {
      "main": [
        [
          {
            "node": "cc836891-9da3-45eb-805e-2e1875332ac6",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "21fed4c4-f686-4fc1-8255-32c76382b377": {
      "main": [
        [
          {
            "node": "6ace04d2-c0dd-494e-8c6a-6c96fca7490c",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "b86c2c98-b12c-410a-a837-46c6d974d3ba": {
      "main": [
        [
          {
            "node": "579c77db-15fb-4ae3-a523-0df994024737",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "579c77db-15fb-4ae3-a523-0df994024737": {
      "main": [
        [
          {
            "node": "e63fae5a-8ccf-4812-bbc8-05e30cd99eb1",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "cc836891-9da3-45eb-805e-2e1875332ac6": {
      "main": [
        [
          {
            "node": "21fed4c4-f686-4fc1-8255-32c76382b377",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "6ace04d2-c0dd-494e-8c6a-6c96fca7490c": {
      "main": [
        [
          {
            "node": "b86c2c98-b12c-410a-a837-46c6d974d3ba",
            "type": "main",
            "index": 0
          }
        ]
      ]
    }
  }
}