Google Sheets Data Manipulation (Append, Lookup, Update, Read) - n8n Workflow

Ideal User Persona

Data Analysts who frequently manipulate large datasets in Google Sheets.
Users seeking robust n8n templates for handling spreadsheet data transformations.
Backend developers integrating real-time spreadsheet data into applications.
Anyone needing to automate sequential Google Sheets tasks using an n8n workflow, ensuring data integrity across multiple operations.

This detailed n8n workflow provides a robust blueprint for performing sequential data operations—append, lookup, modify, and update—within a single Google Sheet. It solves the common challenge of needing to append new records, dynamically look up specific data points (like all entries in 'Berlin'), modify those results (e.g., simulating a value increase), and update the original sheet without manual intervention. Utilizing the powerful Google Sheets n8n node, this comprehensive n8n template ensures data consistency and demonstrates core logic flow control necessary for advanced business process automation. This n8n workflow is crucial for users looking to maximize the potential of n8n for sophisticated data management tasks.

The process is initiated by a manual n8n trigger and proceeds through several critical data manipulation steps:

1. Preparation (Set): The initial Set n8n node generates a sample record, including a random unique ID, 'Name', 'Rent', and 'City' (Berlin) to be inserted.


2. Creation (Google Sheets - Append): The first Google Sheets n8n node executes an Append operation, adding the newly created record to the specified spreadsheet.


3. Extraction (Google Sheets1 - Lookup): The n8n workflow then performs a Lookup for all records where the 'City' column matches 'Berlin', preparing these items for modification.


4. Modification (Set1): This Set1 n8n node receives the looked-up results and uses an expression to dynamically calculate a new 'Rent' value by adding 100 to the existing rent for all matching items.


5. Update (Google Sheets2 - Update): The third Google Sheets n8n node uses the unique ID retrieved during the lookup to execute an Update operation, applying the modified (increased) rent value back into the original sheet.


6. Verification (Google Sheets3 - Read): Finally, the last Google Sheets n8n node executes a Read operation, fetching the entire range (A:D) to confirm that the append and update operations executed successfully across the entire n8n workflow.

Getting Started with this n8n Workflow

1. Import the Workflow: Download the provided n8n workflow JSON file. In your n8n instance, navigate to 'Workflows' and use the 'Import from JSON' function to load the template.


2. Credentials Setup: All Google Sheets n8n nodes require authentication. You must create or select an existing Google Sheets OAuth2 credential. Ensure the connected account has full read and write permissions to the target spreadsheet.


3. Configuration: You must update the Sheet ID parameter in all four Google Sheets n8n nodes (Google Sheets, Google Sheets1, Google Sheets2, Google Sheets3) to match the ID of your intended test spreadsheet.


4. Execution: Since the n8n trigger is set to manual execution ('On clicking execute'), you can test the full data transformation sequence immediately after configuration.

This n8n workflow utilizes several key nodes for its complex Google Sheets operations:

On clicking 'execute' (Manual Trigger): Functions as the n8n trigger, initiating the sequential data flow whenever manually executed.

Set: Function: Prepares the initial data payload, generating a random ID and setting static values for Name, Rent, and City before the first spreadsheet interaction.

Google Sheets (Append): Function: Appends the data created by the Set node to the spreadsheet. Key Configuration: Operation set to append, range A:D, using USER_ENTERED input mode.

Google Sheets1 (Lookup): Function: Locates specific data points within the sheet. In this n8n node, it searches for all entries where the lookupColumn (City) matches 'Berlin'. Key Configuration: Operation set to lookup, lookupValue is 'Berlin', returnAllMatches is enabled.

Set1: Function: Performs a dynamic calculation on the looked-up data, increasing the 'Rent' value by 100 for simulation purposes. Key Configuration: Uses the expression: {{$node["Google Sheets1"].json["Rent"]+100}}.

Google Sheets2 (Update): Function: Updates the specific row identified by the ID with the new 'Rent' value calculated in the previous Set n8n node. Key Configuration: Operation set to update, using 'ID' as the update key.

• Google Sheets3 (Read): Function: Reads the entire spreadsheet range (A:D) after all modifications are complete, providing a verification output of the fully executed n8n workflow. Key Configuration: Operation set to read, range A:D.