n8n Workflow: Deduplicate Lead Data with Google Sheets, Automated Email Alerts, and Log Management

This n8n workflow automates the process of managing lead data in Google Sheets, specifically focusing on identifying and handling duplicate entries. It listens for new data in a Google Sheet, checks for existing records, and then either adds the new lead or flags it as a duplicate, sending an email alert and logging the action.

What it does

This workflow streamlines your lead management by:

1. Monitoring Google Sheet for New Leads: Automatically triggers when new rows are added to a specified Google Sheet.
2. Checking for Duplicates: For each new lead, it queries the same Google Sheet to determine if a lead with the same email already exists.
3. Conditional Processing:
   • If the lead is new (no duplicate found), it appends the new lead data to the Google Sheet.
   • If the lead is a duplicate, it skips adding the lead and proceeds to the alert and logging steps.
4. Sending Email Alerts for Duplicates: Notifies a specified email address when a duplicate lead is detected, providing details of the duplicate entry.
5. Logging Actions: Records whether a lead was added or detected as a duplicate in a separate Google Sheet for auditing and tracking.

Prerequisites/Requirements

To use this workflow, you will need:

• Google Account: With access to Google Sheets.
• Google Sheets Credentials: Configured in n8n for accessing your lead data and log sheets.
• Email Sending Service: Configured in n8n (e.g., an SMTP server or an email integration like Gmail) to send alerts.
• Two Google Sheets:
  • One for your primary lead data (where new leads are added and checked).
  • One for logging workflow actions (e.g., "Lead Processing Log").

Setup/Usage

1. Import the Workflow: Download the provided JSON and import it into your n8n instance.
2. Configure Google Sheets Credentials:
   • For the "Google Sheets Trigger" node, select or create your Google Sheets OAuth2 credential.
   • For the "Google Sheets" nodes (for adding new leads and logging), select or create your Google Sheets OAuth2 credential.
3. Configure Google Sheets Trigger:
   • In the "Google Sheets Trigger" node, specify the Spreadsheet ID and Sheet Name of your primary lead data sheet.
   • Set the Trigger When option to "New Row".
4. Configure Duplicate Check (HTTP Request):
   • The "HTTP Request" node is likely configured to query the Google Sheets API directly to check for existing emails. You might need to adjust the URL and query parameters to match your sheet structure and the email column.
   • Ensure the spreadsheetId and range in the URL are correct. The q parameter should dynamically search for the email from the incoming data.
5. Configure "If" Node:
   • The "If" node likely checks the response from the "HTTP Request" node to see if any matching records were found. Adjust the condition to accurately detect if a duplicate email exists (e.g., checking the length of the returned data array).
6. Configure "Google Sheets" (Add New Lead):
   • In the "Google Sheets" node connected to the "True" branch of the "If" node, specify the Spreadsheet ID and Sheet Name of your primary lead data sheet.
   • Ensure the "Operation" is set to "Append Row" and map the incoming data fields to the correct columns in your sheet.
7. Configure Email Alert:
   • Connect an email node (e.g., "Send Email" or "Gmail") to the "False" branch of the "If" node.
   • Configure the email node with your email service credentials.
   • Set the recipient email address for alerts and customize the subject and body to include details of the duplicate lead (e.g., using expressions to pull data from the incoming item).
8. Configure Log Management (Google Sheets):
   • Connect a "Google Sheets" node to both the "True" and "False" branches of the "If" node (or a single node after a Merge node if you want a unified log entry).
   • Specify the Spreadsheet ID and Sheet Name of your log sheet.
   • Set the "Operation" to "Append Row".
   • Map relevant data (e.g., lead email, timestamp, action taken - "Lead Added" or "Duplicate Detected") to the columns in your log sheet.
9. Activate the Workflow: Once all nodes are configured, activate the workflow to start monitoring your Google Sheet for new leads.