自動領収書リマインダー

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

{
  "id": "n3Pu61XoZKS5Rnqz",
  "meta": {
    "instanceId": "a287613f1596da776459594685fbf4e2b4a12124f80ab8c8772f5e37bff103ae",
    "templateCredsSetupCompleted": true
  },
  "name": "Automated Invoice Reminder",
  "tags": [],
  "nodes": [
    {
      "id": "4715b40c-0219-4c12-825a-bd17f18201e6",
      "name": "1. デイリースケジュールトリガー",
      "type": "n8n-nodes-base.scheduleTrigger",
      "position": [
        -380,
        260
      ],
      "parameters": {
        "rule": {
          "interval": [
            {}
          ]
        }
      },
      "description": "Triggers the workflow daily to check for invoices.",
      "typeVersion": 1
    },
    {
      "id": "49793210-3a62-4aa5-8148-e4b4b61c7608",
      "name": "2. 請求書データ読み込み（Google Sheets）",
      "type": "n8n-nodes-base.googleSheets",
      "position": [
        -140,
        260
      ],
      "parameters": {
        "range": "Invoices!A:F",
        "options": {},
        "sheetId": "YOUR_GOOGLE_SHEET_ID"
      },
      "credentials": {
        "googleSheetsOAuth2Api": {
          "id": "MSnszF5oRAiSGHDo",
          "name": "temp"
        }
      },
      "description": "Reads invoice details from your specified Google Sheet. Make sure your sheet has columns like: InvoiceID, ClientName, ClientEmail, Amount, DueDate, Status.",
      "typeVersion": 2
    },
    {
      "id": "0b9b181c-817f-4b93-8f5e-8362409a6189",
      "name": "3. リマインダーのフィルタリングと準備",
      "type": "n8n-nodes-base.function",
      "position": [
        120,
        260
      ],
      "parameters": {
        "functionCode": "const now = new Date();\nnow.setHours(0, 0, 0, 0); // Normalize to start of day for accurate comparison\n\nconst remindBeforeDays = 3; // Remind 3 days before due date\nconst remindAfterDays = 7; // Remind up to 7 days after due date\n\nconst itemsToRemind = [];\n\nfor (const item of items) {\n  const invoice = item.json;\n  \n  // --- ASSUMPTIONS: Adjust these field names to match your Google Sheet columns ---\n  const invoiceId = invoice.InvoiceID;\n  const clientName = invoice.ClientName || 'Pelanggan Yth.'; // Default name if not provided\n  const clientEmail = invoice.ClientEmail;\n  const amount = invoice.Amount;\n  const dueDateStr = invoice.DueDate; // e.g., 'YYYY-MM-DD' or 'MM/DD/YYYY'\n  const status = invoice.Status; // e.g., 'Paid', 'Pending', 'Overdue'\n\n  // Skip if already paid or missing critical info\n  if (!invoiceId || !clientEmail || !amount || !dueDateStr || (status && status.toLowerCase() === 'paid')) {\n    continue;\n  }\n\n  const dueDate = new Date(dueDateStr);\n  if (isNaN(dueDate.getTime())) { // Check for invalid date\n    console.warn(`Invalid DueDate for InvoiceID ${invoiceId}: ${dueDateStr}`);\n    continue;\n  }\n  dueDate.setHours(0, 0, 0, 0); // Normalize to start of day\n\n  const timeDiff = dueDate.getTime() - now.getTime();\n  const daysDiff = Math.ceil(timeDiff / (1000 * 60 * 60 * 24)); // Days until due date (positive) or since due date (negative)\n\n  let reminderType = null;\n  let subjectPrefix = '';\n  let bodyText = '';\n\n  if (daysDiff >= 0 && daysDiff <= remindBeforeDays) {\n    reminderType = 'due_soon'; // Invoice due in X days\n    subjectPrefix = `Pengingat: Faktur #${invoiceId} Akan Jatuh Tempo!`;\n    bodyText = `Faktur Anda #${invoiceId} sebesar Rp${amount} akan jatuh tempo pada ${dueDateStr}. Mohon segera lakukan pembayaran.`;\n  } else if (daysDiff < 0 && daysDiff >= -remindAfterDays) {\n    reminderType = 'overdue'; // Invoice overdue by X days\n    subjectPrefix = `Segera! Faktur #${invoiceId} Sudah Jatuh Tempo!`;\n    bodyText = `Faktur Anda #${invoiceId} sebesar Rp${amount} telah melewati jatuh tempo pada ${dueDateStr}. Mohon segera lakukan pembayaran Anda.`;\n  }\n\n  if (reminderType) {\n    itemsToRemind.push({\n      json: {\n        clientName: clientName,\n        clientEmail: clientEmail,\n        invoiceId: invoiceId,\n        amount: amount,\n        dueDate: dueDateStr,\n        reminderType: reminderType,\n        daysDiff: Math.abs(daysDiff),\n        subject: subjectPrefix,\n        body: bodyText\n      }\n    });\n  }\n}\n\nreturn itemsToRemind;"
      },
      "description": "Filters invoices that are due soon or overdue, and prepares email content for each.",
      "typeVersion": 1
    },
    {
      "id": "70a688ea-b6c3-46fc-8784-6dbb31ee5602",
      "name": "4. リマインダー対象の請求書あり？",
      "type": "n8n-nodes-base.if",
      "position": [
        380,
        260
      ],
      "parameters": {},
      "description": "Checks if there are any invoices that need reminders.",
      "typeVersion": 1
    },
    {
      "id": "b1a9f0fb-952a-4ca9-b71e-1aa4e6601f74",
      "name": "5. 請求書リマインダー送信（Gmail）",
      "type": "n8n-nodes-base.gmail",
      "position": [
        620,
        260
      ],
      "parameters": {
        "subject": "={{ $json.subject }}",
        "additionalFields": {}
      },
      "credentials": {
        "gmailOAuth2": {
          "id": "QFfaHpKmgq4YPiRN",
          "name": "Temp"
        }
      },
      "description": "Sends a personalized invoice reminder email to the client.",
      "typeVersion": 1
    },
    {
      "id": "b3c00aab-cfc2-4d30-97d7-02cffdb8311d",
      "name": "付箋",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -480,
        200
      ],
      "parameters": {
        "color": 3,
        "width": 1300,
        "height": 240,
        "content": "## Flow"
      },
      "typeVersion": 1
    },
    {
      "id": "e227328a-de6b-429c-a6be-1dfefb9e85b9",
      "name": "付箋1",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -480,
        480
      ],
      "parameters": {
        "color": 5,
        "width": 940,
        "height": 2400,
        "content": "# Workflow Documentation: Automated Invoice Reminder\n\n## Problem 😩\nBusinesses often struggle with delayed payments due to outstanding invoices. Manually tracking due dates and sending timely reminders is a repetitive, time-consuming task that can lead to:\n* **Cash flow issues:** Funds are tied up in unpaid invoices.\n* **Lost time:** Staff spend hours on manual accounts receivable.\n* **Missed opportunities:** Focus shifts from growth to debt collection.\n* **Awkward client interactions:** Late reminders can feel less professional.\n\n## Solution ✨\nThis n8n workflow automates the crucial process of sending invoice reminders. It periodically checks your invoice data and automatically dispatches personalized email reminders to clients whose invoices are either:\n* **Due soon:** Reminding them before the due date.\n* **Overdue:** Prompting for payment if the due date has passed.\n\nThis ensures timely payments, improves cash flow, and frees up your team to focus on core business activities.\n\n## For Who 🤝\nThis workflow is perfect for:\n* **Freelancers & Consultants:** To ensure timely payment for services.\n* **Small & Medium Businesses (SMBs):** To automate accounts receivable and improve cash flow.\n* **Agencies:** To streamline billing processes and reduce administrative burden.\n* **Anyone** who manages invoices and wants to simplify payment collection.\n\n## Scope 🎯\n* **Input Data:** Invoice details (e.g., InvoiceID, ClientName, ClientEmail, Amount, DueDate, Status) from a Google Sheet.\n* **Trigger:** Daily scheduled check.\n* **Logic:** Filters invoices due within a configurable window (e.g., 3 days before and 7 days after the due date) and skips already paid invoices.\n* **Output:** Personalized email reminders sent via Gmail (or another email service).\n* **Customization:** Easily adaptable to different email templates and reminder logic.\n\n## How It Works ⚙️\n\nThis workflow systematically ensures you never miss sending an invoice reminder:\n\n1.  **Daily Schedule Trigger:** ⏰ The workflow starts automatically at a set time each day (e.g., every morning). This ensures continuous monitoring of your invoice statuses.\n2.  **Read Invoice Data (Google Sheets):** 📊 The workflow connects to your specified Google Sheet to retrieve a list of all your invoices and their details. **Ensure your sheet has required columns like `InvoiceID`, `ClientName`, `ClientEmail`, `Amount`, `DueDate`, and `Status`.**\n3.  **Filter & Prepare Reminders (Function):** 🧹 This is the core logic. It processes each invoice row:\n    * Compares the `DueDate` with the current date.\n    * Identifies invoices that are due soon (e.g., within 3 days) or are already overdue (e.g., up to 7 days past due).\n    * Skips invoices marked as 'Paid'.\n    * Prepares a custom subject line and email body for each relevant reminder.\n4.  **If Invoices to Remind?:** 🚦 This node acts as a gate. If the previous step found any invoices needing reminders, the workflow proceeds. If not, it stops gracefully.\n5.  **Send Invoice Reminder (Gmail):** 📧 For each filtered invoice, this node sends a personalized email reminder to the client. The email uses the dynamic subject and body prepared in the 'Filter & Prepare Reminders' step.\n\n## How to Set Up 🛠️\n\nFollow these steps carefully to get your \"Automated Invoice Reminder\" workflow up and running:\n\n1.  **Import Workflow JSON:**\n    * Open your n8n instance.\n    * Click on 'Workflows' in the left sidebar.\n    * Click the '+' button or 'New' to create a new workflow.\n    * Click the '...' (More Options) icon in the top right.\n    * Select 'Import from JSON' and paste this entire JSON code (from the previous response).\n\n2.  **Configure Daily Schedule Trigger:**\n    * Locate the 'Daily Schedule Trigger' node (1. Daily Schedule Trigger).\n    * **Adjust 'interval', 'value', and 'timezone'** to your preferred daily reminder time (e.g., every 24 hours at 9 AM in your local timezone).\n\n3.  **Configure Read Invoice Data (Google Sheets):**\n    * Locate the 'Read Invoice Data (Google Sheets)' node (2. Read Invoice Data).\n    * **Credentials:** Select your existing Google Sheets OAuth2 credential or click 'Create New' to set one up. Replace `YOUR_GOOGLE_SHEETS_CREDENTIAL_ID` with the actual ID or name of your credential.\n    * **Sheet ID:** Replace `YOUR_GOOGLE_SHEET_ID` with the actual ID of your Google Sheet where invoice data is stored.\n    * **Range:** Ensure the 'range' (e.g., `Invoices!A:F`) correctly covers all your invoice data. **Crucially, ensure your Google Sheet has columns with exact names: `InvoiceID`, `ClientName`, `ClientEmail`, `Amount`, `DueDate` (in a parsable date format like YYYY-MM-DD), and `Status` (e.g., 'Pending', 'Paid').**\n\n4.  **Configure Filter & Prepare Reminders (Function):**\n    * Locate the 'Filter & Prepare Reminders' node (3. Filter & Prepare Reminders).\n    * **Date & Field Names:** Review the `functionCode`. **Adjust the variable names (e.g., `invoice.InvoiceID`, `invoice.DueDate`)** if your Google Sheet uses different column headers.\n    * **Reminder Window:** You can modify `remindBeforeDays` and `remindAfterDays` to adjust how many days before/after the due date reminders are sent.\n    * **Email Content:** Modify the `subjectPrefix` and `bodyText` within the code to customize the reminder message for 'due soon' and 'overdue' invoices.\n\n5.  **Configure Send Invoice Reminder (Gmail):**\n    * Locate the 'Send Invoice Reminder (Gmail)' node (5. Send Invoice Reminder).\n    * **Credentials:** Select your existing Gmail OAuth2 credential or click 'Create New'. Replace `YOUR_GMAIL_CREDENTIAL_ID` with the actual ID or name of your credential.\n    * **From Email:** Replace `YOUR_SENDER_EMAIL@example.com` with the email address you want the reminders to be sent from.\n    * **Email Content:** The 'subject' and 'html' fields are dynamically generated by the previous 'Function' node (`={{ $json.subject }}` and `={{ $json.body }}`). You can further customize the HTML email template here if needed.\n\n6.  **Review and Activate:**\n    * Thoroughly review all node configurations. Ensure all placeholder values (like `YOUR_...`) are replaced and settings are correct.\n    * Click the 'Save' button in the top right corner.\n    * Finally, toggle the 'Inactive' switch to 'Active' to enable your workflow. 🟢 Your automated invoice reminder is now live and ready to improve your cash flow!\n\n**Troubleshooting Tips:** 💡\n* **Execution History:** Always check the 'Executions' tab in n8n for detailed error messages if the workflow fails.\n* **Google Sheet Data:** Ensure your Google Sheet data is clean and matches the expected column headers and date formats.\n* **Function Node Logic:** If invoices aren't being filtered correctly, the `Function` node is the place to debug. Use the 'Test Workflow' feature to inspect the data flowing into and out of this node.\n* **Credential Issues:** Double-check that all credentials are correctly set up and active in n8n."
      },
      "typeVersion": 1
    }
  ],
  "active": false,
  "pinData": {},
  "settings": {
    "executionOrder": "v1"
  },
  "versionId": "a2ff031f-c190-4ca6-b3ee-06b6c3dd7c21",
  "connections": {
    "4715b40c-0219-4c12-825a-bd17f18201e6": {
      "main": [
        [
          {
            "node": "49793210-3a62-4aa5-8148-e4b4b61c7608",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "70a688ea-b6c3-46fc-8784-6dbb31ee5602": {
      "main": [
        [
          {
            "node": "b1a9f0fb-952a-4ca9-b71e-1aa4e6601f74",
            "type": "main",
            "index": 0
          }
        ],
        []
      ]
    },
    "0b9b181c-817f-4b93-8f5e-8362409a6189": {
      "main": [
        [
          {
            "node": "70a688ea-b6c3-46fc-8784-6dbb31ee5602",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "49793210-3a62-4aa5-8148-e4b4b61c7608": {
      "main": [
        [
          {
            "node": "0b9b181c-817f-4b93-8f5e-8362409a6189",
            "type": "main",
            "index": 0
          }
        ]
      ]
    }
  }
}