タイムトラッキングと請求書自動化

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

{
  "id": "VyFk3RnfdN7deOBG",
  "meta": {
    "instanceId": "8443f10082278c46aa5cf3acf8ff0f70061a2c58bce76efac814b16290845177",
    "templateCredsSetupCompleted": true
  },
  "name": "Time Tracking & Billing Automation:",
  "tags": [],
  "nodes": [
    {
      "id": "e62e8f45-3b5b-4a1d-a120-b0c07ebcc178",
      "name": "ワークフロー実行時",
      "type": "n8n-nodes-base.manualTrigger",
      "position": [
        -128,
        0
      ],
      "parameters": {},
      "typeVersion": 1
    },
    {
      "id": "fadb2658-c02d-4819-b6eb-12114cc58127",
      "name": "付箋",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        1120,
        -352
      ],
      "parameters": {
        "width": 256,
        "height": 336,
        "content": "Combine Reminder & Invoice Data Streams\n\nAction: Merges reminder and invoice outputs for unified processing.\nDescription:\n\nCombines both branches (missing hours and invoice data).\n\nEnsures all team members get relevant communication.\n\nKeeps all data intact for the next reconciliation step."
      },
      "typeVersion": 1
    },
    {
      "id": "4b5a9488-f7cd-436b-afaf-f698c756d933",
      "name": "付箋1",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        736,
        -368
      ],
      "parameters": {
        "width": 288,
        "height": 336,
        "content": "Identify Issues with Missing Time Logs\n\nAction: Detects issues where time wasn’t logged.\nDescription:\n\nScans each user’s issues to find zero-hour entries.\n\nGenerates HTML reminder messages listing missing issues.\n\nCreates reminders only for users with actual missing logs.\n\nEnsures timely and accurate billing compliance."
      },
      "typeVersion": 1
    },
    {
      "id": "174d0eec-6cce-4d0c-8b38-045b67f12f76",
      "name": "付箋2",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        368,
        -368
      ],
      "parameters": {
        "width": 288,
        "height": 336,
        "content": "Aggregate Hours by Team Member\n\nAction: Groups Jira issues by each assignee and totals their hours.\nDescription:\n\nCalculates total logged hours per user.\n\nKeeps issue details like summary, status, and sprint.\n\nHandles missing data (unassigned or null time entries).\n\nProduces structured user-level data for invoices and reminders."
      },
      "typeVersion": 1
    },
    {
      "id": "13457e31-0e46-48e0-88a1-91610a40b646",
      "name": "付箋3",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        80,
        176
      ],
      "parameters": {
        "width": 304,
        "height": 368,
        "content": "Fetch All Project Issues with Time Data\n\nAction: Retrieves all Jira issues with time tracking info.\nDescription:\n\nUses Jira API to fetch every issue with getAll and returnAll.\n\nCollects essential fields like keys, summaries, time spent, and assignee.\n\nProvides project, sprint, and priority details.\n\nServes as the base dataset for billing and reporting."
      },
      "typeVersion": 1
    },
    {
      "id": "edcde860-f8c8-4229-be41-c4c026be9a46",
      "name": "付箋4",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        720,
        560
      ],
      "parameters": {
        "width": 304,
        "height": 400,
        "content": "Generate Invoice Summary with Text Attachment\n\nAction: Creates detailed text-based invoices per user.\nDescription:\n\nCalculates billing using a default hourly rate ($50/hour).\n\nLists each issue with hours and status.\n\nExports a plain-text invoice as .txt (base64 encoded).\n\nSkips users with zero hours to avoid empty invoices.\n\nSends structured JSON + binary data for email delivery"
      },
      "typeVersion": 1
    },
    {
      "id": "d2450d54-f3e7-4732-bccd-2bc0e29b8238",
      "name": "付箋5",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        1760,
        208
      ],
      "parameters": {
        "width": 272,
        "height": 384,
        "content": "Send Invoices & Reminders to Team\n\nAction: Sends personalized emails with invoices and reminders.\nDescription:\n\nSends to each developer’s email dynamically.\n\nIncludes name, project, and total hours in the message.\n\nAttaches generated invoice files.\n\nCan be extended to CC managers or finance.\n\nFinal delivery step for automated billing communication."
      },
      "typeVersion": 1
    },
    {
      "id": "559bf371-f7b3-477f-82d9-a022ed54856f",
      "name": "付箋6",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        1424,
        -384
      ],
      "parameters": {
        "width": 288,
        "height": 368,
        "content": "Reconcile JSON & Binary Attachments\n\nAction: Aligns JSON data with corresponding invoice files.\nDescription:\n\nMatches items using assignee name as identifier.\n\nFixes cases where JSON or binary is missing.\n\nGuarantees each record includes both email info and invoice.\n\nPrevents send failures and ensures complete data merging."
      },
      "typeVersion": 1
    },
    {
      "id": "0da5f2fa-32f6-4c7e-85a0-474bf3550f4e",
      "name": "時間データ付きプロジェクト課題を全取得",
      "type": "n8n-nodes-base.jira",
      "position": [
        176,
        0
      ],
      "parameters": {
        "options": {},
        "operation": "getAll",
        "returnAll": true
      },
      "credentials": {
        "jiraSoftwareCloudApi": {
          "id": "199LdjjU3PhhL8xb",
          "name": "saurabh jira"
        }
      },
      "typeVersion": 1
    },
    {
      "id": "76eca7bd-8b7d-4119-a18f-81ae5c142653",
      "name": "メンバー別に時間を集計",
      "type": "n8n-nodes-base.code",
      "position": [
        448,
        0
      ],
      "parameters": {
        "jsCode": "// Get all Jira issues from input\nconst items = $input.all().map(i => i.json);\n\n// Initialize grouped output\nconst grouped = {};\n\n// Iterate over each issue\nfor (const issue of items) {\n  const f = issue.fields || {};\n\n  const assigneeName = f.assignee?.displayName || 'Unassigned';\n  const email = f.assignee?.emailAddress || 'unknown@domain.com';\n  const projectName = f.project?.name || 'Unknown Project';\n  const sprint = f.customfield_10020?.[0]?.name || 'No Sprint';\n  const timeSpentSeconds = f.timespent || 0;\n  const timeSpentHours = (timeSpentSeconds / 3600).toFixed(2);\n\n  if (!grouped[assigneeName]) {\n    grouped[assigneeName] = {\n      assignee: assigneeName,\n      email,\n      project_name: projectName,\n      total_hours: 0,\n      issues: []\n    };\n  }\n\n  grouped[assigneeName].total_hours += parseFloat(timeSpentHours);\n  grouped[assigneeName].issues.push({\n    issue_key: issue.key,\n    summary: f.summary,\n    timespent_hours: timeSpentHours,\n    status: f.status?.name || 'Unknown',\n    priority: f.priority?.name || 'None',\n    sprint,\n  });\n}\n\n// Convert grouped object into array format\nconst output = Object.values(grouped).map(user => ({\n  json: {\n    assignee: user.assignee,\n    email: user.email,\n    project_name: user.project_name,\n    total_hours: user.total_hours.toFixed(2),\n    issues: user.issues,\n  }\n}));\n\nreturn output;\n"
      },
      "typeVersion": 2,
      "alwaysOutputData": false
    },
    {
      "id": "3ef99004-e0bd-4346-8ac0-6e83a88260f0",
      "name": "時間記録が不足している課題を特定",
      "type": "n8n-nodes-base.code",
      "position": [
        832,
        0
      ],
      "parameters": {
        "jsCode": "const users = $input.all().map(i => i.json);\n\nconst reminders = [];\n\nfor (const user of users) {\n  const missing = user.issues.filter(i => parseFloat(i.timespent_hours) === 0);\n\n  if (missing.length > 0) {\n    const table = missing.map(i => \n      `<tr>\n        <td>${i.issue_key}</td>\n        <td>${i.summary}</td>\n        <td>${i.status}</td>\n        <td>${i.priority}</td>\n        <td>${i.sprint}</td>\n      </tr>`\n    ).join('');\n\n    reminders.push({\n      json: {\n        assignee: user.assignee,\n        email: user.email,\n        missing_count: missing.length,\n        project_name: user.project_name,\n        html_message: `\n          <p>Hi ${user.assignee},</p>\n          <p>Our system found ${missing.length} issues where no time is logged:</p>\n          <table border=\"1\" cellspacing=\"0\" cellpadding=\"5\">\n            <tr><th>Issue Key</th><th>Summary</th><th>Status</th><th>Priority</th><th>Sprint</th></tr>\n            ${table}\n          </table>\n          <p>Please log your hours for these issues before EOD to ensure accurate billing.</p>\n          <p>Thank you,<br>Automation Bot 🤖</p>\n        `\n      }\n    });\n  }\n}\n\nreturn reminders;\n"
      },
      "typeVersion": 2
    },
    {
      "id": "db74c4fe-3bf1-45f9-a971-64ffc74695df",
      "name": "テキスト添付ファイル付き請求書サマリーを生成",
      "type": "n8n-nodes-base.code",
      "position": [
        832,
        384
      ],
      "parameters": {
        "jsCode": "// Combined Invoice Generator + Text File Export\n\nconst users = $input.all().map(i => i.json);\nconst RATE = 50; // hourly rate\nconst results = [];\n\nfor (const user of users) {\n  const totalHours = parseFloat(user.total_hours || 0);\n  if (totalHours === 0) continue;\n\n  const totalAmount = totalHours * RATE;\n\n  // Build the plain text table\n  const issueLines = user.issues.map(i =>\n    `${i.issue_key.padEnd(10)} | ${i.summary.padEnd(30)} | ${i.timespent_hours} hrs | ${i.status}`\n  ).join('\\n');\n\n  // Final plain-text invoice content\n  const text = `\n===========================================\nINVOICE SUMMARY — ${user.project_name}\n===========================================\n\nAssignee: ${user.assignee}\nEmail: ${user.email}\nRate: $${RATE}/hour\n\n-------------------------------------------\nIssues\n-------------------------------------------\n${issueLines}\n\n-------------------------------------------\nTotal Hours: ${totalHours.toFixed(2)}\nTotal Amount: $${totalAmount.toFixed(2)}\n-------------------------------------------\n\nGenerated automatically by Techdome Billing Automation Bot.\n`;\n\n  // Push JSON + Binary for email attachment\n  results.push({\n    json: {\n      assignee: user.assignee,\n      email: user.email,\n      project_name: user.project_name,\n      total_hours: totalHours.toFixed(2),\n      total_amount: totalAmount.toFixed(2)\n    },\n    binary: {\n      data: {\n        fileName: `Invoice_${user.assignee}.txt`,\n        mimeType: 'text/plain',\n        data: Buffer.from(text, 'utf8').toString('base64')\n      }\n    }\n  });\n}\n\nreturn results;\n"
      },
      "typeVersion": 2
    },
    {
      "id": "064ef92a-f13c-4926-8526-a1eb398fa3db",
      "name": "リマインダーと請求書データストリームを結合",
      "type": "n8n-nodes-base.merge",
      "position": [
        1216,
        16
      ],
      "parameters": {},
      "typeVersion": 3.2
    },
    {
      "id": "d3aec18e-05de-4ca9-8b19-263695b105b7",
      "name": "JSONとバイナリ添付ファイルを調整",
      "type": "n8n-nodes-base.code",
      "position": [
        1488,
        16
      ],
      "parameters": {
        "jsCode": "// Combine JSON + Binary after Merge node\n// Safe for n8n v1.112.6 and later\n\nconst combined = [];\n\n// Get all incoming items\nconst items = $input.all();\n\n// We might have duplicates or partials, so we merge smartly\nfor (const item of items) {\n  const json = item.json || {};\n  const binary = item.binary || {};\n\n  // Case 1: this item already has both\n  if (json.email && binary.data) {\n    combined.push({ json, binary });\n    continue;\n  }\n\n  // Case 2: only has JSON\n  if (json.email && !binary.data) {\n    // Try to find its binary partner\n    const match = items.find(\n      (i) => i.binary && i.binary.data && i.json?.assignee === json.assignee\n    );\n\n    if (match) {\n      combined.push({ json, binary: match.binary });\n    } else {\n      combined.push({ json }); // fallback\n    }\n    continue;\n  }\n\n  // Case 3: only has Binary\n  if (binary.data && !json.email) {\n    // Try to find its JSON partner\n    const match = items.find(\n      (i) => i.json && i.json.assignee && i.binary === undefined\n    );\n    if (match) {\n      combined.push({ json: match.json, binary });\n    } else {\n      combined.push({ binary });\n    }\n    continue;\n  }\n}\n\nreturn combined;\n"
      },
      "typeVersion": 2
    },
    {
      "id": "94b59a38-4e45-47dc-8c3d-d133ddd07455",
      "name": "チームに請求書とリマインダーを送信",
      "type": "n8n-nodes-base.gmail",
      "position": [
        1808,
        16
      ],
      "webhookId": "0c82c299-6938-42ed-bda6-5007d79af34f",
      "parameters": {
        "sendTo": "={{ $json.email }}",
        "message": "=Hi {{$json[\"assignee\"]}},<br><br>\nPlease find attached your invoice summary for <strong>{{$json[\"project_name\"]}}</strong>.<br><br>\n📊 <b>Total Hours:</b> {{ $('Aggregate Hours by Team Member').item.json.total_hours }}<br>\nRegards,<br>\nTechdome Billing Automation Bot\n",
        "options": {
          "attachmentsUi": {
            "attachmentsBinary": [
              {}
            ]
          }
        },
        "subject": "={{ $json.project_name }}"
      },
      "credentials": {
        "gmailOAuth2": {
          "id": "RchiXdmY8WaQhOSJ",
          "name": "Gmail account"
        }
      },
      "typeVersion": 2.1
    }
  ],
  "active": false,
  "pinData": {},
  "settings": {
    "executionOrder": "v1"
  },
  "versionId": "c7c9ac45-c34e-4b9e-a15d-0aea4da1f6d6",
  "connections": {
    "76eca7bd-8b7d-4119-a18f-81ae5c142653": {
      "main": [
        [
          {
            "node": "3ef99004-e0bd-4346-8ac0-6e83a88260f0",
            "type": "main",
            "index": 0
          },
          {
            "node": "db74c4fe-3bf1-45f9-a971-64ffc74695df",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "94b59a38-4e45-47dc-8c3d-d133ddd07455": {
      "main": [
        []
      ]
    },
    "d3aec18e-05de-4ca9-8b19-263695b105b7": {
      "main": [
        [
          {
            "node": "94b59a38-4e45-47dc-8c3d-d133ddd07455",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "e62e8f45-3b5b-4a1d-a120-b0c07ebcc178": {
      "main": [
        [
          {
            "node": "0da5f2fa-32f6-4c7e-85a0-474bf3550f4e",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "3ef99004-e0bd-4346-8ac0-6e83a88260f0": {
      "main": [
        [
          {
            "node": "064ef92a-f13c-4926-8526-a1eb398fa3db",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "064ef92a-f13c-4926-8526-a1eb398fa3db": {
      "main": [
        [
          {
            "node": "d3aec18e-05de-4ca9-8b19-263695b105b7",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "0da5f2fa-32f6-4c7e-85a0-474bf3550f4e": {
      "main": [
        [
          {
            "node": "76eca7bd-8b7d-4119-a18f-81ae5c142653",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "db74c4fe-3bf1-45f9-a971-64ffc74695df": {
      "main": [
        [
          {
            "node": "064ef92a-f13c-4926-8526-a1eb398fa3db",
            "type": "main",
            "index": 1
          }
        ]
      ]
    }
  }
}