Import JSON Data into Google Sheets and CSV File

This n8n workflow demonstrates how to fetch data from an external API, transform it, and then store it in both a Google Sheet and a CSV file. It provides a robust solution for integrating external JSON data into structured formats for reporting, analysis, or archival.

What it does

This workflow performs the following steps:

1. Fetches Data: Initiates an HTTP request to an external API to retrieve JSON data.
2. Transforms Data: Uses the "Edit Fields (Set)" node to manipulate and format the incoming JSON data as needed. This could involve renaming fields, extracting specific values, or restructuring the data.
3. Appends to Google Sheets: Takes the transformed data and appends it as new rows to a specified Google Sheet.
4. Generates CSV File: Converts the transformed data into a CSV file format.
5. Outputs CSV: The generated CSV file is then available as output, which can be further processed, saved to storage, or sent via email.

Prerequisites/Requirements

To use this workflow, you will need:

• n8n Instance: A running instance of n8n.
• Google Account: A Google account with access to Google Sheets.
• Google Sheets Credential: An n8n credential configured for Google Sheets (OAuth2 recommended).
• External API Endpoint: The URL of the API you wish to fetch data from.

Setup/Usage

1. Import the workflow: Download the provided JSON and import it into your n8n instance.
2. Configure Google Sheets Credential:
   • Click on the "Google Sheets" node.
   • Select or create a new Google Sheets OAuth2 credential.
   • Ensure the credential has the necessary permissions to write to your Google Sheets.
3. Configure HTTP Request:
   • Click on the "HTTP Request" node.
   • Enter the URL of the API you want to fetch data from.
   • Configure any necessary HTTP Method, Headers, or Authentication for your API.
4. Configure Edit Fields (Set):
   • Click on the "Edit Fields (Set)" node.
   • Adjust the fields to match the structure of your incoming JSON data and the desired output for Google Sheets and CSV. You can add, rename, or remove properties here.
5. Configure Google Sheets Node:
   • In the "Google Sheets" node, select the Operation (e.g., "Append Row").
   • Specify the Spreadsheet ID and Sheet Name where you want to add the data.
6. Configure Spreadsheet File Node:
   • The "Spreadsheet File" node is pre-configured to convert the data to CSV. You can adjust the File Type if you prefer Excel or other formats.
7. Activate the workflow: Once configured, activate the workflow to start processing data. You can trigger it manually for testing or set up a trigger node (e.g., a Cron node for scheduled runs) to automate it.