Saltar al contenido

Gracewell Automated Registration Tracker Suite

Monitoriza el estado de registro de estudiantes desde Google Sheets, genera un panel interactivo, y automatiza el envío de correos de agradecimiento a estudiantes que han completado y recordatorios a los pendientes vía Gmail.

AIn8n 23 nodos 7 tipos conectado
Cargando workflow...

Nodos

GoogleSheets Code Merge RespondToWebhook Webhook Gmail StickyNote

Herramientas

Google Sheets Gmail Webhook

Detalles

ID
9580
Nodos
23
Conex.
Sí
Tipos
7

Pertenece a:

Educación y Formación Operaciones & Finanzas google-sheets gmail webhook

¿Qué hace este workflow?

Este workflow n8n está diseñado para instituciones educativas, permitiendo una gestión integral y automatizada del proceso de registro de estudiantes. Conecta Google Sheets, donde se almacena la información de registro, para monitorizar activamente el estado de cada estudiante. A partir de esta monitorización, el sistema genera automáticamente un panel de seguimiento interactivo y segmenta a los estudiantes según su estado de registro: completado o pendiente. Para aquellos que han finalizado su proceso, el workflow envía correos electrónicos personalizados de agradecimiento a través de Gmail, fortaleciendo la relación y confirmando su inscripción. Simultáneamente, a los estudiantes con registros incompletos, les envía recordatorios automáticos para asegurar que completen los pasos necesarios, minimizando abandonos y facilitando la captación. Esta solución centraliza la administración, libera tiempo del personal administrativo y mejora drásticamente la comunicación con los estudiantes, asegurando que ningún registro se quede sin seguimiento.

¿Cómo funciona?

Este workflow usa 23 nodos conectados con 7 tipos diferentes: GoogleSheets, Code, Merge, RespondToWebhook, Webhook y 2 más. La estructura está totalmente conectada — listo para importar.

¿Para quién es?

Diseñado para empresas de Educación y Formación y equipos de Operaciones & Finanzas. Nivel avanzado — recomendado para usuarios experimentados. Alto valor de negocio: automatiza una tarea recurrente con impacto directo.

¿Lo quieres en tu empresa?

→

Lo implementamos por ti end-to-end: integración, deploy, mantenimiento y soporte. Consultoría B2B con Genai Sapiens.

Hablemos de tu proyecto

¿Quieres aprender a hacerlo?

→

Sprints de 30 días con companion IA + comunidad. Aprende n8n, automatización y agentes IA desde cero o nivel avanzado.

Ver formación Momentum

Workflows similares

Envía recordatorios automáticos de facturas pendientes y vence el impago

basico

Captura Automática de Datos Web y Almacenamiento en Google Sheets

intermedio

Automatiza el Monitoreo de Políticas GRC y Notifica Dueños Ausentes

basico

Automatiza alertas de tareas urgentes en Google Sheets con Gmail

intermedio

Guarda automáticamente emails recibidos en Google Sheets

basico

Automatiza el envío de actas de reunión desde Google Sheets con Gmail

basico

Automatiza resúmenes diarios de entradas de Google Forms por email

basico
\n\n\n\n\n

📊 Gracewell Form Tracking Dashboard

\n

Total ${summary.total} | Submitted ${summary.submitted} | Pending ${summary.pending} | Completion ${summary.completion}

\n\n\n\n\n
\n

✅ Completed (${completed.length})

\n ${completedRows}
Reg NoStudent NameStatus
\n
\n\n
\n

⚠️ Pending (${incompleted.length})

\n ${pendingRows}
Reg NoStudent NameStatus
\n
\n\n\n\n\n\n\n\n`;\n\n// Step 5 – Return\nreturn [{\n json: { summary, completed, incompleted, html }\n}];\n"},"typeVersion":2},{"id":"751e83f2-92e9-4ca2-80fe-51926866f07e","name":"Merge","type":"n8n-nodes-base.merge","position":[-272,-752],"parameters":{"mode":"combine","options":{},"advanced":true,"mergeByFields":{"values":[{"field1":"Register No.","field2":"Reg No"}]}},"typeVersion":3.2},{"id":"ab22e315-f457-4c6a-b143-3e5795b25275","name":"Respond to Webhook","type":"n8n-nodes-base.respondToWebhook","position":[176,-736],"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":"Webhook1","type":"n8n-nodes-base.webhook","position":[-720,-752],"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":"Webhook - send-acknowledgements","type":"n8n-nodes-base.webhook","position":[-240,-336],"webhookId":"c13eb3e8-733f-4011-93a3-f0dd20bf4703","parameters":{"path":"send-acknowledgements","options":{},"responseMode":"responseNode"},"typeVersion":2.1},{"id":"b8895288-5239-43f4-95b3-48d208bab533","name":"Google Sheets - Student List","type":"n8n-nodes-base.googleSheets","position":[32,-496],"parameters":{"options":{},"sheetName":{"__rl":true,"mode":"list","value":"gid=0","cachedResultUrl":"https://docs.google.com/spreadsheets/d/1-i55cyu2DKZ_alSlR3Bd7abHn_EfvTpg73hv2y3zb4o/edit#gid=0","cachedResultName":"Sheet1"},"documentId":{"__rl":true,"mode":"list","value":"1-i55cyu2DKZ_alSlR3Bd7abHn_EfvTpg73hv2y3zb4o","cachedResultUrl":"https://docs.google.com/spreadsheets/d/1-i55cyu2DKZ_alSlR3Bd7abHn_EfvTpg73hv2y3zb4o/edit?usp=drivesdk","cachedResultName":"B"}},"credentials":{"googleSheetsOAuth2Api":{"id":"6a90qMUou7KnnKCl","name":"Google Sheets account"}},"typeVersion":4.5},{"id":"410063fe-d634-4db6-b2c6-c31bc239fc57","name":"Code - Prepare Messages","type":"n8n-nodes-base.code","position":[480,-400],"parameters":{"jsCode":"//-------------------------------------------------------------\n// Gracewell Workflow 2: Send Acknowledgements (Enhanced & Stable)\n//-------------------------------------------------------------\n\n// --- Read data from both Sheets nodes ---\nconst students = $items('Google Sheets - Student List').map(i => i.json);\nconst responses = $items('Google Sheets FormResponses').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'] || '', // ✅ added\n 'Email Address': s['Email'] || match['Email Address'] || '',\n 'Timestamp': match['Timestamp'] || '',\n 'Status': '✅ Completed'\n });\n }\n}\n\n// --- Prepare Email Content ---\nconst subject = '✅ Thank You for Your Submission';\nconst bodyTemplate = `\nDear {{name}},

\nThank you for completing your form (Reg No: {{reg}}).
\nWe appreciate your prompt response.

\n– Gracewell Technologies 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":"Respond - Confirmation","type":"n8n-nodes-base.respondToWebhook","position":[928,-400],"parameters":{"options":{"responseCode":200,"responseHeaders":{"entries":[{"name":"Content-Type","value":"text/html"}]}},"respondWith":"text","responseBody":"

✅ Acknowledgements Sent

All completed students have received thank-you messages.

← Back to Dashboard"},"typeVersion":1.4},{"id":"f7170ea9-81c5-4602-a9f6-e1d85830694e","name":"Send a message","type":"n8n-nodes-base.gmail","position":[704,-400],"webhookId":"97399fe4-04ae-48c4-8f2f-10e92a9a0655","parameters":{"sendTo":"={{$json[\"Email Address\"]}}","message":"={{ $json.htmlBody }}","options":{},"subject":"{{ $json.subject }}"},"credentials":{"gmailOAuth2":{"id":"cqSWSp3vkWk7gIEM","name":"Gmail account 2"}},"typeVersion":2.1},{"id":"7fa0622b-4f27-4eb2-96d3-1f6a8a96d983","name":"Google Sheets FormResponses","type":"n8n-nodes-base.googleSheets","position":[32,-304],"parameters":{"options":{},"sheetName":{"__rl":true,"mode":"list","value":1113734691,"cachedResultUrl":"https://docs.google.com/spreadsheets/d/10eIZzrzgRy8PKfyhhVc8eG8lFoAxEqE0h-IOnqHwdVE/edit#gid=1113734691","cachedResultName":"Form Responses 1"},"documentId":{"__rl":true,"mode":"list","value":"10eIZzrzgRy8PKfyhhVc8eG8lFoAxEqE0h-IOnqHwdVE","cachedResultUrl":"https://docs.google.com/spreadsheets/d/10eIZzrzgRy8PKfyhhVc8eG8lFoAxEqE0h-IOnqHwdVE/edit?usp=drivesdk","cachedResultName":"n8n forms (Responses)"}},"credentials":{"googleSheetsOAuth2Api":{"id":"6a90qMUou7KnnKCl","name":"Google Sheets account"}},"typeVersion":4.5},{"id":"384d999b-d603-4a1e-ad58-6a055a2b26c4","name":"Merge1","type":"n8n-nodes-base.merge","position":[256,-400],"parameters":{},"typeVersion":3.2},{"id":"1bf63625-8f0d-4f2c-a6ea-08dcb50dc1ad","name":"Google Sheets - FormResponses","type":"n8n-nodes-base.googleSheets","position":[16,208],"parameters":{"options":{},"sheetName":{"__rl":true,"mode":"list","value":1113734691,"cachedResultUrl":"https://docs.google.com/spreadsheets/d/10eIZzrzgRy8PKfyhhVc8eG8lFoAxEqE0h-IOnqHwdVE/edit#gid=1113734691","cachedResultName":"Form Responses 1"},"documentId":{"__rl":true,"mode":"list","value":"10eIZzrzgRy8PKfyhhVc8eG8lFoAxEqE0h-IOnqHwdVE","cachedResultUrl":"https://docs.google.com/spreadsheets/d/10eIZzrzgRy8PKfyhhVc8eG8lFoAxEqE0h-IOnqHwdVE/edit?usp=drivesdk","cachedResultName":"n8n forms (Responses)"}},"credentials":{"googleSheetsOAuth2Api":{"id":"6a90qMUou7KnnKCl","name":"Google Sheets account"}},"typeVersion":4.5},{"id":"199c5e4a-514f-4c51-8568-d5aec1c0c3ea","name":"Webhook - send-acknowledgements1","type":"n8n-nodes-base.webhook","position":[-208,112],"webhookId":"df0a546c-0185-42fc-bf55-64df575ffdf5","parameters":{"path":"send-reminder","options":{},"responseMode":"responseNode"},"typeVersion":2.1},{"id":"cb8662bb-0224-43c5-9596-36428dbc539f","name":"Google Sheets - Student List1","type":"n8n-nodes-base.googleSheets","position":[16,16],"parameters":{"options":{},"sheetName":{"__rl":true,"mode":"list","value":"gid=0","cachedResultUrl":"https://docs.google.com/spreadsheets/d/1-i55cyu2DKZ_alSlR3Bd7abHn_EfvTpg73hv2y3zb4o/edit#gid=0","cachedResultName":"Sheet1"},"documentId":{"__rl":true,"mode":"list","value":"1-i55cyu2DKZ_alSlR3Bd7abHn_EfvTpg73hv2y3zb4o","cachedResultUrl":"https://docs.google.com/spreadsheets/d/1-i55cyu2DKZ_alSlR3Bd7abHn_EfvTpg73hv2y3zb4o/edit?usp=drivesdk","cachedResultName":"B"}},"credentials":{"googleSheetsOAuth2Api":{"id":"6a90qMUou7KnnKCl","name":"Google Sheets account"}},"typeVersion":4.5},{"id":"693ec31d-f225-4533-800f-870cdc838c59","name":"Code - Prepare Messages1","type":"n8n-nodes-base.code","position":[464,112],"parameters":{"jsCode":"//-------------------------------------------------------------\n// Gracewell Workflow 3: Send Reminders (Pending Students)\n//-------------------------------------------------------------\n\n// --- Read data from both Sheets nodes ---\nconst students = $items('Google Sheets - Student List1').map(i => i.json);\nconst responses = $items('Google Sheets - FormResponses').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': '⚠️ 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 = '⚠️ Reminder: Please Complete Your Form';\nconst bodyTemplate = `\nDear {{name}},

\nOur records show that you have not yet completed the required form (Reg No: {{reg}}).
\nKindly fill it at your earliest convenience.

\n– Gracewell AI\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":"Respond - Confirmation1","type":"n8n-nodes-base.respondToWebhook","position":[912,112],"parameters":{"options":{"responseCode":200,"responseHeaders":{"entries":[{"name":"Content-Type","value":"text/html"}]}},"respondWith":"text","responseBody":"

✅ Acknowledgements Sent

All completed students have received thank-you messages.

← Back to Dashboard"},"typeVersion":1.4},{"id":"a6e673c1-677d-4657-b237-095960f5c0c1","name":"Send a message1","type":"n8n-nodes-base.gmail","position":[688,112],"webhookId":"7640c4d6-a8b7-44fd-99e7-1ddedfe0d5c2","parameters":{"sendTo":"={{$json[\"Email Address\"]}}","message":"={{ $json.htmlBody }}","options":{},"subject":"={{ $json.subject }}"},"credentials":{"gmailOAuth2":{"id":"cqSWSp3vkWk7gIEM","name":"Gmail account 2"}},"typeVersion":2.1},{"id":"f1e66b06-43e4-456f-b7e8-a7afb247b752","name":"Merge2","type":"n8n-nodes-base.merge","position":[240,112],"parameters":{},"typeVersion":3.2},{"id":"246db6a2-f974-48b9-85f9-c140a4b52ddf","name":"Sticky Note","type":"n8n-nodes-base.stickyNote","position":[-880,-944],"parameters":{"width":1360,"height":448,"content":"Gracewell Automated Registration Tracker Suite"},"typeVersion":1},{"id":"64153acd-c102-46be-9e71-2154dcb560d8","name":"Sticky Note1","type":"n8n-nodes-base.stickyNote","position":[-384,-480],"parameters":{"color":4,"width":1520,"height":320,"content":" Workflow 2: Send Acknowledgements"},"typeVersion":1},{"id":"7e4346b8-b5e8-4f96-a85b-02a4bab9d1af","name":"Sticky Note2","type":"n8n-nodes-base.stickyNote","position":[-288,-32],"parameters":{"color":6,"width":1456,"height":400,"content":" Workflow 3: Send Reminders"},"typeVersion":1}],"active":false,"pinData":{},"settings":{"callerPolicy":"workflowsFromSameOwner","availableInMCP":false,"executionOrder":"v1"},"versionId":"2a09ba1d-f4f1-4e71-b700-4bfb8edd3d1e","connections":{"Code":{"main":[[{"node":"Respond to Webhook","type":"main","index":0}]]},"Merge":{"main":[[{"node":"Code","type":"main","index":0}]]},"Merge1":{"main":[[{"node":"Code - Prepare Messages","type":"main","index":0}]]},"Merge2":{"main":[[{"node":"Code - Prepare Messages1","type":"main","index":0}]]},"Webhook1":{"main":[[{"node":"Google Sheets","type":"main","index":0},{"node":"Google Sheets1","type":"main","index":0}]]},"Google Sheets":{"main":[[{"node":"Merge","type":"main","index":0}]]},"Google Sheets1":{"main":[[{"node":"Merge","type":"main","index":1}]]},"Send a message":{"main":[[{"node":"Respond - Confirmation","type":"main","index":0}]]},"Send a message1":{"main":[[{"node":"Respond - Confirmation1","type":"main","index":0}]]},"Respond to Webhook":{"main":[[]]},"Code - Prepare Messages":{"main":[[{"node":"Send a message","type":"main","index":0}]]},"Code - Prepare Messages1":{"main":[[{"node":"Send a message1","type":"main","index":0}]]},"Google Sheets FormResponses":{"main":[[{"node":"Merge1","type":"main","index":1}]]},"Google Sheets - Student List":{"main":[[{"node":"Merge1","type":"main","index":0}]]},"Google Sheets - FormResponses":{"main":[[{"node":"Merge2","type":"main","index":1}]]},"Google Sheets - Student List1":{"main":[[{"node":"Merge2","type":"main","index":0}]]},"Webhook - send-acknowledgements":{"main":[[{"node":"Google Sheets - Student List","type":"main","index":0},{"node":"Google Sheets FormResponses","type":"main","index":0}]]},"Webhook - send-acknowledgements1":{"main":[[{"node":"Google Sheets - Student List1","type":"main","index":0},{"node":"Google Sheets - FormResponses","type":"main","index":0}]]}}}