Rechnungs-OCR -> Automatisch zur Tabelle hinzufügen (intern)

• •Google Drive API-Anmeldedaten
• •Möglicherweise sind Ziel-API-Anmeldedaten erforderlich
• •Google Sheets API-Anmeldedaten
• •OpenAI API Key

{
  "id": "<your_workflow_id>",
  "meta": {
    "instanceId": "<your_instance_id>",
    "templateCredsSetupCompleted": true
  },
  "name": "Invoice OCR → Auto Append to Sheets (Internal)",
  "tags": [],
  "nodes": [
    {
      "id": "c79d74f7-f2d7-4e35-891d-c428fcfc5dcc",
      "name": "Zeitplan-Trigger (Wöchentlicher Scan)",
      "type": "n8n-nodes-base.scheduleTrigger",
      "position": [
        368,
        -416
      ],
      "parameters": {
        "rule": {
          "interval": [
            {
              "field": "weeks",
              "triggerAtDay": [
                1
              ],
              "triggerAtHour": 20
            }
          ]
        }
      },
      "typeVersion": 1.2
    },
    {
      "id": "acb76a11-4ee8-468c-8023-a4f886f428c2",
      "name": "Übergeordneten Ordner abrufen",
      "type": "n8n-nodes-base.googleDrive",
      "position": [
        640,
        -416
      ],
      "parameters": {
        "filter": {},
        "options": {},
        "resource": "fileFolder",
        "returnAll": true,
        "queryString": "=支払い請求書自動計算用フォルダ"
      },
      "credentials": {
        "googleDriveOAuth2Api": {
          "id": "<your_google_drive_credential_id>",
          "name": "<your_google_drive_credential_name>"
        }
      },
      "typeVersion": 3
    },
    {
      "id": "499da8a9-f455-4e8e-bf38-f3b5334ec442",
      "name": "Monatlichen Unterordner abrufen",
      "type": "n8n-nodes-base.googleDrive",
      "position": [
        848,
        -416
      ],
      "parameters": {
        "filter": {},
        "options": {},
        "resource": "fileFolder",
        "returnAll": true,
        "queryString": "='{{$json[\"id\"]}}' in parents and mimeType='application/vnd.google-apps.folder' and name contains '{{$now.setZone(\"Asia/Tokyo\").format(\"yyyy年MM月\")}}分'",
        "searchMethod": "query"
      },
      "credentials": {
        "googleDriveOAuth2Api": {
          "id": "<your_google_drive_credential_id>",
          "name": "<your_google_drive_credential_name>"
        }
      },
      "typeVersion": 3,
      "alwaysOutputData": true
    },
    {
      "id": "6c95fb12-8dcd-4df3-a611-d099c3298274",
      "name": "Dateien auflisten",
      "type": "n8n-nodes-base.googleDrive",
      "position": [
        1040,
        -416
      ],
      "parameters": {
        "filter": {},
        "options": {
          "fields": [
            "webViewLink",
            "id",
            "mimeType",
            "name"
          ]
        },
        "resource": "fileFolder",
        "queryString": "='{{$json[\"id\"]}}' in parents and (mimeType='application/pdf' or mimeType contains 'image/')\n",
        "searchMethod": "query"
      },
      "credentials": {
        "googleDriveOAuth2Api": {
          "id": "<your_google_drive_credential_id>",
          "name": "<your_google_drive_credential_name>"
        }
      },
      "typeVersion": 3
    },
    {
      "id": "93009ba2-3af8-4ba0-9044-cf5fa15e0965",
      "name": "Datei herunterladen (Binär von Drive)",
      "type": "n8n-nodes-base.googleDrive",
      "position": [
        1232,
        -416
      ],
      "parameters": {
        "fileId": {
          "__rl": true,
          "mode": "id",
          "value": "={{$json[\"id\"]}}"
        },
        "options": {
          "binaryPropertyName": "data"
        },
        "operation": "download"
      },
      "credentials": {
        "googleDriveOAuth2Api": {
          "id": "<your_google_drive_credential_id>",
          "name": "<your_google_drive_credential_name>"
        }
      },
      "typeVersion": 3
    },
    {
      "id": "b0761836-e013-4728-bb12-dc2e760cfa7f",
      "name": "OCR (OCR.Space Parsing)",
      "type": "n8n-nodes-base.httpRequest",
      "position": [
        1440,
        -416
      ],
      "parameters": {
        "url": "https://api.ocr.space/parse/image",
        "method": "POST",
        "options": {},
        "sendBody": true,
        "contentType": "multipart-form-data",
        "sendHeaders": true,
        "bodyParameters": {
          "parameters": [
            {
              "name": "file",
              "parameterType": "formBinaryData",
              "inputDataFieldName": "data"
            },
            {
              "name": "language",
              "value": "jpn"
            },
            {
              "name": "isOverlayRequired",
              "value": "false"
            },
            {
              "name": "OCREngine",
              "value": "2"
            },
            {
              "name": "isTable",
              "value": "true"
            },
            {
              "name": "scale",
              "value": "true"
            }
          ]
        },
        "headerParameters": {
          "parameters": [
            {
              "name": "=apikey",
              "value": "=<your_ocr_api_key>"
            }
          ]
        }
      },
      "typeVersion": 4.2
    },
    {
      "id": "2f843a4c-82dd-45ae-8be8-b438d93fb8ea",
      "name": "OCR-Text bereinigen (Störungsentfernung)",
      "type": "n8n-nodes-base.code",
      "position": [
        1680,
        -416
      ],
      "parameters": {
        "jsCode": "// 各アイテムごとにOCR結果を整形して返す\nreturn items.map(item => {\n  const parsed = item.json[\"ParsedResults\"]?.[0];\n  let text = parsed?.ParsedText || \"\";\n\n  // ノイズ除去・整形\n  text = text\n    .replace(/\\r/g, \"\\n\")             // 改行コード統一\n    .replace(/\\n{2,}/g, \"\\n\")         // 余分な改行を削除\n    .replace(/[^\\S\\n]+/g, \" \")        // 不要な空白を削除\n    .replace(/　/g, \" \")              // 全角スペースを半角に\n    .replace(/[“”]/g, '\"')            // 変な引用符を統一\n    .replace(/[‘’]/g, \"'\")\n    .replace(/[円¥]/g, \"円\");         // 円記号を統一\n\n  return {\n    json: {\n      text: text.trim()\n    }\n  };\n});\n"
      },
      "typeVersion": 2
    },
    {
      "id": "888bed82-b755-4416-948c-4db664c1b371",
      "name": "AI-Extraktion (Strukturierte JSON generieren)",
      "type": "@n8n/n8n-nodes-langchain.openAi",
      "position": [
        304,
        -32
      ],
      "parameters": {
        "modelId": {
          "__rl": true,
          "mode": "list",
          "value": "gpt-4o-mini",
          "cachedResultName": "GPT-4O-MINI"
        },
        "options": {},
        "messages": {
          "values": [
            {
              "role": "system",
              "content": "You are an AI assistant that analyzes Japanese invoice OCR text and accurately extracts the information required for accounting.\n\n# Input\nBelow is invoice text extracted by OCR.\nIt contains noise, line breaks, and layout issues.\nCarefully reconstruct the content and convert it into structured data as accurately as possible.\n\n---\n{{ $json.text }}\n---\n\n# Output Format\nReturn ONLY the following JSON. Do not include any explanations, chatty text, or code fences.\n\n{\n  \"invoice_date\": \"YYYY-MM-DD\",\n  \"due_date\": \"YYYY-MM-DD\",\n  \"client_name\": \"e.g., Your Client Company\",\n  \"subtotal\": 0,\n  \"tax\": 0,\n  \"total\": 0,\n  \"bank_info\": {\n    \"bank_name\": \"\",\n    \"branch\": \"\",\n    \"account_type\": \"ordinary|checking|savings|unknown\",\n    \"account_number\": \"\",\n    \"account_name\": \"\"\n  },\n  \"items\": [\n    {\n      \"description\": \"\",\n      \"quantity\": 0,\n      \"unit_price\": 0,\n      \"amount\": 0\n    }\n  ],\n  \"notes\": \"\"\n}\n\n# Extraction Rules\n- Identify dates from labels like \"請求日/発行日\" (invoice/issue date) and \"支払期限/お支払い期日\" (due date), then normalize to YYYY-MM-DD.\n- Normalize amounts (subtotal, tax, total) by removing commas, currency symbols, and the \"円\" unit, and output integers. Ensure consistency for tax-inclusive/exclusive totals when needed.\n- For bank info, prioritize extracting the set: bank name, branch, account type, account number, and account holder. Ignore values that cannot be confidently linked.\n- If there are multiple line items, return multiple objects in the items array. When quantity/unit_price/amount are unknown, use 0 (not \"unknown\").\n- Prefer client names that end with honorifics like \"御中\" or \"様\" when present.\n- For unknown fields, use \"unknown\". Always include all keys.\n- Return valid JSON only. No extra characters or leading/trailing newlines.\n"
            },
            {
              "content": "=={{$json.text}}"
            }
          ]
        },
        "jsonOutput": true
      },
      "credentials": {
        "openAiApi": {
          "id": "<your_openai_credential_id>",
          "name": "<your_openai_credential_name>"
        }
      },
      "typeVersion": 1.8
    },
    {
      "id": "2805e6a9-0304-4842-b5b6-eb4996b08dec",
      "name": "AI-Ausgabe in JSON parsen",
      "type": "n8n-nodes-base.code",
      "position": [
        656,
        -32
      ],
      "parameters": {
        "jsCode": "// 各アイテム（PDFごと）を独立して処理\nreturn items.map(item => {\n  const content = item.json.message?.content;\n\n  // JSON構造を安全にパース\n  let parsed = {};\n  try {\n    parsed = typeof content === \"string\" ? JSON.parse(content) : content;\n  } catch (e) {\n    parsed = { error: \"Invalid JSON\", raw: content };\n  }\n\n  // 各アイテムとして出力（個別にスプレッドシートに渡る）\n  return { json: parsed };\n});\n"
      },
      "typeVersion": 2
    },
    {
      "id": "0d2e5f79-3c09-4352-af43-d7d895669991",
      "name": "An Google Sheets anhängen",
      "type": "n8n-nodes-base.googleSheets",
      "position": [
        848,
        -32
      ],
      "parameters": {
        "columns": {
          "value": {
            "合計": "={{ $json.total }}",
            "小計": "={{ $json.subtotal }}",
            "消費税": "={{ $json.tax }}",
            "請求日": "={{ $json.invoice_date }}",
            "銀行名": "={{ $json.bank_info.bank_name }} {{ $json.bank_info.branch }} {{ $json.bank_info.account_type }}",
            "PDFリンク": "={{ $('List Files').item.json.webViewLink }}",
            "取引先名": "={{ $json.client_name }}",
            "口座名義": "={{ $json.bank_info.account_name }}",
            "口座番号": "={{ $json.bank_info.account_number }}",
            "支払期限": "={{ $json.due_date }}"
          },
          "schema": [
            {
              "id": "請求日",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "請求日",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "支払期限",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "支払期限",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "取引先名",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "取引先名",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "小計",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "小計",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "消費税",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "消費税",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "合計",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "合計",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "銀行名",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "銀行名",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "口座番号",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "口座番号",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "口座名義",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "口座名義",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "PDFリンク",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "PDFリンク",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            }
          ],
          "mappingMode": "defineBelow",
          "matchingColumns": [],
          "attemptToConvertTypes": false,
          "convertFieldsToString": false
        },
        "options": {},
        "operation": "append",
        "sheetName": {
          "__rl": true,
          "mode": "name",
          "value": "={{ ($item(0).$node[\"Get Monthly Subfolder\"].json.name || \"請求書台帳\").trim() }}"
        },
        "documentId": {
          "__rl": true,
          "mode": "id",
          "value": "={{ \"<your_google_sheet_id>\" }}\n"
        }
      },
      "credentials": {
        "googleSheetsOAuth2Api": {
          "id": "<your_google_sheets_credential_id>",
          "name": "<your_google_sheets_credential_name>"
        }
      },
      "typeVersion": 4.7
    },
    {
      "id": "07e4ec6a-0526-44e6-a7ef-95b2d3e7cc22",
      "name": "Zeitplan-Trigger (Wiederkehrende Ausführung)",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        272,
        -640
      ],
      "parameters": {
        "color": 3,
        "width": 336,
        "height": 384,
        "content": "## Schedule Trigger (Recurring Execution)\nPurpose\n- Periodically scan the Drive folder that stores invoice PDFs.\n- Can also be executed manually for debugging."
      },
      "typeVersion": 1
    },
    {
      "id": "7175d70d-bd7a-46c5-96c1-71691e215da5",
      "name": "Google Drive-Ordnererkennung",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        624,
        -640
      ],
      "parameters": {
        "color": 2,
        "width": 752,
        "height": 384,
        "content": "## Google Drive Folder Discovery\nPurpose\n- Get the parent folder (e.g., \"支払い請求書自動計算用フォルダ\" meaning \"Folder for automatic invoice calculation\").\n- Auto-detect the current-month subfolder (e.g., a folder named \"2025年10月分\" meaning \"October 2025\").\n- Search PDF/image files inside the target folder."
      },
      "typeVersion": 1
    },
    {
      "id": "7c0a7024-98f7-42f9-8937-4aced9dcbf7a",
      "name": "AI-Extraktion & Sheets-Anhang",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        272,
        -224
      ],
      "parameters": {
        "color": 4,
        "width": 848,
        "height": 384,
        "content": "## AI Structured Extraction & Google Sheets Append\nPurpose\n- Convert OCR results to a strict JSON structure using GPT.\n- Extract invoice_date / due_date / client_name / subtotal / tax / total / bank_info, etc.\n- Normalize with a Code node, then append one row per invoice to Google Sheets."
      },
      "typeVersion": 1
    },
    {
      "id": "bb521714-8b47-4a03-abc0-9e18a471021c",
      "name": "OCR-Verarbeitung (PDF → Textbereinigung)",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        1392,
        -640
      ],
      "parameters": {
        "color": 6,
        "width": 400,
        "height": 384,
        "content": "## OCR Processing (PDF → Text Cleanup)\nPurpose\n- Download Drive files as binary.\n- Use the OCR.Space API for text extraction.\n- Remove noise and normalize line breaks."
      },
      "typeVersion": 1
    }
  ],
  "active": false,
  "pinData": {},
  "settings": {
    "executionOrder": "v1"
  },
  "versionId": "<your_version_id>",
  "connections": {
    "6c95fb12-8dcd-4df3-a611-d099c3298274": {
      "main": [
        [
          {
            "node": "93009ba2-3af8-4ba0-9044-cf5fa15e0965",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "acb76a11-4ee8-468c-8023-a4f886f428c2": {
      "main": [
        [
          {
            "node": "499da8a9-f455-4e8e-bf38-f3b5334ec442",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "499da8a9-f455-4e8e-bf38-f3b5334ec442": {
      "main": [
        [
          {
            "node": "6c95fb12-8dcd-4df3-a611-d099c3298274",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "b0761836-e013-4728-bb12-dc2e760cfa7f": {
      "main": [
        [
          {
            "node": "2f843a4c-82dd-45ae-8be8-b438d93fb8ea",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "2805e6a9-0304-4842-b5b6-eb4996b08dec": {
      "main": [
        [
          {
            "node": "0d2e5f79-3c09-4352-af43-d7d895669991",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "2f843a4c-82dd-45ae-8be8-b438d93fb8ea": {
      "main": [
        [
          {
            "node": "888bed82-b755-4416-948c-4db664c1b371",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "c79d74f7-f2d7-4e35-891d-c428fcfc5dcc": {
      "main": [
        [
          {
            "node": "acb76a11-4ee8-468c-8023-a4f886f428c2",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "93009ba2-3af8-4ba0-9044-cf5fa15e0965": {
      "main": [
        [
          {
            "node": "b0761836-e013-4728-bb12-dc2e760cfa7f",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "888bed82-b755-4416-948c-4db664c1b371": {
      "main": [
        [
          {
            "node": "2805e6a9-0304-4842-b5b6-eb4996b08dec",
            "type": "main",
            "index": 0
          }
        ]
      ]
    }
  }
}