Verkaufs- und Bestandsprognosesystem für Restaurants mit Gemini AI und Google Tabellen

• •Google-Konto + Gmail API-Anmeldedaten
• •Google Sheets API-Anmeldedaten
• •Google Gemini API Key

{
  "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": "Workflow-Erklärung",
      "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": "Wöchentliche Prognose auslösen",
      "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": "Historische Verkaufsdaten laden",
      "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": "Eingabe für KI-Agent formatieren",
      "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": "Prognose mit KI generieren",
      "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": "AI Think Tool",
      "type": "@n8n/n8n-nodes-langchain.toolThink",
      "position": [
        468,
        680
      ],
      "parameters": {},
      "typeVersion": 1
    },
    {
      "id": "c7df1137-e2b1-4ea9-9dd4-e4025e92480e",
      "name": "Chat Model",
      "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": "KI-Prognoseausgabe interpretieren",
      "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": "Prognose in Google Sheets protokollieren",
      "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": "Prognosezusammenfassung per E-Mail",
      "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": "Haftnotiz",
      "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": "Haftnotiz1",
      "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": "Haftnotiz2",
      "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": "Haftnotiz3",
      "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": "Haftnotiz4",
      "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": "Haftnotiz5",
      "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": "Haftnotiz6",
      "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
          }
        ]
      ]
    }
  }
}