I have this formatted JSON by the n8n itself and I would like to save this format and date to some xls or any other spreadsheet file.
How can I achieve that?
This is exactly what I did, but the issue is that it doesn’t save the table in the exact format as represented in the left part of the screenshot above
Hi @Ruslan_Yanyshyn, the Spreadsheet File node (just like most of the formats it support) don’t have a concept of nested fields equivalent to the JSON data structure you currently have.
This is why you will typically get a flat spreadsheet with column headers such as purchased_fuel.AB or distance.MB here. To avoid this you would need to convert these JSON objects into a simple string.
You can do this by using expressions such as {{ JSON.stringify($json.purchased_fuel) }} in an Edit Fields (Set) node before your Spreadsheet File node, for example like so:
Hi @MutedJam, thank you for your response. My goal is to transfer the same visual representation (including format and view) to an Excel spreadsheet. The intention is for the Excel file to mirror the appearance of the left table, creating additional rows for each number that correspond to the number of provinces in this specific case
Hi @Ruslan_Yanyshyn, I am afraid n8n does not provide formatting options on the Spreadsheet node (or its successor) do not allow formatting.
So you’d currently have to write custom code to achieve a specific formatting. A basic example on how to achieve this would be available at Google Spreadsheet to HTML - Variant with JS Function | n8n workflow template. This uses Google Sheets as the data source, but can of course be adjusted to other data sources as well.
Hey @MutedJam,
Thank you once again for your support! I have successfully created an HTML table, but I realized that developing a spreadsheet version could also be beneficial. However, formatting a spreadsheet requires a different approach compared to an HTML table and I don’t really know where to start from.