Google Sheets to Gmail, BGV digests sent per exec
Your BGV tracker is “up to date” until someone asks for a status list, and then it turns into a late-night scramble. Rows are missing. Dates are inconsistent. Follow-ups slip because nobody can quickly see what’s stale.
This Sheets Gmail digests automation hits HR managers hard, but BGV team leads and ops coordinators feel it too. Instead of chasing updates across owners, you get a clean, per-executive email every night with completed and pending cases, plus stale follow-up alerts.
Below you’ll see the workflow, what it fixes, and what changes once it’s running at 23:00 IST on autopilot.
How This Automation Works
The full n8n workflow, from trigger to final output:
n8n Workflow Template: Google Sheets to Gmail, BGV digests sent per exec
flowchart LR
subgraph sg0["Schedule Flow"]
direction LR
n0@{ icon: "mdi:play-circle", form: "rounded", label: "Schedule Trigger", pos: "b", h: 48 }
n1@{ icon: "mdi:database", form: "rounded", label: "Google Sheets", pos: "b", h: 48 }
n2@{ icon: "mdi:message-outline", form: "rounded", label: "Gmail", pos: "b", h: 48 }
n3["<div style='background:#f5f5f5;padding:10px;border-radius:8px;display:inline-block;border:1px solid #e0e0e0'><img src='https://flowpast.com/wp-content/uploads/n8n-workflow-icons/code.svg' width='40' height='40' /></div><br/>Normalize & Parse"]
n4["<div style='background:#f5f5f5;padding:10px;border-radius:8px;display:inline-block;border:1px solid #e0e0e0'><img src='https://flowpast.com/wp-content/uploads/n8n-workflow-icons/code.svg' width='40' height='40' /></div><br/>Group & Filter"]
n5["<div style='background:#f5f5f5;padding:10px;border-radius:8px;display:inline-block;border:1px solid #e0e0e0'><img src='https://flowpast.com/wp-content/uploads/n8n-workflow-icons/code.svg' width='40' height='40' /></div><br/>Format Digest"]
n5 --> n2
n1 --> n3
n4 --> n5
n0 --> n1
n3 --> n4
end
%% Styling
classDef trigger fill:#e8f5e9,stroke:#388e3c,stroke-width:2px
classDef ai fill:#e3f2fd,stroke:#1976d2,stroke-width:2px
classDef aiModel fill:#e8eaf6,stroke:#3f51b5,stroke-width:2px
classDef decision fill:#fff8e1,stroke:#f9a825,stroke-width:2px
classDef database fill:#fce4ec,stroke:#c2185b,stroke-width:2px
classDef api fill:#fff3e0,stroke:#e65100,stroke-width:2px
classDef code fill:#f3e5f5,stroke:#7b1fa2,stroke-width:2px
classDef disabled stroke-dasharray: 5 5,opacity: 0.5
class n0 trigger
class n1 database
class n3,n4,n5 code
classDef customIcon fill:none,stroke:none
class n3,n4,n5 customIcon
The Problem: BGV updates turn into daily chasing
BGV tracking looks simple on paper: keep a Google Sheet, update statuses, follow up when needed. In real life, that “simple sheet” becomes a coordination tax. Executives each own a slice of the queue, but the whole team still needs visibility, which means someone ends up compiling summaries. And because date formats vary (someone types “12/1”, someone else pastes “01-12-2026”), the “overdue” logic becomes guesswork. Add one more thing: people forget to follow up when the sheet doesn’t scream about it. That’s how candidates get stuck in pending for days.
It adds up fast. Here’s where it breaks down.
- Someone has to filter the sheet by executive, then copy the “completed today” list into an email, every day.
- Pending items blend together, so overdue follow-ups don’t stand out until a recruiter escalates.
- Inconsistent date formats quietly ruin sorting and “stale” calculations, which means the report can’t be trusted.
- When managers ask “what’s blocked right now?”, you’re back to screenshots, Slack messages, and manual reconciliation.
The Solution: Daily executive-specific BGV digests from Google Sheets
This workflow turns your existing “BGV Tracker” Google Sheet into nightly, personalized Gmail digests for each executive. It runs on a schedule (23:00 IST, weekends off by default), pulls all sheet rows, then cleans up the data so it behaves consistently. That includes normalizing column names and parsing last_follow_up and bgv_completion_date even when people used different local date formats. Next, it groups candidates by the executive’s email, splits each group into “Completed Today” and “Pending,” and flags stale pending items where the last follow-up is older than about 3 days. Finally, it generates a clean HTML email with two tables and sends it via Gmail to the right owner.
The workflow starts with a nightly schedule trigger and a Google Sheets pull. Then code nodes do the heavy lifting: normalize, group, filter, and format the digest. Gmail sends the final email so each executive wakes up to a clear, scoped list.
What You Get: Automation vs. Results
| What This Workflow Automates | Results You’ll Get |
|---|---|
|
|
Example: What This Looks Like
Say you have 6 BGV executives and you send each of them a daily status email. Manually, even a “quick” routine is about 10 minutes to filter the sheet, scan for today’s completions, spot overdue follow-ups, and format something readable, so that’s roughly 60 minutes every day. With this workflow, it’s basically zero daily effort: the trigger runs automatically at 23:00 IST, processing takes a few minutes, and Gmail sends the digests without you touching the sheet. You get an hour back, and the stale flags show up consistently.
What You’ll Need
- n8n instance (try n8n Cloud free)
- Self-hosting option if you prefer (Hostinger works well)
- Google Sheets to store the “BGV Tracker” rows
- Gmail to send executive-specific digest emails
- Google + Gmail credentials (set up in n8n OAuth/App Password)
Skill level: Intermediate. You’ll connect accounts, map the right sheet/tab, and tweak a couple of code-node settings like stale thresholds or email formatting.
Don’t want to set this up yourself? Talk to an automation expert (free 15-minute consultation).
How It Works
A nightly schedule kicks things off. The workflow runs at 23:00 IST (Asia/Kolkata), with weekends disabled by default so you don’t spam inboxes when nothing is changing.
Your Google Sheet becomes the source of truth. n8n reads all rows from the “BGV Tracker” tab, pulling the key columns like candidate name, BGV status, last follow-up date, completion date, and the executive’s email.
The workflow cleans and organizes your records. It standardizes column names, parses dates from common local formats, flags “completed today,” and marks pending items as stale when the last follow-up is older than about 3 days.
Each executive receives their own digest. The workflow groups rows by bgv_exe_email, generates a simple HTML email with two tables (Completed Today and Pending), and sends it via Gmail with a subject line that includes summary counts.
You can easily modify the stale threshold to match your team’s follow-up policy based on your needs. See the full implementation guide below for customization options.
Step-by-Step Implementation Guide
Step 1: Configure the Scheduled Trigger
Set the workflow schedule so the digest runs automatically.
- Add and open Scheduled Automation Start.
- In Rule, configure your desired interval (the current setup uses an empty interval placeholder under Interval).
- Connect Scheduled Automation Start to Retrieve Sheet Records.
Tip: Set a consistent time for daily digest delivery so the IST date calculations align with expected reporting.
Step 2: Connect Google Sheets
Pull raw verification rows from the tracker spreadsheet.
- Add and open Retrieve Sheet Records.
- Credential Required: Connect your googleSheetsOAuth2Api credentials.
- Set Document to the spreadsheet with value
[YOUR_ID]. - Set Sheet Name to
BGV Tracker(value[YOUR_ID]in the list selector). - Keep Options as
{}unless you need filtering or limits.
⚠️ Common Pitfall: If the sheet selection shows [YOUR_ID], replace it with your actual spreadsheet ID or select the sheet from the list before testing.
Step 3: Set Up Processing Nodes
Normalize date fields, compute stale flags, and group rows by executive for email composition.
- Add Standardize Date Parsing and keep the provided JavaScript Code intact to handle IST dates and stale logic (stale threshold is set to
3days). - Connect Retrieve Sheet Records → Standardize Date Parsing.
- Add Organize Records and keep its JavaScript Code to group by
bgv_exe_emailand split intocompletedTodayandpending. - Connect Standardize Date Parsing → Organize Records.
Tip: Ensure your sheet columns include bgv_completion_date, last_follow_up, bgv_status, and bgv_exe_email so the code can calculate completion and stale flags correctly.
Step 4: Configure Output Email Composition and Delivery
Build HTML digest emails and send them to each executive.
- Add Compose Digest Email and keep the JavaScript Code that builds the HTML digest and subject line.
- Connect Organize Records → Compose Digest Email.
- Add Dispatch Gmail Message.
- Credential Required: Connect your gmailOAuth2 credentials.
- Set Send To to
={{ $json.to }}. - Set Subject to
={{ $json.subject }}. - Set Message to
={{ $json.html }}. - Connect Compose Digest Email → Dispatch Gmail Message.
⚠️ Common Pitfall: If Gmail sends plain text, verify that Message is mapped to the HTML content and that your Gmail account allows programmatic sends.
Step 5: Test and Activate Your Workflow
Run a manual test to verify the digest format and email routing, then activate for scheduled delivery.
- Click Execute Workflow to run the flow from Scheduled Automation Start.
- Confirm Retrieve Sheet Records returns rows with the required fields.
- Check Compose Digest Email output to verify
to,subject, andhtmlare populated. - Verify that Dispatch Gmail Message sends a digest to each executive address.
- Toggle the workflow to Active to enable scheduled delivery.
Common Gotchas
- Google Sheets credentials can expire or lose access to the file. If things break, check the Google Sheets node credential status and the sheet sharing permissions first.
- If you’re using Wait nodes or external rendering, processing times vary. Bump up the wait duration if downstream nodes fail on empty responses.
- Default prompts in AI nodes are generic. Add your brand voice early or you’ll be editing outputs forever.
Frequently Asked Questions
About an hour if your Google accounts are ready.
No. You’ll mostly connect Google Sheets and Gmail, then adjust a few variables like sheet ID and the stale threshold.
Yes. n8n has a free self-hosted option and a free trial on n8n Cloud. Cloud plans start at $20/month for higher volume. You’ll also need to factor in any Google Workspace costs your org already pays (Gmail and Sheets themselves don’t add per-email API fees).
Two options: n8n Cloud (managed, easiest setup) or self-hosting on a VPS. For self-hosting, Hostinger VPS is affordable and handles n8n well. Self-hosting gives you unlimited executions but requires basic server management.
Yes, and it’s a common tweak. You can add a simple CC field in the Gmail send step, or create an additional “manager summary” email after the workflow groups records by executive. Many teams also customize the HTML in the “Compose Digest Email” code node to add a header, escalation notes, or a link back to the exact Google Sheet view.
Usually it’s expired credentials or Google blocking sign-in because the account needs an app password. Update the Gmail credentials in n8n, confirm the sender has permission to send mail, and re-test with a single executive email first. If you’re sending to many recipients at once, you might also be hitting Gmail daily limits, which can look like intermittent failures.
For most teams, hundreds to a few thousand rows in the tracker is fine.
Often, yes, frankly, because this use case isn’t just “move data from A to B.” You’re normalizing messy dates, grouping by executive, splitting into two categorized lists, and generating a formatted HTML email with conditional stale flags. n8n handles that kind of logic cleanly, and you can self-host it if you want unlimited runs. Zapier or Make can still work, but you’ll usually stitch together more steps (and pay more as it scales). Talk to an automation expert if you want a quick recommendation for your specific volume and security requirements.
Once this is live, your sheet stops being a passive tracker and starts pushing the right update to the right person. That’s less chasing, fewer stale cases, and a calmer end to the day.
Need Help Setting This Up?
Our automation experts can build and customize this workflow for your specific needs. Free 15-minute consultation—no commitment required.
