Stripeの税務サマリーからGoogle Sheetsへ転送し、Slackでのリマインダーを付与する

• •Slack Bot Token または Webhook URL
• •Stripe API Key
• •Google Sheets API認証情報

{
  "id": "zRKz4PZ9O47qu8nx",
  "meta": {
    "instanceId": "8443f10082278c46aa5cf3acf8ff0f70061a2c58bce76efac814b16290845177",
    "templateCredsSetupCompleted": true
  },
  "name": "Stripe Tax Summary to Google Sheets with Slack Alerts",
  "tags": [],
  "nodes": [
    {
      "id": "1ef1afae-2f4e-42b4-bcbe-04879dc757fc",
      "name": "税務サマリーワークフロー概要",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        336,
        -112
      ],
      "parameters": {
        "width": 389,
        "height": 704,
        "content": "## 📊 Stripe Tax Summary Reporting Automation\n\nThis workflow automatically generates comprehensive tax summaries from your Stripe invoices, providing detailed breakdowns by country, state, and tax rate for compliance and reporting purposes.\n\n### What this workflow does:\n• Fetches paid invoices from Stripe daily at 2 AM\n• Analyzes tax data from the last 30 days\n• Calculates tax summaries by jurisdiction and rate\n• Updates Google Sheets with formatted tax reports\n• Sends Slack notifications with processing results\n• Maintains complete audit trail for tax compliance\n\n### Key Benefits:\n• Automated tax compliance reporting\n• Multi-jurisdiction tax tracking\n• Real-time tax data analysis\n• Professional reporting format\n• Error handling and notifications\n• Historical tax data preservation\n\n### Setup Requirements:\n1. Stripe account with invoice tax data\n2. Google Sheets for tax report storage\n3. Slack workspace for notifications\n4. Proper tax configuration in Stripe\n\n"
      },
      "typeVersion": 1
    },
    {
      "id": "83f6e13b-315c-4bcd-a2af-9aeba3d657da",
      "name": "処理スケジュール",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        768,
        -512
      ],
      "parameters": {
        "width": 300,
        "height": 508,
        "content": "## ⏰ Daily Tax Processing Schedule\n\nRuns every day at 2 AM to process tax data from the previous 30 days.\n\n**Cron Expression:** `0 2 * * *`\n- 0: minute (0)\n- 2: hour (2 AM)\n- *: any day of month\n- *: any month\n- *: any day of week\n\n**Why 2 AM?**\n• Low system usage time\n• Ensures daily processing\n• Allows overnight Stripe data sync\n• Consistent reporting schedule\n\n💡 **Optimization:** Adjust timing based on your business timezone and tax reporting requirements."
      },
      "typeVersion": 1
    },
    {
      "id": "acac1ac0-6818-4dc4-9d97-52ab8a45103e",
      "name": "日次税務処理トリガー",
      "type": "n8n-nodes-base.scheduleTrigger",
      "position": [
        944,
        16
      ],
      "parameters": {
        "rule": {
          "interval": [
            {
              "field": "cronExpression",
              "expression": "0 2 * * *"
            }
          ]
        }
      },
      "typeVersion": 1.1
    },
    {
      "id": "9ea1dc34-54b0-431a-a99b-d8b034a035c6",
      "name": "Stripe データ取得",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        1008,
        224
      ],
      "parameters": {
        "width": 300,
        "height": 580,
        "content": "## 💳 Stripe Invoice Data Retrieval\n\nFetches paid invoices with expanded tax information from Stripe API.\n\n**Current Issues:**\n- Using HTTP Request instead of native Stripe node\n- Manual API endpoint management\n- Hardcoded query parameters\n\n**Query Parameters:**\n- `status=paid`: Only paid invoices\n- `limit=100`: Maximum 100 invoices per request\n- `expand[]=data.lines.data.tax_amounts`: Include tax details\n- `created[gte]`: Last 30 days of data\n\n**Recommendation:**\nUse native Stripe node for better:\n- Error handling\n- Parameter validation\n- Maintenance\n- Documentation"
      },
      "typeVersion": 1
    },
    {
      "id": "99ff779c-2996-40b0-ac06-849a83de75f1",
      "name": "税務データ付き支払済み請求書を取得",
      "type": "n8n-nodes-base.stripe",
      "position": [
        1168,
        16
      ],
      "parameters": {
        "resource": "invoice"
      },
      "credentials": {
        "stripeApi": {
          "id": "DV4tPpxjbOUkGfAx",
          "name": "Stripe account"
        }
      },
      "typeVersion": 1
    },
    {
      "id": "601354df-03e6-4009-8315-103cf865a3e3",
      "name": "データ検証",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        1248,
        -528
      ],
      "parameters": {
        "width": 300,
        "height": 504,
        "content": "## ✅ Data Validation Check\n\nValidates that invoice data was successfully retrieved before processing.\n\n**Validation Logic:**\n- Checks if data array exists\n- Verifies data array has length > 0\n- Prevents processing empty datasets\n\n**Error Handling:**\n- Success path: Continue to tax processing\n- Failure path: Send error notification\n\n**Why This Matters:**\n- Prevents workflow failures on empty data\n- Provides clear error messaging\n- Maintains data integrity\n- Enables proper troubleshooting"
      },
      "typeVersion": 1
    },
    {
      "id": "350fd6f2-70d7-4857-b7d5-9dfb3429e161",
      "name": "請求書データの存在を確認",
      "type": "n8n-nodes-base.if",
      "position": [
        1376,
        16
      ],
      "parameters": {
        "options": {},
        "conditions": {
          "options": {
            "version": 1,
            "leftValue": "",
            "caseSensitive": true,
            "typeValidation": "strict"
          },
          "combinator": "and",
          "conditions": [
            {
              "id": "data-exists",
              "operator": {
                "type": "array",
                "operation": "lengthGt",
                "rightType": "number"
              },
              "leftValue": "={{ $json.data }}",
              "rightValue": 0
            }
          ]
        }
      },
      "typeVersion": 2
    },
    {
      "id": "6f79bead-6312-47ce-90de-aed6ebb86e61",
      "name": "税務処理ロジック",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        1568,
        -704
      ],
      "parameters": {
        "width": 300,
        "height": 616,
        "content": "## 🧮 Tax Data Processing Engine\n\nAdvanced tax calculation and summary generation from Stripe invoice data.\n\n**Processing Logic:**\n1. **Period Grouping**: Groups by year-month (YYYY-MM)\n2. **Jurisdiction Mapping**: Extracts country and state information\n3. **Tax Rate Analysis**: Calculates by tax rate percentage\n4. **Amount Aggregation**: Sums taxable amounts and taxes collected\n5. **Currency Conversion**: Converts from cents to dollars\n\n**Data Structures:**\n- Uses Map for efficient grouping\n- Handles nested invoice line items\n- Processes multiple tax jurisdictions\n- Accounts for tax-exempt transactions\n\n**Output Format:**\n- Period, country, state, tax rate\n- Taxable amount, tax collected\n- Rounded to 2 decimal places"
      },
      "typeVersion": 1
    },
    {
      "id": "d4547ae7-4cff-465e-9502-982ab05be9e9",
      "name": "管轄区域別に税務サマリーを計算",
      "type": "n8n-nodes-base.code",
      "position": [
        1600,
        -64
      ],
      "parameters": {
        "jsCode": "const invoices = $input.all();\nconst taxSummary = new Map();\n\nconsole.log('Processing invoices for tax summary:', invoices.length);\n\nfor (const invoice of invoices) {\n  // Handle Stripe API response structure\n  const invoiceData = invoice.json.data || invoice.json;\n  \n  if (!invoiceData || !Array.isArray(invoiceData)) {\n    console.log('Skipping invalid invoice data');\n    continue;\n  }\n  \n  for (const invoiceItem of invoiceData) {\n    const invoiceDate = new Date(invoiceItem.created * 1000);\n    const period = `${invoiceDate.getFullYear()}-${String(invoiceDate.getMonth() + 1).padStart(2, '0')}`;\n    \n    // Extract country from account or customer data\n    const country = invoiceItem.account_country || \n                   invoiceItem.customer_details?.address?.country || \n                   'Unknown';\n    \n    // Process line items with tax information\n    if (invoiceItem.lines && invoiceItem.lines.data && invoiceItem.lines.data.length > 0) {\n      for (const line of invoiceItem.lines.data) {\n        // Process tax amounts if available\n        if (line.tax_amounts && line.tax_amounts.length > 0) {\n          for (const taxAmount of line.tax_amounts) {\n            const taxRate = taxAmount.tax_rate ? (taxAmount.tax_rate.percentage || 0) : 0;\n            const jurisdiction = taxAmount.tax_rate ? (taxAmount.tax_rate.jurisdiction || '') : '';\n            const state = jurisdiction.includes('-') ? jurisdiction.split('-')[1] : jurisdiction;\n            \n            const key = `${period}|${country}|${state}|${taxRate}`;\n            \n            if (!taxSummary.has(key)) {\n              taxSummary.set(key, {\n                period,\n                country,\n                state,\n                taxRate,\n                taxableAmount: 0,\n                taxCollected: 0\n              });\n            }\n            \n            const summary = taxSummary.get(key);\n            summary.taxableAmount += (line.amount || 0) / 100; // Convert from cents\n            summary.taxCollected += (taxAmount.amount || 0) / 100; // Convert from cents\n          }\n        } else {\n          // Handle lines without tax (tax-exempt or zero-rate)\n          const key = `${period}|${country}||0`;\n          \n          if (!taxSummary.has(key)) {\n            taxSummary.set(key, {\n              period,\n              country,\n              state: '',\n              taxRate: 0,\n              taxableAmount: 0,\n              taxCollected: 0\n            });\n          }\n          \n          const summary = taxSummary.get(key);\n          summary.taxableAmount += (line.amount || 0) / 100;\n        }\n      }\n    } else {\n      // Handle invoices without detailed line items\n      const key = `${period}|${country}||0`;\n      \n      if (!taxSummary.has(key)) {\n        taxSummary.set(key, {\n          period,\n          country,\n          state: '',\n          taxRate: 0,\n          taxableAmount: 0,\n          taxCollected: 0\n        });\n      }\n      \n      const summary = taxSummary.get(key);\n      summary.taxableAmount += (invoiceItem.total || 0) / 100;\n      summary.taxCollected += (invoiceItem.tax || 0) / 100;\n    }\n  }\n}\n\n// Format results for output\nconst result = Array.from(taxSummary.values()).map(summary => ({\n  json: {\n    period: summary.period,\n    country: summary.country,\n    state: summary.state || '',\n    taxRate: summary.taxRate,\n    taxableAmount: Math.round(summary.taxableAmount * 100) / 100,\n    taxCollected: Math.round(summary.taxCollected * 100) / 100,\n    processingDate: new Date().toISOString().split('T')[0]\n  }\n}));\n\nconsole.log(`Generated ${result.length} tax summary records`);\n\nreturn result.length > 0 ? result : [{ \n  json: { \n    message: 'No tax data found for the period',\n    period: new Date().toISOString().substring(0, 7),\n    processingDate: new Date().toISOString().split('T')[0]\n  } \n}];"
      },
      "typeVersion": 2
    },
    {
      "id": "1b14a11e-dabf-40c4-88af-cbde96cfe81b",
      "name": "データ準備",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        1776,
        96
      ],
      "parameters": {
        "width": 300,
        "height": 536,
        "content": "## 📋 Data Preparation for Sheets\n\nFormats tax summary data for Google Sheets logging.\n\n**Data Mapping:**\n- Period: YYYY-MM format\n- Country: ISO country code\n- State: State/province code\n- Tax Rate: Percentage value\n- Taxable Amount: Dollar amount\n- Tax Collected: Dollar amount\n\n**Data Types:**\n- Strings for identifiers\n- Numbers for monetary values\n- Consistent formatting\n\n**Quality Assurance:**\n- Validates data types\n- Ensures proper formatting\n- Handles null/empty values"
      },
      "typeVersion": 1
    },
    {
      "id": "67fc5b14-e826-488c-9bfa-ef2573af3fa7",
      "name": "Google Sheets 用にデータをフォーマット",
      "type": "n8n-nodes-base.set",
      "position": [
        1824,
        -64
      ],
      "parameters": {
        "options": {},
        "assignments": {
          "assignments": [
            {
              "id": "fc3bb9e0-f7e3-430d-936b-b198e204cf8b",
              "name": "period",
              "type": "string",
              "value": "={{ $json.period }}"
            },
            {
              "id": "f065e7b9-9223-49c8-a1ca-3405483cc2a8",
              "name": "country",
              "type": "string",
              "value": "={{ $json.country }}"
            },
            {
              "id": "a0d0ecca-17d5-470d-8bd4-621d666e0306",
              "name": "state",
              "type": "string",
              "value": "={{ $json.state || '' }}"
            },
            {
              "id": "cd649a01-829d-4205-837f-5e4790860b85",
              "name": "taxRate",
              "type": "number",
              "value": "={{ $json.taxRate }}"
            },
            {
              "id": "ba84aeef-ce9f-4605-98eb-75ba8cf5f3a7",
              "name": "taxableAmount",
              "type": "number",
              "value": "={{ $json.taxableAmount }}"
            },
            {
              "id": "de2a7a75-e865-4157-b41f-8fdd258475a0",
              "name": "taxCollected",
              "type": "number",
              "value": "={{ $json.taxCollected }}"
            },
            {
              "id": "processing-date",
              "name": "processingDate",
              "type": "string",
              "value": "={{ $json.processingDate }}"
            }
          ]
        }
      },
      "typeVersion": 3.3
    },
    {
      "id": "c56bec91-eb1f-4194-92be-7c300542359b",
      "name": "Sheets 連携",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        1952,
        -704
      ],
      "parameters": {
        "width": 300,
        "height": 576,
        "content": "## 📊 Google Sheets Tax Report\n\n**Security Issues:**\n- Hardcoded Google Sheets document ID exposed\n- Personal email in credential name\n\n**Fix Required:**\nReplace hardcoded values with environment variables:\n- `$env.GOOGLE_SHEETS_DOCUMENT_ID`\n- `$env.GOOGLE_SHEETS_SHEET_NAME`\n\n**Sheet Configuration:**\n- Append or update operation\n- Auto-map input data\n- Proper column schema\n- Data type conversion\n\n**Report Features:**\n- Historical tax data\n- Multi-jurisdiction tracking\n- Automated updates\n- Audit trail maintenance"
      },
      "typeVersion": 1
    },
    {
      "id": "92c6ab15-5988-417a-8622-4f531c18f2d8",
      "name": "税務サマリースプレッドシートを更新",
      "type": "n8n-nodes-base.googleSheets",
      "position": [
        2048,
        -64
      ],
      "parameters": {
        "columns": {
          "value": {},
          "schema": [
            {
              "id": "period",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "Period",
              "defaultMatch": true,
              "canBeUsedToMatch": true
            },
            {
              "id": "country",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "Country",
              "defaultMatch": true,
              "canBeUsedToMatch": true
            },
            {
              "id": "state",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "State/Province",
              "defaultMatch": true,
              "canBeUsedToMatch": true
            },
            {
              "id": "taxRate",
              "type": "number",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "Tax Rate (%)",
              "defaultMatch": true,
              "canBeUsedToMatch": true
            },
            {
              "id": "taxableAmount",
              "type": "number",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "Taxable Amount",
              "defaultMatch": false,
              "canBeUsedToMatch": false
            },
            {
              "id": "taxCollected",
              "type": "number",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "Tax Collected",
              "defaultMatch": false,
              "canBeUsedToMatch": false
            },
            {
              "id": "processingDate",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "Processing Date",
              "defaultMatch": false,
              "canBeUsedToMatch": false
            }
          ],
          "mappingMode": "autoMapInputData",
          "matchingColumns": [
            "period",
            "country",
            "state",
            "taxRate"
          ],
          "attemptToConvertTypes": true,
          "convertFieldsToString": false
        },
        "options": {
          "useAppend": false
        },
        "operation": "appendOrUpdate",
        "sheetName": {
          "__rl": true,
          "mode": "list",
          "value": "{{ $env.GOOGLE_SHEETS_SHEET_NAME || 'Tax Summary' }}",
          "cachedResultName": "Tax Summary"
        },
        "documentId": {
          "__rl": true,
          "mode": "list",
          "value": "{{ $env.GOOGLE_SHEETS_DOCUMENT_ID }}",
          "cachedResultName": "Tax Summary Report"
        }
      },
      "credentials": {
        "googleSheetsOAuth2Api": {
          "id": "kpPEOLCGn963qpoh",
          "name": "automations@techdome.ai"
        }
      },
      "typeVersion": 4.3
    },
    {
      "id": "489bb7df-223a-462a-9526-3f7caa35eb8d",
      "name": "成功通知",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        2272,
        112
      ],
      "parameters": {
        "width": 300,
        "height": 540,
        "content": "## 📢 Success Notification Setup\n\n**Security Issues:**\n- Hardcoded Slack channel ID exposed\n- Personal credential reference\n\n**Fix Required:**\nReplace hardcoded values with environment variables:\n- `$env.SLACK_CHANNEL_ID`\n\n**Notification Content:**\n- Processing completion status\n- Record count processed\n- Period information\n- Timestamp for reference\n\n**Message Format:**\n- Professional formatting\n- Key metrics summary\n- Actionable information\n- Clear success indication"
      },
      "typeVersion": 1
    },
    {
      "id": "ab64553d-7549-46b3-8b2d-1e7867f2d992",
      "name": "Slack に成功通知を送信",
      "type": "n8n-nodes-base.slack",
      "position": [
        2272,
        -64
      ],
      "webhookId": "5d751d19-e0dd-48e2-84c5-a95754a2883f",
      "parameters": {
        "text": "✅ **Tax Summary Report Generated Successfully**\n\n📊 **Processing Summary:**\n• Period: {{ $('Calculate Tax Summary by Jurisdiction').first().json.period || 'Current Month' }}\n• Records Processed: {{ $('Calculate Tax Summary by Jurisdiction').all().length }}\n• Total Taxable Amount: ${{ $('Calculate Tax Summary by Jurisdiction').all().reduce((sum, item) => sum + (item.json.taxableAmount || 0), 0).toFixed(2) }}\n• Total Tax Collected: ${{ $('Calculate Tax Summary by Jurisdiction').all().reduce((sum, item) => sum + (item.json.taxCollected || 0), 0).toFixed(2) }}\n\n🕒 **Completed:** {{ new Date().toLocaleString() }}\n📋 **View Report:** Google Sheets updated with latest tax data\n\n💡 Next scheduled run: Tomorrow at 2:00 AM",
        "select": "channel",
        "channelId": {
          "__rl": true,
          "mode": "list",
          "value": "{{ $env.SLACK_CHANNEL_ID }}",
          "cachedResultName": "tax-reports"
        },
        "otherOptions": {
          "mrkdwn": true
        }
      },
      "credentials": {
        "slackApi": {
          "id": "rNqvWj9TfChPVRYY",
          "name": "Slack account vivek"
        }
      },
      "typeVersion": 2.1
    },
    {
      "id": "257f1d24-e589-404e-8ab6-5f0e42ef95db",
      "name": "エラー処理",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        1424,
        320
      ],
      "parameters": {
        "width": 300,
        "height": 504,
        "content": "## ❌ Error Notification Setup\n\n**Security Issues:**\n- Hardcoded Slack channel ID exposed\n- Personal credential reference\n\n**Fix Required:**\nReplace with environment variables:\n- `$env.SLACK_CHANNEL_ID`\n\n**Error Information:**\n- Clear error description\n- Node failure identification\n- Timestamp for troubleshooting\n- Action guidance\n\n**Troubleshooting:**\n- Links to workflow execution\n- Common issue guidance\n- Contact information\n- Next steps for resolution"
      },
      "typeVersion": 1
    },
    {
      "id": "8fcfa5cf-9d4c-4e36-b18e-0bb8450854aa",
      "name": "Slack にエラー通知を送信",
      "type": "n8n-nodes-base.slack",
      "position": [
        1600,
        144
      ],
      "webhookId": "fcb9cae2-b6fe-42b3-904d-8d48155ca630",
      "parameters": {
        "text": "❌ **Tax Summary Workflow Failed**\n\n🚨 **Error Details:**\n• Issue: No invoice data found or data validation failed\n• Possible Causes:\n  - No paid invoices in the last 30 days\n  - Stripe API connection issues\n  - Missing tax configuration in Stripe\n  - Network connectivity problems\n\n🕒 **Failed At:** {{ new Date().toLocaleString() }}\n\n🔧 **Troubleshooting Steps:**\n1. Check Stripe API credentials\n2. Verify invoice data exists for the period\n3. Review Stripe tax configuration\n4. Check n8n execution logs\n\n💬 **Support:** Please check the workflow execution details in n8n dashboard\n⏰ **Next Retry:** Tomorrow at 2:00 AM",
        "select": "channel",
        "channelId": {
          "__rl": true,
          "mode": "list",
          "value": "{{ $env.SLACK_CHANNEL_ID }}",
          "cachedResultName": "tax-reports"
        },
        "otherOptions": {
          "mrkdwn": true
        }
      },
      "credentials": {
        "slackApi": {
          "id": "rNqvWj9TfChPVRYY",
          "name": "Slack account vivek"
        }
      },
      "typeVersion": 2.1
    }
  ],
  "active": false,
  "pinData": {},
  "settings": {
    "executionOrder": "v1"
  },
  "versionId": "d107c22e-17e6-4f0d-9ebe-d4c37e477602",
  "connections": {
    "acac1ac0-6818-4dc4-9d97-52ab8a45103e": {
      "main": [
        [
          {
            "node": "99ff779c-2996-40b0-ac06-849a83de75f1",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "350fd6f2-70d7-4857-b7d5-9dfb3429e161": {
      "main": [
        [
          {
            "node": "d4547ae7-4cff-465e-9502-982ab05be9e9",
            "type": "main",
            "index": 0
          }
        ],
        [
          {
            "node": "8fcfa5cf-9d4c-4e36-b18e-0bb8450854aa",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "67fc5b14-e826-488c-9bfa-ef2573af3fa7": {
      "main": [
        [
          {
            "node": "92c6ab15-5988-417a-8622-4f531c18f2d8",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "92c6ab15-5988-417a-8622-4f531c18f2d8": {
      "main": [
        [
          {
            "node": "ab64553d-7549-46b3-8b2d-1e7867f2d992",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "99ff779c-2996-40b0-ac06-849a83de75f1": {
      "main": [
        [
          {
            "node": "350fd6f2-70d7-4857-b7d5-9dfb3429e161",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "d4547ae7-4cff-465e-9502-982ab05be9e9": {
      "main": [
        [
          {
            "node": "67fc5b14-e826-488c-9bfa-ef2573af3fa7",
            "type": "main",
            "index": 0
          }
        ]
      ]
    }
  }
}