{ "edges": [ { "to": "98b5fc3d-4ea0-4386-8032-568d269b518c", "from": "454cc004-085c-4805-9276-3c4b3bb20d58", "toPort": "0", "fromPort": "0" }, { "to": "98b2007a-618d-4af5-89cc-2fb3a704844c", "from": "4e28e4ce-ca96-40a1-b8da-7d2d2cb55d17", "toPort": "0", "fromPort": "0" }, { "to": "454cc004-085c-4805-9276-3c4b3bb20d58", "from": "08b5c0e9-212d-483c-abe7-08aef59c62bc", "toPort": "0", "fromPort": "0" }, { "to": "4e28e4ce-ca96-40a1-b8da-7d2d2cb55d17", "from": "92190c51-3f14-4bfe-aa35-2cbbd7bde78b", "toPort": "0", "fromPort": "0" }, { "to": "08b5c0e9-212d-483c-abe7-08aef59c62bc", "from": "98b2007a-618d-4af5-89cc-2fb3a704844c", "toPort": "0", "fromPort": "0" } ], "nodes": [ { "id": "92190c51-3f14-4bfe-aa35-2cbbd7bde78b", "name": "Schedule Trigger", "type": "trigger", "params": { "kind": "n8n-nodes-base.scheduleTrigger", "rule": { "interval": [ {} ] } }, "position": [ 0, 0 ] }, { "id": "4e28e4ce-ca96-40a1-b8da-7d2d2cb55d17", "name": "Google Sheets", "type": "integration", "params": { "options": {}, "service": "googleSheets", "operation": "read", "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" } }, "position": [ 220, 0 ] }, { "id": "98b5fc3d-4ea0-4386-8032-568d269b518c", "name": "Gmail", "type": "integration", "params": { "sendTo": "={{ $json.to }}", "message": "={{ $json.html }}", "options": { "appendAttribution": false }, "service": "gmail", "subject": "={{ $json.subject }}", "operation": "read" }, "position": [ 1100, 0 ] }, { "id": "98b2007a-618d-4af5-89cc-2fb3a704844c", "name": "Normalize & Parse", "type": "code", "params": { "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" }, "position": [ 440, 0 ] }, { "id": "08b5c0e9-212d-483c-abe7-08aef59c62bc", "name": "Group & Filter", "type": "code", "params": { "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" }, "position": [ 660, 0 ] }, { "id": "454cc004-085c-4805-9276-3c4b3bb20d58", "name": "Format Digest", "type": "code", "params": { "jsCode": "// Helper: Escape HTML to avoid injection issues\nfunction escapeHtml(text) {\n if (!text) return '';\n return text.toString()\n .replace(/&/g, \"&\")\n .replace(/\"/g, \""\")\n .replace(/'/g, \"'\")\n .replace(//g, \">\");\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 '

None

';\n }\n let html = '';\n html += `\n \n \n \n \n \n \n ${includeStale ? '' : ''}\n \n \n \n `;\n\n rows.forEach(row => {\n html += '';\n html += ``;\n html += ``;\n html += ``;\n html += ``;\n if (includeStale) {\n const lastFollowUpDisplay = row.last_follow_up_parsed || '';\n const statusDisplay = escapeHtml(row.bgv_status) + (row.isStale ? ' ⚠️' : '');\n html += ``;\n html += ``;\n }\n html += '';\n });\n\n html += '

Candidate Name	Previous Company	Previous HR	Previous HR Email	Last Follow-up	Status
${escapeHtml(row.candidate_name)}	${escapeHtml(row.previous_company)}	${escapeHtml(row.prevco_hr_name)}	${escapeHtml(row.prevco_hr_email)}	${lastFollowUpDisplay}	${statusDisplay}

';\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 = `

Dear Executive,

Here is your daily background verification digest for ${todayStr}.

`;\n\n const completedSection = `\n

Completed Today (${completedCount})

\n ${buildTable(completed, false)}\n `;\n\n const pendingSection = `\n

Pending (${pendingCount})

\n ${buildTable(pending, true)}\n

⚠️ indicates stale pending items (no follow-up in last 3 days)

\n `;\n\n const outro = `

Regards,
BGV Operations Team

`;\n\n const htmlBody = `\n \n \n ${intro}\n ${completedSection}\n ${pendingSection}\n ${outro}\n \n \n `;\n\n return {\n json: {\n to: execEmail,\n subject: subject,\n html: htmlBody\n }\n };\n});\n" }, "position": [ 880, 0 ] } ] }

Daily BGV status digest: Track verifications with Google Sheets to Gmail alerts