Sistema de predicción de ventas e inventario de restaurante con Gemini AI y Google Sheets

• •Cuenta de Google y credenciales de API de Gmail
• •Credenciales de API de Google Sheets
• •Clave de API de 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": "Explicación del Flujo de Trabajo",
      "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": "Activar Pronóstico Semanal",
      "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": "Cargar Datos Históricos de Ventas",
      "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": "Formatear Entrada para el Agente de 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": "Generar Pronóstico con 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": "Herramienta de Pensamiento de IA",
      "type": "@n8n/n8n-nodes-langchain.toolThink",
      "position": [
        468,
        680
      ],
      "parameters": {},
      "typeVersion": 1
    },
    {
      "id": "c7df1137-e2b1-4ea9-9dd4-e4025e92480e",
      "name": "Modelo 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": "Interpretar Salida del Pronóstico de 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": "Registrar Pronóstico en 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": "Enviar Resumen del Pronóstico por Correo",
      "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": "Nota Adhesiva",
      "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": "Nota Adhesiva1",
      "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": "Nota Adhesiva2",
      "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": "Nota Adhesiva3",
      "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": "Nota Adhesiva4",
      "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": "Nota Adhesiva5",
      "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": "Nota Adhesiva6",
      "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
          }
        ]
      ]
    }
  }
}