Module 1: Google Sheets

Mini-Project: Update a Sheet from an Event

Next →

Capsule description

The moment to put it all together has arrived. The previous seven capsules were pieces; this one assembles them into a real, working workflow.

The project: a smart lead capture. Every time a lead arrives (via a form, a webhook, whatever), the workflow checks whether that lead already exists in the sheet. If it's new, it adds it. If it already existed, it updates its data without duplicating it. And everything is recorded with a date and a clean format.

It's a small but complete project: it uses the "search before you act" pattern (capsule 03), the write operations (04 and 05), manual mapping (06), and defensive design (07). When you finish, you'll have a workflow you can adapt to dozens of real cases: customer capture, inventory control, order log.

What you'll build

A workflow called Lead Capture that:

  1. Receives a lead (name, email, city)
  2. Searches the sheet for whether that email already exists
  3. If it doesn't exist → adds a new row with status "new" and a signup date
  4. If it already exists → updates its data and records the last-contact date
  5. Is idempotent: the same lead can arrive 100 times, there's always exactly one row

What you'll practice

  • ✅ The full "search before you act" pattern (lookup → IF → action)
  • ✅ Update or Append vs the explicit IF pattern — and why here we use the explicit one
  • ✅ Manual mapping with data transformation
  • ✅ Defensive design: default values, consistent date format
  • ✅ Building a workflow that works with real data, not just in the demo

Setup: the sheet

Create a sheet called Leads CRM with these columns in row 1:

emailnamecitystatussignup_datelast_contact_date

Leave it empty of data (headers only). Format the email column as Plain text (capsule 07).

Workflow architecture

Trigger (Manual or Webhook)
   │
   ▼
Set: normalize the incoming lead
   │
   ▼
Google Sheets: Get Row(s)  ──  filter: email = {{ $json.email }}
   │
   ▼
IF: did the lookup return 0 items?
   │
   ┌──────────────┴──────────────┐
   ▼ TRUE (doesn't exist)        ▼ FALSE (already exists)
Google Sheets: Append Row     Google Sheets: Update Row
(new lead + signup_date)      (update + last_contact_date)

Why not just use Update or Append? Because we want to do something different depending on the case: a new lead gets signup_date, an existing one gets last_contact_date. Since the logic differs (not just the content), we use the explicit IF pattern — exactly the last row of the decision table from capsule 05.

Step 1: The trigger and the input data

For development, use a Manual Trigger followed by a Set that simulates the incoming lead:

Set node (manual mode, create these fields):

name   = Ana López
email  = ANA@example.com
city   = CDMX

Note the email with uppercase on purpose — we normalize it in the next step. In production, this Set is replaced by a Webhook Trigger that receives the real lead from a form. The rest of the workflow doesn't change.

Step 2: Normalize the lead

Add another Set node (or use the same one) to clean the data before touching it. This is defensive design from capsule 06:

email  = {{ $json.email.toLowerCase().trim() }}
name   = {{ $json.name.trim() }}
city   = {{ $json.city || 'Not specified' }}

Now the email is always lowercase and space-free — key so that the next step's lookup finds real matches.

Step 3: The lookup

Add a Google Sheets node:

  • Operation: Get Row(s)
  • Document: Leads CRM
  • Sheet: the tab
  • Filters → Add Filter:
    • Column: email
    • Value: {{ $json.email }}

Rename this node to Find Lead (right-click → Rename). We're going to reference it by name in the IF.

This node returns the matching items — 0 if the lead is new, 1 (or more) if it already existed.

Step 4: The IF — does it exist or not?

Add an IF node after Find Lead:

  • Condition (expression mode):
{{ $('Find Lead').all().length === 0 }}
  • true → the lookup found nothing → new lead
  • false → the lookup found something → existing lead

Important note: the IF must still have access to the normalized lead (from step 2), not to the lookup's output. That's why we reference $('Find Lead') to count results, but we take the lead's data from the Set. If you get tangled, add a Merge node or an explicit reference with $('Set') in the following nodes.

Step 5: TRUE branch — add a new lead

On the IF's true output, a Google Sheets node:

  • Operation: Append Row
  • Document: Leads CRM
  • Sheet: the tab
  • Mapping: manual
    • email → {{ $('Set').item.json.email }}
    • name → {{ $('Set').item.json.name }}
    • city → {{ $('Set').item.json.city }}
    • status → new (fixed text)
    • signup_date → {{ $now.toFormat('yyyy-LL-dd') }}
    • last_contact_date → {{ $now.toFormat('yyyy-LL-dd') }}

(Replace 'Set' with the real name of your normalization node.)

Step 6: FALSE branch — update an existing lead

On the IF's false output, another Google Sheets node:

  • Operation: Update Row
  • Document: Leads CRM
  • Sheet: the tab
  • Column to match on: email
  • Mapping: manual — only the columns that change
    • email → {{ $('Set').item.json.email }} (the match key)
    • name → {{ $('Set').item.json.name }} (in case they updated their name)
    • city → {{ $('Set').item.json.city }} (in case they changed city)
    • last_contact_date → {{ $now.toFormat('yyyy-LL-dd') }}

Note what we don't map: status and signup_date. An existing lead keeps its status and its original signup date — they're data we don't want to overwrite (capsule 05: Update is surgical).

Step 7: Test the workflow

Here you check that it's idempotent:

Test 1: new lead

  1. Run the workflow with Ana López / ANA@example.com / CDMX
  2. Look at the sheet: a new row, status = new, both dates today's

Test 2: the same lead again

  1. Run it again, without changing anything
  2. Look at the sheet: there's still a single row of Ana. It didn't duplicate. last_contact_date was updated

Test 3: the same lead with changed data

  1. Change the Set: Ana López / ANA@example.com / Guadalajara
  2. Run it
  3. Look at the sheet: a single row, city is now Guadalajara, status is still new, signup_date didn't change

Test 4: a different lead

  1. Change the Set: Beto Ruiz / beto@example.com / Lima
  2. Run it
  3. Look at the sheet: now there are two rows — Ana and Beto

If all four tests give the expected result: the workflow is correct and idempotent. Congratulations.

Success checklist

  • A new lead is added with status = new and signup_date
  • The same lead repeated isn't duplicated — it's updated
  • On update, status and signup_date aren't overwritten
  • On update, last_contact_date is refreshed
  • The email is always stored in lowercase (normalization working)
  • A different lead creates its own row
  • The dates show as 2026-05-14, not as odd objects

How to take this to production

What you built is a reusable skeleton. For production:

  • Swap the Manual Trigger for a Webhook Trigger — the form sends the lead directly. The rest of the workflow doesn't change.
  • Connect the real form: Google Forms, Typeform, a form on your website — all can send to a webhook.
  • Add notifications: in the TRUE branch (new lead), you could add a node that alerts the sales team. That connects with Module 4 (Slack) and Module 2 (Gmail) of this guide.
  • Handle errors: if Google Sheets fails, right now the workflow stops. G10 (Production) covers how to make it resilient.

Variations of the same pattern

This workflow is the base pattern for tons of cases. Change the names and you get:

CaseKey column"New" does..."Exists" does...
Lead captureemailadds leadupdates data
Inventory controlskucreates productadjusts stock
Order logorder_idrecords orderupdates shipping status
Subscriber listemailsubscribesre-subscribes / updates preferences

The skeleton is identical. The only things that change are the key column and what gets written in each branch.

Module summary

With this project you close Module 1 — Google Sheets. Recapping what you now master:

  • Connect Google Sheets with OAuth2 (capsule 02)
  • Read and search rows with lookup (capsule 03)
  • Add rows with Append without duplicating (capsule 04)
  • Update existing rows and use upsert (capsule 05)
  • Map data between your workflow and the sheet, with transformation (capsule 06)
  • Diagnose rate limits, formats, and permissions (capsule 07)
  • Integrate everything into an idempotent, reusable workflow (this capsule)

The star pattern of the whole module: search before you act. It's what separates a workflow that duplicates junk from one that keeps your data clean.

What's next (Module 2):

Google Sheets stores data. Module 2 — Gmail and email puts it in motion: sending automatic emails, receiving and filtering incoming emails, labeling, and building automatic replies. You'll be able to close the loop: a lead enters the sheet (M01) and receives a welcome email (M02).

Additional resources

  1. n8n Google Sheets node Docs - Full node reference.
  2. n8n Webhook Trigger Docs - For taking the project to production.
  3. n8n IF node Docs - The node that decides between the two branches.

Created: May 14, 2026 Version: 1.0

← PreviousGoogle Sheets TroubleshootingNext →Module Introduction: Gmail and Email