January 22, 2026

Google Sheets + Gmail, form tracking you can trust

Lisa Granqvist Partner Workflow Automation Expert

Get a free AI assessment → ⬇️ Use template

Manually checking Google Form responses against a roster sounds simple, until you’re doing it every day and still missing people. Someone swears they submitted, your sheet says they didn’t, and now you’re stuck doing detective work instead of follow-up.

This Sheets Gmail automation hits department coordinators hardest, but program managers and small teams running workshops feel it too. You get a live view of who has submitted, who hasn’t, and you can send the right email in minutes, not hours.

You’ll set up an n8n workflow suite that reconciles your roster with responses, shows a simple dashboard, and triggers acknowledgement or reminder emails from Gmail.

How This Automation Works

Here’s the complete workflow you’ll be setting up:

n8n Workflow Template: Google Sheets + Gmail, form tracking you can trust

Click to explore

flowchart LR

    subgraph sg0["Flow 1"]
        direction LR
        n6["<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/webhook.dark.svg' width='40' height='40' /></div><br/>Webhook - send-acknowledgeme.."]
        n7@{ icon: "mdi:database", form: "rounded", label: "Google Sheets - Student List", pos: "b", h: 48 }
        n8["<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/>Code - Prepare Messages"]
        n9["<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/webhook.dark.svg' width='40' height='40' /></div><br/>Respond - Confirmation"]
        n10@{ icon: "mdi:message-outline", form: "rounded", label: "Send a message", pos: "b", h: 48 }
        n11@{ icon: "mdi:database", form: "rounded", label: "Google Sheets FormResponses", pos: "b", h: 48 }
        n12["<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/merge.svg' width='40' height='40' /></div><br/>Merge1"]
        n12 --> n8
        n10 --> n9
        n8 --> n10
        n11 --> n12
        n7 --> n12
        n6 --> n7
        n6 --> n11
    end

    subgraph sg1["Flow 2"]
        direction LR
        n13@{ icon: "mdi:database", form: "rounded", label: "Google Sheets - FormResponses", pos: "b", h: 48 }
        n14["<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/webhook.dark.svg' width='40' height='40' /></div><br/>Webhook - send-acknowledgeme.."]
        n15@{ icon: "mdi:database", form: "rounded", label: "Google Sheets - Student List1", pos: "b", h: 48 }
        n16["<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/>Code - Prepare Messages1"]
        n17["<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/webhook.dark.svg' width='40' height='40' /></div><br/>Respond - Confirmation1"]
        n18@{ icon: "mdi:message-outline", form: "rounded", label: "Send a message1", pos: "b", h: 48 }
        n19["<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/merge.svg' width='40' height='40' /></div><br/>Merge2"]
        n19 --> n16
        n18 --> n17
        n16 --> n18
        n13 --> n19
        n15 --> n19
        n14 --> n15
        n14 --> n13
    end

    subgraph sg2["Flow 3"]
        direction LR
        n0@{ icon: "mdi:database", form: "rounded", label: "Google Sheets", pos: "b", h: 48 }
        n1@{ icon: "mdi:database", form: "rounded", label: "Google Sheets1", pos: "b", h: 48 }
        n2["<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/>Code"]
        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/merge.svg' width='40' height='40' /></div><br/>Merge"]
        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/webhook.dark.svg' width='40' height='40' /></div><br/>Respond to Webhook"]
        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/webhook.dark.svg' width='40' height='40' /></div><br/>Webhook1"]
        n2 --> n4
        n3 --> n2
        n5 --> n0
        n5 --> n1
        n0 --> n3
        n1 --> n3
    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 n7,n11,n13,n15,n0,n1 database
    class n6,n9,n14,n17,n4,n5 api
    class n8,n16,n2 code
    classDef customIcon fill:none,stroke:none
    class n6,n8,n9,n12,n14,n16,n17,n19,n2,n3,n4,n5 customIcon

Why This Matters: Roster vs. form mismatches

Tracking submissions is annoying because the “truth” lives in two places: your roster sheet and your form response sheet. You end up filtering, sorting, searching for register numbers, and copy-pasting names into email drafts. It’s not just time. It’s the mental load of remembering who you already nudged, plus the awkwardness of reminding someone who actually did submit (or forgetting someone who didn’t). After a few rounds, the spreadsheet starts to feel like a crime scene.

It adds up fast. Here’s where it breaks down in real life.

Every manual cross-check turns into 30–60 minutes of filtering and “did I already email them?” second-guessing.
Roster data and form data rarely match perfectly, so one typo in a register number can hide a submission.
Acknowledgements get skipped when things get busy, which means more “I submitted, did you get it?” follow-ups later.
Reminders go out late or not at all, and then you’re chasing responses at the last minute.

What You’ll Build: A live tracking dashboard with one-click emails

This workflow suite turns your Google Sheets into a real-time registration tracker that stays current without babysitting. It starts with a webhook-based dashboard in n8n: you open a simple URL, and n8n pulls your student roster from one sheet and your Google Form responses from another. A small code step compares them using a shared identifier (like Register No.), then generates an HTML dashboard that lists totals, submitted, and pending. From that same dashboard, you can trigger two companion webhooks: one sends a thank-you email to people who completed the form, and the other emails a reminder to the ones still pending. The result is clean tracking plus fast follow-up, with far fewer mistakes.

The workflow begins when you load the live tracking page or press a dashboard button. n8n matches roster rows to form response rows, then prepares the right email content and sends it through Gmail. Finally, it returns a confirmation page so you know the send actually happened.

What You’re Building

What Gets Automated

What You’ll Achieve

Pull the latest roster and the latest form responses from Google Sheets automatically.
Match students to responses using Register No. (or another shared field).
Generate a simple HTML dashboard that shows totals, submitted, and pending.
Send acknowledgement or reminder emails from Gmail using webhook buttons.

Turn a weekly “check and chase” session of about 2 hours into a few minutes.
See pending vs. submitted at a glance, without sorting sheets.
Send consistent emails with the right name and register number filled in.
Cut down on accidental duplicate reminders and missed acknowledgements.
Keep a single, defensible status view when people ask “did you get mine?”

Expected Results

Say you’re tracking 120 students. Manually, you might spend about 45 minutes exporting or opening responses, another 45 minutes matching register numbers, then 30 minutes writing and sending emails, so roughly 2 hours per follow-up round. With this workflow, you open the dashboard URL (about a minute), click “Send Acknowledgements” or “Send Reminders,” and wait a few minutes for Gmail to send. You still keep control, but the grind disappears.

Before You Start

n8n instance (try n8n Cloud free)
Self-hosting option if you prefer (Hostinger works well)
Google Sheets to store roster and responses.
Gmail to send acknowledgements and reminders.
Google OAuth credentials (set up in Google Cloud Console for Sheets/Gmail access).

Skill level: Beginner. You’ll connect Google accounts, confirm sheet columns, and paste in webhook URLs.

Want someone to build this for you? Talk to an automation expert (free 15-minute consultation).

Step by Step

A dashboard page gets opened. The “Live Tracking” workflow starts from an n8n webhook endpoint (like /live-tracking), which means you can bookmark it and treat it like an internal tool.

Your two Sheets are pulled in. n8n reads the student roster sheet (Register No., name, email) and reads the form response sheet (timestamp, Reg No., email address). These come in as two data streams.

The roster is reconciled against responses. A merge step aligns the two sets, and a small code step compares keys (usually Register No.) to figure out who is “submitted” and who is “pending.” It then builds an HTML dashboard showing summary stats and two action buttons.

Emails are sent on demand. When you click “Send Acknowledgements” or “Send Reminders,” a companion webhook runs, merges roster + responses again (so it’s always current), generates personalized email copy, sends via Gmail, then returns a confirmation page.

You can easily modify the matching field (Register No., email, or a custom ID) to fit your process. See the full implementation guide below for customization options.

Step-by-Step Implementation Guide

Step 1: Configure the Webhook Trigger

Set up the three incoming webhooks that drive the dashboard view and the email actions.

Open Incoming Dashboard Hook and set Path to 2235781f-4371-4f6e-8767-41c352ed171f, with Response Mode set to responseNode.
Open Incoming Acknowledgement Hook and set Path to send-acknowledgements, with Response Mode set to responseNode.
Open Incoming Reminder Hook and set Path to send-reminder, with Response Mode set to responseNode.
Confirm the parallel execution: Incoming Dashboard Hook outputs to both Fetch Spreadsheet Data and Retrieve Form Responses in parallel.
Confirm the parallel execution: Incoming Acknowledgement Hook outputs to both Fetch Student Roster and Fetch Response Records in parallel.
Confirm the parallel execution: Incoming Reminder Hook outputs to both Retrieve Roster Records and Fetch Reminder Responses in parallel.

Use unique paths in production and keep the dashboard path private since it can trigger bulk messaging.

Step 2: Connect Google Sheets

Attach Google Sheets data sources for student rosters and form responses across the three flows.

Open Fetch Spreadsheet Data and select the document 1-i55cyu2DKZ_alSlR3Bd7abHn_EfvTpg73hv2y3zb4o and sheet gid=0. Credential Required: Connect your googleSheetsOAuth2Api credentials.
Open Retrieve Form Responses and select the document 10eIZzrzgRy8PKfyhhVc8eG8lFoAxEqE0h-IOnqHwdVE and sheet 1113734691. Credential Required: Connect your googleSheetsOAuth2Api credentials.
Open Fetch Student Roster and select the document 1-i55cyu2DKZ_alSlR3Bd7abHn_EfvTpg73hv2y3zb4o and sheet gid=0. Credential Required: Connect your googleSheetsOAuth2Api credentials.
Open Fetch Response Records and select the document 10eIZzrzgRy8PKfyhhVc8eG8lFoAxEqE0h-IOnqHwdVE and sheet 1113734691. Credential Required: Connect your googleSheetsOAuth2Api credentials.
Open Retrieve Roster Records and select the document 1-i55cyu2DKZ_alSlR3Bd7abHn_EfvTpg73hv2y3zb4o and sheet gid=0. Credential Required: Connect your googleSheetsOAuth2Api credentials.
Open Fetch Reminder Responses and select the document 10eIZzrzgRy8PKfyhhVc8eG8lFoAxEqE0h-IOnqHwdVE and sheet 1113734691. Credential Required: Connect your googleSheetsOAuth2Api credentials.

⚠️ Common Pitfall: Ensure column headers match exactly (e.g., Register No. and Reg No) to avoid mismatches in merge logic.

Step 3: Set Up Dashboard Processing

Merge roster and response data, then generate the HTML dashboard returned to the browser.

Open Combine Register Streams and set Mode to combine with Advanced enabled.
In Combine Register Streams, set Merge By Fields to map Register No. (field1) with Reg No (field2).
Open Build Tracking Dashboard and review the JavaScript that compiles summary stats and the HTML dashboard output.
Open Return Webhook Output and set Respond With to text and Response Body to =={{ $json["html"] }}.

Replace the placeholder https://[YOUR_WEBHOOK_URL] inside Build Tracking Dashboard with your actual webhook base URL for the dashboard buttons.

Step 4: Configure Acknowledgement Email Flow

Join roster and response data, build personalized thank-you emails, send them via Gmail, and return an acknowledgement page.

Confirm Merge Email Sources receives input from both Fetch Student Roster and Fetch Response Records in parallel.
Open Compose Thank You Emails and verify the subject is ✅ Thank You for Your Submission and the body template uses {{name}} and {{reg}} placeholders.
Open Dispatch Email Notices and set Send To to ={{$json["Email Address"]}}, Subject to {{ $json.subject }}, and Message to ={{ $json.htmlBody }}. Credential Required: Connect your gmailOAuth2 credentials.
Open Return Acknowledgement Page and confirm the HTML response body contains your correct dashboard link.

⚠️ Common Pitfall: If Compose Thank You Emails returns “No completed students found,” no emails will be sent—this is expected behavior when there are no matches.

Step 5: Configure Reminder Email Flow

Identify pending students, prepare reminder messages, send them via Gmail, and return the reminder confirmation page.

Confirm Merge Reminder Sources receives input from both Retrieve Roster Records and Fetch Reminder Responses in parallel.
Open Compose Reminder Emails and verify the subject is ⚠️ Reminder: Please Complete Your Form and the template uses {{name}} and {{reg}} placeholders.
Open Send Reminder Emails and set Send To to ={{$json["Email Address"]}}, Subject to ={{ $json.subject }}, and Message to ={{ $json.htmlBody }}. Credential Required: Connect your gmailOAuth2 credentials.
Open Return Reminder Page and confirm the HTML response body contains your correct dashboard link.

⚠️ Common Pitfall: Compose Reminder Emails returns “All students have submitted. No reminders needed.” when there are no pending records—this stops email sending by design.

Step 6: Test and Activate Your Workflow

Validate each webhook path, ensure email delivery, and then activate the workflow for production.

Click Execute Workflow and open the test URL from Incoming Dashboard Hook to confirm the HTML dashboard renders.
Use the dashboard buttons to trigger Incoming Acknowledgement Hook and Incoming Reminder Hook, confirming each returns the correct HTML confirmation page.
Verify that Dispatch Email Notices and Send Reminder Emails send messages to the expected email addresses.
When the test run is successful, toggle the workflow to Active to enable production use.

🔒

Unlock Full Step-by-Step Guide

Get the complete implementation guide + downloadable template

Troubleshooting Tips

Google Sheets credentials can expire or need specific permissions. If things break, check the Google connection inside n8n’s Credentials section first.
If you’re using webhook buttons from the dashboard, make sure your n8n webhook URLs are correct for your environment (test vs. production). A copied “test” URL is a common reason the button works once and then fails later.
Gmail can throttle or reject bursts if you send too many emails too quickly. If you’re emailing a big roster, add a short delay between sends and confirm your Gmail account is allowed to send from that address.

Quick Answers

What’s the setup time for this Sheets Gmail automation?

About 30 minutes if your Sheets columns are already clean.

Is coding required for this Sheets Gmail automation?

No. You’ll paste in a workflow and connect accounts, and the included code nodes are already set up for the matching and email text.

Is n8n free to use for this Sheets Gmail automation workflow?

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 Google Workspace costs if you’re sending from a managed domain.

Where can I host n8n to run this Sheets Gmail automation?

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.

Can I modify this Sheets Gmail automation workflow for different use cases?

Yes, and you should. Most teams swap the “match key” used in the merge and code steps (for example, using email instead of Register No.), then tweak the “Compose Thank You Emails” and “Compose Reminder Emails” text to match their tone. You can also add a log step that writes each send to a “Mail Logs” sheet. If you want WhatsApp instead of email, replace the Gmail send with an HTTP Request to a provider like Twilio.

Why is my Google Sheets connection failing in this workflow?

Usually it’s expired or changed Google OAuth consent. Reconnect the Google Sheets credential in n8n, then confirm the connected Google account still has access to the spreadsheet. Also check that the sheet name and columns haven’t been renamed, because the workflow may be selecting fields that no longer exist. If you’re running this on a shared drive, permissions can be quirky, honestly.

What volume can this Sheets Gmail automation workflow process?

A few hundred rows per run is normal, and Gmail sending limits tend to be the real cap.

Is this Sheets Gmail automation better than using Zapier or Make?

Often, yes, because this setup needs matching logic, “pending vs completed” lists, and an HTML dashboard response, which are awkward in simple two-step tools. n8n handles branching logic without nickel-and-diming you for each path. It also gives you the option to self-host, which matters if you run this daily and don’t want per-task pricing to creep up. Zapier or Make can still work if you only want “new form response → send email,” but that’s not the same as reconciling against a roster. If you’re torn, Talk to an automation expert and we’ll help you pick the simplest option.

Once this is running, your Sheets stay honest and your follow-ups stop slipping through the cracks. Set it up once, then use the time you get back for the work that actually needs you.

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.

Lisa Granqvist

Workflow Automation Expert

Expert in workflow automation and no-code tools.

{
"id": "",
"meta": {
"instanceId": "3e06fa8cd520c40abc91515b4700ef19b648cd36cd9c0f1f9ea4bed207f50366",
"templateId": "",
"templateCredsSetupCompleted": null
},
"name": "Automated Registration Tracking Flow",
"tags": [],
"nodes": [
{
"id": "flowpast-topbar-9580",
"name": "Flowpast Branding",
"type": "n8n-nodes-base.stickyNote",
"position": [
0,
30
],
"parameters": {
"color": 7,
"width": 1370,
"height": 80,
"content": "## Flowpast.com | Automation Workflow Library\n**\ud83d\udcd6 Full tutorial & setup guide:** flowpast.com"
},
"typeVersion": 1
},
{
"id": "6129a28e-055e-4355-8b3d-412cd2b21baf",
"name": "Fetch Spreadsheet Data",
"type": "n8n-nodes-base.googleSheets",
"position": [
230,
130
],
"parameters": {
"options": [],
"sheetName": {
"__rl": true,
"mode": "list",
"value": "gid=0",
"cachedResultUrl": "",
"cachedResultName": "Sheet1"
},
"documentId": {
"__rl": true,
"mode": "list",
"value": "1-i55cyu2DKZ_alSlR3Bd7abHn_EfvTpg73hv2y3zb4o",
"cachedResultUrl": "",
"cachedResultName": "B"
},
"combineFilters": "OR"
},
"credentials": {
"googleSheetsOAuth2Api": {
"id": "credential-id",
"name": ""
}
},
"typeVersion": 4.5
},
{
"id": "939ffb5e-d5ba-4ddd-a34a-b5db3f8be956",
"name": "Retrieve Form Responses",
"type": "n8n-nodes-base.googleSheets",
"position": [
210,
280
],
"parameters": {
"options": [],
"sheetName": {
"__rl": true,
"mode": "list",
"value": 1113734691,
"cachedResultUrl": "",
"cachedResultName": "Form Responses 1"
},
"documentId": {
"__rl": true,
"mode": "list",
"value": "10eIZzrzgRy8PKfyhhVc8eG8lFoAxEqE0h-IOnqHwdVE",
"cachedResultUrl": "",
"cachedResultName": "n8n forms (Responses)"
}
},
"credentials": {
"googleSheetsOAuth2Api": {
"id": "credential-id",
"name": ""
}
},
"typeVersion": 4.5
},
{
"id": "9019ab48-1b0e-497a-bba6-50727a8664c9",
"name": "Build Tracking Dashboard",
"type": "n8n-nodes-base.code",
"position": [
760,
180
],
"parameters": {
"jsCode": "// Step 1 \u2013 Read data from Google Sheets\nconst studentSheet  = $items(\"Fetch Spreadsheet Data\").map(i => i.json);\nconst responseSheet = $items(\"Retrieve Form Responses\").map(i => i.json);\n\n// Step 2 \u2013 Normalize and Compare Register Numbers\nconst submittedRegs = responseSheet.map(r => String(r[\"Reg No\"]).trim().toLowerCase());\nconst completed   = [];\nconst incompleted = [];\n\nfor (const s of studentSheet) {\n  const reg = String(s[\"Register No.\"]).trim().toLowerCase();\n  if (submittedRegs.includes(reg)) {\n    const r = responseSheet.find(x => String(x[\"Reg No\"]).trim().toLowerCase() === reg);\n    completed.push({\n      \"Register No.\": s[\"Register No.\"],\n      \"Student Name\": s[\"Student Name\"],\n      \"Email Address\": r?.[\"Email Address\"] || \"\",\n      \"Timestamp\": r?.[\"Timestamp\"] || \"\",\n      \"Status\": \"\u2705 Completed\"\n    });\n  } else {\n    incompleted.push({\n      \"Register No.\": s[\"Register No.\"],\n      \"Student Name\": s[\"Student Name\"],\n      \"Status\": \"\u26a0\ufe0f Not Submitted\"\n    });\n  }\n}\n\n// Step 3 \u2013 Summary\nconst summary = {\n  total: studentSheet.length,\n  submitted: completed.length,\n  pending: incompleted.length,\n  completion: ((completed.length / Math.max(1, studentSheet.length)) * 100).toFixed(2) + \"%\",\n  updated: new Date().toLocaleString()\n};\n\n// Step 4 \u2013 HTML Dashboard\nconst completedRows = completed\n  .map(s => `<tr><td>${s[\"Register No.\"]}</td><td>${s[\"Student Name\"]}</td><td>\u2705</td></tr>`)\n  .join(\"\");\n\nconst pendingRows = incompleted\n  .map(s => `<tr><td>${s[\"Register No.\"]}</td><td>${s[\"Student Name\"]}</td><td>\u26a0\ufe0f</td></tr>`)\n  .join(\"\");\n\nconst html = `\n<!DOCTYPE html>\n<html lang=\"en\">\n<head>\n<meta charset=\"UTF-8\">\n<title>Form Tracking Dashboard</title>\n<script src=\"https://cdn.jsdelivr.net/npm/sweetalert2@11\"></script>\n<style>\nbody{font-family:'Segoe UI',Arial;background:#f9fafb;text-align:center;padding:30px;}\nh1{color:#2563eb;margin-bottom:10px;}\ntable{border-collapse:collapse;margin:15px auto;width:90%;max-width:900px;background:#fff;\n      box-shadow:0 2px 6px rgba(0,0,0,0.1);}\nth,td{padding:8px 12px;border-bottom:1px solid #eee;text-align:left;}\nth{background:#eff6ff;color:#1e3a8a;}\nbutton{margin:10px 8px;padding:10px 22px;border:none;border-radius:6px;font-size:15px;cursor:pointer;}\n.send{background:#dc2626;color:#fff;}\n.ack{background:#16a34a;color:#fff;}\nfooter{margin-top:25px;color:#666;font-size:14px;}\n</style>\n<link rel="preload" as="image" href="https://flowpast.s3.eu-north-1.amazonaws.com/featured_blog_images/106180.webp"><link rel="preload" as="image" href="https://flowpast.s3.eu-north-1.amazonaws.com/featured_blog_images/106180.webp"></head>
\n<body>\n\n<h1>\ud83d\udcca Form Tracking Dashboard</h1>\n<p>Total <strong>${summary.total}</strong> | Submitted <strong>${summary.submitted}</strong> | Pending <strong>${summary.pending}</strong> | Completion <strong>${summary.completion}</strong></p>\n\n<button class=\"ack\"  onclick=\"acknowledge()\">\u2705 Send Acknowledgements</button>\n<button class=\"send\" onclick=\"remind()\">\u26a0\ufe0f Send Reminders</button>\n\n<section>\n  <h2 style=\"color:#16a34a;\">\u2705 Completed (${completed.length})</h2>\n  <table><tr><th>Reg No</th><th>Student Name</th><th>Status</th></tr>${completedRows}</table>\n</section>\n\n<section>\n  <h2 style=\"color:#dc2626;\">\u26a0\ufe0f Pending (${incompleted.length})</h2>\n  <table><tr><th>Reg No</th><th>Student Name</th><th>Status</th></tr>${pendingRows}</table>\n</section>\n\n<footer>\ud83d\udd52 Last Updated: ${summary.updated}</footer>\n\n<script>\nfunction remind(){\n  Swal.fire({\n    title:'Send Reminders?',\n    text:'${incompleted.length} pending students will receive reminder emails or WhatsApp messages.',\n    icon:'question',showCancelButton:true,\n    confirmButtonText:'Yes, Send'\n  }).then(r=>{\n    if(r.isConfirmed){\n      window.location.href='https://[YOUR_WEBHOOK_URL]/send-reminder';\n    }\n  });\n}\nfunction acknowledge(){\n  Swal.fire({\n    title:'Send Acknowledgements?',\n    text:'${completed.length} students will receive thank-you messages.',\n    icon:'question',showCancelButton:true,\n    confirmButtonText:'Yes, Send'\n  }).then(r=>{\n    if(r.isConfirmed){\n      window.location.href='https://[YOUR_WEBHOOK_URL]/send-acknowledgements';\n    }\n  });\n}\n</script>\n\n</body>\n</html>\n`;\n\n// Step 5 \u2013 Return\nreturn [{\n  json: { summary, completed, incompleted, html }\n}];\n"
},
"typeVersion": 2
},
{
"id": "751e83f2-92e9-4ca2-80fe-51926866f07e",
"name": "Combine Register Streams",
"type": "n8n-nodes-base.merge",
"position": [
500,
210
],
"parameters": {
"mode": "combine",
"options": [],
"advanced": true,
"mergeByFields": {
"values": [
{
"field1": "Register No.",
"field2": "Reg No"
}
]
}
},
"typeVersion": 3.2
},
{
"id": "ab22e315-f457-4c6a-b143-3e5795b25275",
"name": "Return Webhook Output",
"type": "n8n-nodes-base.respondToWebhook",
"position": [
1020,
190
],
"parameters": {
"options": {
"responseCode": 200,
"responseHeaders": {
"entries": [
{
"name": "Content-Type",
"value": "text/html"
}
]
}
},
"respondWith": "text",
"responseBody": "=={{ $json[\"html\"] }}"
},
"typeVersion": 1.4
},
{
"id": "1d83a5bf-9c03-4c8b-8478-1ae605e24e3d",
"name": "Incoming Dashboard Hook",
"type": "n8n-nodes-base.webhook",
"position": [
0,
170
],
"webhookId": "2235781f-4371-4f6e-8767-41c352ed171f",
"parameters": {
"path": "2235781f-4371-4f6e-8767-41c352ed171f",
"options": [],
"responseMode": "responseNode"
},
"typeVersion": 2.1
},
{
"id": "54808873-11d8-44fa-8429-42cabbfa2b5c",
"name": "Incoming Acknowledgement Hook",
"type": "n8n-nodes-base.webhook",
"position": [
40,
430
],
"webhookId": "c13eb3e8-733f-4011-93a3-f0dd20bf4703",
"parameters": {
"path": "send-acknowledgements",
"options": [],
"responseMode": "responseNode"
},
"typeVersion": 2.1
},
{
"id": "b8895288-5239-43f4-95b3-48d208bab533",
"name": "Fetch Student Roster",
"type": "n8n-nodes-base.googleSheets",
"position": [
290,
390
],
"parameters": {
"options": [],
"sheetName": {
"__rl": true,
"mode": "list",
"value": "gid=0",
"cachedResultUrl": "",
"cachedResultName": "Sheet1"
},
"documentId": {
"__rl": true,
"mode": "list",
"value": "1-i55cyu2DKZ_alSlR3Bd7abHn_EfvTpg73hv2y3zb4o",
"cachedResultUrl": "",
"cachedResultName": "B"
}
},
"credentials": {
"googleSheetsOAuth2Api": {
"id": "credential-id",
"name": ""
}
},
"typeVersion": 4.5
},
{
"id": "410063fe-d634-4db6-b2c6-c31bc239fc57",
"name": "Compose Thank You Emails",
"type": "n8n-nodes-base.code",
"position": [
780,
460
],
"parameters": {
"jsCode": "// --- Read data from both Sheets nodes ---\nconst students  = $items('Fetch Student Roster').map(i => i.json);\nconst responses = $items('Fetch Response Records').map(i => i.json);\n\n// --- Normalization helper ---\nconst normalize = v => (v ? String(v).trim().toLowerCase() : '');\n\n// --- Lists ---\nconst completed = [];\n\n// --- Match Register Numbers ---\nfor (const s of students) {\n  const reg = normalize(s['Register No.']);\n  const match = responses.find(r => normalize(r['Reg No']) === reg);\n\n  if (match) {\n    completed.push({\n      'Register No.': s['Register No.'],\n      'Student Name': s['Student Name'],\n      'Contact': s['Contact'] || '',\n      'Email Address': s['Email'] || match['Email Address'] || '',\n      'Timestamp': match['Timestamp'] || '',\n      'Status': '\u2705 Completed'\n    });\n  }\n}\n\n// --- Prepare Email Content ---\nconst subject = '\u2705 Thank You for Your Submission';\nconst bodyTemplate = `\nDear {{name}},<br><br>\nThank you for completing your form (Reg No: {{reg}}).<br>\nWe appreciate your prompt response.<br><br>\n\u2013 Support Team\n`;\n\n// --- Helper for placeholder replacement ---\nfunction fill(template, s) {\n  return template\n    .replace(/\\{\\{\\s*name\\s*\\}\\}/gi, s['Student Name'])\n    .replace(/\\{\\{\\s*reg\\s*\\}\\}/gi, s['Register No.']);\n}\n\n// --- Handle case of no completed students ---\nif (completed.length === 0) {\n  return [{ json: { message: 'No completed students found' } }];\n}\n\n// --- Output: one JSON per student ---\nreturn completed.map(s => ({\n  json: {\n    'Register No.': s['Register No.'],\n    'Student Name': s['Student Name'],\n    'Contact': s['Contact'],\n    'Email Address': s['Email Address'],\n    'subject': subject,\n    'htmlBody': fill(bodyTemplate, s)\n  }\n}));\n"
},
"typeVersion": 2
},
{
"id": "555aad64-769d-4e5a-95ae-65badd883d4d",
"name": "Return Acknowledgement Page",
"type": "n8n-nodes-base.respondToWebhook",
"position": [
1260,
470
],
"parameters": {
"options": {
"responseCode": 200,
"responseHeaders": {
"entries": [
{
"name": "Content-Type",
"value": "text/html"
}
]
}
},
"respondWith": "text",
"responseBody": "<html><body style=\"font-family:Segoe UI,Arial;text-align:center;padding:40px;\"><h2>\u2705 Acknowledgements Sent</h2><p>All completed students have received thank-you messages.</p><a href=\"https://[YOUR_WEBHOOK_URL]/form-dashboard\">\u2190 Back to Dashboard</a></body></html>"
},
"typeVersion": 1.4
},
{
"id": "f7170ea9-81c5-4602-a9f6-e1d85830694e",
"name": "Dispatch Email Notices",
"type": "n8n-nodes-base.gmail",
"position": [
1010,
450
],
"webhookId": "97399fe4-04ae-48c4-8f2f-10e92a9a0655",
"parameters": {
"sendTo": "={{$json[\"Email Address\"]}}",
"message": "={{ $json.htmlBody }}",
"options": [],
"subject": "{{ $json.subject }}"
},
"credentials": {
"gmailOAuth2": {
"id": "credential-id",
"name": ""
}
},
"typeVersion": 2.1
},
{
"id": "7fa0622b-4f27-4eb2-96d3-1f6a8a96d983",
"name": "Fetch Response Records",
"type": "n8n-nodes-base.googleSheets",
"position": [
280,
540
],
"parameters": {
"options": [],
"sheetName": {
"__rl": true,
"mode": "list",
"value": 1113734691,
"cachedResultUrl": "",
"cachedResultName": "Form Responses 1"
},
"documentId": {
"__rl": true,
"mode": "list",
"value": "10eIZzrzgRy8PKfyhhVc8eG8lFoAxEqE0h-IOnqHwdVE",
"cachedResultUrl": "",
"cachedResultName": "n8n forms (Responses)"
}
},
"credentials": {
"googleSheetsOAuth2Api": {
"id": "credential-id",
"name": ""
}
},
"typeVersion": 4.5
},
{
"id": "384d999b-d603-4a1e-ad58-6a055a2b26c4",
"name": "Merge Email Sources",
"type": "n8n-nodes-base.merge",
"position": [
540,
470
],
"parameters": [],
"typeVersion": 3.2
},
{
"id": "1bf63625-8f0d-4f2c-a6ea-08dcb50dc1ad",
"name": "Fetch Reminder Responses",
"type": "n8n-nodes-base.googleSheets",
"position": [
280,
850
],
"parameters": {
"options": [],
"sheetName": {
"__rl": true,
"mode": "list",
"value": 1113734691,
"cachedResultUrl": "",
"cachedResultName": "Form Responses 1"
},
"documentId": {
"__rl": true,
"mode": "list",
"value": "10eIZzrzgRy8PKfyhhVc8eG8lFoAxEqE0h-IOnqHwdVE",
"cachedResultUrl": "",
"cachedResultName": "n8n forms (Responses)"
}
},
"credentials": {
"googleSheetsOAuth2Api": {
"id": "credential-id",
"name": ""
}
},
"typeVersion": 4.5
},
{
"id": "199c5e4a-514f-4c51-8568-d5aec1c0c3ea",
"name": "Incoming Reminder Hook",
"type": "n8n-nodes-base.webhook",
"position": [
60,
720
],
"webhookId": "df0a546c-0185-42fc-bf55-64df575ffdf5",
"parameters": {
"path": "send-reminder",
"options": [],
"responseMode": "responseNode"
},
"typeVersion": 2.1
},
{
"id": "cb8662bb-0224-43c5-9596-36428dbc539f",
"name": "Retrieve Roster Records",
"type": "n8n-nodes-base.googleSheets",
"position": [
300,
690
],
"parameters": {
"options": [],
"sheetName": {
"__rl": true,
"mode": "list",
"value": "gid=0",
"cachedResultUrl": "",
"cachedResultName": "Sheet1"
},
"documentId": {
"__rl": true,
"mode": "list",
"value": "1-i55cyu2DKZ_alSlR3Bd7abHn_EfvTpg73hv2y3zb4o",
"cachedResultUrl": "",
"cachedResultName": "B"
}
},
"credentials": {
"googleSheetsOAuth2Api": {
"id": "credential-id",
"name": ""
}
},
"typeVersion": 4.5
},
{
"id": "693ec31d-f225-4533-800f-870cdc838c59",
"name": "Compose Reminder Emails",
"type": "n8n-nodes-base.code",
"position": [
790,
760
],
"parameters": {
"jsCode": "// --- Read data from both Sheets nodes ---\nconst students  = $items('Retrieve Roster Records').map(i => i.json);\nconst responses = $items('Fetch Reminder Responses').map(i => i.json);\n\n// --- Helper function to normalize Register Numbers ---\nconst normalize = v => (v ? String(v).trim().toLowerCase() : '');\n\n// --- Extract submitted register numbers ---\nconst submittedRegs = responses.map(r => normalize(r['Reg No']));\n\n// --- Identify pending students ---\nconst pending = [];\n\nfor (const s of students) {\n  const reg = normalize(s['Register No.']);\n  if (!submittedRegs.includes(reg)) {\n    pending.push({\n      'Register No.': s['Register No.'],\n      'Student Name': s['Student Name'],\n      'Contact': s['Contact'] || '',\n      'Email Address': s['Email'] || '',\n      'Status': '\u26a0\ufe0f Not Submitted'\n    });\n  }\n}\n\n// --- Stop execution if no pending students ---\nif (pending.length === 0) {\n  return [{ json: { message: 'All students have submitted. No reminders needed.' } }];\n}\n\n// --- Define email subject & body template ---\nconst subject = '\u26a0\ufe0f Reminder: Please Complete Your Form';\nconst bodyTemplate = `\nDear {{name}},<br><br>\nOur records show that you have not yet completed the required form (Reg No: {{reg}}).<br>\nKindly fill it at your earliest convenience.<br><br>\n\u2013 Support Team\n`;\n\n// --- Helper to replace placeholders in the message ---\nfunction fill(template, s) {\n  return template\n    .replace(/\\{\\{\\s*name\\s*\\}\\}/gi, s['Student Name'])\n    .replace(/\\{\\{\\s*reg\\s*\\}\\}/gi, s['Register No.']);\n}\n\n// --- Prepare final output for each pending student ---\nreturn pending.map(s => ({\n  json: {\n    'Register No.': s['Register No.'],\n    'Student Name': s['Student Name'],\n    'Contact': s['Contact'],\n    'Email Address': s['Email Address'],\n    'subject': subject,\n    'htmlBody': fill(bodyTemplate, s)\n  }\n}));\n"
},
"typeVersion": 2
},
{
"id": "f17a315f-b0f1-4c2a-b482-cb6ccc627140",
"name": "Return Reminder Page",
"type": "n8n-nodes-base.respondToWebhook",
"position": [
1270,
760
],
"parameters": {
"options": {
"responseCode": 200,
"responseHeaders": {
"entries": [
{
"name": "Content-Type",
"value": "text/html"
}
]
}
},
"respondWith": "text",
"responseBody": "<html><body style=\"font-family:Segoe UI,Arial;text-align:center;padding:40px;\"><h2>\u2705 Acknowledgements Sent</h2><p>All completed students have received thank-you messages.</p><a href=\"https://[YOUR_WEBHOOK_URL]/form-dashboard\">\u2190 Back to Dashboard</a></body></html>"
},
"typeVersion": 1.4
},
{
"id": "a6e673c1-677d-4657-b237-095960f5c0c1",
"name": "Send Reminder Emails",
"type": "n8n-nodes-base.gmail",
"position": [
1030,
760
],
"webhookId": "7640c4d6-a8b7-44fd-99e7-1ddedfe0d5c2",
"parameters": {
"sendTo": "={{$json[\"Email Address\"]}}",
"message": "={{ $json.htmlBody }}",
"options": [],
"subject": "={{ $json.subject }}"
},
"credentials": {
"gmailOAuth2": {
"id": "credential-id",
"name": ""
}
},
"typeVersion": 2.1
},
{
"id": "f1e66b06-43e4-456f-b7e8-a7afb247b752",
"name": "Merge Reminder Sources",
"type": "n8n-nodes-base.merge",
"position": [
550,
770
],
"parameters": [],
"typeVersion": 3.2
}
],
"active": false,
"pinData": [],
"settings": {
"callerPolicy": "workflowsFromSameOwner",
"availableInMCP": false,
"executionOrder": "v1"
},
"versionId": "",
"connections": {
"Build Tracking Dashboard": {
"main": [
[
{
"node": "Return Webhook Output",
"type": "main",
"index": 0
}
]
]
},
"Combine Register Streams": {
"main": [
[
{
"node": "Build Tracking Dashboard",
"type": "main",
"index": 0
}
]
]
},
"Merge Email Sources": {
"main": [
[
{
"node": "Compose Thank You Emails",
"type": "main",
"index": 0
}
]
]
},
"Merge Reminder Sources": {
"main": [
[
{
"node": "Compose Reminder Emails",
"type": "main",
"index": 0
}
]
]
},
"Incoming Dashboard Hook": {
"main": [
[
{
"node": "Fetch Spreadsheet Data",
"type": "main",
"index": 0
},
{
"node": "Retrieve Form Responses",
"type": "main",
"index": 0
}
]
]
},
"Fetch Spreadsheet Data": {
"main": [
[
{
"node": "Combine Register Streams",
"type": "main",
"index": 0
}
]
]
},
"Retrieve Form Responses": {
"main": [
[
{
"node": "Combine Register Streams",
"type": "main",
"index": 1
}
]
]
},
"Dispatch Email Notices": {
"main": [
[
{
"node": "Return Acknowledgement Page",
"type": "main",
"index": 0
}
]
]
},
"Send Reminder Emails": {
"main": [
[
{
"node": "Return Reminder Page",
"type": "main",
"index": 0
}
]
]
},
"Return Webhook Output": {
"main": [
[]
]
},
"Compose Thank You Emails": {
"main": [
[
{
"node": "Dispatch Email Notices",
"type": "main",
"index": 0
}
]
]
},
"Compose Reminder Emails": {
"main": [
[
{
"node": "Send Reminder Emails",
"type": "main",
"index": 0
}
]
]
},
"Fetch Response Records": {
"main": [
[
{
"node": "Merge Email Sources",
"type": "main",
"index": 1
}
]
]
},
"Fetch Student Roster": {
"main": [
[
{
"node": "Merge Email Sources",
"type": "main",
"index": 0
}
]
]
},
"Fetch Reminder Responses": {
"main": [
[
{
"node": "Merge Reminder Sources",
"type": "main",
"index": 1
}
]
]
},
"Retrieve Roster Records": {
"main": [
[
{
"node": "Merge Reminder Sources",
"type": "main",
"index": 0
}
]
]
},
"Incoming Acknowledgement Hook": {
"main": [
[
{
"node": "Fetch Student Roster",
"type": "main",
"index": 0
},
{
"node": "Fetch Response Records",
"type": "main",
"index": 0
}
]
]
},
"Incoming Reminder Hook": {
"main": [
[
{
"node": "Retrieve Roster Records",
"type": "main",
"index": 0
},
{
"node": "Fetch Reminder Responses",
"type": "main",
"index": 0
}
]
]
}
}
}