Wrong column order when exporting from Google Sheets

[svzi]

Describe the problem/error/question

I need to create a CSV file with a specific delimiter (“;”) based on a template. I need to do this on a regular basis. The template has several columns that will be empty in each row, but must be included in the file.

As I have no MS 365 account and don’t want an active subscription, I decided to go with Google Sheets instead. I imported my template to Google Sheets and everything is fine. Now I collect my data and prepare it. Then I create a copy of my Google Sheets template and append the data.

When I open the file in the browser, everyhing is correct. It looks like this (just a small portion of the file):

When I download it right in the browser from the Google Sheets web app as Excel or CSV file everything is fine (except for the wrong delimiter as CSV). The columns are still in the correct order, like on the screenshot.

Now back to n8n. I’m using the “Convert to File” node twice here (just for demonstration purposes): one time I export to CSV file (with the correct delimiter), the other time I export to Excel. After the export, but files have messed up the column order. In both versions there comes first all the columns with data in it, then all the empty columns.

It probably depends on the already wrong displayed output of the Google Sheet node, which does the mess up:

As you can see here, the order is not the same is in my first screenshot. But for whatever reason, the Google Sheet itself is looking perfect fine (that was the first screenshot).

But n8n seems to work with the output of the Google Sheet node instead of the real data inside the Google Sheet file.

Best regards,
Sven

Information on your n8n setup

• n8n version: 1.76.1
• Database (default: SQLite):
• n8n EXECUTIONS_PROCESS setting (default: own, main):
• Running n8n via (Docker, npm, n8n cloud, desktop app): Docker
• Operating system:

[svzi]

Luckily I was able to solve it by myself, just wanted to note here for documentation reasons. Maybe another user will have a similar issue in the future and he gets some inspiration here on how to solve it.

After appending the data to the sheet (where the file itself was fine, but the output data of the node was messed up) I added two new nodes:

1. a new Google Sheets node to read the newly created sheet with the appended data. This nodes output the data in the correct order, like it is in the file.

2. an additional code node to remove the added column row_number and also to fix the number formatting. The data in my sheets is formatted for german numbers, e.g. “0,5” instead of “0.5”. For whatever reason the “read: sheet” node just ignores the correct values inside my Google Sheet and just converts it to “0.5”. For whatever reasons.

So a few hiccups noted here for such a simple tasks. Maybe these are bugs, but maybe it works as intended. I don’t know.

Best regards,
Sven