Automate User Data Capture from API to Google Sheets & CSV with n8n

Opening Problem Statement

Meet Sarah, a market researcher who spends hours each week manually collecting user data from various public sources for her analysis reports. She often copies names and countries from web APIs into Google Sheets, then exports data into CSV files for sharing with her team. This manual routine is tedious, error-prone, and wastes at least 4 hours weekly—time that could be better spent interpreting data insights rather than gathering them.

Sarah needs a reliable, automated way to fetch user data from a public API and seamlessly save it both to Google Sheets and as a CSV file locally. That way, she reduces manual errors and speeds up her workflow, especially for larger batches of user records.

What This Automation Does

This n8n workflow is designed to fully automate the process of fetching, processing, and saving user data from a public API. Here’s what happens when you run it:

• Fetch user data from the Random User API automatically via an HTTP Request node.
• Extract specific fields like first and last name, and country from the JSON response using the Set node.
• Append the cleaned data into a specified Google Sheets spreadsheet, keeping all records organized and accessible online.
• Generate a CSV file from the same data using the Spreadsheet File node and save it for offline use or sharing.
• Visual markers (Sticky Notes) help you navigate this workflow easily in the n8n editor.
• Improve process reliability by eliminating manual copy-pasting and dual data entry errors.

Thanks to this workflow, Sarah can reclaim hours every week, maintain accurate data logs, and improve collaboration by sharing CSV exports quickly.

Prerequisites ⚙️

• n8n account with access to create workflows and use nodes.
• Google Sheets account with a spreadsheet ready for data appending and OAuth2 authentication enabled.
• Internet access to call the Random User API at https://randomuser.me/api/.

Optional: For those who prefer self-hosting, you can deploy n8n using platforms like Hostinger to have greater control over your automation environment.

Step-by-Step Guide to Build the Workflow

Step 1: Set up the HTTP Request node to fetch user data

Navigate to your n8n editor and add a new node by clicking + Add Node. Search for and select HTTP Request.

In the parameters panel, enter the URL https://randomuser.me/api/. Leave HTTP Method as GET (default) since we are fetching data.

You should see the node ready to pull user information from the public API. This node triggers the data retrieval process in the workflow.

Common mistake: Forgetting to use HTTPS or mistyping the URL will cause connection errors.

Step 2: Use the Set node to extract required user fields

Add a Set node next and connect the output of the HTTP Request node to it.

Open the Set node and under the Values section, add two new string fields:

• name with value set to: {{$json["results"][0]["name"]["first"]}} {{$json["results"][0]["name"]["last"]}}
• country with value set to: {{$json["results"][0]["location"]["country"]}}

Make sure to check Keep Only Set to keep output clean with just these two fields.

This transforms the complex JSON into easy-to-work-with flat data.

Common mistake: Typos in JSON path expressions will result in empty or undefined fields.

Step 3: Append data to Google Sheets

Add the Google Sheets node and connect it to the Set node.

Configure the node by selecting your Google Sheets OAuth2 credentials. Set Operation to Append. Specify the Sheet ID and Range (e.g., qwertz for Sheet ID, A:C for range covering Name and Country columns).

This ensures each new user record is added to the bottom of your spreadsheet.

Common mistake: Using wrong Sheet ID or missing OAuth2 authentication can cause authorization errors.

Step 4: Create a CSV file from the data

Add the Spreadsheet File node next and connect it to the Google Sheets node.

Set Operation to To File and choose file format as CSV. Give a file name, e.g., users_spreadsheet.

This node converts the data to a CSV file that can be downloaded or shared quickly.

Step 5: Add Sticky Notes for clarity

For better visual organization, drag in two Sticky Note nodes and position them next to Google Sheets and Spreadsheet File nodes.

Use titles like “## JSON > Google Sheets” and “## JSON > CSV” to describe their functions.

Customizations ✏️

• Fetch multiple users at once: Modify the HTTP Request node’s URL to include results parameter, e.g., https://randomuser.me/api/?results=10, and adjust the JSON paths in the Set node accordingly to loop through items.
• Append additional user details: Add more fields in the Set node, such as email, phone, or street address from the JSON response, by adding more string values with correct JSON paths.
• Change CSV format settings: In the Spreadsheet File node, you can switch the delimiter or file type to Excel (.xlsx) for different export options.
• Schedule workflow execution: Add a Cron Trigger node to automate this process daily or weekly, keeping your sheet and CSV updated continuously.

Troubleshooting 🔧

Problem: “Unauthorized” or “Permission Denied” in Google Sheets node

Cause: OAuth2 credentials are not set up correctly or token expired.

Solution: Go to n8n credentials section, re-authenticate the Google Sheets OAuth2 API credentials, and update the workflow.

Problem: JSON path returns empty fields in Set node

Cause: Incorrect JSON key syntax or structure mismatch.

Solution: Use the n8n expression editor to verify the correct JSON key hierarchy or test the HTTP Request node’s output first.

Pre-Production Checklist ✅

• Verify Google Sheets OAuth2 authentication works without errors.
• Test HTTP Request node manually to ensure API is reachable and returns expected data.
• Check that the Set node correctly extracts only desired fields from the JSON response.
• Ensure Google Sheets appends new rows accurately at the specified range.
• Confirm the CSV file generated by Spreadsheet File node matches your expected format and name.

Deployment Guide

Once verified, activate the workflow in n8n by switching the workflow toggle to ON. If desired, integrate a Cron Node for scheduled runs.

Monitor executions via the n8n UI for any errors. Logs will detail which node failed and why, so fixes can be quick.

FAQs

• Can I use other APIs instead of Random User? Yes, you can replace the HTTP Request node‘s URL with any REST API that returns JSON data, just adjust the Set node’s JSON paths accordingly.
• Will this workflow consume many API requests? The Random User API is free with generous usage limits; just be mindful if you switch to paid APIs.
• Is my data secure? Data stays in your n8n instance and connected services; use OAuth2 safely and keep n8n access restricted.
• Can I scale this for bulk user imports? Yes, by adjusting the HTTP Request node to fetch multiple users and using looping or batch processing nodes.

Conclusion

By building this n8n workflow, you’ve automated fetching user data from a public API and storing it both in Google Sheets and as a CSV file effortlessly. This cuts down manual copy-pasting errors and frees up hours every week.

You’ve gained a reliable pipeline to capture and store user data that’s perfect for reporting, sharing, and analysis. Next steps could be to add scheduled triggers, enhance data transformation, or even push this data to CRM or messaging platforms for notifications.

Take pride in your automation skills—this workflow sets a solid foundation for building complex data integrations tailored to your needs.