Spesenabrechnung

• •Google-Konto + Gmail API-Anmeldedaten
• •Google Drive API-Anmeldedaten
• •Google Sheets API-Anmeldedaten
• •OpenAI API Key

{
  "id": "oxgrJNmYucFmtqbU",
  "meta": {
    "instanceId": "87616dfce72ffd690a51f4d408ac3b27a946f12a1119e0e281f5ed271497219e",
    "templateCredsSetupCompleted": true
  },
  "name": "Reimbursement Claims",
  "tags": [],
  "nodes": [
    {
      "id": "62704744-327d-4ba6-9c50-27aabe2fbca5",
      "name": "Bei Formularübermittlung",
      "type": "n8n-nodes-base.formTrigger",
      "position": [
        -1060,
        -920
      ],
      "webhookId": "bdaaa5da-76c9-4faa-a469-561b21b7cc4b",
      "parameters": {
        "options": {
          "buttonLabel": "Submit",
          "appendAttribution": false,
          "respondWithOptions": {
            "values": {
              "formSubmittedText": "=Thank you !\nYou will receive an email shortly about your reimbursement request from Finance Department.\n"
            }
          }
        },
        "formTitle": "Reimbursement Form",
        "formFields": {
          "values": [
            {
              "fieldLabel": "Emp Name",
              "placeholder": "Employee Name",
              "requiredField": true
            },
            {
              "fieldLabel": "Emp ID",
              "placeholder": "Employee ID",
              "requiredField": true
            },
            {
              "fieldType": "email",
              "fieldLabel": "Email",
              "placeholder": "Email Address",
              "requiredField": true
            },
            {
              "fieldType": "number",
              "fieldLabel": "Claim Amount",
              "placeholder": "INR 0.00",
              "requiredField": true
            },
            {
              "fieldType": "dropdown",
              "fieldLabel": "Nature of Bill",
              "fieldOptions": {
                "values": [
                  {
                    "option": "Food"
                  },
                  {
                    "option": "Travel"
                  },
                  {
                    "option": "Accomodation"
                  },
                  {
                    "option": "Others"
                  }
                ]
              },
              "requiredField": true
            },
            {
              "fieldType": "file",
              "fieldLabel": "Upload Bills",
              "requiredField": true,
              "acceptFileTypes": ".jpg, .png, .pdf"
            }
          ]
        },
        "responseMode": "lastNode",
        "formDescription": "Reimbursement Form"
      },
      "typeVersion": 2.2
    },
    {
      "id": "03b7edb5-2f55-48a4-9fbb-9c230f45df70",
      "name": "Zusammenführen",
      "type": "n8n-nodes-base.merge",
      "position": [
        -420,
        -940
      ],
      "parameters": {
        "mode": "chooseBranch",
        "useDataOfInput": 2
      },
      "typeVersion": 3.2
    },
    {
      "id": "6393835a-0680-482d-86eb-fd9703a562ff",
      "name": "Wenn Rechnung existiert",
      "type": "n8n-nodes-base.if",
      "position": [
        -660,
        -1080
      ],
      "parameters": {
        "options": {},
        "conditions": {
          "options": {
            "version": 2,
            "leftValue": "",
            "caseSensitive": true,
            "typeValidation": "strict"
          },
          "combinator": "and",
          "conditions": [
            {
              "id": "b07ac5ff-eb0d-4d85-b15f-60eba9384546",
              "operator": {
                "name": "filter.operator.equals",
                "type": "string",
                "operation": "equals"
              },
              "leftValue": "={{ $json.name }}",
              "rightValue": "={{ $('On form submission').item.json['Upload Bills'][0].filename }}"
            }
          ]
        }
      },
      "typeVersion": 2.2
    },
    {
      "id": "548f0d70-643c-4ef5-861a-5ad584a40321",
      "name": "Vorhandene Rechnung herunterladen",
      "type": "n8n-nodes-base.googleDrive",
      "position": [
        -280,
        -1100
      ],
      "parameters": {
        "fileId": {
          "__rl": true,
          "mode": "id",
          "value": "={{ $json.id }}"
        },
        "options": {},
        "operation": "download"
      },
      "credentials": {
        "googleDriveOAuth2Api": {
          "id": "xFTXDFHVEmRlrR5e",
          "name": "Google Drive account 2"
        }
      },
      "typeVersion": 3
    },
    {
      "id": "0127a1b4-5a2e-42a6-b2bc-7476dd7bf817",
      "name": "Ungültigen Antrag mailen",
      "type": "n8n-nodes-base.gmail",
      "position": [
        720,
        -1180
      ],
      "webhookId": "6e325205-b968-4dc0-a796-c0ebc41dd2fc",
      "parameters": {
        "sendTo": "={{ $('On form submission').item.json.Email }}",
        "message": "=Dear {{ $json.receiptContent.empName }},\n\nIt was identified that the claims requested does not match the total bill from the uploaded receipts . \n\nAmount Claimed: $ {{ $json.receiptContent.amountClaimed }}/-\nTotal Bill: $ {{ $json.receiptContent.totalBill }}/-\nUploaded Recipt: {{ $json.receiptContent.uploadedFile }}\nBill Type: {{ $json.receiptContent.billType }}\n\nPlease review and resubmit the request.\n\n*Claims are Subject to Approvals*\n\nRegards,\nFinance Depeartment\n\n",
        "options": {
          "ccList": "prath002@gmail.com",
          "appendAttribution": false
        },
        "subject": "=New Reimbursement Request - INR {{ $('On form submission').item.json['Claim Amount'] }} /-",
        "emailType": "text"
      },
      "credentials": {
        "gmailOAuth2": {
          "id": "qRfscNFGDLVMQs6g",
          "name": "Gmail account"
        }
      },
      "typeVersion": 2.1
    },
    {
      "id": "13253bbd-890a-49b2-a89e-821575f298dc",
      "name": "Gültigen Antrag mailen",
      "type": "n8n-nodes-base.gmail",
      "position": [
        880,
        -980
      ],
      "webhookId": "6e325205-b968-4dc0-a796-c0ebc41dd2fc",
      "parameters": {
        "sendTo": "={{ $('On form submission').item.json.Email }}",
        "message": "=Dear {{ $json['Employee Name'] }},\n\nThank you for your email. \n\nRequest ID: {{ $json.RequestID }}\n\nYou reimbursement rquest as below is being reviewed.\n\nAmount Claimed: ${{ $('Validate False Claims').item.json.receiptContent.amountClaimed }}/-\nTotal Bill: ${{ $json['Total Billed Amount'] }}/-\nUploaded Recipt: {{ $json['Recieipt Link'] }}\nBill Type: {{ $json['Receipt Type'] }}\n\n*Claims are subject to approvals*\n\nRegards,\nFinance Depeartment\n",
        "options": {
          "ccList": "prath002@gmail.com",
          "appendAttribution": false
        },
        "subject": "=New Reimbursement Request - INR {{ $('On form submission').item.json['Claim Amount'] }} /- | {{ $json.RequestID }}",
        "emailType": "text"
      },
      "credentials": {
        "gmailOAuth2": {
          "id": "qRfscNFGDLVMQs6g",
          "name": "Gmail account"
        }
      },
      "typeVersion": 2.1
    },
    {
      "id": "ca855f2a-c40e-4dec-845c-8b7b0d81b330",
      "name": "Eingaben zusammenführen",
      "type": "@n8n/n8n-nodes-langchain.openAi",
      "position": [
        100,
        -1100
      ],
      "parameters": {
        "text": "=Create a JSON as below and just return structed JSON in strings. \n\n{\n  empID: {{ $('On form submission').item.json['Emp ID'] }},\n  empName: {{ $('On form submission').item.json['Emp Name'] }},\n  empEmail: {{ $('On form submission').item.json.Email }},\n  billType: {{ $('On form submission').item.json['Nature of Bill'] }},\n  amountClaimed: {{ $('On form submission').item.json['Claim Amount'] || 0.00}},\n  totalBill: Extract the total amount from the binary,\n  uploadedFile: {{ $json.webViewLink }}\n\n## NOTE\n - Do not append ```json\\n in the output",
        "modelId": {
          "__rl": true,
          "mode": "list",
          "value": "gpt-4o-mini",
          "cachedResultName": "GPT-4O-MINI"
        },
        "options": {},
        "resource": "image",
        "inputType": "base64",
        "operation": "analyze"
      },
      "credentials": {
        "openAiApi": {
          "id": "qlhg6EzexBZ9bwXO",
          "name": "OpenAi account"
        }
      },
      "typeVersion": 1.8
    },
    {
      "id": "de6eb222-2e33-4277-8cfb-45042e6902e3",
      "name": "Falschangaben validieren",
      "type": "n8n-nodes-base.code",
      "position": [
        260,
        -1100
      ],
      "parameters": {
        "jsCode": "var jsonObj =  JSON.parse($input.first().json.content)\n\nif (jsonObj.amountClaimed != jsonObj.totalBill) {\n  return {isFalseClaim: true, receiptContent: jsonObj,requestID: generateRandomString()}\n} else return {isFalseClaim: false, receiptContent: jsonObj, requestID: generateRandomString()}\n\n\nfunction generateRandomString(length = 20) {\n    const chars = 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789';\n    let result = jsonObj.empID+'_';\n    for (let i = 0; i < length; i++) {\n        result += chars.charAt(Math.floor(Math.random() * chars.length));\n    }\n    return result;\n}\n"
      },
      "typeVersion": 2
    },
    {
      "id": "34b61b4d-a314-4d3d-9e9a-806c5a747ce4",
      "name": "Rechnungen hochladen",
      "type": "n8n-nodes-base.googleDrive",
      "position": [
        -260,
        -940
      ],
      "parameters": {
        "name": "={{ $json['Upload Bills'][0].filename }}",
        "driveId": {
          "__rl": true,
          "mode": "list",
          "value": "My Drive"
        },
        "options": {},
        "folderId": {
          "__rl": true,
          "mode": "list",
          "value": "1huBmHtSlSU2nV5xbRDDOaBY-CKIPmnjs",
          "cachedResultUrl": "https://drive.google.com/drive/folders/1huBmHtSlSU2nV5xbRDDOaBY-CKIPmnjs",
          "cachedResultName": "Reimburse Bills"
        },
        "inputDataFieldName": "=Upload_Bills"
      },
      "credentials": {
        "googleDriveOAuth2Api": {
          "id": "xFTXDFHVEmRlrR5e",
          "name": "Google Drive account 2"
        }
      },
      "typeVersion": 3
    },
    {
      "id": "bf47788d-8326-4524-b0e4-58cfc453da72",
      "name": "Rechnungen herunterladen",
      "type": "n8n-nodes-base.googleDrive",
      "position": [
        -100,
        -940
      ],
      "parameters": {
        "fileId": {
          "__rl": true,
          "mode": "id",
          "value": "={{ $json.id }}"
        },
        "options": {},
        "operation": "download"
      },
      "credentials": {
        "googleDriveOAuth2Api": {
          "id": "xFTXDFHVEmRlrR5e",
          "name": "Google Drive account 2"
        }
      },
      "typeVersion": 3
    },
    {
      "id": "b9ae6568-76dd-4605-8402-765a25e850ed",
      "name": "Rechnung suchen",
      "type": "n8n-nodes-base.googleDrive",
      "onError": "continueRegularOutput",
      "position": [
        -840,
        -1080
      ],
      "parameters": {
        "limit": 5,
        "filter": {
          "folderId": {
            "__rl": true,
            "mode": "list",
            "value": "1huBmHtSlSU2nV5xbRDDOaBY-CKIPmnjs",
            "cachedResultUrl": "https://drive.google.com/drive/folders/1huBmHtSlSU2nV5xbRDDOaBY-CKIPmnjs",
            "cachedResultName": "Reimburse Bills"
          }
        },
        "options": {
          "fields": [
            "id",
            "name",
            "webViewLink"
          ]
        },
        "resource": "fileFolder",
        "queryString": "={{ $json['Upload Bills'][0].filename }}"
      },
      "credentials": {
        "googleDriveOAuth2Api": {
          "id": "xFTXDFHVEmRlrR5e",
          "name": "Google Drive account 2"
        }
      },
      "typeVersion": 3,
      "alwaysOutputData": true
    },
    {
      "id": "b9a932ea-9404-453d-b45d-1ced96120d57",
      "name": "Wenn",
      "type": "n8n-nodes-base.if",
      "position": [
        420,
        -1100
      ],
      "parameters": {
        "options": {},
        "conditions": {
          "options": {
            "version": 2,
            "leftValue": "",
            "caseSensitive": true,
            "typeValidation": "strict"
          },
          "combinator": "and",
          "conditions": [
            {
              "id": "9d358347-9a20-425a-b6ef-4f03007c99fd",
              "operator": {
                "type": "boolean",
                "operation": "true",
                "singleValue": true
              },
              "leftValue": "={{ $json.isFalseClaim }}",
              "rightValue": ""
            }
          ]
        }
      },
      "typeVersion": 2.2
    },
    {
      "id": "7182bbde-016f-4ff0-abb9-43dcf82f9bf2",
      "name": "Antrag hinzufügen",
      "type": "n8n-nodes-base.googleSheets",
      "position": [
        720,
        -980
      ],
      "parameters": {
        "columns": {
          "value": {
            "Bill Date": "={{ $now }}",
            "RequestID": "={{ $json.requestID }}",
            "Employee ID": "={{ $('Validate False Claims').item.json.receiptContent.empID }}",
            "Is_Approved": "No",
            "Uploaded On": "={{ $now }}",
            "Receipt Type": "={{ $('Validate False Claims').item.json.receiptContent.billType }}",
            "Employee Name": "={{ $('Validate False Claims').item.json.receiptContent.empName }}",
            "Recieipt Link": "={{ $('Validate False Claims').item.json.receiptContent.uploadedFile }}",
            "Total Billed Amount": "={{ $('Validate False Claims').item.json.receiptContent.totalBill }}"
          },
          "schema": [
            {
              "id": "RequestID",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "RequestID",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "Employee ID",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "Employee ID",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "Employee Name",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "Employee Name",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "Receipt Type",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "Receipt Type",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "Total Billed Amount",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "Total Billed Amount",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "Bill Date",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "Bill Date",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "Uploaded On",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "Uploaded On",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "Recieipt Link",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "Recieipt Link",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "Is_Approved",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "Is_Approved",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            }
          ],
          "mappingMode": "defineBelow",
          "matchingColumns": [],
          "attemptToConvertTypes": false,
          "convertFieldsToString": false
        },
        "options": {},
        "operation": "append",
        "sheetName": {
          "__rl": true,
          "mode": "list",
          "value": "gid=0",
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/1ttZJOK5L-_zFTSRUoOyNkJ9VL60mgd6HRTvaQWKvYAs/edit#gid=0",
          "cachedResultName": "Sheet1"
        },
        "documentId": {
          "__rl": true,
          "mode": "list",
          "value": "1ttZJOK5L-_zFTSRUoOyNkJ9VL60mgd6HRTvaQWKvYAs",
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/1ttZJOK5L-_zFTSRUoOyNkJ9VL60mgd6HRTvaQWKvYAs/edit?usp=drivesdk",
          "cachedResultName": "Receipts"
        }
      },
      "credentials": {
        "googleSheetsOAuth2Api": {
          "id": "tFyqtoZjnmHw7ZN5",
          "name": "Google Sheets account"
        }
      },
      "typeVersion": 4.6
    },
    {
      "id": "64793a3d-e3d1-462a-8870-4af280f81423",
      "name": "Notiz",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -1300,
        -1000
      ],
      "parameters": {
        "color": 4,
        "width": 380,
        "height": 220,
        "content": "## Submit Form\nThis form will get all required information from the user and pass it to other nodes\n"
      },
      "typeVersion": 1
    },
    {
      "id": "cb6b5988-9eb9-4686-9f5b-3a2ce92179d9",
      "name": "Notiz1",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -880,
        -1260
      ],
      "parameters": {
        "width": 360,
        "height": 340,
        "content": "## Validate User Uploaded Bills.\n\nValidate if the user uploaded bill already exists into the system.\n\nIf **Yes** then reuse and download for proccesing.\n**Else** -- Upload the bill and then use for processing. "
      },
      "typeVersion": 1
    },
    {
      "id": "b1501bda-4ad4-45a0-9b95-322d307b3688",
      "name": "Notiz2",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -480,
        -1180
      ],
      "parameters": {
        "color": 5,
        "width": 520,
        "height": 560,
        "content": "## Uploads and Downloads\nIf **Bills Already Exist]** Download the file and send to next node for OCR.\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\nIf **Bills Does not Exists**\n- **Merge** - This node will get the user uploaded file [**binary**] and then uploads to **GDrive** from the next node. \n- The Same file get downloaded again and then passed to next node for OCR"
      },
      "typeVersion": 1
    },
    {
      "id": "46d1de94-b727-450e-adc2-10e424f39291",
      "name": "Notiz3",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        60,
        -1300
      ],
      "parameters": {
        "width": 500,
        "height": 360,
        "content": "## Extract, Validate\nThese nodes will \n- Extract the content from the bill\n- Validate if the claim made by the user is correct. Whether the amount claimed and the amount from the bill match.\n- If **false claim** the Notify user to resubmit.\n- If **correct claim** send for Finance Team for processing. "
      },
      "typeVersion": 1
    },
    {
      "id": "45aa008a-5f9f-41ed-b7ba-0473edf9a597",
      "name": "Notiz4",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        680,
        -1240
      ],
      "parameters": {
        "color": 3,
        "width": 280,
        "height": 220,
        "content": "## Invalid Claim\n\n"
      },
      "typeVersion": 1
    },
    {
      "id": "5391e33a-7b73-43d6-9a13-444ccf6840e8",
      "name": "Notiz5",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        680,
        -1000
      ],
      "parameters": {
        "color": 4,
        "width": 380,
        "height": 300,
        "content": "\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n## Valid Claim\n- Notify User with the request id.\n- Notify Finance Team to proceed with review."
      },
      "typeVersion": 1
    }
  ],
  "active": false,
  "pinData": {},
  "settings": {
    "executionOrder": "v1"
  },
  "versionId": "94c7c211-e7bf-45ec-b0c9-8f31bf7b7229",
  "connections": {
    "b9a932ea-9404-453d-b45d-1ced96120d57": {
      "main": [
        [
          {
            "node": "0127a1b4-5a2e-42a6-b2bc-7476dd7bf817",
            "type": "main",
            "index": 0
          }
        ],
        [
          {
            "node": "7182bbde-016f-4ff0-abb9-43dcf82f9bf2",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "03b7edb5-2f55-48a4-9fbb-9c230f45df70": {
      "main": [
        [
          {
            "node": "34b61b4d-a314-4d3d-9e9a-806c5a747ce4",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "7182bbde-016f-4ff0-abb9-43dcf82f9bf2": {
      "main": [
        [
          {
            "node": "13253bbd-890a-49b2-a89e-821575f298dc",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "ca855f2a-c40e-4dec-845c-8b7b0d81b330": {
      "main": [
        [
          {
            "node": "de6eb222-2e33-4277-8cfb-45042e6902e3",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "b9ae6568-76dd-4605-8402-765a25e850ed": {
      "main": [
        [
          {
            "node": "6393835a-0680-482d-86eb-fd9703a562ff",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "34b61b4d-a314-4d3d-9e9a-806c5a747ce4": {
      "main": [
        [
          {
            "node": "bf47788d-8326-4524-b0e4-58cfc453da72",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "bf47788d-8326-4524-b0e4-58cfc453da72": {
      "main": [
        [
          {
            "node": "ca855f2a-c40e-4dec-845c-8b7b0d81b330",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "6393835a-0680-482d-86eb-fd9703a562ff": {
      "main": [
        [
          {
            "node": "548f0d70-643c-4ef5-861a-5ad584a40321",
            "type": "main",
            "index": 0
          }
        ],
        [
          {
            "node": "03b7edb5-2f55-48a4-9fbb-9c230f45df70",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "13253bbd-890a-49b2-a89e-821575f298dc": {
      "main": [
        []
      ]
    },
    "62704744-327d-4ba6-9c50-27aabe2fbca5": {
      "main": [
        [
          {
            "node": "b9ae6568-76dd-4605-8402-765a25e850ed",
            "type": "main",
            "index": 0
          },
          {
            "node": "03b7edb5-2f55-48a4-9fbb-9c230f45df70",
            "type": "main",
            "index": 1
          }
        ]
      ]
    },
    "de6eb222-2e33-4277-8cfb-45042e6902e3": {
      "main": [
        [
          {
            "node": "b9a932ea-9404-453d-b45d-1ced96120d57",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "548f0d70-643c-4ef5-861a-5ad584a40321": {
      "main": [
        [
          {
            "node": "ca855f2a-c40e-4dec-845c-8b7b0d81b330",
            "type": "main",
            "index": 0
          }
        ]
      ]
    }
  }
}