n8n + Google Sheets + WhatsApp: An EOD Field-Sales Roll-Up for a 40-Rep FMCG Distributor

A Nashik-based FMCG distributor runs 40 field sales reps across 3 states. Every evening at 6 pm the regional sales head used to chase reps over WhatsApp for their daily order sheets, then spend 90 minutes consolidating them into a master sheet for the founder's morning review. We replaced both with a 16-node n8n workflow that pulls each rep's daily Google Sheet at 19:30 IST, handles missing entries and offline-submitted forms, computes 9 numbers per rep + 5 territory totals, and pushes a single WhatsApp summary to the sales head and founder. Cost: ₹680/month all-in. Time saved at the sales head's desk: 12 hours per week. ## TL;DR — what this workflow actually does Each of the 40 reps fills a Google Form on their phone at the end of every visit. The form writes to a shared Google Sheet with one row per visit. At 19:30 IST a cron-triggered n8n workflow pulls the day's rows, normalises rep names (Hindi/Marathi/English variants), fills missing entries (no rows = "no submission today"), aggregates 9 numbers per rep, rolls up by territory, and posts a structured WhatsApp summary to the sales head's number plus the founder. Failed parses get logged for the next morning's call. ## Why this matters now — September 2025 Three changes made this practical at this volume. WhatsApp Cloud API utility-template pricing dropped to ₹0.125 per delivered message in India (effective 1 July 2025). Sending a single 120-character roll-up to two numbers daily costs ₹0.25/day, ₹7.50/month. n8n v1.x stabilised the Google Sheets node's range-reference handling in the August-September releases — earlier versions would silently truncate sheets with more than 1,000 rows, which was killing larger FMCG flows. And the 4G coverage map for Tier-2 Maharashtra (where this client's reps work) finally crossed 96% of villages — meaning offline-submitted forms now sync reliably within 2 hours of the rep getting back to a town. A recent r/n8n thread from a Lucknow distributor running a similar 28-rep flow shows the same architecture — Forms → Sheets → n8n → WhatsApp — and the same edge cases (rep name typos, missing days, partial submissions). ## The 4-block architecture ## The 3 critical n8n nodes ### Node 1 — Google Sheets read with date filter

{
    "parameters": {
      "authentication": "serviceAccount",
      "operation": "read",
      "documentId": {
        "__rl": true,
        "mode": "id",
        "value": "1AbC_REDACTED_SHEET_ID"
      },
      "sheetName": {
        "__rl": true,
        "mode": "name",
        "value": "Daily_Visits"
      },
      "options": {
        "dataLocation": "valueRender",
        "valueRenderMode": "FORMATTED_VALUE",
        "rangeDefinition": "specifyRange",
        "range": "A2:N5000"
      }
    },
    "name": "Sheets all-rows",
    "type": "n8n-nodes-base.googleSheets",
    "typeVersion": 4.5,
    "position": [460, 300]
  }

We read the entire sheet (range A2:N5000) and filter in the next Code node, not in the Sheets node. Two reasons. First, Sheets API filters do not support multi-condition predicates well. Second, we want to detect missing reps — a rep with zero rows for today should still appear in the output as "no submission". Filtering at the Sheets level would simply exclude them, which is the bug we are trying to surface. ### Node 2 — Data cleaning + imputation (Code node)

{
    "parameters": {
      "jsCode": "const rows = $input.all().map(i => i.json);\nconst today = new Date().toISOString().slice(0,10);\n\n// Canonical rep registry — name variants mapped to ID\nconst REPS = {\n  'R001': ['Suresh K', 'Suresh Kulkarni', 'सुरेश कुलकर्णी', 'Suresh K.'],\n  'R002': ['Anita J', 'Anita Joshi', 'अनिता जोशी'],\n  'R003': ['Mahesh P', 'Mahesh Patil', 'महेश पाटील'],\n  // ... 37 more\n};\nconst nameToId = {};\nfor (const [id, names] of Object.entries(REPS)) {\n  for (const n of names) nameToId[n.trim().toLowerCase()] = id;\n}\n\n// Filter today's rows + map to canonical rep ID\nconst todays = rows\n  .filter(r => r.date && r.date.startsWith(today))\n  .map(r => ({ ...r, repId: nameToId[(r.rep_name || '').trim().toLowerCase()] || 'UNKNOWN' }));\n\n// Group by repId\nconst byRep = {};\nfor (const r of todays) {\n  if (!byRep[r.repId]) byRep[r.repId] = [];\n  byRep[r.repId].push(r);\n}\n\n// Build output for ALL 40 reps (missing reps get zero-rows entry)\nconst out = [];\nfor (const id of Object.keys(REPS)) {\n  const visits = byRep[id] || [];\n  out.push({\n    json: {\n      repId: id,\n      repName: REPS[id][0],\n      visits: visits.length,\n      orders: visits.filter(v => Number(v.order_value || 0) > 0).length,\n      grossRupees: visits.reduce((s,v) => s + Number(v.order_value || 0), 0),\n      newOutlets: visits.filter(v => v.is_new === 'Yes').length,\n      missing: visits.length === 0\n    }\n  });\n}\nreturn out;"
    },
    "name": "Normalise + impute",
    "type": "n8n-nodes-base.code",
    "typeVersion": 2,
    "position": [680, 300]
  }

The rep registry is the most important piece. Field reps write their name differently every time — "Suresh K", "Suresh Kulkarni", "S. Kulkarni", and the Marathi spelling. Without canonical IDs, you end up with 73 reps in your output instead of 40. The lowercase + trim + lookup pattern handles 95% of the variants we have seen. The remaining 5% (genuinely new typos) fall to "UNKNOWN" and get flagged in the morning call. We refresh the registry whenever a rep flags a new spelling. ### Node 3 — WhatsApp Cloud API send (Meta Graph API)

{
    "parameters": {
      "method": "POST",
      "url": "=https://graph.facebook.com/v20.0/{{ $credentials.metaWhatsApp.phoneNumberId }}/messages",
      "sendHeaders": true,
      "headerParameters": {
        "parameters": [
          { "name": "Authorization", "value": "=Bearer {{ $credentials.metaWhatsApp.accessToken }}" },
          { "name": "Content-Type", "value": "application/json" }
        ]
      },
      "sendBody": true,
      "specifyBody": "json",
      "jsonBody": "={\n  \"messaging_product\": \"whatsapp\",\n  \"to\": \"{{ $json.recipient_msisdn }}\",\n  \"type\": \"template\",\n  \"template\": {\n    \"name\": \"eod_sales_rollup\",\n    \"language\": { \"code\": \"en\" },\n    \"components\": [\n      { \"type\": \"body\", \"parameters\": [\n        { \"type\": \"text\", \"text\": \"{{ $json.recipient_name }}\" },\n        { \"type\": \"text\", \"text\": \"{{ $json.gross_today }}\" },\n        { \"type\": \"text\", \"text\": \"{{ $json.active_reps }}/40\" },\n        { \"type\": \"text\", \"text\": \"{{ $json.missing_reps }}\" },\n        { \"type\": \"text\", \"text\": \"{{ $json.top_brand }}\" },\n        { \"type\": \"text\", \"text\": \"{{ $json.new_outlets }}\" }\n      ]}\n    ]\n  }\n}"
    },
    "name": "WhatsApp send",
    "type": "n8n-nodes-base.httpRequest",
    "typeVersion": 4.2,
    "position": [1100, 300]
  }

Two notes. The template eod_sales_rollup must be pre-approved in Meta Business Manager under category Utility (not Marketing) for the cheaper ₹0.125/message tier. The recipient MSISDN is in international format with no plus — e.g., 919876543210, not +91 98765 43210. We learned this on a Tuesday at 19:32 when zero messages went through and the Meta error code was 131005 ("invalid recipient"). ## The 16-node graph — full list

• 1. Schedule Trigger (cron 30 19 * * * Asia/Kolkata)
• 2. Sheets read — full Daily_Visits sheet
• 3. Code — normalise rep names + filter today + impute zero-row reps
• 4. IF — split into "submitted" and "missing" branches
• 5. Aggregate — by territory (5 groups)
• 6. Aggregate — by brand (top 3)
• 7. Set — format gross with Indian comma format (₹1,28,400)
• 8. Code — build WhatsApp template variables for each recipient
• 9. Split In Batches (size 1, sequential — Meta rate-limits per second)
• 10. HTTP — WhatsApp Cloud API send
• 11. Code — parse Meta response, extract message_id
• 12. Sheets append — deliveries log row per recipient
• 13. IF — branch on Meta error codes (131047, 131005, 132015)
• 14. Sheets append — error log
• 15. Wait — 1 hour (lets late offline submissions sync)
• 16. Sub-flow — late-arrival re-roll-up to a private channel

## The offline-submission edge case Field reps in Maharashtra's interior lose 4G regularly. Google Forms caches submissions on the device and syncs when connectivity returns — sometimes 1-3 hours after the form was submitted. If our flow runs at 19:30 sharp, late-syncing rows are missed. ## The cost — actual numbers from 8 weeks live The Hetzner box is dedicated to this client because they wanted their data fully isolated for compliance with their FMCG principal's contract. Smaller clients share a CX22 four ways. Meta API costs are negligible — 60 utility template messages a month at ₹0.125 each is ₹7.50. ## The pre-flight checklist

• Google service-account JSON pasted into n8n; sheet shared with bot email
• Rep registry CSV reviewed by sales head, all 40 names + Hindi/Marathi variants confirmed
• Meta Business Manager: utility template approved (category Utility, language en)
• Recipient MSISDNs in international format with no plus (919876543210)
• Cron timezone set to Asia/Kolkata explicitly
• Code node returns valid output for an empty day (e.g., a public holiday)
• Late-arrival sub-flow scheduled at 20:30 and 21:30 IST
• Error log sheet has columns: timestamp, recipient, error_code, message_id, raw_response
• Founder briefed: morning message at 09:00 is the canonical version, evening DM is operational
• Workflow exported to git as JSON the day of go-live

## Common mistakes — symptoms first Symptom: "Sales head sees 73 reps in the output instead of 40." Cause: rep-name normalisation registry is incomplete; new typos went to "UNKNOWN". Fix: the Code node groups UNKNOWN entries into one bucket and DMs the sales head a list of unrecognised names. Add the new variants to the registry. Symptom: "WhatsApp returns error 131047." Cause: 24-hour customer-service window expired (recipient has not messaged the business number in 24+ hours). Fix: use a template message (which we do — utility template bypasses the 24-hour rule). If you tried a freeform message, switch to a template. Symptom: "Numbers do not match what reps remember submitting." Cause: late offline submissions arrived after the 19:30 run. Fix: the 20:30/21:30 reconciliation sub-flow catches this. Sales head learns to wait for the morning canonical message before challenging numbers. Symptom: "Founder gets the same message twice." Cause: workflow was triggered manually during testing and the schedule fired it again. Fix: add a deduplication key based on date + recipient — Sheets append step checks if a row exists for today, skips if yes. Symptom: "Marathi rep names do not match anything." Cause: the rep wrote in Devanagari but registry only has Latin variants. Fix: each registry entry has both. We use trim + lowercase before lookup; Devanagari is case-insensitive but trim still matters. ## Mini case study — 8 weeks at the Nashik distributor The first install ran on 14 July 2025. After 8 weeks: 11,000+ visit rows processed, 56 evening roll-ups sent, 3 days where the system flagged a missing rep correctly (rep had a family emergency, sales head followed up), 1 day where the WhatsApp send failed (Meta API outage, all 2 messages bounced; we resent at 21:00). The sales head's evening consolidation work — previously 90 minutes per day, 12 hours per week — is now zero. Saved time goes into rep coaching calls during the day. For the smaller-team variant of this pattern, see our Saturday weekend roll-up from November 2025 (Indore retail, 4 founders). The architecture is the same; the data volume and number of recipients are different. ## When NOT to build this Skip this if (a) your reps are not yet using a structured form (Google Form or otherwise) — you cannot consolidate what is not in a sheet, build the data hygiene first, (b) you have under 8 reps — a manual evening WhatsApp scan by the sales head is genuinely faster, or (c) your sales head treats the 90-minute evening consolidation as their thinking time. We turned down one client in 2025 for reason (c). Automation does not fix a process that exists for cognitive reasons. For a similar field-team pattern in a different vertical, see the Radiant Finance case study — same n8n + WhatsApp stack, different data shape. As Hrishikesh, our CTO, points out, the hard part is rarely the n8n flow — it is the data hygiene that has to exist before any of this works. ## FAQ ### How long to build and deploy this n8n + Sheets + WhatsApp roll-up? For us, 9 working days end-to-end: 1 day on the rep registry (sales head joins for 2 hours), 2 days on the n8n flow, 2 days on Meta template approval, 2 days on testing across edge cases (offline submissions, missing reps, Marathi names), 2 days on the late-arrival sub-flow. ### What if a rep is on leave for a week? The "missing" status in the output is just a marker, not an alarm. We added a leave sheet that the sales head updates — reps marked as on-leave do not appear in the missing list. Took 30 minutes to add. Saves daily noise. ### Can the sales head edit the rep registry without redeploying? Yes. We moved the registry from the Code node into a separate Google Sheet. The Code node reads the registry sheet at the start of each run. Sales head edits the sheet, no redeploy needed. ### How do I avoid spamming reps' WhatsApp during testing? Meta provides a test phone number for every WhatsApp Cloud API setup. Point the workflow at it for the first 5 runs. The test number renders templates in the developer console and never delivers to real numbers. ### Why utility template instead of marketing? Utility templates cost ₹0.125/message in India, marketing templates cost ₹0.78. Internal operational messages (like a roll-up to the sales head) qualify as Utility. Marketing templates are for promotional content sent to customers. ### What is the right way to handle public holidays? The flow runs every evening regardless. On a holiday, all reps will show "missing" — the message goes out as "39 missing of 40" and the founder reads it as "everyone took the holiday". We considered adding a holiday-skip but the false-positive cost of skipping a real low-activity day was higher. ### Can this scale to 200 reps? Yes, with two changes. The Code node's rep registry moves into a database (Postgres or Sheets is fine up to 500 reps, beyond that use Postgres). The WhatsApp send loop becomes Split In Batches with size 5 to respect Meta's per-second limits. We have not run this at 200 reps in production but the architecture is the same. For the broader pattern of replacing a daily ops meeting with a cron + WhatsApp loop, see our AI automation services page.