Système de prévision des ventes et des stocks de restaurant avec Gemini AI et Google Sheets

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

{
  "id": "Y6Pn9PLNoMU7e8Xb",
  "meta": {
    "instanceId": "dd69efaf8212c74ad206700d104739d3329588a6f3f8381a46a481f34c9cc281",
    "templateCredsSetupCompleted": true
  },
  "name": "Restaurant Sales & Inventory Forecasting System using Gemini AI & Google Sheets",
  "tags": [],
  "nodes": [
    {
      "id": "80bda799-9bcd-41b5-a94e-ab32919b04e3",
      "name": "Explication du Workflow",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        140,
        -200
      ],
      "parameters": {
        "color": 4,
        "width": 740,
        "height": 200,
        "content": "## Workflow Overview \n\n### This workflow automates weekly forecasting of restaurant sales and raw material requirements using historical data from Google Sheets and AI predictions powered by Google Gemini. The forecast is then emailed to stakeholders for efficient planning and waste reduction."
      },
      "typeVersion": 1
    },
    {
      "id": "848c741c-7206-46cb-b10e-205feb126544",
      "name": "Déclencheur Hebdomadaire de Prévision",
      "type": "n8n-nodes-base.scheduleTrigger",
      "position": [
        -340,
        460
      ],
      "parameters": {
        "rule": {
          "interval": [
            {
              "field": "weeks",
              "triggerAtHour": 20
            }
          ]
        }
      },
      "typeVersion": 1.2
    },
    {
      "id": "75c05f50-aea8-48d7-ae9c-fc1e9af03179",
      "name": "Charger les Données Historiques de Ventes",
      "type": "n8n-nodes-base.googleSheets",
      "position": [
        -120,
        460
      ],
      "parameters": {
        "options": {},
        "sheetName": {
          "__rl": true,
          "mode": "list",
          "value": "gid=0",
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/1QZXX_gjNTTZ0vqPozNysjASQS3UcgwwWVpYvN7r-T_I/edit#gid=0",
          "cachedResultName": "current data"
        },
        "documentId": {
          "__rl": true,
          "mode": "list",
          "value": "1QZXX_gjNTTZ0vqPozNysjASQS3UcgwwWVpYvN7r-T_I",
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/1QZXX_gjNTTZ0vqPozNysjASQS3UcgwwWVpYvN7r-T_I/edit?usp=drivesdk",
          "cachedResultName": "Restaurant stock predictions"
        },
        "authentication": "serviceAccount"
      },
      "credentials": {
        "googleApi": {
          "id": "ScSS2KxGQULuPtdy",
          "name": "Google Sheets- test"
        }
      },
      "typeVersion": 4.6
    },
    {
      "id": "ecbf01be-4691-4a9e-b4dd-312a96ba73b2",
      "name": "Formater l'Entrée pour l'Agent IA",
      "type": "n8n-nodes-base.code",
      "position": [
        100,
        460
      ],
      "parameters": {
        "jsCode": "// Fetch all incoming items\nconst items = $input.all();\n\n// Extract the raw row data (each item.json is one row)\nconst rawRows = items.map(item => item.json);\n\n// Bundle everything into a single field\nconst payload = { rows: rawRows };\n\n// Return a single output item whose json contains your full dataset\nreturn [{ json: { data: payload } }];\n"
      },
      "typeVersion": 2
    },
    {
      "id": "b83e66e4-2d98-47e0-9fb6-e5837863494e",
      "name": "Générer la Prévision avec l'IA",
      "type": "@n8n/n8n-nodes-langchain.agent",
      "position": [
        320,
        460
      ],
      "parameters": {
        "text": "={{ $json.data }}",
        "options": {
          "systemMessage": "You are a restaurant demand forecasting assistant.\n\nYou will be given a JSON object 'data' containing an array of historical weekly records. Each record includes:\n- row_number\n- Date (ISO format)\n- Sales for dishes (e.g. \"Neapolitan Pizza Sold\", \"Picanha Sold\", etc.)\n- Quantities of raw materials used that week (e.g. \"Flour (kg)\", \"Cheese (kg)\", etc.)\n\nYour task:\n1. Analyze trends across the historical weeks.\n2. Forecast next Monday’s sales (units sold for each dish).\n3. Calculate the required quantity of each raw material needed next week to match that forecast, based on per‑unit usage rates inferred from the data.\n4. Output exactly one JSON record, following the same structure as the input records. For example:\n\n```json\n{\n  \"row_number\": 7,\n  \"Date\": \"2025-07-14\",\n  \"Neapolitan Pizza Sold\": 58,\n  \"Picanha Sold\": 36,\n  \"Huevos Rancheros Sold\": 62,\n  \"Japanese Curry Sold\": 48,\n  \"Birria Ramen Sold\": 31,\n  \"Flour (kg)\": 11.5,\n  \"Cheese (kg)\": 5.8,\n  \"Tomato Sauce (L)\": 4.4,\n  \"Beef (kg)\": 18,\n  \"Pork (kg)\": 10,\n  \"Rice (kg)\": 5.8,\n  \"Tortillas (pcs)\": 62,\n  \"Eggs (pcs)\": 62,\n  \"Curry Powder (kg)\": 1.45,\n  \"Chili Powder (kg)\": 1.05\n}\n\nUse think tool if needed\n"
        },
        "promptType": "define"
      },
      "typeVersion": 1.9
    },
    {
      "id": "d0cc88f1-0bdb-48c0-9d6e-ff68e93517f0",
      "name": "Outil de Réflexion IA",
      "type": "@n8n/n8n-nodes-langchain.toolThink",
      "position": [
        468,
        680
      ],
      "parameters": {},
      "typeVersion": 1
    },
    {
      "id": "c7df1137-e2b1-4ea9-9dd4-e4025e92480e",
      "name": "Modèle de Chat",
      "type": "@n8n/n8n-nodes-langchain.lmChatGoogleGemini",
      "position": [
        348,
        680
      ],
      "parameters": {
        "options": {},
        "modelName": "models/gemini-2.5-pro"
      },
      "credentials": {
        "googlePalmApi": {
          "id": "RvSkIBjP48ORJKhU",
          "name": "Google Gemini(PaLM) Api account - test"
        }
      },
      "typeVersion": 1
    },
    {
      "id": "40dd2b9c-85b4-4e44-8e48-2972fb75645b",
      "name": "Interpréter la Sortie de Prévision de l'IA",
      "type": "n8n-nodes-base.code",
      "position": [
        696,
        460
      ],
      "parameters": {
        "jsCode": "// Run this in 'Run Once for All Items' mode\n\n// Assume AI agent returned a single item and that its output is in item.json.output\nconst items = $input.all();\nconst aiText = items[0].json.output;\n\n// Remove markdown fences and extract JSON\nconst jsonString = aiText\n  .replace(/```json\\s*([\\s\\S]*?)```/i, '$1') // strip markdown fences\n  .trim();\n\n// Parse it to a JS object\nlet parsed;\ntry {\n  parsed = JSON.parse(jsonString);\n} catch (e) {\n  throw new Error('Failed to parse JSON from AI output: ' + e.message);\n}\n\n// Ready to output\nreturn [{ json: parsed }];\n"
      },
      "typeVersion": 2
    },
    {
      "id": "537236ac-461a-416d-ada7-4e6be84cb790",
      "name": "Enregistrer la Prévision dans Google Sheets",
      "type": "n8n-nodes-base.googleSheets",
      "position": [
        916,
        460
      ],
      "parameters": {
        "columns": {
          "value": {},
          "schema": [
            {
              "id": "Date",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "Date",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "Neapolitan Pizza Sold",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "Neapolitan Pizza Sold",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "Picanha Sold",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "Picanha Sold",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "Huevos Rancheros Sold",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "Huevos Rancheros Sold",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "Japanese Curry Sold",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "Japanese Curry Sold",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "Birria Ramen Sold",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "Birria Ramen Sold",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "Flour (kg)",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "Flour (kg)",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "Cheese (kg)",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "Cheese (kg)",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "Tomato Sauce (L)",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "Tomato Sauce (L)",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "Beef (kg)",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "Beef (kg)",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "Pork (kg)",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "Pork (kg)",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "Rice (kg)",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "Rice (kg)",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "Tortillas (pcs)",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "Tortillas (pcs)",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "Eggs (pcs)",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "Eggs (pcs)",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "Curry Powder (kg)",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "Curry Powder (kg)",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "Chili Powder (kg)",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "Chili Powder (kg)",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            }
          ],
          "mappingMode": "autoMapInputData",
          "matchingColumns": [],
          "attemptToConvertTypes": false,
          "convertFieldsToString": false
        },
        "options": {},
        "operation": "append",
        "sheetName": {
          "__rl": true,
          "mode": "list",
          "value": 370915330,
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/1QZXX_gjNTTZ0vqPozNysjASQS3UcgwwWVpYvN7r-T_I/edit#gid=370915330",
          "cachedResultName": "prediction data"
        },
        "documentId": {
          "__rl": true,
          "mode": "list",
          "value": "1QZXX_gjNTTZ0vqPozNysjASQS3UcgwwWVpYvN7r-T_I",
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/1QZXX_gjNTTZ0vqPozNysjASQS3UcgwwWVpYvN7r-T_I/edit?usp=drivesdk",
          "cachedResultName": "Restaurant stock predictions"
        },
        "authentication": "serviceAccount"
      },
      "credentials": {
        "googleApi": {
          "id": "ScSS2KxGQULuPtdy",
          "name": "Google Sheets- test"
        }
      },
      "typeVersion": 4.6
    },
    {
      "id": "0af7f39a-aa13-44fc-a653-71533d2851b6",
      "name": "Envoyer un Résumé de Prévision par Email",
      "type": "n8n-nodes-base.gmail",
      "position": [
        1136,
        460
      ],
      "webhookId": "64ff6f6e-2765-447c-b68c-352172b67174",
      "parameters": {
        "sendTo": "xyz@gmail.com",
        "message": "=Dear Manager,<br><br>  \nPlease find the details for the monday predicted data.<br><br>  \n\n\n\n<b>Scraping Date:</b>{{ $json.Date }}<br> \n<br>  \n\n\n<b>Sheet link with all data:</b><br> \n<a href=\"https://docs.google.com/spreadsheets/d/1QZXX_gjNTTZ0vqPozNysjASQS3UcgwwWVpYvN7r-T_I/edit?usp=sharing\">Click here to view the data</a><br>\n\n\n\n<br>  Thanks,<br> Ajay Mishra",
        "options": {},
        "subject": "Next monday prediction"
      },
      "credentials": {
        "gmailOAuth2": {
          "id": "PcTqvGU9uCunfltE",
          "name": "Gmail account - test"
        }
      },
      "typeVersion": 2.1
    },
    {
      "id": "761f823d-4ba8-44d3-9ae7-c595de84cf9e",
      "name": "Note Adhésive",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -380,
        140
      ],
      "parameters": {
        "width": 180,
        "height": 480,
        "content": "Automatically starts the workflow at a scheduled time."
      },
      "typeVersion": 1
    },
    {
      "id": "aa031a03-5275-4aef-b1e9-a4e54b782a55",
      "name": "Note Adhésive1",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -160,
        140
      ],
      "parameters": {
        "color": 3,
        "width": 180,
        "height": 480,
        "content": "Pulls weekly sales and material usage from Google Sheets."
      },
      "typeVersion": 1
    },
    {
      "id": "f87f5e45-590f-466e-8869-7d79216ee073",
      "name": "Note Adhésive2",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        656,
        140
      ],
      "parameters": {
        "color": 2,
        "width": 180,
        "height": 480,
        "content": "Parses the AI's response into readable, usable JSON format."
      },
      "typeVersion": 1
    },
    {
      "id": "d2dfb72b-056a-429c-b1b2-0fd95acea27d",
      "name": "Note Adhésive3",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        876,
        140
      ],
      "parameters": {
        "color": 5,
        "width": 180,
        "height": 480,
        "content": "Stores the new forecast data back into a Google Sheet."
      },
      "typeVersion": 1
    },
    {
      "id": "c59672c3-2e43-4720-9952-1cca1af8b1ea",
      "name": "Note Adhésive4",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        1096,
        140
      ],
      "parameters": {
        "color": 3,
        "width": 180,
        "height": 480,
        "content": "Sends a summary of the forecast via Gmail."
      },
      "typeVersion": 1
    },
    {
      "id": "265d61ce-6aad-46ee-a30d-81e291b91bed",
      "name": "Note Adhésive5",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        60,
        140
      ],
      "parameters": {
        "color": 4,
        "width": 180,
        "height": 480,
        "content": "Transforms raw data into a structured format suitable for the AI Agent.\n\n"
      },
      "typeVersion": 1
    },
    {
      "id": "ee2932b4-96ec-464f-8b6c-0971a30740e3",
      "name": "Note Adhésive6",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        320,
        140
      ],
      "parameters": {
        "color": 6,
        "width": 260,
        "height": 480,
        "content": "Uses Gemini AI to analyze trends and predict upcoming needs."
      },
      "typeVersion": 1
    }
  ],
  "active": false,
  "pinData": {},
  "settings": {
    "executionOrder": "v1"
  },
  "versionId": "01083ee7-6a3e-4dd5-92b3-13b38dbc019e",
  "connections": {
    "c7df1137-e2b1-4ea9-9dd4-e4025e92480e": {
      "ai_languageModel": [
        [
          {
            "node": "b83e66e4-2d98-47e0-9fb6-e5837863494e",
            "type": "ai_languageModel",
            "index": 0
          }
        ]
      ]
    },
    "d0cc88f1-0bdb-48c0-9d6e-ff68e93517f0": {
      "ai_tool": [
        [
          {
            "node": "b83e66e4-2d98-47e0-9fb6-e5837863494e",
            "type": "ai_tool",
            "index": 0
          }
        ]
      ]
    },
    "848c741c-7206-46cb-b10e-205feb126544": {
      "main": [
        [
          {
            "node": "75c05f50-aea8-48d7-ae9c-fc1e9af03179",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "ecbf01be-4691-4a9e-b4dd-312a96ba73b2": {
      "main": [
        [
          {
            "node": "b83e66e4-2d98-47e0-9fb6-e5837863494e",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "b83e66e4-2d98-47e0-9fb6-e5837863494e": {
      "main": [
        [
          {
            "node": "40dd2b9c-85b4-4e44-8e48-2972fb75645b",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "75c05f50-aea8-48d7-ae9c-fc1e9af03179": {
      "main": [
        [
          {
            "node": "ecbf01be-4691-4a9e-b4dd-312a96ba73b2",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "40dd2b9c-85b4-4e44-8e48-2972fb75645b": {
      "main": [
        [
          {
            "node": "537236ac-461a-416d-ada7-4e6be84cb790",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "537236ac-461a-416d-ada7-4e6be84cb790": {
      "main": [
        [
          {
            "node": "0af7f39a-aa13-44fc-a653-71533d2851b6",
            "type": "main",
            "index": 0
          }
        ]
      ]
    }
  }
}