Automate Excel Data Handling with n8n: Import, Transform, Export

1. Opening Problem Statement

Meet Sarah, a data analyst at a mid-sized e-commerce company. Each week, she receives Excel spreadsheets containing customer data from multiple sources — sometimes from cloud platforms like Google Drive or OneDrive, sometimes via HTTP URLs, and occasionally from local files. Sarah needs to calculate customer ages based on their birthdates, update the spreadsheets, and then upload the new files back to multiple destinations for her sales and marketing teams.

This process is consuming hours of her time every week, involving multiple downloads, file conversions, age calculations, and re-uploads. Manual errors often creep in, causing data inconsistencies and delays that impact campaign timing. Sarah is eager to automate this Excel data handling process to save time and ensure reliable accuracy.

2. What This Automation Does

This n8n workflow is designed specifically to work with Excel and spreadsheet files by automating the entire process of importing, transforming, and exporting spreadsheet data.

When this workflow runs, it achieves the following:

• Imports Excel files dynamically from various sources such as HTTP URLs, Google Drive, Microsoft OneDrive, or local files.
• Converts imported spreadsheet files into JSON format for easy data manipulation within n8n.
• Performs data transformations like calculating customer age from birthdate fields.
• Converts the updated data back into an Excel (.xlsx) file with a timestamped filename.
• Optionally uploads or saves the new file to local storage, SFTP, or cloud services like Google Drive or OneDrive.
• Saves time and eliminates manual errors while managing spreadsheet workflows across different systems.

3. Prerequisites ⚙️

• n8n account with access to create workflows ⚙️
• Access to cloud platforms Google Drive and Microsoft OneDrive, if you want to automate cloud file downloads/upload 🔐
• HTTP access to fetch files via URL (optional)
• Local filesystem access for reading/writing files (optional)
• FTP/SFTP server credentials if uploading files remotely (optional)

4. Step-by-Step Guide

Step 1: Manually Trigger the Workflow

In n8n, start by creating a new workflow and add a Manual Trigger node. This lets you execute the workflow on-demand for testing.
Navigate: Click “+” → Search “Manual Trigger” → Add

Outcome: You’ll see a trigger node named “On clicking ‘execute'” ready to start the workflow.

Common mistake: Forgetting to use the manual trigger during testing can cause confusion as the workflow won’t start automatically.

Step 2: Download Excel File from HTTP URL

Add an HTTP Request node named “Download Excel File”.
Navigate: Click “+” → Search “HTTP Request” → Add.
Set Method to GET and paste your file URL, e.g., https://internal.users.n8n.cloud/webhook/709a234d-add7-41d2-9326-8d981f58120b.

This node fetches the spreadsheet file into the workflow for further processing.

Common mistake: Using incorrect URL formats or not having public access to the file causing download failure.

Step 3: Import Excel from Cloud Storage (Optional)

If you want to fetch files from Google Drive or Microsoft OneDrive instead, add the corresponding nodes:

• Google Drive Node: Set to download the Excel file by specifying File ID.
  Ensure you have connected credentials for Google Drive.
• Microsoft OneDrive Node: Set operation to download and specify File ID.
  Connect Microsoft credentials accordingly.

These nodes seamlessly integrate private cloud files into your workflow.

Step 4: Read the Spreadsheet File

Add a Spreadsheet File node named “Read Spreadsheet File”.
This node converts the binary data of the Excel file into JSON format for manipulation.
Settings: Default options usually suffice.

Outcome: You get structured JSON data of rows and columns accessible in following nodes.

Step 5: Calculate Customer Age Using Set Node

Add a Set node named “Work out Age”.
Use the expression editor to calculate age based on the birthday field in your data.

={{ Math.trunc($today.diff(DateTime.fromFormat($json["created"], 'yyyy-MM-dd'), 'years').toObject().years) }}

This expression uses Luxon DateTime to find the full years’ difference between today’s date and the customer’s birthdate stored in the “created” field.

Common mistake: Make sure your date fields match the format and field names exactly or the formula won’t compute correctly.

Step 6: Convert Data Back to Excel File

Add another Spreadsheet File node named “Write Spreadsheet File”.
Set operation to “toFile”.
Set file name using an expression like =customer-datastore_{{$today.toFormat('yyyyMMdd')}}.xlsx for dynamic daily filenames.

Outcome: Your transformed data is saved as a downloadable Excel file in the workflow.

Step 7: Save or Upload the Resulting File

This step is optional depending on your needs. You can:

• Add a Write Binary File node to save the file locally.
  Set file name: =/tmp/{{$binary.data.fileName}}
• Add an SFTP node to upload the file remotely.
  Connect with your SFTP credentials and specify remote path.
• Use Google Drive or Microsoft OneDrive nodes to upload the file back to cloud storage.

Choose your preferred storage or delivery option to complete the workflow.

5. Customizations ✏️

• Change Source File Type: In the HTTP Request or cloud storage node, update the URL or File ID to process different files.
• Modify Age Calculation: Adjust the Set node’s expression to calculate different date-related fields like tenure, days until expiry, etc.
• Add Data Validation: Insert a Code or Function node after reading data to validate or filter rows before writing back.
• Toggle Upload Destination: Enable or disable upload nodes like SFTP or cloud nodes depending on where you want to save the completed file.
• Automate Workflow Trigger: Replace the Manual Trigger node with a Cron node to run this process on a schedule.

6. Troubleshooting 🔧

Problem: “File not found or permission denied when downloading from cloud storage.”
Cause: Incorrect File ID or expired credentials.
Solution: Verify File ID in Google Drive/OneDrive and refresh OAuth credentials in n8n settings.

Problem: “Age calculation not working, error in Set node.”
Cause: Date field format mismatch.
Solution: Check the exact formatting of the date in your spreadsheet, update the expression accordingly.

Problem: “File save/upload failed.”
Cause: Incorrect path or credentials.
Solution: Confirm file paths and verify credentials for SFTP or cloud uploads.

7. Pre-Production Checklist ✅

• Test manual execution with sample Excel files from each source (HTTP, Google Drive, OneDrive, local).
• Verify data integrity after reading spreadsheet (print JSON output in logs).
• Confirm calculated fields (like age) are accurate.
• Ensure file writes complete without errors and files upload successfully.
• Back up original files before running automated jobs.

8. Deployment Guide

Activate your workflow by clicking the “Activate” button in n8n once tested.
Set up scheduling if you want regular runs, replacing the manual trigger with the Cron node.
Monitor executions through n8n’s execution list to confirm success or catch errors early.

10. Conclusion

By following this workflow, you automated importing Excel files from multiple sources, transforming your data with precise age calculations, and exporting updated spreadsheets back to various storage locations.
You’ve saved potentially hours each week by eliminating manual downloads, conversions, and uploads, and gained reliable data accuracy reducing costly errors.
Next, consider automations to integrate updated Excel data directly into dashboards, send summary reports via email, or notify teams in Slack when new files are ready.

Happy automating with n8n!