Data Transformation and Record Upsert to Google Sheets - n8n Workflow

Data analysts and operations teams needing to standardize data formats before storage.
Users learning how to leverage the Set n8n node for mandatory data cleaning.
Anyone seeking reliable n8n templates for integrating data sources with Google Sheets.
System administrators looking for robust n8n workflow examples.

A common challenge in automation is dealing with data mismatch: the source system exports data fields (e.g., name) that do not match the required format of the destination system (e.g., Full name). This powerful n8n workflow addresses this by demonstrating how to use the versatile Set n8n node to restructure and prepare items before insertion.

This specific n8n workflow fetches simulated customer data, renames crucial fields (id to ID), adds calculated fields (Created time), and then uses the Google Sheets n8n node to perform an efficient upsert (update existing records or insert new ones). Utilizing this n8n template ensures that your data is always standardized, clean, and ready for use in downstream applications, maximizing the value of your overall n8n automation strategy.

This automation is initiated manually, making it an excellent n8n template for testing and demonstration, starting with a basic n8n trigger.

1. Execution Trigger: The process begins with the Manual Trigger n8n node, titled 'On clicking 'execute''. This manually starts the entire n8n workflow run.


2. Data Source Simulation: The workflow immediately calls the 'Customer Datastore - Generate some data' n8n node. This simulates retrieving a list of customer records, providing the raw input items for the transformation stage.


3. Data Preparation (The Core Logic): The flow moves to the 'Set - Prepare fields' n8n node. This is the most crucial step, where the raw fields are renamed, mapped, and augmented to match the column headers expected by Google Sheets. For example, the incoming name field is mapped to Full name, and a new field Created time is dynamically added using the $now expression. The configuration is set to Keep Only Set to ensure that only the perfectly structured data moves forward in the n8n workflow.


4. Destination Action: Finally, the prepared data items are passed to the 'Create or Update record in Google Sheet' n8n node. Using the upsert operation, the n8n node efficiently updates existing records based on keys or creates new rows if the keys are not found in the specified spreadsheet and range (A:C).

To deploy this useful n8n workflow, follow these steps:

1. Import: Copy the provided JSON code. In your n8n instance, click 'New' -> 'Import from JSON' and paste the code.


2. Google Sheets Credential Setup: Locate the 'Create or Update record in Google Sheet' n8n node. Click on the credential selector (Sheets). You will need to set up an OAuth2 connection to Google Sheets, ensuring n8n has permission to read and write to the specific spreadsheet you intend to target.


3. Spreadsheet Configuration: Ensure the target Google Sheet ID within the Google Sheets n8n node parameters is updated to point to your actual spreadsheet. The sheet should ideally have column headers matching the output of the Set n8n node: ID, Full name, Email, and Created time.


4. Testing: As this n8n workflow uses a Manual Trigger, you can execute it manually by clicking 'Execute Workflow' to test the data flow and transformation logic instantly. This confirms your n8n node configuration is correct.

On clicking 'execute' (Manual Trigger): The starting point for this n8n workflow. It is a standard n8n trigger used to manually initiate the execution cycle.
Customer Datastore - Generate some data (n8nTrainingCustomerDatastore): Simulates fetching customer records. It uses the getAllPeople operation to generate sample input data that requires cleaning.
Set - Prepare fields (Set n8n node): This core transformation n8n node is crucial for data integrity.
Function: Renames fields (e.g., id to ID, name to Full name), drops extraneous data, and adds a dynamic timestamp (Created time using {{$now}}).
Key Configuration: keepOnlySet is enabled, ensuring only the newly defined, properly formatted fields proceed to the next n8n node.
Create or Update record in Google Sheet (Google Sheets n8n node): The final action n8n node.
Function: Performs an upsert operation, which intelligently handles existing records (updates them) and new records (inserts them).
Key Configuration: Uses OAuth2 authentication and targets a specific spreadsheet ID with the range A:C for updating customer data.