이메일 스캔과 Google 스프레드시트 구매 주문 생성

• •Google 계정 및 Gmail API 인증 정보
• •Google Sheets API 인증 정보
• •Google Gemini API Key

{
  "id": "xfKuFCSndnxYSb8t",
  "meta": {
    "instanceId": "bbc3fa3cd7d64d8ff0c4877d98dee68ce7dadacc5e089546680c915b3e5a212b",
    "templateCredsSetupCompleted": true
  },
  "name": "Email scanning and purchase order creation in Google Sheet",
  "tags": [],
  "nodes": [
    {
      "id": "7fa9ff57-fb2c-4db0-a3ff-4f2fa1d2184f",
      "name": "다수 메시지 가져오기",
      "type": "n8n-nodes-base.gmail",
      "position": [
        -2608,
        -592
      ],
      "webhookId": "38e977e5-65c8-4b40-b201-bbe71fea8aea",
      "parameters": {
        "limit": 100,
        "simple": false,
        "filters": {
          "readStatus": "unread",
          "receivedAfter": "={{ $today.minus({ days: 1 }).toISODate() }}"
        },
        "options": {
          "downloadAttachments": true
        },
        "operation": "getAll"
      },
      "typeVersion": 2.1
    },
    {
      "id": "e87d2e42-8bd6-473b-87ed-aeb6ec643e58",
      "name": "Cron",
      "type": "n8n-nodes-base.cron",
      "position": [
        -2784,
        -592
      ],
      "parameters": {
        "triggerTimes": {
          "item": [
            {
              "mode": "everyMinute"
            }
          ]
        }
      },
      "typeVersion": 1
    },
    {
      "id": "aad75605-ee41-4fc8-8e61-5e5932a30d67",
      "name": "If",
      "type": "n8n-nodes-base.if",
      "position": [
        -2112,
        -592
      ],
      "parameters": {
        "options": {},
        "conditions": {
          "options": {
            "version": 2,
            "leftValue": "",
            "caseSensitive": true,
            "typeValidation": "strict"
          },
          "combinator": "and",
          "conditions": [
            {
              "id": "60e07c88-8125-4a64-8212-14935fc3d73a",
              "operator": {
                "type": "object",
                "operation": "exists",
                "singleValue": true
              },
              "leftValue": "={{ $('Filter emails').item.binary}}",
              "rightValue": ""
            }
          ]
        }
      },
      "typeVersion": 2.2
    },
    {
      "id": "f1d9876b-8e9d-4824-9650-a3641f294532",
      "name": "AI 에이전트",
      "type": "@n8n/n8n-nodes-langchain.agent",
      "position": [
        -1648,
        -576
      ],
      "parameters": {
        "text": "=# Role  \nYou are an Expert AI Agent specialized in reading emails, extracting purchase order details, enriching them with product information using the Google Sheet tool, and normalizing dates into calendar weeks (Kalenderwoche).  \n\n# Task  \n- Input: {{ $('If').item.json.text }}  \n- Context: The email may contain purchase order details either in:  \n  - A formatted table, or  \n  - A freeform paragraph.  \n- Language: The email text can be in any language. You must understand and process it.  \n\n# Instructions  \n1. Read the email text carefully.  \n2. Identify purchase order details, including:  \n   - Product/package name  \n   - Quantity / number of items  \n   - Any other order-related details (sizes, variants, etc. if available).  \n3. Detect **dates or time expressions** that refer to delivery, booking, or campaign execution.  \n   - If the text contains a direct week reference (e.g., *KW36*), use it directly.  \n   - If the text contains a month or vague time reference (e.g., *end of October*), convert it into the appropriate calendar week(s).  \n     - *“Start of X month”* → first calendar week of that month.  \n     - *“Mid of X month”* → middle calendar week of that month.  \n     - *“End of X month”* → last calendar week of that month.  \n   - Use ISO week numbering (Monday as first day of the week).  \n4. Populate both `Kalenderwoche Start` and `Kalenderwoche Ende`.  \n   - If only one week is identified, set both Start and End to the same week.  \n   - If a date range is mentioned, map Start and End accordingly.  \n5. Query the **Google Sheet tool** to fetch full product details (e.g., product code, price, description, stock availability).  \n6. Merge the extracted order information with the Google Sheet product details.  \n7. **Return only the final `items` array as JSON. Do not include order_id, customer, or notes.**  \n8. Ensure all keys are translated into the email’s language or standardized consistently.  \n\n# Output Format  \nReturn **only this JSON array**:  \n```json\n[\n  { \"Laufende Nummer\":\"<string>\",\n    \"Lieferant\":\"<string>\",\n    \"Lieferanten-Nr\": \"<number or null>\",\n    \"Marke\": \"<string or null>\",\n    \"Marken-Nr\": \"<number or null>\",\n    \"Kalenderwoche Start\": \"<string, e.g., KW36>\",\n    \"Kalenderwoche Ende\": \"<string, e.g., KW36>\",\n    \"Marketing Status\": \"<string or null>\",\n    \"Paket\": \"<string>\",\n    \"Produkt\": \"<string>\",\n    \"Länder-Aktivierung\": \"<string or null>\",\n    \"Kosten\": \"<number or null>\",\n    \"quantity\": \"<number>\"\n  }\n]\n",
        "options": {},
        "promptType": "define"
      },
      "typeVersion": 2.2
    },
    {
      "id": "68af1754-d9c2-4ab3-b3e6-a72deaea11df",
      "name": "Google Gemini Chat Model",
      "type": "@n8n/n8n-nodes-langchain.lmChatGoogleGemini",
      "position": [
        -1664,
        -384
      ],
      "parameters": {
        "options": {}
      },
      "typeVersion": 1
    },
    {
      "id": "b3adb22f-622c-48c1-83eb-2d3f4551251e",
      "name": "시트에서 행 가져오기 (Google Sheets)",
      "type": "n8n-nodes-base.googleSheetsTool",
      "position": [
        -1488,
        -368
      ],
      "parameters": {
        "sheetName": {
          "__rl": true,
          "mode": "list",
          "value": "",
          "cachedResultUrl": "",
          "cachedResultName": ""
        },
        "documentId": {
          "__rl": true,
          "mode": "id",
          "value": "="
        }
      },
      "typeVersion": 4.7
    },
    {
      "id": "a6ef8668-1b13-4f96-820d-1dd5d25b693f",
      "name": "시트에 행 추가",
      "type": "n8n-nodes-base.googleSheets",
      "position": [
        -1088,
        -576
      ],
      "parameters": {
        "columns": {
          "value": {
            "Marke": "={{ $json.Marke }}",
            "Paket": "={{ $json.Paket }}",
            "Kosten": "={{ $json.Kosten }}",
            "Produkt": "={{ $json.Produkt }}",
            "Lieferant": "={{ $json.Lieferant }}",
            "Marken-Nr": "={{ $json[\"Marken-Nr\"] }}",
            "Lieferanten-Nr": "={{ $json[\"Lieferanten-Nr\"] }}",
            "Laufende Nummer": "={{ $json[\"Laufende Nummer\"] }}",
            "Marketing Status": "={{ $json[\"Marketing Status\"] }}",
            "Kalenderwoch Start": "={{ $json[\"Kalenderwoche Start\"] }}",
            "Kalenderwoche Ende": "={{ $json[\"Kalenderwoche Ende\"] }}",
            "Länder-Aktivierung": "={{ $json[\"Länder-Aktivierung\"] }}"
          },
          "schema": [
            {
              "id": "Laufende Nummer",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "Laufende Nummer",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "Lieferant",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "Lieferant",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "Lieferanten-Nr",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "Lieferanten-Nr",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "Marke",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "Marke",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "Marken-Nr",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "Marken-Nr",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "Kalenderwoch Start",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "Kalenderwoch Start",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "Kalenderwoche Ende",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "Kalenderwoche Ende",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "Marketing Status",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "Marketing Status",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "Paket",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "Paket",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "Produkt",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "Produkt",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "Länder-Aktivierung",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "Länder-Aktivierung",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "Kosten",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "Kosten",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            }
          ],
          "mappingMode": "defineBelow",
          "matchingColumns": [],
          "attemptToConvertTypes": false,
          "convertFieldsToString": false
        },
        "options": {},
        "operation": "append",
        "sheetName": {
          "__rl": true,
          "mode": "id",
          "value": "="
        },
        "documentId": {
          "__rl": true,
          "mode": "id",
          "value": "="
        }
      },
      "credentials": {
        "googleSheetsOAuth2Api": {
          "id": "Hqjs4o2PKY8T8cY1",
          "name": "Google Sheets account"
        }
      },
      "typeVersion": 4.7
    },
    {
      "id": "90d90230-7fea-431e-af80-e22753b41856",
      "name": "스티키 노트",
      "type": "n8n-nodes-base.stickyNote",
      "disabled": true,
      "position": [
        -2800,
        -704
      ],
      "parameters": {
        "width": 528,
        "height": 320,
        "content": "## Scan Email on every minute and read new emails.\n**Get new emails frequently and filer them which has purchase order**"
      },
      "typeVersion": 1
    },
    {
      "id": "de0dcfdd-bb42-4170-8506-d2fa8a5846cd",
      "name": "스티키 노트1",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -2144,
        -736
      ],
      "parameters": {
        "width": 400,
        "height": 368,
        "content": "## Check the document has attachment\n**Check for email without attachment. To read purchase order from the email body**"
      },
      "typeVersion": 1
    },
    {
      "id": "24b56bad-9b5a-4710-9cc8-8fdcb07bd864",
      "name": "최종 출력 키 설정",
      "type": "n8n-nodes-base.set",
      "position": [
        -1888,
        -576
      ],
      "parameters": {
        "options": {},
        "assignments": {
          "assignments": [
            {
              "id": "a05c046c-6afb-421d-8b9e-128d0960d006",
              "name": "final_json_keys",
              "type": "array",
              "value": "=[\n  \"Laufende Nummer\",\n  \"Lieferant\",\n  \"Lieferanten-Nr\",\n  \"Marke\",\n  \"Marken-Nr\",\n  \"Kalenderwoche\\nStart\",\n  \"Kalenderwoche\\nEnde\",\n  \"Marketing Status\",\n  \"Paket\",\n  \"Produkt\",\n  \"Länder-Aktivierung\",\n  \"Kosten\"\n]\n"
            }
          ]
        }
      },
      "typeVersion": 3.4
    },
    {
      "id": "24616df2-e7e4-4105-830f-8bc095f818f7",
      "name": "이메일 필터링",
      "type": "n8n-nodes-base.filter",
      "position": [
        -2416,
        -592
      ],
      "parameters": {
        "options": {},
        "conditions": {
          "options": {
            "version": 2,
            "leftValue": "",
            "caseSensitive": true,
            "typeValidation": "strict"
          },
          "combinator": "or",
          "conditions": [
            {
              "id": "6b9ca560-7c08-40e0-9d9b-8a916ff1368c",
              "operator": {
                "type": "string",
                "operation": "contains"
              },
              "leftValue": "={{ $json.subject }}",
              "rightValue": "=Marketing"
            },
            {
              "id": "518e65fe-c130-4a27-821b-20f6c51b2dd7",
              "operator": {
                "type": "string",
                "operation": "contains"
              },
              "leftValue": "={{ $json.subject }}",
              "rightValue": "Buchungsanfrage"
            }
          ]
        }
      },
      "typeVersion": 2.2
    },
    {
      "id": "0c0cb36a-2ff1-4b53-8830-90d000ddbe51",
      "name": "Google 시트에 업로드 형식 재구성",
      "type": "n8n-nodes-base.code",
      "position": [
        -1264,
        -576
      ],
      "parameters": {
        "jsCode": "// Input from previous node\nconst input = items[0].json.output;\n\n// Remove markdown ```json ... ``` wrappers if present\nconst cleaned = input.replace(/```json|```/g, '').trim();\n\nlet parsed;\ntry {\n  parsed = JSON.parse(cleaned);\n} catch (error) {\n  throw new Error(`Failed to parse JSON: ${error.message}\\nRaw input: ${cleaned}`);\n}\n\n// Return each object as separate item in n8n\nreturn parsed.map(obj => ({ json: obj }));\n"
      },
      "typeVersion": 2
    },
    {
      "id": "6867e395-dcc0-491d-b406-c914418d30b0",
      "name": "스티키 노트2",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -1712,
        -672
      ],
      "parameters": {
        "width": 352,
        "height": 464,
        "content": "## AI Agent to read and summarize the order."
      },
      "typeVersion": 1
    },
    {
      "id": "4c38683c-1398-432e-b780-ad7d51a0d238",
      "name": "스티키 노트3",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -1312,
        -688
      ],
      "parameters": {
        "width": 352,
        "height": 304,
        "content": "## Append purchase order to Google sheet\n"
      },
      "typeVersion": 1
    },
    {
      "id": "06417501-207f-45f9-8239-3676f14f9457",
      "name": "스티키 노트4",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -3152,
        -1280
      ],
      "parameters": {
        "width": 480,
        "height": 544,
        "content": "## 📧 Email Reading & Purchase Order Creation (AI-powered)\n\n**✨ What it does**  \n- ⏱ Reads unread emails every minute  \n- 🎯 Filters emails based on **Subject**  \n- 🤖 Uses Gemini AI to summarize emails & extract purchase order details  \n- 📊 Appends purchase order data to Google Sheets  \n\n**🛠 Requirements**  \n- 📩 Gmail account access to fetch unread emails  \n- 🔑 Gemini AI credentials for summarization & extraction  \n- 📑 Google Sheet with predefined purchase order headers  \n\n**⚙️ Setup Instructions**  \n1. 🔗 Set up Google Sheets & Gmail credentials in n8n  \n2. 📝 Configure the filter node with your subject rules  \n3. 🤝 Connect Gemini AI with the correct credentials  \n4. 📂 Create & configure a Google Sheet with the necessary purchase order headers  \n"
      },
      "typeVersion": 1
    }
  ],
  "active": false,
  "pinData": {},
  "settings": {
    "executionOrder": "v1"
  },
  "versionId": "6ef223ac-0d4b-424d-b174-f3072229fbe6",
  "connections": {
    "aad75605-ee41-4fc8-8e61-5e5932a30d67": {
      "main": [
        [],
        [
          {
            "node": "24b56bad-9b5a-4710-9cc8-8fdcb07bd864",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "e87d2e42-8bd6-473b-87ed-aeb6ec643e58": {
      "main": [
        [
          {
            "node": "7fa9ff57-fb2c-4db0-a3ff-4f2fa1d2184f",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "f1d9876b-8e9d-4824-9650-a3641f294532": {
      "main": [
        [
          {
            "node": "0c0cb36a-2ff1-4b53-8830-90d000ddbe51",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "24616df2-e7e4-4105-830f-8bc095f818f7": {
      "main": [
        [
          {
            "node": "aad75605-ee41-4fc8-8e61-5e5932a30d67",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "7fa9ff57-fb2c-4db0-a3ff-4f2fa1d2184f": {
      "main": [
        [
          {
            "node": "24616df2-e7e4-4105-830f-8bc095f818f7",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "24b56bad-9b5a-4710-9cc8-8fdcb07bd864": {
      "main": [
        [
          {
            "node": "f1d9876b-8e9d-4824-9650-a3641f294532",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "68af1754-d9c2-4ab3-b3e6-a72deaea11df": {
      "ai_languageModel": [
        [
          {
            "node": "f1d9876b-8e9d-4824-9650-a3641f294532",
            "type": "ai_languageModel",
            "index": 0
          }
        ]
      ]
    },
    "b3adb22f-622c-48c1-83eb-2d3f4551251e": {
      "ai_tool": [
        [
          {
            "node": "f1d9876b-8e9d-4824-9650-a3641f294532",
            "type": "ai_tool",
            "index": 0
          }
        ]
      ]
    },
    "0c0cb36a-2ff1-4b53-8830-90d000ddbe51": {
      "main": [
        [
          {
            "node": "a6ef8668-1b13-4f96-820d-1dd5d25b693f",
            "type": "main",
            "index": 0
          }
        ]
      ]
    }
  }
}