I am building a workflow that exports JSON data from a page to Excel, then modifications will be made in the excel file. In another workflow the excel is loaded into n8n again and the excel is transformed to JSON and therefore changed in the application.
I have trouble with the reading and writing of the Spreadsheet File node as the JSON input data is not the same as the JSON output data. Therefore the API where we have to put it does not recognise the format of the JSON.
I have an example of the data and the difference below.
This is a part of the JSON that is written to an excel file through the Spreadsheet File node. This is the format I would like to keep, as the application wants it in this format.
Then when I read the Excel file through the Spreadsheet file node (without modifying the data )
The output of the Write to File with the Spreadsheet File node is put in a totally different formatted JSON like this:
Is there a way to transfer it the same way? As I want to use the data in the workflow again to put it to an application and it needs the first format.
We will read the file as it is written which head of the header rows being used as a json key, If you open the excel file that will give you a good idea of how it will look once read.
The only thing I can think of here would be to add a processing step after the read to work out how the data should be structured to be used again which should be possible with a code node.
Hi Jon,
Thanks for your answer. I see what n8n does, but I do not understand why the format has to be changed?
Did you already see the shared workflow I added later or did you see it already?.
I always try not to use the code node, as my coding is not good… And with n8n thats almost always possible, I’ve tried transforming the data with edit field node and the aggregate into a single list, but without any success. Maybe one of you can help me out a little bit with transforming the data in the right format?
The format is changed because of how Excel and other spreadsheets display and store data. They tyically work with a header row then values under it which is how we store the data. Then we read it in the same way you would when you open the file in Excel which means anything other than that will require some logic specific to your data to display in the format you want.
Sadly this week I won’t have time to be able to spend writing that code as it is very specific to your workflow and not really something generic, If you did need help with it maybe another community member will have availability.
It might be worth giving it a go to see if you can do it
