Google Sheets Data Validation and LEDGERS Contact Synchronization - n8n Workflow

Data Entry Teams needing automated validation.
Finance and Sales Operations managing contact data.
Users looking for advanced n8n templates that showcase error handling.
n8n automation specialists seeking a complex implementation of core logic.

Maintaining data integrity when syncing records between systems is crucial. This powerful n8n workflow addresses this challenge by providing comprehensive data validation and error handling before attempting contact creation in the LEDGERS platform. Starting with a Google Sheets n8n trigger, the workflow subjects every row to strict checks for mandatory fields (Contact Name) and proper formatting (Email and Mobile). If any validation fails, the automation immediately stops processing that record and sends a detailed failure notification via Gmail, preventing bad data from entering your LEDGERS system. Only successfully validated and processed contacts are updated in the source Google Sheet with their new LEDGERS ID and creation timestamp. This highly reliable n8n workflow significantly reduces manual data cleaning and ensures smoother operations.

This comprehensive n8n workflow operates through a multi-stage validation process:

1. Start: The process begins with the Google Sheets n8n trigger, which constantly monitors a specified spreadsheet for new rows or updates.


2. Name Validation: The first step uses an If n8n node to check if the 'Name' field is present. If the name is missing, the flow branches to the Contact Name Error Mail Trigger (Gmail n8n node) to alert the user.


3. Format Checking: If the name is valid, the data proceeds to the Email & Mobile Format Checker (Code n8n node). This uses regular expressions to confirm that the Email and Mobile fields adhere to standard formats. Empty fields are treated as valid in this context.


4. Format Validation Branch: The Email & Mobile Validator (If n8n node) checks the results of the format checker. If validation fails for either field, the record branches to the Email/Mobile Error Mail Trigger (Gmail n8n node) for notification.


5. Preparation for Sync: Valid records are batched via the Loop Over Items n8n node and then passed to the Mobile Formatter Code n8n node, which standardizes mobile numbers, attempting to separate country codes if provided with a hyphen.


6. LEDGERS Creation: The prepared data is sent to the custom LEDGERS n8n node to create the contact.


7. Creation Status Check: The Contact Creation Validator (If n8n node) checks the response from LEDGERS for any error codes. If an error is detected, the workflow sends a final error email via the Contact Creation Failure Mail Trigger.


8. Success Logging: If creation is successful, the Get Created Time Code n8n node generates a timestamp.


9. Sheet Update: Finally, the Update Sheet Contact Creation Google Sheets n8n node updates the original source sheet with the creation time and the new LEDGERS Contact ID, completing the n8n automation process.

To deploy this n8n workflow template, follow these steps:

1. Import: Copy the provided JSON data and import it directly into your n8n instance.


2. Credentials Setup: You must configure three sets of credentials:

1. Sheet Configuration: Configure the Google Sheets Trigger n8n node parameters to point to your specific input document ID and sheet name.


2. Error Email Recipients: Update the sendTo fields in the three Gmail n8n node instances (error triggers) to ensure notifications are sent to the appropriate technical contact.


3. Activate: Save and activate the n8n workflow.

Google Sheets Trigger (n8n trigger):
Function: Starts the n8n workflow when new data appears in the specified Google Sheet.
Key Configuration: Polls for new items every minute (Configured via pollTimes).
Contact Name Validation (If n8n node):
Function: Checks if the Name field retrieved from the spreadsheet is not empty. Essential for data integrity.
Email & Mobile Format Checker (Code n8n node):
Function: Executes custom JavaScript using regex to determine if the Email and Mobile fields are in a valid format, appending boolean flags (validEmail, validMobile) to the data structure.
Email & Mobile Validator (If n8n node):
Function: Determines the flow path based on the boolean validation flags created by the previous Code n8n node. If either is false, it routes to error handling.
Loop Over Items (Split In Batches n8n node):
Function: Ensures each valid row is processed individually and sequentially before attempting creation in the target system.
Mobile Formatter (Code n8n node):
Function: Custom n8n code logic to clean and standardize the mobile number, specifically handling hyphenated inputs to extract a country code.
LEDGERS (Custom n8n node):
Function: The core action n8n node; uses the LEDGERS API to attempt contact creation using the Name, Email, and Mobile data. This relies on proper credential setup.
Contact Creation Validator (If n8n node):
Function: Checks the LEDGERS response for an errorCode to detect API-level failures. This is a critical error handling step in the n8n workflow.
Contact Name Error Mail Trigger / Email/Mobile Error Mail Trigger / Contact Creation Failure Mail Trigger (Gmail n8n node):
Function: Three separate n8n node instances responsible for sending specific, context-aware email notifications detailing why a contact record failed at different stages of the n8n workflow.
Update Sheet Contact Creation (Google Sheets n8n node):
* Function: Logs the success of the operation back to the original Google Sheet, updating the row with the timestamp and the official Contact ID from LEDGERS.