Workflow n8n
Automatisation Google Sheets avec n8n : gestion des stocks simplifiée
Ce workflow n8n a pour objectif d'automatiser la gestion des stocks à l'aide de Google Sheets et d'autres services intégrés. Dans un contexte où les entreprises doivent suivre de près leurs niveaux de stock pour éviter les ruptures, ce workflow permet de recevoir des demandes de matériel, d'approuver les demandes, et de mettre à jour les niveaux de stock en temps réel. Les cas d'usage incluent la détection de stocks bas, l'envoi de notifications pour approbation, et la mise à jour automatique des enregistrements dans Google Sheets et Supabase. Le processus débute par la réception d'une demande via un webhook, qui déclenche l'ensemble du workflow. Ensuite, les données sont standardisées et les demandes d'approbation sont envoyées par email. Une fois les approbations reçues, le stock est mis à jour en fonction des quantités validées. Des étapes clés incluent la recherche d'ID de produit dans Supabase, la détection de stocks bas via des scripts JavaScript, et la mise à jour des enregistrements dans Google Sheets pour assurer une visibilité en temps réel. Les bénéfices business de cette automatisation sont significatifs : réduction des erreurs humaines, gain de temps dans la gestion des stocks, et amélioration de la réactivité face aux demandes de matériel. En intégrant ce workflow, les entreprises peuvent optimiser leur gestion des ressources et garantir une disponibilité constante des produits. Tags clés : automatisation, Google Sheets, gestion des stocks.
Catégorie: Webhook · Tags: automatisation, Google Sheets, gestion des stocks, n8n, workflow❤ 0
Vue d'ensemble du workflow n8n
Schéma des nœuds et connexions de ce workflow n8n, généré à partir du JSON n8n.
Détail des nœuds du workflow n8n
Inscris-toi pour voir l'intégralité du workflow
Inscription gratuite
S'inscrire gratuitementBesoin d'aide ?{
"meta": {
"instanceId": "3da9aa1165fccd6e57ad278dd59febaa1dfaafc31e0e52a95d11801200f09024",
"templateCredsSetupCompleted": true
},
"nodes": [
{
"id": "c983fae5-a779-4a56-ace0-304aaefe0433",
"name": "Append Material Request",
"type": "n8n-nodes-base.googleSheets",
"position": [
6780,
3240
],
"parameters": {
"columns": {
"value": {},
"schema": [
{
"id": "Timestamp",
"type": "string",
"display": true,
"required": false,
"displayName": "Timestamp",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "Product ID",
"type": "string",
"display": true,
"required": false,
"displayName": "Product ID",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "Quantity Requested",
"type": "string",
"display": true,
"removed": false,
"required": false,
"displayName": "Quantity Requested",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "Measurement Unit",
"type": "string",
"display": true,
"removed": false,
"required": false,
"displayName": "Measurement Unit",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "Requested By",
"type": "string",
"display": true,
"removed": false,
"required": false,
"displayName": "Requested By",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "Issue Date",
"type": "string",
"display": true,
"required": false,
"displayName": "Issue Date",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "Description",
"type": "string",
"display": true,
"removed": false,
"required": false,
"displayName": "Description",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "Submission ID",
"type": "string",
"display": true,
"removed": false,
"required": false,
"displayName": "Submission ID",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "Status",
"type": "string",
"display": true,
"removed": false,
"required": false,
"displayName": "Status",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "Approval Link",
"type": "string",
"display": true,
"removed": false,
"required": false,
"displayName": "Approval Link",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "Request Date",
"type": "string",
"display": true,
"removed": false,
"required": false,
"displayName": "Request Date",
"defaultMatch": false,
"canBeUsedToMatch": true
}
],
"mappingMode": "autoMapInputData",
"matchingColumns": [],
"attemptToConvertTypes": false,
"convertFieldsToString": false
},
"options": {},
"operation": "append",
"sheetName": {
"__rl": true,
"mode": "list",
"value": 328307238,
"cachedResultUrl": "https://docs.google.com/spreadsheets/d/1q0S6AVK7uxZG8sUQkpcZr01KToHPjOZ0gG3zKHLR6lw/edit#gid=328307238",
"cachedResultName": "Materials Issued"
},
"documentId": {
"__rl": true,
"mode": "list",
"value": "1q0S6AVK7uxZG8sUQkpcZr01KToHPjOZ0gG3zKHLR6lw",
"cachedResultUrl": "https://docs.google.com/spreadsheets/d/1q0S6AVK7uxZG8sUQkpcZr01KToHPjOZ0gG3zKHLR6lw/edit?usp=drivesdk",
"cachedResultName": "Plumbee Raw Material Delivery (Responses)"
}
},
"typeVersion": 4.5
},
{
"id": "25d745c1-8167-4c55-9f88-461f94843286",
"name": "Get Approvals",
"type": "n8n-nodes-base.webhook",
"position": [
5900,
4060
],
"webhookId": "33876465-33a7-4cc1-bbb5-bc8c630edd9f",
"parameters": {
"path": "/approve-issue",
"options": {}
},
"typeVersion": 2
},
{
"id": "c4d96a9c-b70b-4e40-bf9d-5e8f9426ee22",
"name": "Standardize Data",
"type": "n8n-nodes-base.set",
"position": [
6120,
3400
],
"parameters": {
"options": {},
"assignments": {
"assignments": [
{
"id": "77dc2acf-9657-4013-9675-99311d299abe",
"name": "Timestamp",
"type": "string",
"value": "={{ $json[\"Timestamp\"] || new Date().toISOString() }}"
},
{
"id": "a5706f57-d7ba-4ffa-a8c6-030bdb2e3d55",
"name": "Product ID",
"type": "string",
"value": "={{ $json.body['Product ID'] }}"
},
{
"id": "53e04ca2-88cb-49a6-b878-4d7abde8806d",
"name": "Quantity Requested",
"type": "number",
"value": "={{ $json.body['Quantity Requested'] }}"
},
{
"id": "9612c7a7-1f76-4168-9c89-d89421cc7c5a",
"name": "Requested By",
"type": "string",
"value": "={{ $json.body['Requested By'] }}"
},
{
"id": "4b0f98cc-3e9f-42a4-81e7-c4c8c0a904eb",
"name": "Description",
"type": "string",
"value": "={{ $json.body.Description }}"
},
{
"id": "a6a134ac-280c-4ef2-bbd6-e121376f9bbf",
"name": "Submission ID",
"type": "string",
"value": "={{ $json.body['Submission ID'] }}"
},
{
"id": "e3a62912-773f-43f2-bf35-5b5e757c345d",
"name": "Approval Link",
"type": "string",
"value": "=https://test.app.n8n.cloud/webhook/approve-issue?submissionId={{ $json.body['Submission ID'] }}\n\n"
},
{
"id": "22fb6d08-5f7e-42dc-a3ea-015f1f4f890c",
"name": "Status",
"type": "string",
"value": "Pending"
},
{
"id": "2c3340dc-b995-4342-9e51-fff09d3d4ca6",
"name": "Measurement Unit",
"type": "string",
"value": "={{ $json.body['Measurement Unit'] }}"
}
]
},
"includeOtherFields": "="
},
"typeVersion": 3.4
},
{
"id": "47d2bb01-99e6-4ab1-b19d-bc9912243150",
"name": "Update Stock",
"type": "n8n-nodes-base.code",
"position": [
7440,
3860
],
"parameters": {
"jsCode": "const currentStock = parseFloat($input.first().json['Current Stock']\n );\nconst approvedQuantity = parseFloat(\n $('Verify Approval Data').first().json['Approved Quantity']);\nconst newStock = currentStock - approvedQuantity;\n\nif (newStock < 0) throw new Error(`Insufficient stock for ${\n $('Retrieve Issue Request Details').first().json['Product ID']}`);\n\nreturn {\n json: {\n ...$json,\n \"Updated Current Stock\": newStock,\n\"Material Name\":$input.first().json['Material Name'],\"Measurement Unit\":$input.first().json['Measurement Unit'],\n\"Minimum Stock Level\": \n $input.first().json['Minimum Stock Level'],\n \"Issue Date\":\n $('Retrieve Issue Request Details').first().json['Issue Date'],\n\"Product ID\": \n $('Retrieve Issue Request Details').first().json['Product ID']\n \n }\n};"
},
"typeVersion": 2
},
{
"id": "dcbb196f-1ecf-4137-af29-e511c4b7b9d9",
"name": "Receive Issue Request",
"type": "n8n-nodes-base.webhook",
"position": [
5900,
3400
],
"webhookId": "73d4bdfc-2d8b-42f4-85d5-43ecae0953c1",
"parameters": {
"path": "raw-materials-issue",
"options": {},
"httpMethod": "POST"
},
"typeVersion": 2
},
{
"id": "430599b6-3758-4eb7-a924-8530a7c5dc7e",
"name": "Send Approval Request",
"type": "n8n-nodes-base.gmail",
"position": [
7660,
3400
],
"webhookId": "db24c5e3-8113-4d8a-b562-9c248f47fa3c",
"parameters": {
"sendTo": "example@gmail.com",
"message": "=<!DOCTYPE html>\n<html lang=\"en\">\n<head>\n <meta charset=\"UTF-8\">\n <meta name=\"viewport\" content=\"width=device-width, initial-scale=1.0\">\n <title>Material Issue Request Approval</title>\n <style>\n /* Reset and Base Styles */\n body {\n font-family: 'Segoe UI', Tahoma, Geneva, Verdana, sans-serif;\n line-height: 1.5;\n color: #444;\n background-color: #f0f4f8;\n margin: 0;\n padding: 0;\n }\n .container {\n width: 90%;\n max-width: 550px;\n margin: 15px auto;\n background-color: #ffffff;\n border-radius: 8px;\n box-shadow: 0 4px 10px rgba(0, 0, 0, 0.1);\n overflow: hidden;\n border: 1px solid #e0e6ed;\n }\n /* Header with Gradient */\n .header {\n background: linear-gradient(135deg, #3498db 0%, #2ecc71 100%);\n padding: 15px;\n text-align: center;\n border-top-left-radius: 8px;\n border-top-right-radius: 8px;\n }\n h2 {\n color: #ffffff;\n font-size: 20px;\n margin: 0;\n text-shadow: 1px 1px 2px rgba(0, 0, 0, 0.2);\n }\n /* Content Area */\n .content {\n padding: 15px;\n }\n p {\n margin: 5px 0;\n font-size: 14px;\n }\n p.greeting {\n font-size: 16px;\n font-weight: 500;\n color: #2c3e50;\n }\n /* Details List */\n ul {\n list-style-type: none;\n padding: 0;\n margin: 10px 0;\n background-color: #f8fafc;\n padding: 10px;\n border-radius: 6px;\n border-left: 4px solid #3498db;\n box-shadow: inset 0 1px 3px rgba(0, 0, 0, 0.05);\n }\n ul li {\n margin: 6px 0;\n font-size: 13px;\n display: flex;\n align-items: center;\n }\n ul li strong {\n display: inline-block;\n width: 130px;\n color: #2c3e50;\n font-weight: 600;\n }\n /* Action Buttons */\n .actions {\n text-align: center;\n margin: 10px 0;\n display: flex;\n justify-content: center;\n gap: 10px;\n }\n .btn {\n display: inline-block;\n padding: 8px 20px;\n text-decoration: none;\n color: #ffffff;\n font-size: 13px;\n font-weight: 600;\n border-radius: 20px;\n transition: transform 0.2s ease, box-shadow 0.3s ease;\n box-shadow: 0 2px 8px rgba(0, 0, 0, 0.15);\n }\n .btn:hover {\n transform: translateY(-2px);\n box-shadow: 0 4px 12px rgba(0, 0, 0, 0.2);\n }\n .btn-approve {\n background: linear-gradient(90deg, #2ecc71, #27ae60);\n }\n .btn-reject {\n background: linear-gradient(90deg, #e74c3c, #c0392b);\n }\n /* Footer */\n .footer {\n text-align: center;\n padding: 10px;\n background-color: #f8fafc;\n border-bottom-left-radius: 8px;\n border-bottom-right-radius: 8px;\n font-size: 12px;\n color: #777;\n border-top: 1px solid #e0e6ed;\n }\n .footer p {\n margin: 0;\n }\n /* Responsive Adjustments */\n @media (max-width: 600px) {\n .container {\n width: 95%;\n margin: 10px auto;\n }\n .header {\n padding: 10px;\n }\n h2 {\n font-size: 18px;\n }\n .content {\n padding: 10px;\n }\n ul li {\n flex-direction: column;\n align-items: flex-start;\n }\n ul li strong {\n width: auto;\n margin-bottom: 3px;\n }\n .actions {\n flex-direction: column;\n gap: 8px;\n }\n .btn {\n width: 80%;\n }\n }\n </style>\n</head>\n<body>\n <div class=\"container\">\n <!-- Header -->\n <div class=\"header\">\n <h2>Material Issue Request Approval</h2>\n </div>\n\n <!-- Content -->\n <div class=\"content\">\n <p class=\"greeting\">Dear XXX,</p>\n <p>Please review the following material issue request:</p>\n\n <ul>\n <li><strong>Product ID:</strong> {{ $('Append Material Request').item.json['Product ID'] }}</li>\n <li><strong>Material:</strong> {{ $json[\"Material Name\"] }}</li>\n <li><strong>Quantity Requested:</strong> {{ $('Append Material Request').item.json['Quantity Requested'] }} {{ $json[\"Measurement Unit\"] }}</li>\n <li><strong>Current Stock:</strong> {{ $json[\"Current Stock\"] }} {{ $json[\"Measurement Unit\"] }}</li>\n <li><strong>Requested By:</strong> {{ $('Append Material Request').item.json['Requested By'] }}</li>\n \n <li><strong>Description:</strong> {{ $('Append Material Request').item.json['Description'] }}</li>\n <li><strong>Submission ID:</strong> {{ $('Append Material Request').item.json['Submission ID'] }}</li>\n <li><strong>Stock Available:</strong> {{ $json[\"Is Enough\"] ? \"Yes\" : \"No\" }}</li>\n </ul>\n\n <div class=\"actions\">\n <p><strong>To approve:</strong></p>\n <a href=\"{{ $('Append Material Request').item.json['Approval Link'] }}&action=approve&quantity={{ $('Append Material Request').item.json['Quantity Requested'] }}&approvedBy=PB\" class=\"btn btn-approve\">Approve Request</a>\n <p><strong>To reject:</strong></p>\n <a href=\"{{ $('Append Material Request').item.json['Approval Link'] }}&action=reject&approvedBy=PB\" class=\"btn btn-reject\">Reject Request</a>\n </div>\n </div>\n\n <!-- Footer -->\n <div class=\"footer\">\n <p>Regards,<br>Your Company<</p>\n </div>\n </div>\n</body>\n</html>",
"options": {},
"subject": "=Approval Required: Material Issue Request - {{ $json['Product ID'] }}"
},
"typeVersion": 2.1
},
{
"id": "7c68ef5d-5518-4236-803c-157fe8c581dd",
"name": "Prepare Approval",
"type": "n8n-nodes-base.code",
"position": [
7440,
3400
],
"parameters": {
"jsCode": "const currentStock = parseFloat(\n $input.first().json['Current Stock']|| 0);\nconst quantityRequested = parseFloat(\n$('Append Material Request').first().json['Quantity Requested']);\nconst isEnough = currentStock >= quantityRequested;\n\nreturn {\n json: {\n ...$json,\n \"Current Stock\": currentStock,\n \"Is Enough\": isEnough,\n \"Material Name\":$input.first().json['Material Name'],\n\"Unit\":$input.first().json['Measurement Unit'],\n\"Minimum Stock Level\": $input.first().json['Minimum Stock Level']\n }\n};"
},
"typeVersion": 2
},
{
"id": "bf6487d1-dd4e-4bc1-9447-c3aaeffd5df0",
"name": "Create Record Issue",
"type": "n8n-nodes-base.supabase",
"position": [
6780,
3560
],
"parameters": {
"tableId": "Materials Issued",
"dataToSend": "autoMapInputData"
},
"typeVersion": 1
},
{
"id": "86899f38-6412-447f-9b6d-a402f6c39fcd",
"name": "Search Product ID",
"type": "n8n-nodes-base.supabase",
"position": [
7000,
3560
],
"parameters": {
"filters": {
"conditions": [
{
"keyName": "Product ID",
"keyValue": "={{ $json[\"Product ID\"] }}",
"condition": "eq"
}
]
},
"tableId": "Current Stock",
"operation": "getAll"
},
"typeVersion": 1
},
{
"id": "6bb9053b-9a46-4e9e-9097-d5e2ae99e259",
"name": "Searck Issues",
"type": "n8n-nodes-base.supabase",
"position": [
6560,
4220
],
"parameters": {
"filters": {
"conditions": [
{
"keyName": "Submission ID",
"keyValue": "={{ $json[\"Submission ID\"] }}",
"condition": "eq"
}
]
},
"tableId": "Materials Issued",
"operation": "getAll"
},
"typeVersion": 1
},
{
"id": "420d242b-6a17-4538-bca1-09283a49742f",
"name": "Update Current Stck",
"type": "n8n-nodes-base.supabase",
"position": [
7680,
3740
],
"parameters": {
"filters": {
"conditions": [
{
"keyName": "Product ID",
"keyValue": "={{ $json['Product ID'] }}",
"condition": "eq"
}
]
},
"tableId": "Current Stock",
"fieldsUi": {
"fieldValues": [
{
"fieldId": "Material Name",
"fieldValue": "={{ $json['Material Name'] }}"
},
{
"fieldId": "Previous Stock",
"fieldValue": "={{ $json['Current Stock'] }}"
},
{
"fieldId": "Current Stock",
"fieldValue": "={{ $json['Updated Current Stock'] }}"
},
{
"fieldId": "Last Updated",
"fieldValue": "={{ $json['Last Updated'] }}"
},
{
"fieldId": "Minimum Stock Level",
"fieldValue": "={{ $json['Minimum Stock Level'] }}"
}
]
},
"operation": "update"
},
"typeVersion": 1
},
{
"id": "f4c8cb13-acd9-4d7e-ac73-fb528c1700e1",
"name": "Merge Lookups",
"type": "n8n-nodes-base.merge",
"position": [
7220,
3400
],
"parameters": {
"mode": "chooseBranch"
},
"typeVersion": 3.1,
"alwaysOutputData": true
},
{
"id": "0cc01e7c-aa88-4783-af20-5b98f8795935",
"name": "Update Current Stock1",
"type": "n8n-nodes-base.googleSheets",
"position": [
7660,
3960
],
"parameters": {
"columns": {
"value": {
"Product ID": "={{ $json['Product ID'] }}",
"Last Updated": "={{ $json['Last Updated'] }}",
"Current Stock": "={{ $json['Updated Current Stock'] }}",
"Material Name": "={{ $json['Material Name'] }}",
"Previous Stock": "={{ $json['Current Stock'] }}",
"Minimum Stock Level": "={{ $json['Minimum Stock Level'] }}"
},
"schema": [
{
"id": "Product ID",
"type": "string",
"display": true,
"removed": false,
"required": false,
"displayName": "Product ID",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "Material Name",
"type": "string",
"display": true,
"required": false,
"displayName": "Material Name",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "Previous Stock",
"type": "string",
"display": true,
"removed": false,
"required": false,
"displayName": "Previous Stock",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "Current Stock",
"type": "string",
"display": true,
"required": false,
"displayName": "Current Stock",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "Measurement Unit",
"type": "string",
"display": true,
"removed": false,
"required": false,
"displayName": "Measurement Unit",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "Last Updated",
"type": "string",
"display": true,
"required": false,
"displayName": "Last Updated",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "Minimum Stock Level",
"type": "string",
"display": true,
"required": false,
"displayName": "Minimum Stock Level",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "row_number",
"type": "string",
"display": true,
"removed": true,
"readOnly": true,
"required": false,
"displayName": "row_number",
"defaultMatch": false,
"canBeUsedToMatch": true
}
],
"mappingMode": "defineBelow",
"matchingColumns": [
"Product ID"
],
"attemptToConvertTypes": false,
"convertFieldsToString": false
},
"options": {},
"operation": "update",
"sheetName": {
"__rl": true,
"mode": "list",
"value": 1019183415,
"cachedResultUrl": "https://docs.google.com/spreadsheets/d/1q0S6AVK7uxZG8sUQkpcZr01KToHPjOZ0gG3zKHLR6lw/edit#gid=1019183415",
"cachedResultName": "Current Stock"
},
"documentId": {
"__rl": true,
"mode": "list",
"value": "1q0S6AVK7uxZG8sUQkpcZr01KToHPjOZ0gG3zKHLR6lw",
"cachedResultUrl": "https://docs.google.com/spreadsheets/d/1q0S6AVK7uxZG8sUQkpcZr01KToHPjOZ0gG3zKHLR6lw/edit?usp=drivesdk",
"cachedResultName": "Plumbee Raw Material Delivery (Responses)"
}
},
"typeVersion": 4.5
},
{
"id": "67cf6b2c-7166-4075-904b-67c82d94df70",
"name": "LookUp Current stock1",
"type": "n8n-nodes-base.googleSheets",
"position": [
7880,
3960
],
"parameters": {
"options": {},
"filtersUI": {
"values": [
{
"lookupValue": "={{ $json['Product ID'] }}",
"lookupColumn": "Product ID"
}
]
},
"sheetName": {
"__rl": true,
"mode": "list",
"value": 1019183415,
"cachedResultUrl": "https://docs.google.com/spreadsheets/d/1q0S6AVK7uxZG8sUQkpcZr01KToHPjOZ0gG3zKHLR6lw/edit#gid=1019183415",
"cachedResultName": "Current Stock"
},
"documentId": {
"__rl": true,
"mode": "list",
"value": "1q0S6AVK7uxZG8sUQkpcZr01KToHPjOZ0gG3zKHLR6lw",
"cachedResultUrl": "https://docs.google.com/spreadsheets/d/1q0S6AVK7uxZG8sUQkpcZr01KToHPjOZ0gG3zKHLR6lw/edit?usp=drivesdk",
"cachedResultName": "Plumbee Raw Material Delivery (Responses)"
}
},
"typeVersion": 4.5
},
{
"id": "bb65a800-e307-46a9-a668-b3e7afa32792",
"name": "Low stock Detection1",
"type": "n8n-nodes-base.code",
"position": [
8100,
3960
],
"parameters": {
"mode": "runOnceForEachItem",
"jsCode": "const currentStock = parseFloat($input.item.json[\"Current Stock\"]);\nconst minStock = parseFloat($input.item.json[\"Minimum Stock Level\"]);\n\n// Check if stock is below minimum\nconst isLow = currentStock < minStock;\n\nreturn {\n json: {\n ...$input.item.json,\n \"Is Low\": isLow,\n \"Alert Message\": isLow ? \n `Low stock alert: ${$input.item.json[\"Material Name\"]} (ID: ${$input.item.json[\"Product ID\"]}) - Current Stock: ${currentStock} ${$input.item.json[\"Measurement Unit\"]}, Minimum: ${minStock}` \n : null\n }\n};"
},
"typeVersion": 2
},
{
"id": "02bd1da9-ecdf-4d05-aa1f-9974f00849b7",
"name": "Merge1",
"type": "n8n-nodes-base.merge",
"position": [
6780,
4060
],
"parameters": {
"mode": "chooseBranch"
},
"typeVersion": 3.1,
"alwaysOutputData": true
},
{
"id": "1e06a4e7-243a-40cd-8aef-1a06a373778a",
"name": "Sticky Note",
"type": "n8n-nodes-base.stickyNote",
"position": [
5840,
3060
],
"parameters": {
"width": 2820,
"height": 1400,
"content": "# Material Issue Request and Approval"
},
"typeVersion": 1
},
{
"id": "ee7270e1-83ff-4d91-8ba8-db4f13c63a57",
"name": "Append Raw Materials",
"type": "n8n-nodes-base.googleSheets",
"position": [
6660,
1820
],
"parameters": {
"columns": {
"value": {},
"schema": [
{
"id": "Timestamp",
"type": "string",
"display": true,
"required": false,
"displayName": "Timestamp",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "Product ID",
"type": "string",
"display": true,
"required": false,
"displayName": "Product ID",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "Supplier Name",
"type": "string",
"display": true,
"required": false,
"displayName": "Supplier Name",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "Material Name",
"type": "string",
"display": true,
"required": false,
"displayName": "Material Name",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "Quantity Received",
"type": "string",
"display": true,
"required": false,
"displayName": "Quantity Received",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "Description",
"type": "string",
"display": true,
"required": false,
"displayName": "Description",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "Measurement Unit",
"type": "string",
"display": true,
"required": false,
"displayName": "Measurement Unit",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "Unit Price",
"type": "string",
"display": true,
"required": false,
"displayName": "Unit Price",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "Date of Delivery",
"type": "string",
"display": true,
"required": false,
"displayName": "Date of Delivery",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "Received By",
"type": "string",
"display": true,
"required": false,
"displayName": "Received By",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "Total Price",
"type": "string",
"display": true,
"required": false,
"displayName": "Total Price",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "Submission ID",
"type": "string",
"display": true,
"removed": false,
"required": false,
"displayName": "Submission ID",
"defaultMatch": false,
"canBeUsedToMatch": true
}
],
"mappingMode": "autoMapInputData",
"matchingColumns": [],
"attemptToConvertTypes": false,
"convertFieldsToString": false
},
"options": {},
"operation": "append",
"sheetName": {
"__rl": true,
"mode": "list",
"value": 1680576943,
"cachedResultUrl": "https://docs.google.com/spreadsheets/d/1q0S6AVK7uxZG8sUQkpcZr01KToHPjOZ0gG3zKHLR6lw/edit#gid=1680576943",
"cachedResultName": "Raw Materials"
},
"documentId": {
"__rl": true,
"mode": "list",
"value": "1q0S6AVK7uxZG8sUQkpcZr01KToHPjOZ0gG3zKHLR6lw",
"cachedResultUrl": "https://docs.google.com/spreadsheets/d/1q0S6AVK7uxZG8sUQkpcZr01KToHPjOZ0gG3zKHLR6lw/edit?usp=drivesdk",
"cachedResultName": "Plumbee Raw Material Delivery (Responses)"
}
},
"typeVersion": 4.5
},
{
"id": "21c17077-9f9a-489a-b6a5-ea7a70a85cee",
"name": "Calculate Total Price",
"type": "n8n-nodes-base.code",
"position": [
6340,
2040
],
"parameters": {
"jsCode": "// Get the input data\nconst input = $input.all()[0].json;\n\n// Debug: Log the entire input to see all available fields\nconsole.log(\"Complete Input Data:\", JSON.stringify(input, null, 2));\n\n// Improved number parser that handles different formats\nconst getNumber = (value) => {\n if (value === undefined || value === null || value === \"\") return null;\n \n // Remove any currency symbols, commas, or extra spaces\n const cleaned = String(value)\n .replace(/[^\\d.-]/g, '')\n .trim();\n \n const num = parseFloat(cleaned);\n return isNaN(num) ? null : num;\n};\n\n// Use EXACT field names from your webhook payload\nconst quantity = getNumber(input[\"Quantity Received\"]); // Not \"Quantity Received\"\nconst unitPrice = getNumber(input[\"Unit Price\"]); // Not \"Unit Price\"\n\n// Validate\nif (quantity === null) throw new Error(`Invalid quantity: ${input[\"Quantity Received\"]}`);\nif (unitPrice === null) throw new Error(`Invalid price: ${input[\"Unit Price\"]}`);\n\n// Calculate total\nconst totalPrice = quantity * unitPrice;\n\n// Return results\n// Return clean output without debug info\nreturn {\n json: {\n \"Timestamp\": new Date().toISOString(),\n \"Product ID\": input[\"Product ID\"],\n \"Supplier Name\": input[\"Supplier Name\"],\n \"Material Name\": input[\"Material Name\"],\n \"Quantity Received\": quantity,\n \"Description\": input[\"Description\"] || \"\",\n \"Measurement Unit\": input[\"Measurement Unit\"],\n \"Unit Price\": unitPrice,\n \"Total Price\": totalPrice.toFixed(2),\n \"Date of Delivery\": input[\"Date of Delivery\"],\n \"Received By\": input[\"Received By\"],\n \"Submission ID\": input[\"Submission ID\"]\n }\n};"
},
"typeVersion": 2
},
{
"id": "4ce817b0-2283-438f-82c7-6f4901fffdd3",
"name": "Calculate Updated Current Stock",
"type": "n8n-nodes-base.code",
"position": [
7640,
1840
],
"parameters": {
"mode": "runOnceForEachItem",
"jsCode": "const existingStock = parseFloat(\n$('Lookup Existing Stock').first().json['Current Stock']\n|| 0);\nconst newQuantity = parseFloat(\n $('Validate Quantity Received').first().json['Quantity Received']);\nconst updatedStock = existingStock + newQuantity;\n\n\n \nreturn {\n json: {\n ...$json,\n \"Updated Current Stock\": updatedStock\n }\n};"
},
"typeVersion": 2
},
{
"id": "79fa9b6a-45c7-43bd-b5ba-bc2526a87d1e",
"name": "Validate Quantity Received",
"type": "n8n-nodes-base.code",
"position": [
6840,
1820
],
"parameters": {
"jsCode": "const input = $input.all()[0].json;\n\nconst getNumber = (value) => {\n if (!value) return 0; // Default to 0 if null/undefined\n const cleaned = String(value).replace(/[^\\d.-]/g, '').trim();\n const num = parseFloat(cleaned);\n return isNaN(num) ? 0 : num;\n};\n\n\n// Use EXACT field names from your webhook payload\nconst quantity = getNumber(input[\"Quantity Received\"]); // Not \"Quantity Received\"\nif (quantity === 0) throw new Error(`Invalid quantity: ${input[\"Quantity Received\"]}`);\n\nreturn {\n json: {\n ...input,\n \"Quantity Received\": quantity // Ensure it’s a number\n }\n};"
},
"typeVersion": 2
},
{
"id": "298cee40-074c-4888-af10-05b0be136a75",
"name": "Initialize New Product stock",
"type": "n8n-nodes-base.googleSheets",
"position": [
7860,
2200
],
"parameters": {
"columns": {
"value": {
"Product ID": "={{ $('Validate Quantity Received').item.json['Product ID'] }}",
"Last Updated": "={{ $('Validate Quantity Received').item.json['Date of Delivery'] }}",
"Current Stock": "={{ $('Validate Quantity Received').item.json['Quantity Received'] }}",
"Material Name": "={{ $('Validate Quantity Received').item.json['Material Name'] }}",
"Previous Stock": "=0",
"Measurement Unit": "={{ $('Validate Quantity Received').item.json['Measurement Unit'] }}",
"Minimum Stock Level": "50"
},
"schema": [
{
"id": "Product ID",
"type": "string",
"display": true,
"required": false,
"displayName": "Product ID",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "Material Name",
"type": "string",
"display": true,
"required": false,
"displayName": "Material Name",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "Previous Stock",
"type": "string",
"display": true,
"removed": false,
"required": false,
"displayName": "Previous Stock",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "Current Stock",
"type": "string",
"display": true,
"required": false,
"displayName": "Current Stock",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "Measurement Unit",
"type": "string",
"display": true,
"removed": false,
"required": false,
"displayName": "Measurement Unit",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "Last Updated",
"type": "string",
"display": true,
"required": false,
"displayName": "Last Updated",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "Minimum Stock Level",
"type": "string",
"display": true,
"required": false,
"displayName": "Minimum Stock Level",
"defaultMatch": false,
"canBeUsedToMatch": true
}
],
"mappingMode": "defineBelow",
"matchingColumns": [],
"attemptToConvertTypes": false,
"convertFieldsToString": false
},
"options": {},
"operation": "append",
"sheetName": {
"__rl": true,
"mode": "list",
"value": 1019183415,
"cachedResultUrl": "https://docs.google.com/spreadsheets/d/1q0S6AVK7uxZG8sUQkpcZr01KToHPjOZ0gG3zKHLR6lw/edit#gid=1019183415",
"cachedResultName": "Current Stock"
},
"documentId": {
"__rl": true,
"mode": "list",
"value": "1q0S6AVK7uxZG8sUQkpcZr01KToHPjOZ0gG3zKHLR6lw",
"cachedResultUrl": "https://docs.google.com/spreadsheets/d/1q0S6AVK7uxZG8sUQkpcZr01KToHPjOZ0gG3zKHLR6lw/edit?usp=drivesdk",
"cachedResultName": "Plumbee Raw Material Delivery (Responses)"
}
},
"typeVersion": 4.5
},
{
"id": "4f102052-db49-4767-b856-41d5e4a6cf33",
"name": "Update Current Stock",
"type": "n8n-nodes-base.googleSheets",
"position": [
7860,
1940
],
"parameters": {
"columns": {
"value": {
"Product ID": "={{ $json[\"Product ID\"] }}",
"Last Updated": "={{ $json['Last Updated'] }}",
"Current Stock": "={{ $json['Updated Current Stock'] }}",
"Material Name": "={{ $json['Material Name'] }}",
"Measurement Unit": "={{ $json['Measurement Unit'] }}",
"Minimum Stock Level": "50"
},
"schema": [
{
"id": "Product ID",
"type": "string",
"display": true,
"removed": false,
"required": false,
"displayName": "Product ID",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "Material Name",
"type": "string",
"display": true,
"required": false,
"displayName": "Material Name",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "Previous Stock",
"type": "string",
"display": true,
"removed": false,
"required": false,
"displayName": "Previous Stock",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "Current Stock",
"type": "string",
"display": true,
"required": false,
"displayName": "Current Stock",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "Measurement Unit",
"type": "string",
"display": true,
"removed": false,
"required": false,
"displayName": "Measurement Unit",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "Last Updated",
"type": "string",
"display": true,
"required": false,
"displayName": "Last Updated",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "Minimum Stock Level",
"type": "string",
"display": true,
"required": false,
"displayName": "Minimum Stock Level",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "row_number",
"type": "string",
"display": true,
"removed": true,
"readOnly": true,
"required": false,
"displayName": "row_number",
"defaultMatch": false,
"canBeUsedToMatch": true
}
],
"mappingMode": "defineBelow",
"matchingColumns": [
"Product ID"
],
"attemptToConvertTypes": false,
"convertFieldsToString": false
},
"options": {},
"operation": "update",
"sheetName": {
"__rl": true,
"mode": "list",
"value": 1019183415,
"cachedResultUrl": "https://docs.google.com/spreadsheets/d/1q0S6AVK7uxZG8sUQkpcZr01KToHPjOZ0gG3zKHLR6lw/edit#gid=1019183415",
"cachedResultName": "Current Stock"
},
"documentId": {
"__rl": true,
"mode": "list",
"value": "1q0S6AVK7uxZG8sUQkpcZr01KToHPjOZ0gG3zKHLR6lw",
"cachedResultUrl": "https://docs.google.com/spreadsheets/d/1q0S6AVK7uxZG8sUQkpcZr01KToHPjOZ0gG3zKHLR6lw/edit?usp=drivesdk",
"cachedResultName": "Plumbee Raw Material Delivery (Responses)"
}
},
"typeVersion": 4.5
},
{
"id": "33d107ac-960e-44aa-b643-993ef4973beb",
"name": "LookUp Current stock",
"type": "n8n-nodes-base.googleSheets",
"position": [
8080,
1940
],
"parameters": {
"options": {},
"filtersUI": {
"values": [
{
"lookupValue": "={{ $json['Product ID'] }}",
"lookupColumn": "Product ID"
}
]
},
"sheetName": {
"__rl": true,
"mode": "list",
"value": 1019183415,
"cachedResultUrl": "https://docs.google.com/spreadsheets/d/1q0S6AVK7uxZG8sUQkpcZr01KToHPjOZ0gG3zKHLR6lw/edit#gid=1019183415",
"cachedResultName": "Current Stock"
},
"documentId": {
"__rl": true,
"mode": "list",
"value": "1q0S6AVK7uxZG8sUQkpcZr01KToHPjOZ0gG3zKHLR6lw",
"cachedResultUrl": "https://docs.google.com/spreadsheets/d/1q0S6AVK7uxZG8sUQkpcZr01KToHPjOZ0gG3zKHLR6lw/edit?usp=drivesdk",
"cachedResultName": "Plumbee Raw Material Delivery (Responses)"
}
},
"typeVersion": 4.5
},
{
"id": "e0c03d90-f580-43f4-b794-2d278d123b08",
"name": "New Row Current Stock",
"type": "n8n-nodes-base.supabase",
"position": [
7860,
2520
],
"parameters": {
"tableId": "Current Stock",
"fieldsUi": {
"fieldValues": [
{
"fieldId": "Product ID",
"fieldValue": "={{ $('Validate Quantity Received').item.json['Product ID'] }}"
},
{
"fieldId": "Material Name",
"fieldValue": "={{ $('Validate Quantity Received').item.json['Material Name'] }}"
},
{
"fieldId": "Previous Stock",
"fieldValue": "0"
},
{
"fieldId": "Current Stock",
"fieldValue": "={{ $('Validate Quantity Received').item.json['Quantity Received'] }}"
},
{
"fieldId": "Measurement Unit",
"fieldValue": "={{ $('Validate Quantity Received').item.json['Measurement Unit'] }}"
},
{
"fieldId": "Last Updated",
"fieldValue": "={{ $('Validate Quantity Received').item.json['Date of Delivery'] }}"
},
{
"fieldId": "Minimum Stock Level",
"fieldValue": "50"
}
]
}
},
"typeVersion": 1
},
{
"id": "f9e1fae8-ce0a-4ab7-9dbb-f2eaccdf0ac9",
"name": "Current Stock Update",
"type": "n8n-nodes-base.supabase",
"position": [
7860,
1720
],
"parameters": {
"filters": {
"conditions": [
{
"keyName": "Product ID",
"keyValue": "={{ $json['Product ID'] }}",
"condition": "eq"
}
]
},
"tableId": "Current Stock",
"fieldsUi": {
"fieldValues": [
{
"fieldId": "Product ID",
"fieldValue": "={{ $json['Product ID'] }}"
},
{
"fieldId": "Material Name",
"fieldValue": "={{ $json['Material Name'] }}"
},
{
"fieldId": "Current Stock",
"fieldValue": "={{ $json['Updated Current Stock'] }}"
},
{
"fieldId": "Measurement Unit",
"fieldValue": "={{ $json['Measurement Unit'] }}"
},
{
"fieldId": "Last Updated",
"fieldValue": "={{ $json['Last Updated'] }}"
},
{
"fieldId": "Minimum Stock Level",
"fieldValue": "50"
}
]
},
"operation": "update"
},
"typeVersion": 1
},
{
"id": "ef8ac9f6-a26e-4e74-b0f6-59066991a343",
"name": "Search Current Stock",
"type": "n8n-nodes-base.supabase",
"position": [
6960,
2260
],
"parameters": {
"filters": {
"conditions": [
{
"keyName": "Product ID",
"keyValue": "={{ $json[\"Product ID\"] }}",
"condition": "eq"
}
]
},
"tableId": "Current Stock",
"operation": "getAll",
"returnAll": true
},
"typeVersion": 1,
"alwaysOutputData": true
},
{
"id": "3e519621-e955-4033-8197-249c5e153dea",
"name": "Format response",
"type": "n8n-nodes-base.itemLists",
"position": [
7620,
2220
],
"parameters": {
"operation": "removeDuplicates"
},
"typeVersion": 3
},
{
"id": "16b0aefb-b295-47ef-b818-ab133ac8190f",
"name": "Merge",
"type": "n8n-nodes-base.merge",
"position": [
7200,
2040
],
"parameters": {
"mode": "chooseBranch"
},
"typeVersion": 3.1,
"alwaysOutputData": true
},
{
"id": "d7f06346-91fc-427a-ad23-e1547180f3e3",
"name": "Low stock Detection2",
"type": "n8n-nodes-base.code",
"position": [
8380,
1940
],
"parameters": {
"mode": "runOnceForEachItem",
"jsCode": "const currentStock = parseFloat($input.item.json[\"Current Stock\"]);\nconst minStock = parseFloat($input.item.json[\"Minimum Stock Level\"]);\n\n// Check if stock is below minimum\nconst isLow = currentStock < minStock;\n\nreturn {\n json: {\n ...$input.item.json,\n \"Is Low\": isLow,\n \"Alert Message\": isLow ? \n `Low stock alert: ${$input.item.json[\"Material Name\"]} (ID: ${$input.item.json[\"Product ID\"]}) - Current Stock: ${currentStock} ${$input.item.json[\"Measurement Unit\"]}, Minimum: ${minStock}` \n : null\n }\n};"
},
"typeVersion": 2
},
{
"id": "1c054902-eb01-4f22-9e0b-31077a0ea978",
"name": "Sticky Note4",
"type": "n8n-nodes-base.stickyNote",
"position": [
5820,
1620
],
"parameters": {
"color": 3,
"width": 2840,
"height": 1380,
"content": "# Raw Materials Receiving and Stock Update"
},
"typeVersion": 1
},
{
"id": "e0003f1e-1ab5-4b7e-a241-02eeed000c51",
"name": "Sticky Note2",
"type": "n8n-nodes-base.stickyNote",
"position": [
8720,
1620
],
"parameters": {
"width": 2700,
"height": 2840,
"content": "\n# Raw Materials Inventory Management with Google Sheets and Supabase using n8n Webhooks\n\n\n## Introduction\nThis n8n automation streamlines raw materials inventory management by automating the receipt of materials, issuing materials upon approval, updating stock levels, and sending low stock alerts. It integrates webhooks, Google Sheets, Supabase, and Gmail to ensure efficient inventory tracking and communication.\n\n## Problem Statement\nManual inventory management is time-consuming and error-prone, often leading to stock discrepancies, delayed approvals for material issuance, and missed low stock alerts. This automation addresses these issues by providing a seamless workflow for receiving raw materials, processing issue requests, and monitoring stock levels in real time.\n\n## Target Audience\nThis template is designed for:\n- Small to medium-sized businesses managing raw materials inventory.\n- Inventory managers seeking to automate stock updates and approvals.\n- n8n users familiar with Google Sheets, Supabase, and Gmail integrations.\n\n## Description\n\n### Flow 1: Raw Materials Receiving and Stock Update\n**Purpose**: Automates the receipt of raw materials, calculates costs, updates stock, and sends low stock alerts.\n\n- **Receive Raw Materials Webhook**\n - **Purpose**: Receives raw material data via HTTP POST at a webhook URL from a form submission.\n - **Input**: JSON with fields like `product_id`, `quantity_received`, `unit_price`, submitted via a form (e.g., Google Form or custom form).\n - **Output**: Raw webhook data.\n - **Notes**: Expects `Content-Type: application/json`.\n\n- **Standardize Raw Material Data**\n - **Purpose**: Maps webhook data into a consistent format.\n - **Input**: Webhook JSON from form submission.\n - **Output**: JSON with fields like `Timestamp`, `Product ID`, `Quantity Received`.\n - **Notes**: Aligns field names for downstream nodes.\n\n- **Calculate Total Price**\n - **Purpose**: Computes total cost and validates numeric inputs.\n - **Input**: Standardized JSON.\n - **Output**: JSON with `Total Price` (Quantity Received * Unit Price).\n - **Notes**: Uses a custom function to handle invalid numbers.\n\n- **Append Raw Materials**\n - **Purpose**: Records the receipt in Google Sheets.\n - **Input**: Calculated JSON.\n - **Output**: Updated \"Raw Materials\" sheet with new record.\n - **Notes**: Requires Google Sheets credentials (to be configured by the user).\n\n- **Check Quantity Received Validity**\n - **Purpose**: Ensures `Quantity Received` is a positive number.\n - **Input**: JSON from Append Raw Materials.\n - **Output**: Validated JSON with numeric `Quantity Received`.\n - **Notes**: Throws error if invalid.\n\n- **Lookup Existing Stock**\n - **Purpose**: Retrieves existing stock for the `Product ID`.\n - **Input**: Validated JSON.\n - **Output**: JSON with `Current Stock` from \"Current Stock\" sheet.\n - **Notes**: Google Sheets lookup by `Product ID`.\n\n- **Check If Product Exists**\n - **Purpose**: Branches based on whether the `Product ID` exists in stock.\n - **Input**: JSON from Lookup Existing Stock.\n - **Output**: True/False branch.\n - **Notes**: Condition checks for `Product ID` existence.\n\n- **Calculate Updated Current Stock** (True Branch)\n - **Purpose**: Updates stock by adding `Quantity Received`.\n - **Input**: JSON with existing stock.\n - **Output**: JSON with `Updated Current Stock`.\n - **Notes**: Ensures numeric accuracy.\n\n- **Update Current Stock** (True Branch)\n - **Purpose**: Updates the \"Current Stock\" sheet with new stock.\n - **Input**: Updated stock JSON.\n - **Output**: Updated \"Current Stock\" sheet.\n - **Notes**: Matches by `Product ID`.\n\n- **Retrieve Updated Stock for Check** (True Branch)\n - **Purpose**: Retrieves updated stock for low stock check.\n - **Input**: Updated stock JSON.\n - **Output**: JSON with current stock data.\n - **Notes**: Google Sheets lookup.\n\n- **Detect Low Stock Level** (True Branch)\n - **Purpose**: Flags if stock falls below the minimum level.\n - **Input**: Retrieved stock data.\n - **Output**: JSON with `Is Low` flag and `Alert Message`.\n - **Notes**: Compares with `Minimum Stock Level` (default: 50).\n\n- **Trigger Low Stock Alert** (True Branch)\n - **Purpose**: Triggers notification if stock is low.\n - **Input**: Low stock detection JSON.\n - **Output**: True branch sends email.\n - **Notes**: Condition: `{{ $json['Is Low'] }}`.\n\n- **Send Low Stock Email Alert** (True Branch, Low)\n - **Purpose**: Sends low stock alert email to the stock manager.\n - **Input**: JSON with alert details.\n - **Output**: HTML email to a user-configured email address.\n - **Notes**: Includes product info and reorder link; email address must be set by the user.\n\n- **Add New Product to Stock** (False Branch)\n - **Purpose**: Adds new product to \"Current Stock\" sheet.\n - **Input**: Validated JSON.\n - **Output**: New row with initial stock (Quantity Received).\n - **Notes**: Sets `Minimum Stock Level` to 50.\n\n- **Current Stock Update** (True Branch, Supabase)\n - **Purpose**: Updates Supabase `Current Stock` table.\n - **Input**: Updated stock JSON.\n - **Output**: Updated Supabase record.\n - **Notes**: Matches by `Product ID`; requires user-configured Supabase credentials.\n\n- **New Row Current Stock** (False Branch, Supabase)\n - **Purpose**: Inserts new product into Supabase `Current Stock` table.\n - **Input**: Validated JSON.\n - **Output**: New Supabase record.\n - **Notes**: Sets initial stock; requires Supabase credentials.\n\n- **Search Current Stock** (Supabase)\n - **Purpose**: Retrieves `Current Stock` records for `Product ID`.\n - **Input**: JSON with `Product ID`.\n - **Output**: JSON array of matching records.\n - **Notes**: Uses `returnAll: true`.\n\n- **New Record Raw** (Supabase)\n - **Purpose**: Inserts raw material record into Supabase `Raw Materials` table.\n - **Input**: Calculated JSON.\n - **Output**: New Supabase record.\n - **Notes**: Auto-maps input data.\n\n- **Format Response**\n - **Purpose**: Removes duplicates from response.\n - **Input**: Search Current Stock data.\n - **Output**: Cleaned JSON array.\n - **Notes**: Ensures unique records.\n\n- **Combine Stock Update Branches**\n - **Purpose**: Combines branches (existing/new product).\n - **Input**: Outputs from Check If Product Exists branches.\n - **Output**: Merged JSON.\n - **Notes**: Ensures data continuity.\n\n**Impact**: Automates raw material receipt, ensures accurate stock updates, and provides timely low stock notifications.\n\n### Flow 2: Material Issue Request and Approval\n**Purpose**: Automates material issue requests, processes approvals/rejections, updates stock, and sends low stock alerts.\n\n- **Receive Material Issue Webhook**\n - **Purpose**: Receives material issue request via HTTP POST at a webhook URL from a form submission.\n - **Input**: JSON with `Product ID`, `Quantity Requested`, etc., submitted via a form (e.g., Google Form or custom form).\n - **Output**: Raw webhook data.\n - **Notes**: Webhook trigger for issue requests.\n\n- **Standardize Data**\n - **Purpose**: Normalizes request data and generates approval link.\n - **Input**: Webhook JSON from form submission.\n - **Output**: JSON with `Status` \"Pending,\" `Approval Link`.\n - **Notes**: Maps form fields for consistency.\n\n- **Validate Issue Request Data**\n - **Purpose**: Ensures `Quantity Requested` is a positive number.\n - **Input**: Standardized JSON.\n - **Output**: Validated JSON or error.\n - **Notes**: JavaScript validation.\n\n- **Verify Requested Quantity**\n - **Purpose**: Validates additional fields like `Product ID` and `Submission ID`.\n - **Input**: Validated JSON.\n - **Output**: Further validated JSON or error.\n - **Notes**: Ensures data integrity.\n\n- **Append Material Request**\n - **Purpose**: Records request in \"Materials Issued\" sheet.\n - **Input**: Verified JSON.\n - **Output**: Updated \"Materials Issued\" sheet.\n - **Notes**: Google Sheets append operation.\n\n- **Check Available Stock for Issue**\n - **Purpose**: Retrieves `Current Stock` for `Product ID`.\n - **Input**: Appended JSON.\n - **Output**: JSON with stock data.\n - **Notes**: Google Sheets lookup.\n\n#### Approval Process\nThe following steps handle the approval of material issue requests, ensuring that requests are reviewed and either approved or rejected before stock is updated.\n\n- **Prepare Approval**\n - **Purpose**: Checks if stock is sufficient to fulfill the request.\n - **Input**: Stock data from Check Available Stock for Issue.\n - **Output**: JSON with `Is Enough` flag (true if `Current Stock` >= `Quantity Requested`).\n - **Notes**: Prepares data for the approval email.\n\n- **Send Approval Request**\n - **Purpose**: Sends an email to the approver with clickable Approve/Reject buttons.\n - **Input**: JSON with `Is Enough`, `Product ID`, `Quantity Requested`, and `Approval Link`.\n - **Output**: HTML email to a user-configured email address.\n - **Notes**: Email contains buttons linking to the Receive Approval Response webhook; email address must be set by the user.\n\n- **Receive Approval Response**\n - **Purpose**: Captures the approver’s decision via a webhook triggered by clicking Approve/Reject.\n - **Input**: Webhook parameters like `submissionId`, `action` (\"approve\" or \"reject\"), `quantity`.\n - **Output**: Raw webhook data with approval details.\n - **Notes**: Webhook URL must be configured to match the links in the approval email.\n\n- **Format Approval Response**\n - **Purpose**: Processes the approval response and adds metadata.\n - **Input**: Webhook JSON from Receive Approval Response.\n - **Output**: JSON with `Action`, `Approved Quantity`, `Approval Date`.\n - **Notes**: Sets `Approval Date` to the current timestamp.\n\n- **Verify Approval Data**\n - **Purpose**: Validates the approval response to ensure it’s complete and correct.\n - **Input**: Formatted JSON.\n - **Output**: Validated JSON or error.\n - **Notes**: Checks for valid `Submission ID`, `Action`, and `Approved Quantity` (> 0).\n\n- **Retrieve Issue Request Details**\n - **Purpose**: Retrieves the original issue request for updating.\n - **Input**: Validated JSON with `Submission ID`.\n - **Output**: JSON with request data from \"Materials Issued\" sheet.\n - **Notes**: Google Sheets lookup by `Submission ID`.\n\n- **Process Approval Decision**\n - **Purpose**: Branches the flow based on the approver’s decision.\n - **Input**: JSON with `Action` (\"approve\" or \"reject\").\n - **Output**: True branch (approved) or False branch (rejected).\n - **Notes**: Condition: `{{ $json['Action'] === \"approve\" }}`.\n\n#### Post-Approval Steps\n- **Get Stock for Issue Update** (True Branch, Approved)\n - **Purpose**: Retrieves the latest `Current Stock` before updating.\n - **Input**: Approved JSON.\n - **Output**: JSON with stock data.\n - **Notes**: Google Sheets lookup.\n\n- **Deduct Issued Stock** (True Branch, Approved)\n - **Purpose**: Reduces stock by `Approved Quantity`.\n - **Input**: Stock and approval data.\n - **Output**: JSON with `Updated Current Stock`.\n - **Notes**: Errors if stock is insufficient.\n\n- **Update Stock After Issue** (True Branch, Approved)\n - **Purpose**: Updates \"Current Stock\" sheet with new stock.\n - **Input**: Updated stock JSON.\n - **Output**: Updated \"Current Stock\" sheet.\n - **Notes**: Matches by `Product ID`.\n\n- **Retrieve Stock After Issue** (True Branch, Approved)\n - **Purpose**: Retrieves updated stock for low stock check.\n - **Input**: Updated stock JSON.\n - **Output**: JSON with stock data.\n - **Notes**: Google Sheets lookup.\n\n- **Detect Low Stock After Issue** (True Branch, Approved)\n - **Purpose**: Flags if stock is low after issuance.\n - **Input**: Retrieved stock data.\n - **Output**: JSON with `Is Low` flag and `Alert Message`.\n - **Notes**: Compares with `Minimum Stock Level`.\n\n- **Trigger Low Stock Alert After Issue** (True Branch, Approved)\n - **Purpose**: Triggers notification if stock is low.\n - **Input**: Low stock detection JSON.\n - **Output**: True branch sends email.\n - **Notes**: Condition: `{{ $json['Is Low'] }}`.\n\n- **Send Low Stock Email After Issue** (True Branch, Low)\n - **Purpose**: Sends low stock alert email.\n - **Input**: JSON with alert details.\n - **Output**: HTML email to a user-configured email address.\n - **Notes**: Includes product info; email address must be set by the user.\n\n- **Update Issue Request Status** (True/False Branch)\n - **Purpose**: Updates request status in \"Materials Issued\" sheet.\n - **Input**: Approval action JSON.\n - **Output**: Updated sheet with `Status` \"Approved\" or \"Rejected.\"\n - **Notes**: Google Sheets update; includes `Approved By` and `Approval Date` if approved.\n\n- **Combine Stock Lookup Results**\n - **Purpose**: Combines stock lookup branches.\n - **Input**: Outputs from Check Available Stock for Issue and Search Stock by Product ID.\n - **Output**: Merged JSON.\n - **Notes**: Ensures data continuity.\n\n- **Create Record Issue** (Supabase)\n - **Purpose**: Inserts issue request into Supabase `Materials Issued` table.\n - **Input**: Verified JSON.\n - **Output**: New Supabase record.\n - **Notes**: Auto-maps data; requires user-configured credentials.\n\n- **Search Stock by Product ID** (Supabase)\n - **Purpose**: Retrieves `Current Stock` records.\n - **Input**: JSON with `Product ID`.\n - **Output**: JSON array of records.\n - **Notes**: Filters by `Product ID`.\n\n- **Issues Table Update** (Supabase, True/False Branch)\n - **Purpose**: Updates Supabase `Materials Issued` table.\n - **Input**: Approval action JSON.\n - **Output**: Updated Supabase record.\n - **Notes**: Matches by `Submission ID`.\n\n- **Update Current Stock** (Supabase, True Branch)\n - **Purpose**: Updates Supabase `Current Stock` table.\n - **Input**: Updated stock JSON.\n - **Output**: Updated Supabase record.\n - **Notes**: Matches by `Product ID`.\n\n- **Combine Issue Lookup Branches**\n - **Purpose**: Combines issue lookup branches.\n - **Input**: Outputs from Retrieve Issue Request Details and Search Issue by Submission ID.\n - **Output**: Merged JSON.\n - **Notes**: Ensures data continuity.\n\n- **Search Issue by Submission ID** (Supabase)\n - **Purpose**: Retrieves issue records by `Submission ID`.\n - **Input**: Validated JSON.\n - **Output**: JSON array of records.\n - **Notes**: Filters by `Submission ID`.\n\n**Impact**: Streamlines material issuance, ensures accurate stock updates, and provides proactive low stock alerts.\n\n## Conclusion\nThis automation enhances inventory management by integrating n8n with Google Sheets, Supabase, and Gmail. It reduces manual effort, ensures data accuracy, and keeps teams informed with timely alerts. Community feedback and contributions are welcome!"
},
"typeVersion": 1
},
{
"id": "8b6ee379-d020-44d7-892f-7b5479fa6944",
"name": "Receive Raw Materials Webhook",
"type": "n8n-nodes-base.webhook",
"position": [
5940,
2040
],
"webhookId": "be8290c0-bdd9-489e-938a-ba7a3dd5745e",
"parameters": {
"path": "Pb-raw-materials",
"options": {
"responseHeaders": {
"entries": [
{
"name": "Content-Type",
"value": "application/json"
}
]
}
},
"httpMethod": "POST"
},
"typeVersion": 2
},
{
"id": "087a3182-2a5d-47a0-a3ac-33f1f3eb6a31",
"name": "Standardize Raw Material Data",
"type": "n8n-nodes-base.set",
"position": [
6160,
2040
],
"parameters": {
"options": {},
"assignments": {
"assignments": [
{
"id": "f3b4487d-ab8e-4b5d-9bea-19ec7195a76c",
"name": "Timestamp",
"type": "string",
"value": "={{ $json.body.timestamp }}"
},
{
"id": "f2f15ff8-d8f6-4bd6-b892-ec2fc1f92c29",
"name": "Product ID",
"type": "string",
"value": "={{ $json.body.product_id }}"
},
{
"id": "9e48b196-6176-4077-bac9-32bef81dd1c0",
"name": "Supplier Name",
"type": "string",
"value": "={{ $json.body.supplier_name }}"
},
{
"id": "4b79875e-f4ee-4452-8507-5c7f2d85786e",
"name": "Quantity Received",
"type": "number",
"value": "={{ $json.body.quantity_received }}"
},
{
"id": "d223e0fa-f80a-4cdb-9d34-60f453feecc0",
"name": "Description",
"type": "string",
"value": "={{ $json.body.description }}"
},
{
"id": "f87b4c22-d8db-470b-9c65-14a3e07ba31a",
"name": "Measurement Unit",
"type": "string",
"value": "={{ $json.body.measurement_unit }}"
},
{
"id": "0a0be214-59b7-4cb6-9d0e-0c3e06bba070",
"name": "Unit Price",
"type": "number",
"value": "={{ $json.body.unit_price }}"
},
{
"id": "0bbac1f8-c89b-4af8-a82e-3f937014bbce",
"name": "Date of Delivery",
"type": "string",
"value": "={{ $json.body.date_of_delivery }}"
},
{
"id": "02cd7f92-cd88-48ed-9f9d-8a64a5d1c95e",
"name": "Received By",
"type": "string",
"value": "={{ $json.body.received_by }}"
},
{
"id": "5a484f8b-a3f7-48bf-a34c-78e1f5e22af5",
"name": "Total Price",
"type": "string",
"value": "={{ $json.body.total_price }}"
},
{
"id": "2bbf891b-372c-4f81-9176-bc50a94a543a",
"name": "Material Name",
"type": "string",
"value": "={{ $json.body.material_name }}"
},
{
"id": "f5ce72d9-a704-4410-ae5b-2c0b1a3b907b",
"name": "Submission ID",
"type": "string",
"value": "={{ $json.body.submissionId }}"
}
]
},
"includeOtherFields": "="
},
"typeVersion": 3.4
},
{
"id": "ff7d279b-2447-4423-a0ff-4512e4a8a913",
"name": "Lookup Existing Stock",
"type": "n8n-nodes-base.googleSheets",
"position": [
7000,
1820
],
"parameters": {
"options": {},
"filtersUI": {
"values": [
{
"lookupValue": "={{ $json[\"Product ID\"] }}",
"lookupColumn": "Product ID"
}
]
},
"sheetName": {
"__rl": true,
"mode": "list",
"value": 1019183415,
"cachedResultUrl": "https://docs.google.com/spreadsheets/d/1q0S6AVK7uxZG8sUQkpcZr01KToHPjOZ0gG3zKHLR6lw/edit#gid=1019183415",
"cachedResultName": "Current Stock"
},
"documentId": {
"__rl": true,
"mode": "list",
"value": "1q0S6AVK7uxZG8sUQkpcZr01KToHPjOZ0gG3zKHLR6lw",
"cachedResultUrl": "https://docs.google.com/spreadsheets/d/1q0S6AVK7uxZG8sUQkpcZr01KToHPjOZ0gG3zKHLR6lw/edit?usp=drivesdk",
"cachedResultName": "Plumbee Raw Material Delivery (Responses)"
}
},
"typeVersion": 4.5,
"alwaysOutputData": true
},
{
"id": "52698913-69d6-4473-9e77-7ef4530bf81a",
"name": "Check If Product ID Exists",
"type": "n8n-nodes-base.if",
"position": [
7420,
2040
],
"parameters": {
"options": {},
"conditions": {
"options": {
"version": 2,
"leftValue": "",
"caseSensitive": true,
"typeValidation": "loose"
},
"combinator": "and",
"conditions": [
{
"id": "bcff2100-54d5-4480-87ab-1d7ce23bd007",
"operator": {
"type": "string",
"operation": "exists",
"singleValue": true
},
"leftValue": "={{ $json[\"Product ID\"] }}",
"rightValue": ""
}
]
},
"looseTypeValidation": true
},
"typeVersion": 2.2
},
{
"id": "ecc30bd0-206e-448f-952a-7a2c4ea98bc5",
"name": "New Record Row",
"type": "n8n-nodes-base.supabase",
"position": [
6700,
2260
],
"parameters": {
"tableId": "Raw Materials",
"dataToSend": "autoMapInputData"
},
"typeVersion": 1
},
{
"id": "9ffaeb38-b6fc-47f7-8611-c7da61c9cd08",
"name": "Trigger Low Stock Alert",
"type": "n8n-nodes-base.if",
"position": [
8200,
2280
],
"parameters": {
"options": {},
"conditions": {
"options": {
"version": 2,
"leftValue": "",
"caseSensitive": true,
"typeValidation": "strict"
},
"combinator": "and",
"conditions": [
{
"id": "e0493b94-1e9c-4f68-ba66-4abd2bd5b569",
"operator": {
"type": "boolean",
"operation": "true",
"singleValue": true
},
"leftValue": "={{ $json['Is Low'] }}",
"rightValue": "="
}
]
}
},
"typeVersion": 2.2
},
{
"id": "469bb7fe-5595-4503-9034-8df0c974cbc2",
"name": "Send Low Stock Email Alert",
"type": "n8n-nodes-base.gmail",
"position": [
8440,
2260
],
"webhookId": "fd7aff46-cf63-4ca3-9406-0b90c2f8aa8b",
"parameters": {
"sendTo": "example@gmail.com",
"message": "=<!DOCTYPE html>\n<html lang=\"en\">\n<head>\n <meta charset=\"UTF-8\">\n <meta name=\"viewport\" content=\"width=device-width, initial-scale=1.0\">\n <title>Low Stock Alert</title>\n <style>\n body {\n font-family: Arial, sans-serif;\n line-height: 1.6;\n color: #333;\n background-color: #f4f4f4;\n margin: 0;\n padding: 0;\n }\n .container {\n width: 80%;\n max-width: 600px;\n margin: 20px auto;\n background-color: #fff;\n padding: 20px;\n border-radius: 8px;\n box-shadow: 0 0 10px rgba(0, 0, 0, 0.1);\n }\n h2 {\n color: #e74c3c;\n text-align: center;\n }\n p {\n margin: 10px 0;\n }\n .alert-message {\n background-color: #ffe6e6;\n padding: 15px;\n border-left: 4px solid #e74c3c;\n margin: 20px 0;\n font-weight: bold;\n }\n ul {\n list-style-type: none;\n padding: 0;\n margin: 20px 0;\n background-color: #f9f9f9;\n padding: 15px;\n border-left: 4px solid #3498db;\n }\n ul li {\n margin: 8px 0;\n }\n ul li strong {\n display: inline-block;\n width: 150px;\n }\n .action {\n text-align: center;\n margin: 20px 0;\n }\n .btn {\n display: inline-block;\n padding: 10px 20px;\n text-decoration: none;\n color: #fff;\n background-color: #3498db;\n border-radius: 5px;\n font-weight: bold;\n }\n .footer {\n text-align: center;\n margin-top: 20px;\n font-size: 0.9em;\n color: #777;\n }\n </style>\n</head>\n<body>\n <div class=\"container\">\n <h2>Low Stock Alert</h2>\n <p>Dear Stock Manager,</p>\n\n <p>We have detected a low stock level for the following material:</p>\n\n <div class=\"alert-message\">\n {{ $json[\"Alert Message\"] }}\n </div>\n\n <ul>\n <li><strong>Product ID:</strong> {{ $json[\"Product ID\"] }}</li>\n <li><strong>Material:</strong> {{ $json[\"Material Name\"] }}</li>\n <li><strong>Current Stock:</strong> {{ $json[\"Current Stock\"] }} {{ $json[\"Measurement Unit\"] }}</li>\n <li><strong>Minimum Stock:</strong> {{ $json[\"Minimum Stock Level\"] }} {{ $json[\"Measurement Unit\"] }}</li>\n </ul>\n\n <div class=\"action\">\n <p>Please take action to reorder this material.</p>\n <a href=\"https://forms.gle/reorder-form\" class=\"btn\">Reorder Now</a>\n </div>\n\n <div class=\"footer\">\n <p>Regards,<br>Your Company</p>\n </div>\n </div>\n</body>\n</html>",
"options": {},
"subject": "Low Stock Alert: Immediate Action Required"
},
"typeVersion": 2.1
},
{
"id": "24fb479d-6f25-4d69-bc5a-925645ae4837",
"name": "Low Stock Email Alert",
"type": "n8n-nodes-base.gmail",
"position": [
8540,
3940
],
"webhookId": "fd7aff46-cf63-4ca3-9406-0b90c2f8aa8b",
"parameters": {
"sendTo": "example@gmail.com",
"message": "=<!DOCTYPE html>\n<html lang=\"en\">\n<head>\n <meta charset=\"UTF-8\">\n <meta name=\"viewport\" content=\"width=device-width, initial-scale=1.0\">\n <title>Low Stock Alert</title>\n <style>\n body {\n font-family: Arial, sans-serif;\n line-height: 1.6;\n color: #333;\n background-color: #f4f4f4;\n margin: 0;\n padding: 0;\n }\n .container {\n width: 80%;\n max-width: 600px;\n margin: 20px auto;\n background-color: #fff;\n padding: 20px;\n border-radius: 8px;\n box-shadow: 0 0 10px rgba(0, 0, 0, 0.1);\n }\n h2 {\n color: #e74c3c;\n text-align: center;\n }\n p {\n margin: 10px 0;\n }\n .alert-message {\n background-color: #ffe6e6;\n padding: 15px;\n border-left: 4px solid #e74c3c;\n margin: 20px 0;\n font-weight: bold;\n }\n ul {\n list-style-type: none;\n padding: 0;\n margin: 20px 0;\n background-color: #f9f9f9;\n padding: 15px;\n border-left: 4px solid #3498db;\n }\n ul li {\n margin: 8px 0;\n }\n ul li strong {\n display: inline-block;\n width: 150px;\n }\n .action {\n text-align: center;\n margin: 20px 0;\n }\n .btn {\n display: inline-block;\n padding: 10px 20px;\n text-decoration: none;\n color: #fff;\n background-color: #3498db;\n border-radius: 5px;\n font-weight: bold;\n }\n .footer {\n text-align: center;\n margin-top: 20px;\n font-size: 0.9em;\n color: #777;\n }\n </style>\n</head>\n<body>\n <div class=\"container\">\n <h2>Low Stock Alert</h2>\n <p>Dear Stock Manager,</p>\n\n <p>We have detected a low stock level for the following material:</p>\n\n <div class=\"alert-message\">\n {{ $json[\"Alert Message\"] }}\n </div>\n\n <ul>\n <li><strong>Product ID:</strong> {{ $json[\"Product ID\"] }}</li>\n <li><strong>Material:</strong> {{ $json[\"Material Name\"] }}</li>\n <li><strong>Current Stock:</strong> {{ $json[\"Current Stock\"] }} {{ $json[\"Measurement Unit\"] }}</li>\n <li><strong>Minimum Stock:</strong> {{ $json[\"Minimum Stock Level\"] }} {{ $json[\"Measurement Unit\"] }}</li>\n </ul>\n\n <div class=\"action\">\n <p>Please take action to reorder this material.</p>\n <a href=\"https://forms.gle/reorder-form\" class=\"btn\">Reorder Now</a>\n </div>\n\n <div class=\"footer\">\n <p>Regards,<br>Your Company</p>\n </div>\n </div>\n</body>\n</html>",
"options": {},
"subject": "Low Stock Alert: Immediate Action Required"
},
"typeVersion": 2.1
},
{
"id": "ac8781e9-f694-467d-b40b-95bdbab98880",
"name": "Validate Issue Request Data",
"type": "n8n-nodes-base.code",
"position": [
6340,
3400
],
"parameters": {
"jsCode": "const input = $input.all()[0].json;\nconst quantityRequested= input[\"Quantity Requested\"];\n\nif (quantityRequested <= 0) throw new Error(`Invalid quantity Requested: ${quantityRequested}. Must be greater than 0`);\n\nreturn { json: input };"
},
"typeVersion": 2
},
{
"id": "6d88db70-6b4f-47c5-8093-ab339762edbe",
"name": "Verify Requested Quantity",
"type": "n8n-nodes-base.code",
"position": [
6560,
3400
],
"parameters": {
"jsCode": "const input = $input.all()[0].json;\nconst quantityRequested = input[\"Quantity Requested\"];\nif (!input[\"Product ID\"]) throw new Error(\"Product ID is missing\");\nif (quantityRequested <= 0) throw new Error(`Invalid quantity requested: ${quantityRequested}`);\nif (!input[\"Submission ID\"]) throw new Error(\"Submission ID is missing\");\nreturn { json: input };"
},
"typeVersion": 2
},
{
"id": "bd2313cc-e3c9-4405-a1ed-8f64969e5bca",
"name": "Check Available Stock for Issue",
"type": "n8n-nodes-base.googleSheets",
"position": [
7000,
3240
],
"parameters": {
"options": {},
"filtersUI": {
"values": [
{
"lookupValue": "={{ $json[\"Product ID\"] }}",
"lookupColumn": "Product ID"
}
]
},
"sheetName": {
"__rl": true,
"mode": "list",
"value": 1019183415,
"cachedResultUrl": "https://docs.google.com/spreadsheets/d/1q0S6AVK7uxZG8sUQkpcZr01KToHPjOZ0gG3zKHLR6lw/edit#gid=1019183415",
"cachedResultName": "Current Stock"
},
"documentId": {
"__rl": true,
"mode": "list",
"value": "1q0S6AVK7uxZG8sUQkpcZr01KToHPjOZ0gG3zKHLR6lw",
"cachedResultUrl": "https://docs.google.com/spreadsheets/d/1q0S6AVK7uxZG8sUQkpcZr01KToHPjOZ0gG3zKHLR6lw/edit?usp=drivesdk",
"cachedResultName": "Plumbee Raw Material Delivery (Responses)"
}
},
"typeVersion": 4.5
},
{
"id": "8cd87b7d-8dc8-41c7-b76e-b5ebe35278b0",
"name": "Format Approval Response",
"type": "n8n-nodes-base.set",
"position": [
6120,
4060
],
"parameters": {
"options": {},
"assignments": {
"assignments": [
{
"id": "1b3dd2ac-a54f-49ea-8302-f3e490de5d00",
"name": "Submission ID",
"type": "string",
"value": "={{ $json.query.submissionId }}"
},
{
"id": "007d727d-09f1-4414-a114-6f526afe877a",
"name": "Action",
"type": "string",
"value": "={{ $json.query.action }}"
},
{
"id": "dbb729e9-feac-48de-add1-ba1f810fafde",
"name": "Approved Quantity",
"type": "number",
"value": "={{ $json.query.quantity }}"
},
{
"id": "8b516a0b-9ca1-4c12-90b3-7b442fef0f17",
"name": "Approved By",
"type": "string",
"value": "={{ $json.query.approvedBy }}"
},
{
"id": "b5a2e71a-038d-4bf7-9edc-2ea606bec091",
"name": "Approval Date",
"type": "string",
"value": "={{ new Date().toISOString() }}"
}
]
}
},
"typeVersion": 3.4
},
{
"id": "6749923b-1032-4adb-b805-eda6efd5ee1c",
"name": "Verify Approval Data",
"type": "n8n-nodes-base.code",
"position": [
6340,
4060
],
"parameters": {
"jsCode": "const input = $input.all()[0].json;\nif (!input[\"Submission ID\"]) throw new Error(\"Submission ID is missing\");\nif (![\"approve\", \"reject\"].includes(input[\"Action\"])) throw new Error(\"Invalid action\");\nif (input[\"Action\"] === \"approve\" && input[\"Approved Quantity\"] <= 0) throw new Error(\"Approved quantity must be greater than 0\");\nreturn { json: input };"
},
"typeVersion": 2
},
{
"id": "c5e34da4-81ec-47dc-aacf-4d6e0cf4256c",
"name": "Retrieve Issue Request Details",
"type": "n8n-nodes-base.googleSheets",
"position": [
6560,
3840
],
"parameters": {
"options": {},
"filtersUI": {
"values": [
{
"lookupValue": "={{ $json[\"Submission ID\"] }}",
"lookupColumn": "Submission ID"
}
]
},
"sheetName": {
"__rl": true,
"mode": "list",
"value": 328307238,
"cachedResultUrl": "https://docs.google.com/spreadsheets/d/1q0S6AVK7uxZG8sUQkpcZr01KToHPjOZ0gG3zKHLR6lw/edit#gid=328307238",
"cachedResultName": "Materials Issued"
},
"documentId": {
"__rl": true,
"mode": "list",
"value": "1q0S6AVK7uxZG8sUQkpcZr01KToHPjOZ0gG3zKHLR6lw",
"cachedResultUrl": "https://docs.google.com/spreadsheets/d/1q0S6AVK7uxZG8sUQkpcZr01KToHPjOZ0gG3zKHLR6lw/edit?usp=drivesdk",
"cachedResultName": "Plumbee Raw Material Delivery (Responses)"
}
},
"typeVersion": 4.5
},
{
"id": "e3c9b60b-fa41-4ec2-9f8f-789ac4fc6323",
"name": "Process Approval Decision",
"type": "n8n-nodes-base.if",
"position": [
6980,
4060
],
"parameters": {
"options": {},
"conditions": {
"options": {
"version": 2,
"leftValue": "",
"caseSensitive": true,
"typeValidation": "loose"
},
"combinator": "and",
"conditions": [
{
"id": "c5734b4b-e63a-4ec4-866f-8c1dace02ef1",
"operator": {
"type": "boolean",
"operation": "true",
"singleValue": true
},
"leftValue": "={{ $('Verify Approval Data').item.json.Action === \"approve\" }}",
"rightValue": ""
}
]
},
"looseTypeValidation": true
},
"typeVersion": 2.2
},
{
"id": "406226c7-89b3-4a09-ba05-4b640a619ae1",
"name": "Get Stock for Issue Update from Current",
"type": "n8n-nodes-base.googleSheets",
"position": [
7220,
3780
],
"parameters": {
"options": {},
"filtersUI": {
"values": [
{
"lookupValue": "={{ $json['Product ID'] }}",
"lookupColumn": "Product ID"
}
]
},
"sheetName": {
"__rl": true,
"mode": "list",
"value": 1019183415,
"cachedResultUrl": "https://docs.google.com/spreadsheets/d/1q0S6AVK7uxZG8sUQkpcZr01KToHPjOZ0gG3zKHLR6lw/edit#gid=1019183415",
"cachedResultName": "Current Stock"
},
"documentId": {
"__rl": true,
"mode": "list",
"value": "1q0S6AVK7uxZG8sUQkpcZr01KToHPjOZ0gG3zKHLR6lw",
"cachedResultUrl": "https://docs.google.com/spreadsheets/d/1q0S6AVK7uxZG8sUQkpcZr01KToHPjOZ0gG3zKHLR6lw/edit?usp=drivesdk",
"cachedResultName": "Plumbee Raw Material Delivery (Responses)"
}
},
"typeVersion": 4.5
},
{
"id": "75046f2a-7949-4280-b6a8-500848e41357",
"name": "Update Stock After Issue",
"type": "n8n-nodes-base.googleSheets",
"position": [
7240,
4040
],
"parameters": {
"columns": {
"value": {
"Status": "={{ $('Verify Approval Data').item.json.Action === \"approve\" ? \"Approved\" : $('Verify Approval Data').item.json.Action === \"reject\" ? \"Rejected\" : $json[\"action\"] }}\n\n",
"Timestamp": "={{ $json.Timestamp }}",
"Issue Date": "={{ $('Verify Approval Data').item.json['Approval Date'] }}",
"Product ID": "={{ $json['Product ID'] }}",
"Description": "={{ $json.Description }}",
"Requested By": "={{ $json['Requested By'] }}",
"Approval Link": "={{ $json['Approval Link'] }}",
"Submission ID": "={{ $json['Submission ID'] }}",
"Measurement Unit": "={{ $json['Measurement Unit'] }}",
"Quantity Requested": "={{ $json['Quantity Requested'] }}"
},
"schema": [
{
"id": "Timestamp",
"type": "string",
"display": true,
"required": false,
"displayName": "Timestamp",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "Product ID",
"type": "string",
"display": true,
"required": false,
"displayName": "Product ID",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "Quantity Requested",
"type": "string",
"display": true,
"required": false,
"displayName": "Quantity Requested",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "Measurement Unit",
"type": "string",
"display": true,
"required": false,
"displayName": "Measurement Unit",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "Requested By",
"type": "string",
"display": true,
"required": false,
"displayName": "Requested By",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "Issue Date",
"type": "string",
"display": true,
"required": false,
"displayName": "Issue Date",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "Description",
"type": "string",
"display": true,
"required": false,
"displayName": "Description",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "Submission ID",
"type": "string",
"display": true,
"removed": false,
"required": false,
"displayName": "Submission ID",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "Status",
"type": "string",
"display": true,
"required": false,
"displayName": "Status",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "Approval Link",
"type": "string",
"display": true,
"required": false,
"displayName": "Approval Link",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "row_number",
"type": "string",
"display": true,
"removed": true,
"readOnly": true,
"required": false,
"displayName": "row_number",
"defaultMatch": false,
"canBeUsedToMatch": true
}
],
"mappingMode": "defineBelow",
"matchingColumns": [
"Submission ID"
],
"attemptToConvertTypes": false,
"convertFieldsToString": false
},
"options": {},
"operation": "update",
"sheetName": {
"__rl": true,
"mode": "list",
"value": 328307238,
"cachedResultUrl": "https://docs.google.com/spreadsheets/d/1q0S6AVK7uxZG8sUQkpcZr01KToHPjOZ0gG3zKHLR6lw/edit#gid=328307238",
"cachedResultName": "Materials Issued"
},
"documentId": {
"__rl": true,
"mode": "list",
"value": "1q0S6AVK7uxZG8sUQkpcZr01KToHPjOZ0gG3zKHLR6lw",
"cachedResultUrl": "https://docs.google.com/spreadsheets/d/1q0S6AVK7uxZG8sUQkpcZr01KToHPjOZ0gG3zKHLR6lw/edit?usp=drivesdk",
"cachedResultName": "Plumbee Raw Material Delivery (Responses)"
}
},
"typeVersion": 4.5
},
{
"id": "3005f241-e0c3-4acd-9998-9b3f2cdece0c",
"name": "Materials Issue Table Update",
"type": "n8n-nodes-base.supabase",
"position": [
7220,
4260
],
"parameters": {
"filters": {
"conditions": [
{
"keyName": "Submission ID",
"keyValue": "={{ $json[\"Submission ID\"] }}",
"condition": "eq"
}
]
},
"tableId": "Materials Issued",
"fieldsUi": {
"fieldValues": [
{
"fieldId": "Timestamp",
"fieldValue": "={{ $json.Timestamp }}"
},
{
"fieldId": "Product ID",
"fieldValue": "={{ $json['Product ID'] }}"
},
{
"fieldId": "Quantity Requested",
"fieldValue": "={{ $json['Quantity Requested'] }}"
},
{
"fieldId": "Measurement Unit",
"fieldValue": "={{ $json['Measurement Unit'] }}"
},
{
"fieldId": "Requested By",
"fieldValue": "={{ $json['Requested By'] }}"
},
{
"fieldId": "Description",
"fieldValue": "={{ $json.Description }}"
},
{
"fieldId": "Status",
"fieldValue": "={{ $('Verify Approval Data').item.json.Action === \"approve\" ? \"Approved\" : $('Verify Approval Data').item.json.Action === \"reject\" ? \"Rejected\" : $json[\"action\"] }}"
},
{
"fieldId": "Approval Link",
"fieldValue": "={{ $json['Approval Link'] }}"
},
{
"fieldId": "Submission ID",
"fieldValue": "={{ $json['Submission ID'] }}"
},
{
"fieldId": "Issue Date",
"fieldValue": "={{ $('Verify Approval Data').item.json['Approval Date'] }}"
}
]
},
"operation": "update"
},
"typeVersion": 1
},
{
"id": "808ce6c2-6620-40ae-8c6d-518cf28dce26",
"name": "Is Stock is Low",
"type": "n8n-nodes-base.if",
"position": [
8300,
3960
],
"parameters": {
"options": {},
"conditions": {
"options": {
"version": 2,
"leftValue": "",
"caseSensitive": true,
"typeValidation": "strict"
},
"combinator": "and",
"conditions": [
{
"id": "e0493b94-1e9c-4f68-ba66-4abd2bd5b569",
"operator": {
"type": "boolean",
"operation": "true",
"singleValue": true
},
"leftValue": "={{ $json['Is Low'] }}",
"rightValue": "="
}
]
}
},
"typeVersion": 2.2
}
],
"pinData": {},
"connections": {
"Merge": {
"main": [
[
{
"node": "Check If Product ID Exists",
"type": "main",
"index": 0
}
]
]
},
"Merge1": {
"main": [
[
{
"node": "Process Approval Decision",
"type": "main",
"index": 0
}
]
]
},
"Update Stock": {
"main": [
[
{
"node": "Update Current Stock1",
"type": "main",
"index": 0
},
{
"node": "Update Current Stck",
"type": "main",
"index": 0
}
]
]
},
"Get Approvals": {
"main": [
[
{
"node": "Format Approval Response",
"type": "main",
"index": 0
}
]
]
},
"Merge Lookups": {
"main": [
[
{
"node": "Prepare Approval",
"type": "main",
"index": 0
}
]
]
},
"Searck Issues": {
"main": [
[
{
"node": "Merge1",
"type": "main",
"index": 1
}
]
]
},
"New Record Row": {
"main": [
[
{
"node": "Search Current Stock",
"type": "main",
"index": 0
}
]
]
},
"Format response": {
"main": [
[
{
"node": "Initialize New Product stock",
"type": "main",
"index": 0
},
{
"node": "New Row Current Stock",
"type": "main",
"index": 0
}
]
]
},
"Is Stock is Low": {
"main": [
[
{
"node": "Low Stock Email Alert",
"type": "main",
"index": 0
}
]
]
},
"Prepare Approval": {
"main": [
[
{
"node": "Send Approval Request",
"type": "main",
"index": 0
}
]
]
},
"Standardize Data": {
"main": [
[
{
"node": "Validate Issue Request Data",
"type": "main",
"index": 0
}
]
]
},
"Search Product ID": {
"main": [
[
{
"node": "Merge Lookups",
"type": "main",
"index": 1
}
]
]
},
"Create Record Issue": {
"main": [
[
{
"node": "Search Product ID",
"type": "main",
"index": 0
}
]
]
},
"Append Raw Materials": {
"main": [
[
{
"node": "Validate Quantity Received",
"type": "main",
"index": 0
}
]
]
},
"LookUp Current stock": {
"main": [
[
{
"node": "Low stock Detection2",
"type": "main",
"index": 0
}
]
]
},
"Low stock Detection1": {
"main": [
[
{
"node": "Is Stock is Low",
"type": "main",
"index": 0
}
]
]
},
"Low stock Detection2": {
"main": [
[
{
"node": "Trigger Low Stock Alert",
"type": "main",
"index": 0
}
]
]
},
"Search Current Stock": {
"main": [
[
{
"node": "Merge",
"type": "main",
"index": 0
}
]
]
},
"Update Current Stock": {
"main": [
[
{
"node": "LookUp Current stock",
"type": "main",
"index": 0
}
]
]
},
"Verify Approval Data": {
"main": [
[
{
"node": "Retrieve Issue Request Details",
"type": "main",
"index": 0
},
{
"node": "Searck Issues",
"type": "main",
"index": 0
}
]
]
},
"Calculate Total Price": {
"main": [
[
{
"node": "Append Raw Materials",
"type": "main",
"index": 0
},
{
"node": "New Record Row",
"type": "main",
"index": 0
}
]
]
},
"LookUp Current stock1": {
"main": [
[
{
"node": "Low stock Detection1",
"type": "main",
"index": 0
}
]
]
},
"Lookup Existing Stock": {
"main": [
[
{
"node": "Merge",
"type": "main",
"index": 0
}
]
]
},
"Receive Issue Request": {
"main": [
[
{
"node": "Standardize Data",
"type": "main",
"index": 0
}
]
]
},
"Update Current Stock1": {
"main": [
[
{
"node": "LookUp Current stock1",
"type": "main",
"index": 0
}
]
]
},
"Append Material Request": {
"main": [
[
{
"node": "Check Available Stock for Issue",
"type": "main",
"index": 0
}
]
]
},
"Trigger Low Stock Alert": {
"main": [
[
{
"node": "Send Low Stock Email Alert",
"type": "main",
"index": 0
}
]
]
},
"Format Approval Response": {
"main": [
[
{
"node": "Verify Approval Data",
"type": "main",
"index": 0
}
]
]
},
"Process Approval Decision": {
"main": [
[
{
"node": "Update Stock After Issue",
"type": "main",
"index": 0
},
{
"node": "Materials Issue Table Update",
"type": "main",
"index": 0
},
{
"node": "Get Stock for Issue Update from Current",
"type": "main",
"index": 0
}
]
]
},
"Verify Requested Quantity": {
"main": [
[
{
"node": "Append Material Request",
"type": "main",
"index": 0
},
{
"node": "Create Record Issue",
"type": "main",
"index": 0
}
]
]
},
"Check If Product ID Exists": {
"main": [
[
{
"node": "Calculate Updated Current Stock",
"type": "main",
"index": 0
}
],
[
{
"node": "Format response",
"type": "main",
"index": 0
}
]
]
},
"Validate Quantity Received": {
"main": [
[
{
"node": "Lookup Existing Stock",
"type": "main",
"index": 0
}
]
]
},
"Validate Issue Request Data": {
"main": [
[
{
"node": "Verify Requested Quantity",
"type": "main",
"index": 0
}
]
]
},
"Receive Raw Materials Webhook": {
"main": [
[
{
"node": "Standardize Raw Material Data",
"type": "main",
"index": 0
}
]
]
},
"Standardize Raw Material Data": {
"main": [
[
{
"node": "Calculate Total Price",
"type": "main",
"index": 0
}
]
]
},
"Retrieve Issue Request Details": {
"main": [
[
{
"node": "Merge1",
"type": "main",
"index": 0
}
]
]
},
"Calculate Updated Current Stock": {
"main": [
[
{
"node": "Update Current Stock",
"type": "main",
"index": 0
},
{
"node": "Current Stock Update",
"type": "main",
"index": 0
}
]
]
},
"Check Available Stock for Issue": {
"main": [
[
{
"node": "Merge Lookups",
"type": "main",
"index": 0
}
]
]
},
"Get Stock for Issue Update from Current": {
"main": [
[
{
"node": "Update Stock",
"type": "main",
"index": 0
}
]
]
}
}
}Pour qui est ce workflow ?
Ce workflow s'adresse aux entreprises de taille moyenne à grande, notamment dans le secteur de la distribution et de la logistique. Il est idéal pour les équipes de gestion des stocks et d'approvisionnement, ainsi que pour les professionnels ayant des compétences techniques de base en automatisation. Les utilisateurs bénéficieront d'une solution efficace pour gérer leurs stocks sans nécessiter de développement complexe.
Problème résolu
Ce workflow résout le problème de la gestion manuelle des stocks, qui peut entraîner des erreurs, des ruptures de stock et des retards dans les approvisionnements. En automatisant le processus de demande et d'approbation, il réduit le temps consacré à ces tâches et minimise les risques d'erreurs. Les utilisateurs obtiennent ainsi une visibilité en temps réel sur leurs niveaux de stock, ce qui leur permet de prendre des décisions éclairées et d'améliorer leur efficacité opérationnelle.
Étapes du workflow
Étape 1 : Le workflow commence par la réception d'une demande de matériel via un webhook. Étape 2 : Les données de la demande sont standardisées pour assurer leur cohérence. Étape 3 : Une demande d'approbation est envoyée par email au responsable concerné. Étape 4 : Une fois l'approbation reçue, le stock est mis à jour dans Google Sheets et Supabase. Étape 5 : Des scripts JavaScript détectent les niveaux de stock bas et envoient des alertes si nécessaire. Étape 6 : Les enregistrements de stock sont mis à jour en temps réel pour refléter les changements.
Guide de personnalisation du workflow n8n
Pour personnaliser ce workflow, commencez par ajuster les paramètres du webhook pour correspondre à votre système de demande. Modifiez les ID de document Google Sheets pour pointer vers vos propres feuilles de calcul. Vous pouvez également adapter les scripts JavaScript pour répondre à vos besoins spécifiques en matière de gestion des stocks. Enfin, n'hésitez pas à intégrer d'autres outils ou services via n8n pour enrichir le flux de travail, comme des notifications Slack ou des intégrations CRM.