Automatisierung basierend auf Antwortzeiten

• •Google-Konto + Gmail API-Anmeldedaten
• •Telegram Bot Token
• •Google Sheets API-Anmeldedaten

{
  "id": "RB08fvn7cXuNcJKU",
  "meta": {
    "instanceId": "8443f10082278c46aa5cf3acf8ff0f70061a2c58bce76efac814b16290845177",
    "templateCredsSetupCompleted": true
  },
  "name": "Automated reply_hours based automation",
  "tags": [],
  "nodes": [
    {
      "id": "b3eabad0-a164-4795-b945-28aef6495a00",
      "name": "5 Minuten warten",
      "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": "Geschäftszeiten prüfen",
      "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 Geschäftszeiten?",
      "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 senden (Geschäftszeiten)",
      "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 senden (Außerhalb der Geschäftszeiten)",
      "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 benachrichtigen",
      "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 Sheets Trigger1",
      "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": "Felder bearbeiten",
      "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": "Haftnotiz",
      "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": "Haftnotiz1",
      "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": "Haftnotiz2",
      "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": "Haftnotiz3",
      "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": "Haftnotiz4",
      "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": "Haftnotiz5",
      "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": "Haftnotiz6",
      "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": "Haftnotiz7",
      "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
          }
        ]
      ]
    }
  }
}