Solutions 03 · Google Sheets Lead Tracker

Exercise 1

1. Add a first_seen column to the sheet.
2. Between Valid Lead? (true) and Save Lead, add Google Sheets Find Existing → Get Row(s), same document and sheet, filter email = {{ $json.email }}. In node Settings, enable Always Output Data.
3. In Save Lead, map first_seen = {{ $json.first_seen || $now.toISO() }}, and map the other fields from $('Normalize').item.json, because the current item is now the sheet row (or empty).

If the row exists, first_seen is carried over. If not, Always Output Data gives an empty item and first_seen becomes now.

Exercise 2

Webhook (GET lead-stats) → Google Sheets Get Row(s) → Code → Respond to Webhook

Code node (Run Once for All Items):

const counts = {};
for (const item of $input.all()) {
  const source = item.json.source || 'unknown';
  counts[source] = (counts[source] || 0) + 1;
}
return [{ json: counts }];

Respond with JSON {{ $json }}.

Exercise 3

Add a third condition to Valid Lead? (AND):

• {{ ['mailinator.com', 'tempmail.com', '10minutemail.com'].includes($json.email.split('@')[1]) }}
• Boolean → is false

Put the list in a Settings node if you want to edit it without touching the IF.