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
          }
        ]
      ]
    }
  }
}