Automatisation du traitement et de l'analyse des notes de frais de déplacement des employés avec Jotform et OpenAI

• •Compte Google et informations d'identification Gmail API
• •Informations d'identification Google Drive API
• •Informations d'identification Google Sheets API
• •Clé API OpenAI

{
  "id": "Xxg3JlB1tLokdTLX",
  "meta": {
    "instanceId": "277842713620d9f5554de3b1518b865a152c8c4db680008bd8aec536fc18b4a8"
  },
  "name": "Automated Employee Trip Expense Reporting/Parsing with Jotform, OpenAI",
  "tags": [
    {
      "id": "KDDDkIQdfPrxgNg7",
      "name": "Finance Workflow",
      "createdAt": "2025-10-13T16:01:43.475Z",
      "updatedAt": "2025-10-13T16:01:43.475Z"
    }
  ],
  "nodes": [
    {
      "id": "ae436ed6-120f-48be-b7e7-e804e9d7971a",
      "name": "Note adhésive5",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        2304,
        464
      ],
      "parameters": {
        "color": 6,
        "width": 892,
        "height": 496,
        "content": "## Extract and Parse Invoices with AI Agent\n\nUploaded PDF receipts are processed using OCR and AI. The DocClaim Assistant extracts key invoice details such as vendor, date, amount, and itemized lines, then outputs them in a clean, structured JSON format ready for further processing."
      },
      "typeVersion": 1
    },
    {
      "id": "02b1d929-bda6-4a7f-a65c-f43de6240c04",
      "name": "Note adhésive",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        3952,
        496
      ],
      "parameters": {
        "color": 3,
        "width": 556,
        "height": 336,
        "content": "## Send Claim Summary to Finance Team\n\nA formatted email with the employee’s business trip and expense breakdown is sent to the finance team for review and reimbursement. The email includes vendor names, invoice totals, dates, and itemized expense tables."
      },
      "typeVersion": 1
    },
    {
      "id": "22dcfa29-8e16-408a-9b92-34a48b2658cf",
      "name": "Transformer la sortie",
      "type": "n8n-nodes-base.code",
      "position": [
        3376,
        640
      ],
      "parameters": {
        "jsCode": "// Static or dynamic employee information\nconst profile = $('On form submission').first().json;\nconst employee = {\n  name: profile[\"Employee Name\"],\n  department: profile[\"Department\"],\n  tripPurpose: profile[\"Trip Purpose\"],\n  fromDate: profile[\"From Date\"],\n  toDate: profile[\"To Date\"],\n};\n\n// Collect parsed expense outputs\nconst items = $input.all();\nconst expenses = items.map((item, index) => {\n  const data = item.json.output;\n\n  return {\n    index: index + 1,\n    expenseType: data.expense_type || \"\",\n    vendor: data.vendor_name || \"\",\n    invoiceNumber: data.invoice_number || \"\",\n    receiptNumber: data.receipt_number || \"\",\n    issueDate: data.issue_date || \"\",\n    totalAmount: data.total_amount || 0,\n    currency: data.currency || \"\",\n    taxAmount: data.tax_amount || 0,\n    paymentMethod: data.payment_method || \"\",\n    location: data.location || \"\",\n    notes: data.notes || \"\",\n    items: data.itemized_descriptions || []\n  };\n});\n\nreturn [\n  {\n    json: {\n      employee,\n      expenses\n    }\n  }\n];"
      },
      "typeVersion": 2
    },
    {
      "id": "8e2b4203-b162-4a1b-97ae-4d99c40cb4ee",
      "name": "Téléverser le fichier",
      "type": "n8n-nodes-base.googleDrive",
      "position": [
        2448,
        256
      ],
      "parameters": {
        "name": "=invoice-{{ $now.toFormat(\"yyyyLLdd-HHmmss\") }}-{{$binary.data.fileName}}",
        "driveId": {
          "__rl": true,
          "mode": "list",
          "value": "My Drive"
        },
        "options": {},
        "folderId": {
          "__rl": true,
          "mode": "list",
          "value": "1IPcko8bzogO3W4mxhrW2Q017QA0Lc5MI",
          "cachedResultUrl": "https://drive.google.com/drive/folders/1IPcko8bzogO3W4mxhrW2Q017QA0Lc5MI",
          "cachedResultName": "SmartSales"
        }
      },
      "typeVersion": 3
    },
    {
      "id": "f466932a-518c-42fb-b4d5-5336fe13217e",
      "name": "Note adhésive9",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        2304,
        112
      ],
      "parameters": {
        "color": 5,
        "width": 892,
        "height": 320,
        "content": "## Store Invoices in Google Sheet\n\nThe parsed invoice records are transformed and appended to a Google Sheet for tracking, auditing, or reimbursement purposes. Each uploaded receipt is saved with relevant trip metadata and financial details."
      },
      "typeVersion": 1
    },
    {
      "id": "e22da4b4-536b-49f5-9fd5-ad9e42fa916b",
      "name": "Note adhésive10",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        3264,
        496
      ],
      "parameters": {
        "color": 7,
        "width": 620,
        "height": 336,
        "content": "## Transform and Generate HTML Email\n\nThis step prepares a professional email summary by combining employee trip details and all extracted expense information. The result is an HTML email template suitable for sending to the finance department."
      },
      "typeVersion": 1
    },
    {
      "id": "7e56bf01-4ba6-4beb-919b-786ce0ad2011",
      "name": "Extraire du fichier",
      "type": "n8n-nodes-base.extractFromFile",
      "position": [
        2448,
        640
      ],
      "parameters": {
        "options": {},
        "operation": "pdf"
      },
      "typeVersion": 1
    },
    {
      "id": "a384703f-fd64-4743-ad74-283d38d2ab1a",
      "name": "Ajouter une ligne dans la feuille",
      "type": "n8n-nodes-base.googleSheets",
      "position": [
        2928,
        256
      ],
      "parameters": {
        "columns": {
          "value": {},
          "schema": [
            {
              "id": "EmployeeName",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "EmployeeName",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "Department",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "Department",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "TripPurpose",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "TripPurpose",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "FromDate",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "FromDate",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "ToDate",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "ToDate",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "FileName",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "FileName",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "DownloadURL",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "DownloadURL",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "Size",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "Size",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "SubmittedAt",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "SubmittedAt",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            }
          ],
          "mappingMode": "autoMapInputData",
          "matchingColumns": [],
          "attemptToConvertTypes": false,
          "convertFieldsToString": false
        },
        "options": {},
        "operation": "append",
        "sheetName": {
          "__rl": true,
          "mode": "list",
          "value": "gid=0",
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/1qk6OebcuZkIRorf1k235ew88oZ-UlUJSyiHFqZYDbaU/edit#gid=0",
          "cachedResultName": "Sheet1"
        },
        "documentId": {
          "__rl": true,
          "mode": "list",
          "value": "1qk6OebcuZkIRorf1k235ew88oZ-UlUJSyiHFqZYDbaU",
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/1qk6OebcuZkIRorf1k235ew88oZ-UlUJSyiHFqZYDbaU/edit?usp=drivesdk",
          "cachedResultName": "Invoices Tracking"
        }
      },
      "typeVersion": 4.6
    },
    {
      "id": "67200fe9-b12a-4c5a-998d-1b1791771f2b",
      "name": "Transformer l'enregistrement de facture",
      "type": "n8n-nodes-base.code",
      "position": [
        2688,
        256
      ],
      "parameters": {
        "mode": "runOnceForEachItem",
        "jsCode": "const claimForm = $('On form submission').first().json;\nreturn {\n      EmployeeName: claimForm[\"Employee Name\"],\n      Department: claimForm[\"Department\"],\n      TripPurpose: claimForm[\"Trip Purpose\"],\n      FromDate: claimForm[\"From Date\"],\n      ToDate: claimForm[\"To Date\"],\n      FileName: $json.name,\n      DownloadURL: $json.webContentLink,\n      Size: $json.size,\n      SubmittedAt: claimForm[\"submittedAt\"]\n    }"
      },
      "typeVersion": 2
    },
    {
      "id": "87b360c0-219a-451e-8035-7de9a3d1d1c5",
      "name": "Analyseur de sortie structurée",
      "type": "@n8n/n8n-nodes-langchain.outputParserStructured",
      "position": [
        2928,
        816
      ],
      "parameters": {
        "jsonSchemaExample": "{\n  \"expense_type\": \"Hotel\",\n  \"vendor_name\": \"Grand Central Hotel\",\n  \"invoice_number\": \"INV-20250802-001\",\n  \"receipt_number\": \"RCPT-56789\",\n  \"issue_date\": \"2025-08-02\",\n  \"total_amount\": 150.00,\n  \"tax_amount\": 15.00,\n  \"currency\": \"USD\",\n  \"payment_method\": \"Credit Card\",\n  \"location\": \"Hanoi, Vietnam\",\n  \"trip_purpose\": \"Client Meeting\",\n  \"trip_dates\": {\n    \"from\": \"2025-08-02\",\n    \"to\": \"2025-08-03\"\n  },\n  \"itemized_descriptions\": [\n    {\n      \"category\": \"Accommodation\",\n      \"description\": \"1 night stay - Deluxe Room\",\n      \"quantity\": 1,\n      \"unit_price\": 120.00,\n      \"line_total\": 120.00\n    },\n    {\n      \"category\": \"Service Charge\",\n      \"description\": \"Hotel service fee\",\n      \"quantity\": 1,\n      \"unit_price\": 10.00,\n      \"line_total\": 10.00\n    },\n    {\n      \"category\": \"Tax\",\n      \"description\": \"VAT 10%\",\n      \"quantity\": 1,\n      \"unit_price\": 15.00,\n      \"line_total\": 15.00\n    }\n  ],\n  \"notes\": \"\"\n}"
      },
      "typeVersion": 1.3
    },
    {
      "id": "1f1e51c8-918b-4284-b5b2-72cfceb4fb46",
      "name": "Créer un modèle d'email HTML",
      "type": "n8n-nodes-base.code",
      "position": [
        3664,
        640
      ],
      "parameters": {
        "jsCode": "const data = $input.first().json;\n\nconst { employee, expenses } = data;\n\n// Helper to generate expense summary rows\nconst expenseSummaryRows = expenses.map(expense => `\n  <tr>\n    <td>${expense.index}</td>\n    <td>${expense.expenseType}</td>\n    <td>${expense.vendor}</td>\n    <td>${expense.issueDate}</td>\n    <td>${expense.totalAmount}</td>\n    <td>${expense.currency}</td>\n    <td>${expense.taxAmount}</td>\n    <td>${expense.paymentMethod}</td>\n  </tr>\n`).join(\"\");\n\n// Helper to generate detailed tables for each expense\nconst expenseDetailSections = expenses.map(expense => {\n  const itemRows = (expense.items || []).map(item => `\n    <tr>\n      <td>${item.category}</td>\n      <td>${item.description}</td>\n      <td>${item.quantity}</td>\n      <td>${item.unit_price}</td>\n      <td>${item.line_total}</td>\n    </tr>\n  `).join(\"\");\n\n  return `\n    <div style=\"margin-top:32px;\">\n      <h4>Details for Expense #${expense.index} - ${expense.expenseType}</h4>\n      <p><strong>Vendor:</strong> ${expense.vendor}</p>\n      <p><strong>Invoice No.:</strong> ${expense.invoiceNumber}</p>\n      <p><strong>Receipt No.:</strong> ${expense.receiptNumber}</p>\n      <p><strong>Location:</strong> ${expense.location}</p>\n      <p><strong>Notes:</strong> ${expense.notes}</p>\n      <table border=\"1\" cellpadding=\"6\" cellspacing=\"0\" width=\"100%\" style=\"border-collapse:collapse;margin-top:10px;\">\n        <thead>\n          <tr style=\"background-color:#f4f4f4;\">\n            <th>Category</th>\n            <th>Description</th>\n            <th>Qty</th>\n            <th>Unit Price</th>\n            <th>Line Total</th>\n          </tr>\n        </thead>\n        <tbody>${itemRows}</tbody>\n      </table>\n    </div>\n  `;\n}).join(\"\");\n\nconst html = `\n<!DOCTYPE html>\n<html>\n<head>\n  <meta charset=\"UTF-8\" />\n  <style>\n    body { font-family: Arial, sans-serif; color: #333; }\n    h2 { color: #0077b6; }\n    table { width: 100%; border-collapse: collapse; margin-bottom: 24px; }\n    th, td { padding: 8px; border: 1px solid #ddd; font-size: 14px; }\n    th { background-color: #f4f4f4; text-align: left; }\n    h4 { margin-bottom: 5px; margin-top: 30px; color: #444; }\n  </style>\n</head>\n<body>\n\n  <h2>Expense Claim Request</h2>\n\n  <p><strong>Employee Name:</strong> ${employee.name}</p>\n  <p><strong>Department:</strong> ${employee.department}</p>\n  <p><strong>Trip Purpose:</strong> ${employee.tripPurpose}</p>\n  <p><strong>Trip Dates:</strong> ${employee.fromDate} to ${employee.toDate}</p>\n\n  <h3>Expense Summary</h3>\n  <table>\n    <thead>\n      <tr>\n        <th>#</th>\n        <th>Type</th>\n        <th>Vendor</th>\n        <th>Issue Date</th>\n        <th>Total</th>\n        <th>Currency</th>\n        <th>Tax</th>\n        <th>Payment</th>\n      </tr>\n    </thead>\n    <tbody>\n      ${expenseSummaryRows}\n    </tbody>\n  </table>\n\n  ${expenseDetailSections}\n\n  <p style=\"margin-top:32px;\">Thank you,<br/>${employee.name}</p>\n\n</body>\n</html>\n`;\n\nreturn [{ json: { html } }];"
      },
      "typeVersion": 2
    },
    {
      "id": "8869ddc8-9058-4fd0-a9f6-fd8cdf4ce152",
      "name": "Gérer les fichiers multiples",
      "type": "n8n-nodes-base.code",
      "position": [
        2080,
        496
      ],
      "parameters": {
        "jsCode": "const data = $input.item.json;\nconst binaryData = $input.item.binary;\n\nlet output = [];\n\nObject.keys(binaryData)\n  .filter(label => label.startsWith(\"Receipts___Invoices_\"))\n  .forEach(label => {\n    output.push({\n      json: data,\n      binary: { data: binaryData[label] }\n    });\n  });\n\nreturn output;"
      },
      "typeVersion": 2
    },
    {
      "id": "55eb7432-ce93-470a-89f3-b97eba58f0bb",
      "name": "GPT",
      "type": "@n8n/n8n-nodes-langchain.lmChatOpenAi",
      "position": [
        2704,
        816
      ],
      "parameters": {
        "model": {
          "__rl": true,
          "mode": "list",
          "value": "gpt-4.1-mini",
          "cachedResultName": "gpt-4.1-mini"
        },
        "options": {}
      },
      "credentials": {
        "openAiApi": {
          "id": "8IkhtT3EbXygnvcr",
          "name": "Klinsman OpenAI"
        }
      },
      "typeVersion": 1.2
    },
    {
      "id": "2f1a67be-2e59-4c6c-b3a5-1e0604c0b45a",
      "name": "Déclencheur JotForm",
      "type": "n8n-nodes-base.jotFormTrigger",
      "position": [
        1760,
        496
      ],
      "webhookId": "fd91b6ad-cbf1-4889-b652-49a03a911722",
      "parameters": {
        "form": "252815424602048"
      },
      "credentials": {
        "jotFormApi": {
          "id": "cOSh16Q5l4e0EB1A",
          "name": "Jotform jitesh@mediajade.com"
        }
      },
      "typeVersion": 1
    },
    {
      "id": "b84f4ee1-1b5c-462a-aa28-a3728f0278b1",
      "name": "Envoyer un message",
      "type": "n8n-nodes-base.gmail",
      "position": [
        4160,
        656
      ],
      "webhookId": "fde82d4c-3a00-47f1-a5d0-af76e3916ba0",
      "parameters": {
        "message": "={{ $json.html }}",
        "options": {},
        "subject": "=Expense Claim Request - {{ $('Transform Output').item.json.employee.name }} – {{ $('Transform Output').item.json.employee.department }} - {{ $('Transform Output').item.json.employee.tripPurpose }}"
      },
      "credentials": {
        "gmailOAuth2": {
          "id": "PIMDNhXNj8Zyiz3G",
          "name": "Gmail account - Deepanshi"
        }
      },
      "typeVersion": 2.1
    },
    {
      "id": "5d3b5722-a2a5-4bfe-91a6-629debcfe895",
      "name": "Note adhésive1",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        1648,
        304
      ],
      "parameters": {
        "width": 336,
        "height": 144,
        "content": "## Jotform Trigger\nJotform account with expense form setup [Sign up for free here](https://www.jotform.com/?partner=mediajade)"
      },
      "typeVersion": 1
    },
    {
      "id": "c4147239-207f-414e-b185-61137351bde9",
      "name": "Extracteur de documents",
      "type": "@n8n/n8n-nodes-langchain.agent",
      "position": [
        2720,
        640
      ],
      "parameters": {
        "text": "=Extract all relevant information from thisreceipt or invoice below:\n---\n{{ $json.text }}\n---\nI need the output in a structured format suitable for generating a business expense claim report. Include fields like vendor name, invoice or receipt number, date, total amount, tax, payment method, currency, and item descriptions.",
        "options": {
          "systemMessage": "You are an intelligent document extraction assistant trained to accurately parse receipts and invoices from uploaded PDF files.\n\nYour task is to extract all relevant financial and business details typically required for expense reporting, including but not limited to:\n\nVendor Name\n\nInvoice Number / Receipt Number\n\nDate of Issue\n\nTotal Amount\n\nTax Amount\n\nCurrency\n\nPayment Method\n\nItemized Descriptions (each with name, quantity, price, and tax if available)\n\nInstructions:\n\nOutput the extracted data in a clean, structured JSON format.\n\nUse consistent and standardized field names (e.g., vendor_name, invoice_number, date, total_amount, currency, etc.).\n\nIf any field is missing or unreadable, include it with a null or empty string value — do not omit it.\n\nExclude all decorative, explanatory, or non-financial text.\n\nFocus on accuracy, completeness, and format consistency."
        },
        "promptType": "define",
        "hasOutputParser": true
      },
      "typeVersion": 2.1
    }
  ],
  "active": false,
  "pinData": {},
  "settings": {
    "executionOrder": "v1"
  },
  "versionId": "65220257-464a-4790-8da1-35c58e263ceb",
  "connections": {
    "55eb7432-ce93-470a-89f3-b97eba58f0bb": {
      "ai_languageModel": [
        [
          {
            "node": "c4147239-207f-414e-b185-61137351bde9",
            "type": "ai_languageModel",
            "index": 0
          }
        ]
      ]
    },
    "8e2b4203-b162-4a1b-97ae-4d99c40cb4ee": {
      "main": [
        [
          {
            "node": "67200fe9-b12a-4c5a-998d-1b1791771f2b",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "b84f4ee1-1b5c-462a-aa28-a3728f0278b1": {
      "main": [
        []
      ]
    },
    "2f1a67be-2e59-4c6c-b3a5-1e0604c0b45a": {
      "main": [
        [
          {
            "node": "8869ddc8-9058-4fd0-a9f6-fd8cdf4ce152",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "22dcfa29-8e16-408a-9b92-34a48b2658cf": {
      "main": [
        [
          {
            "node": "1f1e51c8-918b-4284-b5b2-72cfceb4fb46",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "7e56bf01-4ba6-4beb-919b-786ce0ad2011": {
      "main": [
        [
          {
            "node": "c4147239-207f-414e-b185-61137351bde9",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "c4147239-207f-414e-b185-61137351bde9": {
      "main": [
        [
          {
            "node": "22dcfa29-8e16-408a-9b92-34a48b2658cf",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "8869ddc8-9058-4fd0-a9f6-fd8cdf4ce152": {
      "main": [
        [
          {
            "node": "8e2b4203-b162-4a1b-97ae-4d99c40cb4ee",
            "type": "main",
            "index": 0
          },
          {
            "node": "7e56bf01-4ba6-4beb-919b-786ce0ad2011",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "87b360c0-219a-451e-8035-7de9a3d1d1c5": {
      "ai_outputParser": [
        [
          {
            "node": "c4147239-207f-414e-b185-61137351bde9",
            "type": "ai_outputParser",
            "index": 0
          }
        ]
      ]
    },
    "67200fe9-b12a-4c5a-998d-1b1791771f2b": {
      "main": [
        [
          {
            "node": "a384703f-fd64-4743-ad74-283d38d2ab1a",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "1f1e51c8-918b-4284-b5b2-72cfceb4fb46": {
      "main": [
        [
          {
            "node": "b84f4ee1-1b5c-462a-aa28-a3728f0278b1",
            "type": "main",
            "index": 0
          }
        ]
      ]
    }
  }
}