Data Transformation and Spreadsheet File Management - n8n Workflow

• Data analysts needing automated data ingestion into spreadsheets.


• Users looking for advanced examples of binary file manipulation within an n8n workflow.


• Developers needing repeatable n8n templates for fetching data from external APIs (like random user generators).


• Anyone requiring complex data routing involving JSON, CSV, and Google Sheets operations.

This automation provides a powerful example of how to handle various data formats within a single n8n workflow. It begins by using an n8n node to request sample user data via HTTP. The critical value this automation provides is its comprehensive demonstration of data transformation: taking raw JSON, mapping specific fields, appending the results directly to Google Sheets, and simultaneously converting that structured JSON into a CSV binary file. Finally, it showcases how to write that binary file, attach it to a Gmail message, and even read data back from the attachment for further processing, providing a complete data lifecycle management solution using n8n.

1. Data Fetch: The process starts implicitly with an n8n trigger (manual execution or schedule) leading to an HTTP Request n8n node, which fetches random user data in JSON format from an external API.


2. Data Structuring: A Set n8n node extracts the user's first name, last name, and country from the complex JSON structure, combining the name fields and simplifying the resulting data items. This ensures clean data flows through the rest of the n8n workflow.


3. Sheet Writing (Path A): The streamlined data is immediately appended to a specified Google Sheets document using the Google Sheets n8n node, demonstrating one method of structured storage.


4. CSV Conversion (Path B): Simultaneously, the data is passed to a Spreadsheet File n8n node to convert the JSON items into a CSV binary file format, suitable for file transfers. This is a crucial step in this n8n workflow.


5. Binary File Handling: The resulting binary data is processed and written to a file named randomusers.json using the Write Binary File n8n node.


6. Email Delivery: The generated file is attached to an outbound email using the Gmail n8n node.


7. Attachment Reprocessing: Finally, the n8n workflow demonstrates advanced data looping: it uses a Move Binary Data n8n node to extract the attachment data and feeds this extracted content into a second Google Sheets n8n node, appending the data to the spreadsheet again, showcasing a complete data input and output cycle within this intricate n8n template.

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


2. Credentials Setup:

1. Configure Sheet ID: Locate both Google Sheets n8n node instances and update the placeholder sheetId from qwertz to the actual ID of your target Google Sheet.


2. Start: Execute the n8n workflow, manually or via a designated n8n trigger, to begin the data fetch and transformation process.

HTTP Request n8n node: Fetches sample user data from https://randomuser.me/api/. This is the starting n8n node for receiving external JSON.
Set n8n node: Performs essential data mapping and cleaning. It combines first and last names into a single name property and extracts the country. It ensures only the cleaned attributes proceed in the n8n workflow.
Google Sheets n8n node (Initial Append): Used to append the refined JSON data directly to a spreadsheet (sheetId: qwertz), configured to use column headers found in the first row.
Spreadsheet File n8n node (CSV Conversion): Converts the incoming JSON data structure into a binary CSV file (fileFormat: csv), configured with the filename usersspreadsheet.
Write Binary File n8n node: Saves the intermediate binary data as randomusers.json, preparing it for attachment.
Gmail n8n node: Handles the communication step. It sends an email and attaches the previously created JSON file.
Move Binary Data n8n node (Conversion/Selection): Key component for managing binary files. It selects the file attachment data (sourceKey: attachment0) after the email step, enabling it to be piped into the final storage step.
Google Sheets2 n8n node (Final Append): Demonstrates reading data from a file handler (the attachment) and appending it again to the Google Sheet. (Note: The sticky note indicates this purpose: "Append data to sheet").