Lesson 03 · Google Sheets Lead Tracker

Goal

Store leads from any source in Google Sheets, cleaned and without duplicates. This is your first workflow with credentials, and the start of every "mini CRM" a small business asks for.

Prerequisites

• Modules 01–02
• A Google account

Concepts

• Credentials: n8n stores API keys and OAuth tokens separately from workflows. Exported JSON never contains them, so buyers connect their own.
• Normalize first: trim spaces and lowercase emails before validating or saving. " Ana@Example.com " and "ana@example.com" must be the same lead.
• Regex validation: the IF node's matches regex operator.
• Append or Update Row: Google Sheets looks for a row where the matching column equals your value. It updates the row if found, appends if not. This is dedupe without extra nodes.

Build it

Step 1: Prepare the sheet

Create a Google Sheet, rename the first tab to Leads, and add the header row from assets/leads-template.csv: email | name | source | updated_at.

Step 2: Webhook + Normalize

• Webhook New Lead: POST, path lead, respond using a Respond to Webhook node.
• Edit Fields Normalize:
  • email = {{ String($json.body.email ?? '').trim().toLowerCase() }}
  • name = {{ String($json.body.name ?? '').trim() }}
  • source = {{ String($json.body.source ?? 'website').trim().toLowerCase() }}
  • updated_at = {{ $now.toISO() }}

String(... ?? '') makes sure .trim() never runs on undefined.

Step 3: Validate

IF Valid Lead?, both conditions (AND):

• {{ $json.email }} String → matches regex → ^[^@\s]+@[^@\s]+\.[^@\s]+$
• {{ $json.name }} String → is not empty

False branch: Edit Fields Build Error (error = a name and a valid email are required) → Respond to Webhook with response code 400. You already know this pattern from Module 01.

Step 4: Connect Google Sheets

Add Google Sheets → Append or Update Row and create a credential:

• n8n Cloud: click Sign in with Google.
• Self-hosted: create an OAuth client in Google Cloud Console (APIs & Services → Credentials → OAuth client ID → Web application), enable the Google Sheets API and Google Drive API, and paste n8n's OAuth Redirect URL into the client's authorized redirect URIs. Then paste the Client ID and Secret into n8n and sign in.

Step 5: Configure the Sheets node (Save Lead)

• Document: By URL → paste your sheet URL
• Sheet: By Name → Leads
• Mapping: Map Each Column Manually
• Column to match on: email
• Map email, name, source, updated_at from the Normalize output

Then Respond to Webhook Respond OK: JSON {{ { status: 'saved', email: $('Normalize').item.json.email } }}.

Publish the workflow.

Test it

./test/test.sh
WITH_CREDS=1 ./test/test.sh

The first runs the three validation checks. The second also saves Ana, then sends the same email with a new name. Open the sheet: there must be one row for ana@example.com with name Ana B.

Common problems

• Valid leads return 500 {"message":"Error in workflow"}: Google Sheets has no credential yet (or the sheet URL is still the YOUR_SHEET_ID placeholder). Invalid leads still get their 400 because they never reach the Sheets node.
• "Sheet with name Leads not found": tab name mismatch (case-sensitive).
• Duplicates still appear: Column to match on isn't set, or the operation is Append instead of Append or Update.
• Columns don't show in the mapper: the header row is missing. Add it, then click the refresh icon in the mapper.
• redirect_uri_mismatch on sign-in: the redirect URL in Google Cloud doesn't exactly match the one n8n shows.