Gemini AI와 Google 스프레드시트를 사용한 레스토랑 매출 및 재고 예측 시스템

• •Google 계정 및 Gmail API 인증 정보
• •Google Sheets API 인증 정보
• •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": "워크플로우 설명",
      "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": "주간 예측 트리거",
      "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": "과거 매출 데이터 로드",
      "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": "AI 에이전트 입력 형식 지정",
      "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": "AI로 예측 생성",
      "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": "AI 예측 결과 해석",
      "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": "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": "예측 요약 이메일 전송",
      "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": "Sticky Note",
      "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": "Sticky Note1",
      "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": "Sticky Note2",
      "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": "Sticky Note3",
      "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": "Sticky Note4",
      "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": "Sticky Note5",
      "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": "Sticky Note6",
      "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
          }
        ]
      ]
    }
  }
}