Resumen fiscal de Stripe a Google Sheets con notificaciones en Slack

• •Bot Token de Slack o URL de Webhook
• •Clave de API de Stripe
• •Credenciales de API de Google Sheets

{
  "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": "Descripción General del Flujo de Trabajo de Resumen Fiscal",
      "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": "Programación de Procesamiento",
      "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": "Activador de Procesamiento Fiscal Diario",
      "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": "Obtención de Datos de 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": "Obtener Facturas Pagadas con Datos Fiscales",
      "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": "Validación de Datos",
      "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": "Validar que Existen Datos de Factura",
      "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": "Lógica de Procesamiento Fiscal",
      "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": "Calcular Resumen Fiscal por Jurisdicción",
      "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": "Preparación de Datos",
      "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": "Formatear Datos para 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": "Integración con 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": "Actualizar Hoja de Resumen Fiscal",
      "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": "Notificación de Éxito",
      "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": "Enviar Notificación de Éxito a 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": "Manejo de Errores",
      "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": "Enviar Notificación de Error a 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
          }
        ]
      ]
    }
  }
}