Hello n8n community,
I’m an IT specialist working on an automation workflow in n8n to send customized NPS survey emails based on data from two Google Sheets: one for “aliados” (participants with names, emails, etc.) and one for “envio_nps” (send details with type_participante for template selection, links, etc.). The goal is to merge them by “aliado_id” to generate personalized HTML emails and send via Gmail.
Goal
- Fetch all rows from “aliados” sheet (e.g., 117 rows).
- For each aliado, fetch matching rows from “envio_nps” where estado_enviado = “No Enviado”.
- Merge the data by “aliado_id”.
- In a Function node, generate HTML email based on tipo_participante (marca or emprendedor), with dynamic Typeform URL and optional folder link.
- Send via Gmail.
- Update “envio_nps” to “Enviado” after sending.
The sheets schemas are as per the initial post (I can attach screenshots if needed).
Current Setup
- Schedule Trigger.
- Google Sheets node “Aliados” (get many rows, all data).
- Code in JavaScript node (trying to merge and generate email data).
- Google Sheets node “envio_nps” (get rows, filtered by aliado_id = {{$json[“aliado_id”]}} and estado_enviado = “No Enviado”).
- Gmail node (Send Message, HTML type).
Current output from “envio_nps” (4 items, correct):
[
{
"row_number": 2,
"envio_id": "6ec64416",
"proyecto_id": "PRJ-npstest01",
"programa_id": "PRJ-npstest01",
"aliado_id": "Ally-001ait",
"tipo_participante": "Cliente (marca)",
"link_expediente_programa": "",
"fecha_envio": "",
"estado_enviado": "No Enviado",
"link_encuesta": ""
},
// and the other 3 items...
]
Current Function code attempts to match aliados and envios, but the emails have empty “to” fields, leading to “Invalid email address” in Gmail.
Gmail config:
- To: {{ $json.to }}
- Subject: {{ $json.subject }}
- Email Type: HTML
- Message: {{ $json.html }}
What I’ve Tried
- Various Function scripts to merge using $input.all() and find by aliado_id – they work for envios but can’t reliably fetch aliados data from previous node.
- Tried Merge node in “Merge By Key” mode, but my n8n version (cloud) shows “We didn’t make that… yet” when searching for it.
- Tried Item Lists → Aggregate in “Combine” mode, but the config doesn’t show “Combine” or “Key” options – only “Individual Fields” (see attached screenshot).
- Adjusted $items(“Aliados”) with different names (e.g., “Google Sheets”, “Get aliados”), but still empty.
- Reordered nodes (Aliados → Function → envio_nps), but then filtro in envio_nps can’t access aliado_id.
Errors
- In Function: “Cannot read properties of undefined (reading ‘trim’)” when accessing aliado.correo.
- In Gmail: “Invalid email address (item 0)” because “to” is empty.
- No output data returned from Function when trying some versions.
I’m on n8n cloud, latest version as of December 2025. Any ideas how to merge these two sheets data by key without custom code? Or a working Function script that reliably grabs both datasets?
Thanks in advance for any help – I’ve been stuck for hours!
Attached: Screenshots of nodes, schemas, errors, and PDF with email templates.
