返信時間に基づく自動化

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

{
  "id": "RB08fvn7cXuNcJKU",
  "meta": {
    "instanceId": "8443f10082278c46aa5cf3acf8ff0f70061a2c58bce76efac814b16290845177",
    "templateCredsSetupCompleted": true
  },
  "name": "Automated reply_hours based automation",
  "tags": [],
  "nodes": [
    {
      "id": "b3eabad0-a164-4795-b945-28aef6495a00",
      "name": "5分間待機",
      "type": "n8n-nodes-base.wait",
      "position": [
        -720,
        0
      ],
      "webhookId": "f91de49f-1455-487f-9c38-5a4be0742f83",
      "parameters": {
        "unit": "minutes",
        "amount": 5
      },
      "typeVersion": 1
    },
    {
      "id": "b0ccf87c-4703-41e8-98ee-79f5ff41b400",
      "name": "営業時間を確認",
      "type": "n8n-nodes-base.function",
      "position": [
        -496,
        0
      ],
      "parameters": {
        "functionCode": "return items.map(item => {\n  const timestamp = item.json[\"Time Stamp\"]; // <-- corrected key\n\n  // Convert Excel serial timestamp to JS Date\n  const excelEpoch = new Date(Date.UTC(1899, 11, 30));\n  const jsDate = new Date(excelEpoch.getTime() + timestamp * 24 * 60 * 60 * 1000);\n\n  const day = jsDate.getDay(); // 0=Sunday, 6=Saturday\n  const hour = jsDate.getHours();\n\n  // Configure business hours here\n  const businessDays = [1, 2, 3, 4, 5]; // Mon-Fri\n  const startHour = 9;\n  const endHour = 18;\n\n  let isBusinessHours = false;\n  if (businessDays.includes(day) && hour >= startHour && hour < endHour) {\n    isBusinessHours = true;\n  }\n\n  // Format only the time (HH:mm:ss)\n  const onlyTime = jsDate.toTimeString().substring(0, 8);\n\n  return {\n    json: {\n      ...item.json,\n      exactDateTime: jsDate.toISOString(), // valid datetime for IF node\n      onlyTime,                            // e.g. \"19:05:03\" (for display only)\n      isBusinessHours\n    }\n  };\n});\n"
      },
      "typeVersion": 1
    },
    {
      "id": "a2107454-799e-4c31-b23f-bd586d1eaf86",
      "name": "IF 営業時間内？",
      "type": "n8n-nodes-base.if",
      "position": [
        -272,
        0
      ],
      "parameters": {
        "conditions": {
          "dateTime": [
            {
              "value1": "={{ $json.exactDateTime }}",
              "value2": "2025-09-02T18:00:00",
              "operation": "before"
            }
          ]
        }
      },
      "typeVersion": 1
    },
    {
      "id": "365df3f2-c7b4-4b47-a1ce-5d12ecc2fbec",
      "name": "Gmail送信（営業時間内）",
      "type": "n8n-nodes-base.gmail",
      "position": [
        176,
        -96
      ],
      "parameters": {
        "toList": [
          "={{ $json[\"Email Address\"] }}"
        ],
        "message": "=Hii {{ $json[\"First Name\"] }}{{ $json[\"Last name\"] }}\n\nThanks for connecting,\nOur team will get back to you soon",
        "subject": "Thanks for reaching out!",
        "resource": "message",
        "additionalFields": {}
      },
      "credentials": {
        "gmailOAuth2": {
          "id": "RchiXdmY8WaQhOSJ",
          "name": "Gmail account"
        }
      },
      "typeVersion": 1
    },
    {
      "id": "40e0ccb1-681b-468d-a046-3d24b32f2a26",
      "name": "Gmail送信（営業時間外）",
      "type": "n8n-nodes-base.gmail",
      "position": [
        176,
        96
      ],
      "parameters": {
        "toList": [
          "={{ $json[\"Email address\"] }}"
        ],
        "message": "=Hii {{ $json[\"First name\"] }}{{ $json[\"Last name\"] }}\n\nThanks for connecting in after hours\nOur team will get back to you tomorrow",
        "subject": "Thanks for contacting us (After Hours)",
        "resource": "message",
        "additionalFields": {}
      },
      "credentials": {
        "gmailOAuth2": {
          "id": "RchiXdmY8WaQhOSJ",
          "name": "Gmail account"
        }
      },
      "typeVersion": 1
    },
    {
      "id": "a2824f7c-78c0-4bb2-92c6-b2f56156908e",
      "name": "Telegramに通知",
      "type": "n8n-nodes-base.telegram",
      "position": [
        400,
        0
      ],
      "webhookId": "93d3561a-1fbd-4114-9459-75994be03027",
      "parameters": {
        "text": "=📩 New lead received!\nName:{{ $('Google Sheets Trigger1').item.json['First Name'] }} {{ $('Google Sheets Trigger1').item.json['Last name'] }} \nEmail: {{ $('Google Sheets Trigger1').item.json['Email Address'] }}\n\nConnect with the lead tomorrow",
        "chatId": "1848105620",
        "additionalFields": {}
      },
      "credentials": {
        "telegramApi": {
          "id": "eoz18tywRndbv87T",
          "name": "Telegram account -anuj"
        }
      },
      "typeVersion": 1
    },
    {
      "id": "f56122fd-a759-4f8b-9cef-df47e6787a55",
      "name": "Google スプレッドシートトリガー1",
      "type": "n8n-nodes-base.googleSheetsTrigger",
      "position": [
        -944,
        0
      ],
      "parameters": {
        "event": "rowUpdate",
        "options": {},
        "pollTimes": {
          "item": [
            {
              "mode": "everyMinute"
            }
          ]
        },
        "sheetName": {
          "__rl": true,
          "mode": "list",
          "value": 2142376877,
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/1NxuC7u6ycvn18f-vyUjvIiYmQ_DJiH7q6YwLndIlLi8/edit#gid=2142376877",
          "cachedResultName": "Form Responses 1"
        },
        "documentId": {
          "__rl": true,
          "mode": "list",
          "value": "1NxuC7u6ycvn18f-vyUjvIiYmQ_DJiH7q6YwLndIlLi8",
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/1NxuC7u6ycvn18f-vyUjvIiYmQ_DJiH7q6YwLndIlLi8/edit?usp=drivesdk",
          "cachedResultName": "Lead form (Responses)"
        }
      },
      "credentials": {
        "googleSheetsTriggerOAuth2Api": {
          "id": "tjY8nawuuRO1rxAE",
          "name": "Google Sheets Trigger account"
        }
      },
      "typeVersion": 1
    },
    {
      "id": "319a8e69-f652-4713-aaac-d2365c8fb0e3",
      "name": "フィールド編集",
      "type": "n8n-nodes-base.set",
      "position": [
        -48,
        96
      ],
      "parameters": {
        "options": {},
        "assignments": {
          "assignments": [
            {
              "id": "70fef5df-53fd-43dc-bd0c-536bd53876f5",
              "name": "First name",
              "type": "string",
              "value": "={{ $('Google Sheets Trigger1').item.json['First name'] }}"
            },
            {
              "id": "e4c87a58-3ac6-427a-aa8b-3f8fe4614173",
              "name": "Last name",
              "type": "string",
              "value": "={{ $('Google Sheets Trigger1').item.json['Last name'] }}"
            },
            {
              "id": "bff7e63c-cf7a-4249-a4a3-8e587abefc91",
              "name": "Email address",
              "type": "string",
              "value": "={{ $('Google Sheets Trigger1').item.json['Email address'] }}"
            }
          ]
        }
      },
      "typeVersion": 3.4
    },
    {
      "id": "9761aed1-58b5-4fa6-a6fb-0a2c278ca047",
      "name": "付箋",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -1072,
        -416
      ],
      "parameters": {
        "height": 400,
        "content": "## Node 1: Google Sheets Trigger1\n**Type**: Trigger Node\n**Purpose**: Lead Form Monitor\n**Description**: This node continuously monitors a spreadsheet for new lead form submissions or updates to existing entries. It polls the data source every minute and automatically triggers the workflow when new responses are detected or when existing rows are updated. The node captures lead information including names, email addresses, and timestamps from form submissions."
      },
      "typeVersion": 1
    },
    {
      "id": "8a747a3f-7199-450e-b73d-e57f407fdd63",
      "name": "付箋1",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -864,
        160
      ],
      "parameters": {
        "height": 336,
        "content": "## Node 2: Wait 5 Minutes\n**Type**: Delay Node\n**Purpose**: Processing Buffer\n**Description**: This node introduces a 5-minute delay before processing the lead data. This buffer time allows for any additional form data to be fully captured and ensures system stability before proceeding with the automated response logic. The delay helps prevent processing incomplete or rapidly changing data."
      },
      "typeVersion": 1
    },
    {
      "id": "b3a3477d-09e9-43b8-85a8-6bda95a18ba1",
      "name": "付箋2",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -608,
        -432
      ],
      "parameters": {
        "height": 416,
        "content": "## Node 3: Check Business Hours\n**Type**: Custom Function Node\n**Purpose**: Time Analysis Engine\n**Description**: This node processes timestamp data from the lead form to determine when the inquiry was received. It converts Excel serial timestamps to standard datetime formats, analyzes the day of the week and hour, and determines whether the submission occurred during configured business hours (typically Monday-Friday, 9 AM-6 PM). The node outputs formatted time information and a business hours flag."
      },
      "typeVersion": 1
    },
    {
      "id": "4a0f74be-c781-4d0e-8602-629a374666e5",
      "name": "付箋3",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -432,
        144
      ],
      "parameters": {
        "height": 368,
        "content": "## Node 4: IF Business Hours?\n**Type**: Conditional Logic Node\n**Purpose**: Response Path Router\n**Description**: This node evaluates whether the lead submission occurred during business hours based on the analysis from the previous node. It creates two distinct execution paths: one for leads received during business hours and another for after-hours submissions. The conditional logic ensures appropriate response messaging based on timing."
      },
      "typeVersion": 1
    },
    {
      "id": "95366c59-2534-4958-9486-69bce28df5db",
      "name": "付箋4",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -96,
        -448
      ],
      "parameters": {
        "width": 256,
        "height": 368,
        "content": "## Node 5: Send Gmail (Business Hours)\n**Type**: Email Communication Node\n**Purpose**: Standard Business Response\n**Description**: This node sends an automated email response to leads who submitted inquiries during business hours. It composes a personalized message using the lead's name, acknowledges their inquiry, and provides a standard business-hours response indicating that the team will respond soon. The email is sent from a configured business email account."
      },
      "typeVersion": 1
    },
    {
      "id": "a14ce2d8-a41e-4fb1-93e8-60ed91476603",
      "name": "付箋5",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -112,
        256
      ],
      "parameters": {
        "height": 320,
        "content": "## Node 6: Edit Fields\n**Type**: Data Processing Node\n**Purpose**: Field Standardizer\n**Description**: This node processes and standardizes lead data fields for after-hours submissions. It ensures consistent field naming and formatting between the trigger data and the email sending requirements. The node maps and cleans up field names (first name, last name, email address) to ensure compatibility with the after-hours email template."
      },
      "typeVersion": 1
    },
    {
      "id": "bc1ce307-a9d6-4ccd-94cc-aa432ddc6241",
      "name": "付箋6",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        224,
        256
      ],
      "parameters": {
        "height": 432,
        "content": "## Node 7: Send Gmail (After Hours)\n**Type**: Email Communication Node\n**Purpose**: After-Hours Response\n**Description**: This node sends a specialized automated email response to leads who submitted inquiries outside of business hours. It composes a personalized message acknowledging the after-hours contact and sets appropriate expectations by informing the lead that the team will respond the next business day. The messaging is tailored for after-hours communications."
      },
      "typeVersion": 1
    },
    {
      "id": "1670bf9f-7bcd-452a-90f0-680fb4a3f969",
      "name": "付箋7",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        576,
        -128
      ],
      "parameters": {
        "height": 368,
        "content": "## Node 8: Notify Telegram\n**Type**: Team Notification Node\n**Purpose**: Internal Alert System\n**Description**: This node sends immediate notifications to a team communication channel whenever a new lead is processed. It formats lead information (name, email) into a structured message and sends it via a messaging platform to alert team members about new inquiries. The notification includes relevant lead details and timing context for team awareness."
      },
      "typeVersion": 1
    }
  ],
  "active": false,
  "pinData": {},
  "settings": {
    "executionOrder": "v1"
  },
  "versionId": "1e243867-6190-4393-8668-3ce609784a0e",
  "connections": {
    "319a8e69-f652-4713-aaac-d2365c8fb0e3": {
      "main": [
        [
          {
            "node": "40e0ccb1-681b-468d-a046-3d24b32f2a26",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "b3eabad0-a164-4795-b945-28aef6495a00": {
      "main": [
        [
          {
            "node": "b0ccf87c-4703-41e8-98ee-79f5ff41b400",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "a2107454-799e-4c31-b23f-bd586d1eaf86": {
      "main": [
        [
          {
            "node": "365df3f2-c7b4-4b47-a1ce-5d12ecc2fbec",
            "type": "main",
            "index": 0
          }
        ],
        [
          {
            "node": "319a8e69-f652-4713-aaac-d2365c8fb0e3",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "b0ccf87c-4703-41e8-98ee-79f5ff41b400": {
      "main": [
        [
          {
            "node": "a2107454-799e-4c31-b23f-bd586d1eaf86",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "f56122fd-a759-4f8b-9cef-df47e6787a55": {
      "main": [
        [
          {
            "node": "b3eabad0-a164-4795-b945-28aef6495a00",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "40e0ccb1-681b-468d-a046-3d24b32f2a26": {
      "main": [
        [
          {
            "node": "a2824f7c-78c0-4bb2-92c6-b2f56156908e",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "365df3f2-c7b4-4b47-a1ce-5d12ecc2fbec": {
      "main": [
        [
          {
            "node": "a2824f7c-78c0-4bb2-92c6-b2f56156908e",
            "type": "main",
            "index": 0
          }
        ]
      ]
    }
  }
}