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

Background Verification Executive Digest Automation: Track Completed & Pending Verifications via Email (from Google Sheets)

This automation sends a daily late-night email summary to each Background Verification (BGV) executive — straight out of your “BGV Tracker” Google Sheet. Each executive gets their own digest listing candidates whose background checks were completed today and which ones are still pending, complete with clear “stale” alerts for overdue follow-ups. The workflow runs reliably via n8n, auto-grouping entries and packaging the results in a user-friendly tabular email, every day at 23:00 IST.

Who’s it for

- BGV team leads & vendors who want automatic updates on their queue. - HR managers looking to automate candidate tracking without manual report compilation. - Ops teams with Google Sheets-based BGV tracking wanting digest emails for each owner. - Any organization managing candidate checks across multiple owners/executives.

How it works

1. Schedule Trigger: Runs every night at 23:00 IST (Asia/Kolkata), weekends OFF by default. 2. Google Sheets: Reads all rows from your “BGV Tracker” (tab), using columns: - candidatename, previouscompany, prevcohrname, prevcohremail - bgvstatus, lastfollowup, bgvcompletiondate, bgvexecutive, bgvexeemail 3. Code Node 1 (“Normalize & Parse”): - Cleans and lower-cases all column names for consistency. - Parses lastfollowup & bgvcompletiondate from multiple local date formats. - Adds helper flags: isCompletedToday, isStale (pending and last-follow-up ≥ 3 days). 4. Code Node 2 (“Group & Filter”): - Groups rows by bgvexeemail (executive). - Within each group: Segregates “Completed Today” and “Pending” items. 5. Code Node 3 (“Format Digest”): - Generates a personalized HTML email per executive, with two tables: - Completed Today (with candidate/prevco info) - Pending (with “stale” ⚠️ flags) - Builds the subject line showing per-executive summary counts. 6. Gmail Node: - Sends the formatted email to each executive automatically. - Can be customized for preview/test mode, CC or CSV attachments.

How to set up 1. Prepare Google Sheet: - Create “BGV Tracker” tab (case-insensitive column names). - Fill columns: - candidatename, previouscompany, prevcohrname, prevcohremail, bgvstatus, lastfollowup, bgvcompletiondate, bgvexecutive, bgvexeemail 2. n8n Configuration: - Authenticate Google Sheets node (OAuth). - Authenticate Gmail node (App Password recommended). - Import workflow JSON and adjust credentials and sheet IDs. 3. Test run (optional): - Fill sample data in the sheet. - Start workflow and review “dry run” outputs (optional). 4. Activate workflow for daily auto-emails.

Requirements - A Google Sheet tab called “BGV Tracker” with supported columns. - Gmail (SMTP or OAuth) credentials with permissions to send. - n8n cloud or self-hosted, with Google and Gmail creds configured.