GmailのメタデータをGoogle Sheetに抽出

• •Googleアカウント + Gmail API認証情報
• •Google Sheets API認証情報

{
  "id": "Mw3kkNKzGTQ5hB2t",
  "meta": {
    "instanceId": "ac3395400729d0f53e6b8e43c425ec1af04a99e154bcd808417b3b72fa9dec1f",
    "templateCredsSetupCompleted": true
  },
  "name": "Extract Gmail Meta data into Google Sheet",
  "tags": [],
  "nodes": [
    {
      "id": "ab804462-804b-4c33-8d17-b9b950ec41b9",
      "name": "Gmail トリガー",
      "type": "n8n-nodes-base.gmailTrigger",
      "position": [
        -640,
        0
      ],
      "parameters": {
        "filters": {},
        "pollTimes": {
          "item": [
            {
              "mode": "everyMinute"
            }
          ]
        }
      },
      "credentials": {
        "gmailOAuth2": {
          "id": "F9eGgjqXjaly1d2v",
          "name": "Gmail account"
        }
      },
      "typeVersion": 1.2
    },
    {
      "id": "30dabca0-384f-4df5-b4bb-87a029584a92",
      "name": "コード",
      "type": "n8n-nodes-base.code",
      "position": [
        -20,
        0
      ],
      "parameters": {
        "jsCode": "// Try to pull subject from different common locations\nconst subject =\n  $json.subject ||\n  $json.Subject ||\n  $json.headers?.subject ||\n  \"No Subject\";\n\n// Try to pull body text from common fields\nconst body =\n  $json.body ||\n  $json.text ||\n  $json.snippet ||\n  \"No message found.\";\n\n// Try to pull \"from\" field from common sources\nconst fromHeader =\n  $json.from ||\n  $json.From ||\n  $json.headers?.from ||\n  \"\";\n\n// Initialize name and email placeholders\nlet senderName = \"\";\nlet email = \"\";\n\n// Extract \"Name <email@example.com>\" if present\nconst match = fromHeader.match(/(.*?)<(.+?)>/);\nif (match) {\n  senderName = match[1].trim();\n  email = match[2].trim();\n} else {\n  // If only email address is provided\n  email = fromHeader.trim();\n}\n\n// Attempt to extract name from message body like: \"I am John Doe from ...\"\nlet extractedName = \"\";\nconst nameMatch = body.match(/I am (.*?) from/i);\nif (nameMatch) {\n  extractedName = nameMatch[1].trim();\n}\n\n// Choose final name: prefer extracted from body, else senderName, else fallback\nconst finalName = extractedName || senderName || \"Unknown\";\n\nreturn [{\n  json: {\n    name: finalName,\n    email,\n    subject,\n    message: body,\n    timestamp: new Date().toISOString()\n  }\n}];\n"
      },
      "typeVersion": 2
    },
    {
      "id": "99aadc61-1a46-45ee-8f92-4159ffd3d8f7",
      "name": "Edit Fields",
      "type": "n8n-nodes-base.set",
      "position": [
        540,
        0
      ],
      "parameters": {
        "options": {},
        "assignments": {
          "assignments": [
            {
              "id": "9ad38b82-4d5e-4ec5-9f7e-69142b7576a8",
              "name": "Full Name",
              "type": "string",
              "value": "={{ $json.name }}"
            },
            {
              "id": "fae560c7-88e1-40d8-9721-fc8136646c26",
              "name": "Email Address",
              "type": "string",
              "value": "={{ $json.email }}"
            },
            {
              "id": "6764dbcd-beb0-44c1-a235-bf1c5da47b3d",
              "name": "Subject",
              "type": "string",
              "value": "={{ $json.subject }}"
            },
            {
              "id": "47bfeda7-805c-43ea-afd6-50b1a6851619",
              "name": "Body of the email",
              "type": "string",
              "value": "={{ $json.message }}"
            },
            {
              "id": "fee870b7-0d55-4a66-b9d8-7ad6e6b35107",
              "name": "Time",
              "type": "string",
              "value": "={{ $json.timestamp }}"
            }
          ]
        }
      },
      "typeVersion": 3.4
    },
    {
      "id": "f81a918b-3f2b-4f07-b9c2-89aa98b1ce36",
      "name": "Append row in sheet",
      "type": "n8n-nodes-base.googleSheets",
      "position": [
        940,
        0
      ],
      "parameters": {
        "columns": {
          "value": {
            "Tme": "={{ $json.Time }}",
            "Name": "={{ $json['Full Name'] }}",
            "Subject": "={{ $json.Subject }}",
            "Email Address": "={{ $json['Email Address'] }}",
            "Body of the email": "={{ $json['Body of the email'] }}"
          },
          "schema": [
            {
              "id": "Name",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "Name",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "Email Address",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "Email Address",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "Subject",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "Subject",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "Body of the email",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "Body of the email",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "Tme",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "Tme",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            }
          ],
          "mappingMode": "defineBelow",
          "matchingColumns": [
            "Email Address"
          ],
          "attemptToConvertTypes": false,
          "convertFieldsToString": false
        },
        "options": {},
        "operation": "appendOrUpdate",
        "sheetName": {
          "__rl": true,
          "mode": "list",
          "value": "gid=0",
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/11nRQWiVvGWIbeUhEJIi0EL5dh3KuoSqZCuXv3ktBjxI/edit#gid=0",
          "cachedResultName": "Sheet1"
        },
        "documentId": {
          "__rl": true,
          "mode": "list",
          "value": "11nRQWiVvGWIbeUhEJIi0EL5dh3KuoSqZCuXv3ktBjxI",
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/11nRQWiVvGWIbeUhEJIi0EL5dh3KuoSqZCuXv3ktBjxI/edit?usp=drivesdk",
          "cachedResultName": "Email Data"
        }
      },
      "credentials": {
        "googleSheetsOAuth2Api": {
          "id": "kGYmhjcnx8Fu3k1c",
          "name": "Google Sheets account"
        }
      },
      "typeVersion": 4.5
    },
    {
      "id": "03af6ae2-7b35-4b26-ac11-04289a7376a9",
      "name": "付箋",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -200,
        -1320
      ],
      "parameters": {
        "color": 4,
        "width": 460,
        "height": 1480,
        "content": "## It extracts useful details (like name, email, subject, and message) from incoming emails or form submissions — even if the data format varies.\n\n🧩 Step-by-Step Explanation:\n✅ 1. Get the Subject:\nLooks for a subject line in multiple possible fields:\n\n$json.subject, $json.Subject, $json.headers.subject\n\nIf none found → sets it to \"No Subject\"\n\n✅ 2. Get the Message Body:\nLooks for the main message in common fields:\n\n$json.body, $json.text, $json.snippet\n\nIf none found → \"No message found.\"\n\n✅ 3. Get the \"From\" Information:\nChecks where the message came from:\n\n$json.from, $json.From, $json.headers.from\n\n✅ 4. Extract Name & Email:\nIf the sender is in format like:\n\nJohn Doe <john@example.com>\n\nIt will:\n\nsenderName = \"John Doe\"\n\nemail = \"john@example.com\"\n\nIf only an email is provided (like john@example.com), it just sets the email.\n\n✅ 5. Try to Extract Name from the Message:\nIf the message body has something like:\n\nHi, I am Alice Johnson from XYZ Agency.\n\nIt will extract \"Alice Johnson\" as the name.\n\n✅ 6. Choose the Final Name:\nOrder of priority:\n\nName from message body (\"I am ___ from...\")\n\nName from the email header (John Doe)\n\nIf not found → \"Unknown\"\n\n✅ 7. Return Structured Data:\nThe final output is:\n\n{\n  name: \"Alice Johnson\",\n  email: \"john@example.com\",\n  subject: \"Website Inquiry\",\n  message: \"Hi, I am Alice Johnson from XYZ...\",\n  timestamp: \"2025-07-20T09:22:10.121Z\"\n}\n\n\n"
      },
      "typeVersion": 1
    },
    {
      "id": "c6f941e6-8392-4055-9c75-e8e0b940c73b",
      "name": "付箋1",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -780,
        -160
      ],
      "parameters": {
        "width": 340,
        "height": 320,
        "content": "## Gmail Triggers when the new email has came \n"
      },
      "typeVersion": 1
    },
    {
      "id": "0a6106ac-648e-4f57-baf9-829746f6fecc",
      "name": "付箋2",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -1800,
        -720
      ],
      "parameters": {
        "color": 3,
        "width": 900,
        "height": 1920,
        "content": "## What This Automation Flow Does (in Simple Terms)\n\nThis automation is designed to process incoming customer emails, extract the important details, and store them in Airtable or any system you like — automatically, without any manual copy-pasting or data cleaning.\n\n\n---\n\n⚙ Tools Used\n\nn8n: Automation platform where the entire workflow is built.\n\nAirtable: Used as a database to store all the extracted customer data in a structured table format.\n\n\n\n---\n\n📦 Complete Flow Breakdown (Step-by-Step)\n\n1. Trigger - New Email Received\n\nThe flow starts when a new email arrives. This could be:\n\nA contact form submission from your Shopify store\n\nA customer sending you a question or feedback\n\nA support request\n\n\nNode Used: IMAP/Email Trigger\n\n\n---\n\n2. Custom JavaScript Code - Smart Data Extraction\n\nThis is the core logic where we:\n\nExtract the sender's email address and name (even if hidden inside angled brackets like John <john@email.com>)\n\nClean the subject line and message body\n\nUse fallback values like \"No Subject\" or \"No message found\" when content is missing\n\nExtract names from phrases like \"Hi, I’m Alex\" if available in the message\n\nAdd a timestamp to track when the message came in\n\n\nNode Used: Function Node\nPurpose: Makes the data clean, structured, and usable — no junk text or broken formatting.\n\n\n---\n\n3. Send to Airtable (or any CRM)\n\nOnce the data is extracted and cleaned:\n\nIt is sent directly to your Airtable base (or CRM/Sheet/Database)\n\nOne row per message, including Name, Email, Subject, Message, and Timestamp\n\n\nNode Used: Airtable - Create Record\n(You can also add filters or conditional routing if needed)\n\n\n---\n\n💡 Why This Is Valuable to You as a Store Owner\n\n✅ Saves hours of manual work: No need to check emails, copy details, and paste them into spreadsheets or CRMs\n\n✅ Never miss a lead: Every message is captured and stored in one place\n\n✅ Clean, structured data: No more messy email threads — just clear info you can act on\n\n✅ Scalable: Works whether you get 10 messages a day or 1,000\n\n✅ Expandable: Later you can auto-send replies, tag messages, or forward to your team\n\n\n\n---\n\n🧠 Bonus: Why the Code Logic Matters\n\nThe JavaScript in the Function node is like a smart assistant:\n\nIt understands where to find data, even if email formats are different\n\nIt removes guesswork, keeps things clean, and ensures nothing breaks downstream\n\nIt’s future-proof — you don’t have to update every time someone sends an email slightly differently\n\n\n\n---\n\n📈 Final Result\n\nYou get a real-time dashboard of every incoming customer message stored neatly — ready for follow-up, reporting, or automation.\n\n \n"
      },
      "typeVersion": 1
    }
  ],
  "active": false,
  "pinData": {},
  "settings": {
    "executionOrder": "v1"
  },
  "versionId": "7facb1ff-d4dc-4141-b177-ca28725fcfb8",
  "connections": {
    "Code": {
      "main": [
        [
          {
            "node": "99aadc61-1a46-45ee-8f92-4159ffd3d8f7",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "99aadc61-1a46-45ee-8f92-4159ffd3d8f7": {
      "main": [
        [
          {
            "node": "f81a918b-3f2b-4f07-b9c2-89aa98b1ce36",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Gmail Trigger": {
      "main": [
        [
          {
            "node": "Code",
            "type": "main",
            "index": 0
          }
        ]
      ]
    }
  }
}