Automated API Data Collection and Google Sheets Backup - n8n Workflow

Technical teams requiring automated data synchronization from external APIs.
Analysts who need API data deposited directly into Google Sheets for reporting.
Users looking for robust, production-ready n8n templates with built-in error handling.
Anyone needing a reliable CSV backup of collected user data for offline archiving.

This robust integration solution is a key piece of documentation among available n8n templates. It eliminates manual data entry by automatically executing an HTTP request to fetch user records from any configured API. The primary value of this n8n workflow lies in its data pipeline control: it verifies a successful HTTP 200 response before proceeding, preventing invalid or incomplete data from corrupting your records. Once verified, a specialized n8n node transforms complex JSON structures into simple, tabular format suitable for business use. Finally, the data is simultaneously written to a specified Google Sheet and converted into a standard CSV file for archival or further integration. This entire process is defined by a single, powerful n8n workflow, ensuring reliability and data fidelity.

1. Start: The process begins with the Manual Trigger n8n node, initiating the data collection. This can easily be swapped for an automated n8n trigger like a Cron schedule for continuous automation.


2. Fetch Data: An HTTP Request n8n node contacts the external API specified by the BASEURL environment variable.


3. Validation Check: An IF n8n node verifies the status code of the API response. If the code is not 200, the workflow stops via the "Stop on API Failure" node, ensuring data integrity and error logging. This flow control is essential in production n8n templates.


4. Data Transformation: If successful, a Function n8n node runs custom JavaScript to extract the required fields (first name, last name, and country), mapping them into structured items ready for storage.


5. Google Sheets Append: The Google Sheets n8n node appends the newly formatted data into the designated spreadsheet using the GOOGLESHEETID.


6. CSV Backup: The final step uses the Spreadsheet File n8n node to take the current data stream and convert it into a portable CSV backup file (usersbackup_export.csv), completing the run of the n8n workflow.

1. Import the n8n Workflow: Copy the provided JSON data and paste it into your n8n instance using the "New" > "Import from JSON" option.


2. Credentials: Set up or select your Google Sheets OAuth2 credentials within the "Append Data to Google Sheets" n8n node.


3. Environment Variables: You must define two environment variables in your n8n settings or in the workflow itself. These are crucial for the HTTP Request and Google Sheets n8n node:

1. Sheet Preparation: Ensure the target Google Sheet has appropriate column headers (e.g., 'Name', 'Country') in the first row to match the data output from the Function n8n node.


2. Execution: Run the n8n workflow manually or configure a scheduled n8n trigger for automation.

Start Workflow Manually (Manual Trigger n8n trigger):
Function: Serves as the starting n8n trigger point for manual testing and execution. Easily replaceable with a Cron n8n trigger for scheduled production runs.
Key Configuration: None (default trigger settings).
Fetch User Data from API (HTTP Request n8n node):
Function: Retrieves the raw user data from the configured external API endpoint.
Key Configuration: URL set dynamically using an expression referencing the environment variable: ={{ $env.BASEURL }}.
Verify API Response Success (IF n8n node):
Function: Acts as critical error handling. It ensures the n8n workflow only proceeds if the API returned a successful HTTP 200 status code.
Key Configuration: Condition checks if statusCode is equal to 200.
Transform API Data to Name and Country (Function n8n node):
Function: Runs custom JavaScript code to reshape the complex API JSON into simplified objects with name and country keys, preparing the data stream for the next n8n node.
Key Configuration: Custom code iterates over the results array, concatenating fields.
Append Data to Google Sheets (Google Sheets n8n node):
Function: Adds the processed records as new rows to the designated spreadsheet, completing the primary storage action of this n8n workflow.
Key Configuration: Operation is Append. Sheet ID is dynamic: ={{ $env.GOOGLESHEETID }}. Uses OAuth2.
Create CSV Backup File (Spreadsheet File n8n node):
Function: Converts the incoming data stream into a downloadable CSV binary file, providing a crucial backup step for this n8n workflow template.
Key Configuration: File format set to csv, filename set to usersbackup_export.