Append data to multiple new empty google sheet

Questions
1

Hi,

Very new here and very non techy.
I fetch data from a Google sheet, then run a code to create new sheets based on the number of “Dimensions” {{$json.dimension}} identified in the sheet. I then try to append data {{$json.table}} to those newly created google sheets. My issue is that the sheets are empty and the data cannot be appended since there are no headers.
I need to find a solution that will append headers and the data to each newly created sheet.

Workflow (last two nodes not working)

n8n setup

  • n8n version: 1.91.3
  • Database (default: SQLite): Default
  • n8n EXECUTIONS_PROCESS setting (default: own, main): own
  • Running n8n via (Docker, npm, n8n cloud, desktop app): n8n cloud
  • Operating system: Microsoft Windows 11 Pro
2

Hey @Arthur_Hascoet

I think the tricky part is that your data needs to be in an array of objects format in order to append it correctly to a Google Sheet.

For example, if your Code node outputs something like this:

{
  "table": [
    ["Channel", "ROI2", "ROI1", "ROI Evol", "iCPC", "iCVR", "iAOV", "i%spend", "iTotal"],
    ["Facebook", 3.1, 2.8, 0.3, 0.05, 0.1, 0.02, 0.13, 0.3],
    ["Google", 2.6, 3.0, -0.4, -0.12, -0.1, -0.05, -0.13, -0.4]
  ]
}

You need to convert this into an array of key-value JSON objects like:

{ "Channel": "Facebook", "ROI2": 3.1, "ROI1": 2.8, ... }
{ "Channel": "Google",  ... }

This way, when you use the Google Sheets node with “Append” and set the “Column Mapping” mode to “Map Automatically”, it will:

  • Create the header row if it doesn’t already exist.
  • Append the values under the correct columns.

Since we’re transforming the table into an array of objects, n8n will automatically iterate over each item and append them one by one.

Sorry if my explanation isn’t clear, I understand what you’re trying to achieve, and I believe this will solve your issue.

Here is the working workflow for your case, just tweak it to fit your data.

image
image943×162 11.4 KB

2 Likes
3

Thanks @mohamed3nan .
Your solution works on your data, but I can’t seem to make it work for mine. I believe this is because there are multiple tabs involvded dynamically.

In the Append data node, I now get the error
"Multiple matching items for expression [item 0]
The code here won’t work because it uses .item and n8n can’t figure out the matching item. (There are multiple possible matches)

Try using .first(), .last() or .all()[index] instead of .item or reference a different node.

Any idea how II’d be able to fix this?

4

Hi @Arthur_Hascoet,

Yes, it’s working on my data, unfortunately, you didn’t attach any sample inputs, so I had to guess the structure based on your logic.

By the way, I now understand your point more clearly and was able to reproduce the error you mentioned. This helped me better grasp the issue and use case.

I updated the Transformation Code to use the mode: “Run once for each item”, which I believe is the key detail. With this change, the code returns items with the “dimension” field and loops over the “mappedRows” for each one. These rows are then formatted so Google Sheets can process them properly.

As a result, each item now creates a separate sheet and populates it with the corresponding data.

Please give it a try and let me know if you encounter any issues

5

This is a better version to make it more logical