BGV-Tracker

• •Google-Konto + Gmail API-Anmeldedaten
• •Google Sheets API-Anmeldedaten

{
  "id": "RAGbASSkkdJed4ur",
  "meta": {
    "instanceId": "14e4c77104722ab186539dfea5182e419aecc83d85963fe13f6de862c875ebfa",
    "templateCredsSetupCompleted": true
  },
  "name": "BGV Tracker",
  "tags": [],
  "nodes": [
    {
      "id": "92190c51-3f14-4bfe-aa35-2cbbd7bde78b",
      "name": "Zeitplan-Trigger",
      "type": "n8n-nodes-base.scheduleTrigger",
      "position": [
        0,
        0
      ],
      "parameters": {
        "rule": {
          "interval": [
            {}
          ]
        }
      },
      "typeVersion": 1.2
    },
    {
      "id": "4e28e4ce-ca96-40a1-b8da-7d2d2cb55d17",
      "name": "Google Tabellen",
      "type": "n8n-nodes-base.googleSheets",
      "position": [
        220,
        0
      ],
      "parameters": {
        "options": {},
        "sheetName": {
          "__rl": true,
          "mode": "list",
          "value": "gid=0",
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/132inVcr60cNg5AUY51aKG-4R8UgQ6xlGgLgC3ZwKxuU/edit#gid=0",
          "cachedResultName": "BGV Tracker"
        },
        "documentId": {
          "__rl": true,
          "mode": "list",
          "value": "132inVcr60cNg5AUY51aKG-4R8UgQ6xlGgLgC3ZwKxuU",
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/132inVcr60cNg5AUY51aKG-4R8UgQ6xlGgLgC3ZwKxuU/edit?usp=drivesdk",
          "cachedResultName": "BGV Tracker"
        }
      },
      "credentials": {
        "googleSheetsOAuth2Api": {
          "id": "AuKRQmTf8cbXf3oC",
          "name": "Google Sheets account 13"
        }
      },
      "typeVersion": 4.6
    },
    {
      "id": "98b5fc3d-4ea0-4386-8032-568d269b518c",
      "name": "Gmail",
      "type": "n8n-nodes-base.gmail",
      "position": [
        1100,
        0
      ],
      "webhookId": "df9b384a-84a3-4233-90fa-d8a8581d6f3d",
      "parameters": {
        "sendTo": "={{ $json.to }}",
        "message": "={{ $json.html }}",
        "options": {
          "appendAttribution": false
        },
        "subject": "={{ $json.subject }}"
      },
      "credentials": {
        "gmailOAuth2": {
          "id": "WZ9I0DxvDMdi0ZtY",
          "name": "Gmail account 13"
        }
      },
      "typeVersion": 2.1
    },
    {
      "id": "98b2007a-618d-4af5-89cc-2fb3a704844c",
      "name": "Normalisieren & Parsen",
      "type": "n8n-nodes-base.code",
      "position": [
        440,
        0
      ],
      "parameters": {
        "jsCode": "// Helper to parse various local date formats to UTC Date object in Asia/Kolkata timezone\n// Supports formats like DD/MM/YYYY, DD-MM-YYYY, MM/DD/YYYY, and ISO strings\nfunction parseDateToIST(dateStr) {\n  if (!dateStr) return null;\n\n  // Remove extra spaces and check if ISO first\n  dateStr = dateStr.trim();\n  const isoMatch = dateStr.match(/^\\d{4}-\\d{2}-\\d{2}/);\n  if (isoMatch) {\n    // ISO string, parse directly\n    return new Date(dateStr);\n  }\n\n  // Handle DD/MM/YYYY or DD-MM-YYYY or MM/DD/YYYY formats by heuristics\n  // We assume Indian locale DD/MM/YYYY by default\n  // We'll try parsing with a few patterns; fallback to null if fails\n  const parts1 = dateStr.split(/[\\/\\-]/);\n  if (parts1.length !== 3) return null;\n\n  let dd, mm, yyyy;\n  // Heuristic to detect if third part is year or not\n  if (parts1[2].length === 4) {\n    // Probably DD/MM/YYYY or MM/DD/YYYY\n    // Let's assume DD/MM/YYYY (Indian standard)\n    dd = parseInt(parts1[0], 10);\n    mm = parseInt(parts1[1], 10) - 1;  // month is zero-based in JS Date\n    yyyy = parseInt(parts1[2], 10);\n  } else {\n    return null;\n  }\n\n  // Build Date in IST timezone by creating UTC equivalent adjusted for IST offset\n  // IST is UTC+05:30, so build Date in UTC and add offset\n  // To avoid confusion, create Date in UTC and adjust by offset\n  const date = new Date(Date.UTC(yyyy, mm, dd));\n  return date;\n}\n\n// Get today's date in IST (without time, for comparison)\nfunction getTodayIST() {\n  const nowUTC = new Date();\n  // IST offset in minutes\n  const offsetMinutes = 5 * 60 + 30;\n  // convert to IST\n  const nowIST = new Date(nowUTC.getTime() + offsetMinutes * 60 * 1000);\n  // Zero out time fields for date-only comparison\n  return new Date(nowIST.getFullYear(), nowIST.getMonth(), nowIST.getDate());\n}\n\n// Calculate difference in full days between two dates\nfunction daysBetween(date1, date2) {\n  if (!(date1 instanceof Date) || !(date2 instanceof Date)) return null;\n  const msPerDay = 24 * 60 * 60 * 1000;\n  const utc1 = Date.UTC(date1.getFullYear(), date1.getMonth(), date1.getDate());\n  const utc2 = Date.UTC(date2.getFullYear(), date2.getMonth(), date2.getDate());\n  return Math.floor((utc2 - utc1) / msPerDay);\n}\n\nconst todayIST = getTodayIST();\nconst staleThresholdDays = 3;  // Adjust as per config if needed\n\nreturn items.map(item => {\n  // Normalize keys to lowercase for consistency:\n  const normalized = {};\n  for (const key in item.json) {\n    normalized[key.toLowerCase()] = item.json[key];\n  }\n\n  // Parse dates\n  const completionDateRaw = normalized['bgv_completion_date'];\n  const lastFollowUpRaw = normalized['last_follow_up'];\n\n  const completionDate = parseDateToIST(completionDateRaw);\n  const lastFollowUpDate = parseDateToIST(lastFollowUpRaw);\n\n  // Determine if Completed Today (completed and completionDate == today IST)\n  const isCompleted = normalized['bgv_status'] === 'Completed';\n  const isCompletedToday = isCompleted && completionDate &&\n    daysBetween(completionDate, todayIST) === 0;\n\n  // Calculate days since last follow up (null if no date)\n  const daysSinceFollowUp = lastFollowUpDate ? daysBetween(lastFollowUpDate, todayIST) : null;\n\n  // Is stale if last follow up was >= staleThresholdDays ago (and status Pending)\n  const isPending = normalized['bgv_status'] === 'Pending';\n  const isStale = isPending && daysSinceFollowUp !== null && daysSinceFollowUp >= staleThresholdDays;\n\n  return {\n    json: {\n      ...normalized,\n      bgv_completion_date_parsed: completionDate ? completionDate.toISOString().slice(0,10) : null,\n      last_follow_up_parsed: lastFollowUpDate ? lastFollowUpDate.toISOString().slice(0,10) : null,\n      isCompletedToday,\n      daysSinceFollowUp,\n      isStale,\n    }\n  };\n});\n"
      },
      "typeVersion": 2
    },
    {
      "id": "08b5c0e9-212d-483c-abe7-08aef59c62bc",
      "name": "Gruppieren & Filtern",
      "type": "n8n-nodes-base.code",
      "position": [
        660,
        0
      ],
      "parameters": {
        "jsCode": "// Input items assumed to have these properties already calculated in the previous node:\n// - bgv_exe_email (string)\n// - isCompletedToday (boolean)\n// - bgv_status (string)\n// - isStale (boolean)\n\n// Create a map keyed by bgv_exe_email\nconst grouped = {};\n\n// Iterate over each item from previous node\nitems.forEach(item => {\n  const json = item.json;\n  const exeEmail = json['bgv_exe_email'];\n\n  if (!exeEmail) {\n    // Skip rows without executive email (or could collect in a \"no-exe\" group if desired)\n    return;\n  }\n\n  if (!grouped[exeEmail]) {\n    grouped[exeEmail] = {\n      bgv_exe_email: exeEmail,\n      completedToday: [],\n      pending: []\n    };\n  }\n\n  // Check conditions\n  if (json.isCompletedToday) {\n    grouped[exeEmail].completedToday.push(json);\n  } \n  else if (json.bgv_status === 'Pending' && json.bgv_status !== 'To be Sent') {\n    grouped[exeEmail].pending.push(json);\n  }\n});\n\n// For each group, add the stale flags (already on rows), so no need to add extra here\n\n// Prepare output array, one item per executive with grouped data\nconst output = Object.values(grouped).map(group => {\n  return {\n    json: group\n  };\n});\n\nreturn output;\n"
      },
      "typeVersion": 2
    },
    {
      "id": "454cc004-085c-4805-9276-3c4b3bb20d58",
      "name": "Zusammenfassung formatieren",
      "type": "n8n-nodes-base.code",
      "position": [
        880,
        0
      ],
      "parameters": {
        "jsCode": "// Helper: Escape HTML to avoid injection issues\nfunction escapeHtml(text) {\n  if (!text) return '';\n  return text.toString()\n    .replace(/&/g, \"&amp;\")\n    .replace(/\"/g, \"&quot;\")\n    .replace(/'/g, \"&#39;\")\n    .replace(/</g, \"&lt;\")\n    .replace(/>/g, \"&gt;\");\n}\n\n// Template configuration (can be replaced with variables or data from config node)\nconst subjectTemplate = (date, completedCount, pendingCount) =>\n  `BGV digest for ${date} — ${completedCount} completed, ${pendingCount} pending`;\n\n// For date display in subject and intro - IST date as YYYY-MM-DD\nconst todayIST = new Date();\ntodayIST.setHours(todayIST.getHours() + 5);\ntodayIST.setMinutes(todayIST.getMinutes() + 30);\nconst yyyy = todayIST.getFullYear();\nconst mm = String(todayIST.getMonth() + 1).padStart(2, '0');\nconst dd = String(todayIST.getDate()).padStart(2, '0');\nconst todayStr = `${yyyy}-${mm}-${dd}`;\n\n// Function to build a HTML table for a list of candidates\nfunction buildTable(rows, includeStale=false) {\n  if (!rows || rows.length === 0) {\n    return '<p><i>None</i></p>';\n  }\n  let html = '<table border=\"1\" cellpadding=\"5\" cellspacing=\"0\" style=\"border-collapse: collapse;\">';\n  html += `\n    <thead>\n      <tr>\n        <th>Candidate Name</th>\n        <th>Previous Company</th>\n        <th>Previous HR</th>\n        <th>Previous HR Email</th>\n        ${includeStale ? '<th>Last Follow-up</th><th>Status</th>' : ''}\n      </tr>\n    </thead>\n    <tbody>\n  `;\n\n  rows.forEach(row => {\n    html += '<tr>';\n    html += `<td>${escapeHtml(row.candidate_name)}</td>`;\n    html += `<td>${escapeHtml(row.previous_company)}</td>`;\n    html += `<td>${escapeHtml(row.prevco_hr_name)}</td>`;\n    html += `<td><a href=\"mailto:${escapeHtml(row.prevco_hr_email)}\">${escapeHtml(row.prevco_hr_email)}</a></td>`;\n    if (includeStale) {\n      const lastFollowUpDisplay = row.last_follow_up_parsed || '';\n      const statusDisplay = escapeHtml(row.bgv_status) + (row.isStale ? ' ⚠️' : '');\n      html += `<td>${lastFollowUpDisplay}</td>`;\n      html += `<td>${statusDisplay}</td>`;\n    }\n    html += '</tr>';\n  });\n\n  html += '</tbody></table>';\n  return html;\n}\n\n// Array to hold output per executive for SMTP node\nreturn items.map(item => {\n  const execEmail = item.json.bgv_exe_email;\n  const completed = item.json.completedToday || [];\n  const pending = item.json.pending || [];\n\n  const completedCount = completed.length;\n  const pendingCount = pending.length;\n\n  // Compose email subject\n  const subject = subjectTemplate(todayStr, completedCount, pendingCount);\n\n  // Compose the HTML body content\n  const intro = `<p>Dear Executive,</p><p>Here is your daily background verification digest for <b>${todayStr}</b>.</p>`;\n\n  const completedSection = `\n    <h2>Completed Today (${completedCount})</h2>\n    ${buildTable(completed, false)}\n  `;\n\n  const pendingSection = `\n    <h2>Pending (${pendingCount})</h2>\n    ${buildTable(pending, true)}\n    <p><small>⚠️ indicates stale pending items (no follow-up in last 3 days)</small></p>\n  `;\n\n  const outro = `<p>Regards,<br/>BGV Operations Team</p>`;\n\n  const htmlBody = `\n    <html>\n    <body>\n      ${intro}\n      ${completedSection}\n      ${pendingSection}\n      ${outro}\n    </body>\n    </html>\n  `;\n\n  return {\n    json: {\n      to: execEmail,\n      subject: subject,\n      html: htmlBody\n    }\n  };\n});\n"
      },
      "typeVersion": 2
    },
    {
      "id": "1a75d763-8f88-4e2e-819e-f9a6ec8ccc87",
      "name": "Kurznotiz",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -40,
        -120
      ],
      "parameters": {
        "width": 1340,
        "height": 360,
        "content": "##BGV Executive Digest Automation: Track Completed & Pending Verifications via Email (from Google Sheets)"
      },
      "typeVersion": 1
    },
    {
      "id": "6da3d9e8-d1d4-4c49-8d26-7e0b1bf6f03c",
      "name": "Kurznotiz1",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -40,
        280
      ],
      "parameters": {
        "width": 1340,
        "height": 1080,
        "content": "### **Node List & Descriptions**\n\n1. **Schedule Trigger**\n   - **What:** Kicks off the workflow every night at 23:00 IST.\n   - **Why:** Ensures digests are sent consistently at the same time.\n\n2. **Google Sheets**\n   - **What:** Reads all candidate background verification data from the “BGV Tracker” tab.\n   - **Why:** Pulls the latest statuses and updates for processing.\n\n3. **Code Node** (Normalize & Parse Data)\n   - **What:**  \n     - Converts all Sheet column names to lowercase (case-insensitive).\n     - Parses `bgv_completion_date` and `last_follow_up` (supports DD/MM/YYYY, DD-MM-YYYY, MM/DD/YYYY, ISO).\n     - Adds these flags:\n       - `isCompletedToday` (for \"Completed\" records with today’s date)\n       - `isStale` (for \"Pending\" items with last follow-up ≥ 3 days ago)\n   - **Why:** Standardizes input for downstream logic and alerts.\n\n4. **Code Node** (Group & Filter Entries)\n   - **What:**  \n     - Groups rows by `bgv_exe_email` (executive owner)\n     - In each group:\n       - Filters “Completed Today” (finalized today)\n       - Filters “Pending” (excluding “To be Sent”)\n       - Carries stale flag per item\n   - **Why:** Ensures personalized digests for each executive.\n\n5. **Code Node** (Format Digest Content)\n   - **What:**  \n     - Creates the email subject and HTML body for each executive:\n       - **Completed Today**: Tabular summary\n       - **Pending**: Table with “stale” ⚠️ highlight\n     - Sets `to`, `subject`, and `html` for email sending\n   - **Why:** Prepares clear, actionable digest with candidate info and overdue flags.\n\n6. **Gmail**\n   - **What:**  \n     - Sends the personalized email to each executive (`bgv_exe_email`)\n     - Uses subject and HTML generated in previous node\n   - **Why:** Delivers daily updates automatically, eliminating manual summary work.\n\n***\n\n### **Quick Reference: Flow Logic**\n\n- **Trigger → Read sheet → Clean/parse data → Group by exec → Format email → Send**"
      },
      "typeVersion": 1
    }
  ],
  "active": false,
  "pinData": {},
  "settings": {
    "executionOrder": "v1"
  },
  "versionId": "139bdfc3-3aa7-4b5b-b0a7-4ce504bbec00",
  "connections": {
    "454cc004-085c-4805-9276-3c4b3bb20d58": {
      "main": [
        [
          {
            "node": "98b5fc3d-4ea0-4386-8032-568d269b518c",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Google Sheets": {
      "main": [
        [
          {
            "node": "98b2007a-618d-4af5-89cc-2fb3a704844c",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "08b5c0e9-212d-483c-abe7-08aef59c62bc": {
      "main": [
        [
          {
            "node": "454cc004-085c-4805-9276-3c4b3bb20d58",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "92190c51-3f14-4bfe-aa35-2cbbd7bde78b": {
      "main": [
        [
          {
            "node": "Google Sheets",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "98b2007a-618d-4af5-89cc-2fb3a704844c": {
      "main": [
        [
          {
            "node": "08b5c0e9-212d-483c-abe7-08aef59c62bc",
            "type": "main",
            "index": 0
          }
        ]
      ]
    }
  }
}