Spreadsheet node: needed input to have multiline output

Hi there,

I am trying to transform processed data from a function node to a spreadsheet with the help of the spreadsheet node.

However, I am unable to receive anything from the spreadsheet node, which isn’t flat.

I created an array of rows, from which the first one should be the headline.
[“headline1”,“headline2”][“data row 1”,“data row 1 second variable”] …

Instead of having a table, the first row indicates just where the data comes from
“data.0.0 data.0.1 data.0.2 data.0.3 data.0.4 data.0.5 data.0.6 data.1.0 data.1.1 data.1.2 data.1.3 data.1.4 data.1.5”
and the actual data is line 2.

How does the spreadsheet node expect the data to be formatted?

Any suggestion appreciated.

Everything in n8n is normally expected to be an object with a key -> value pair. So the format has to be:

[
  {
    headline1: 'row 1',
    headline2: 'row 1',
  },
  {

    headline1: 'row 2',
    headline2: 'row 2',
  }
]

So here a node-example:

{
  "nodes": [
    {
      "parameters": {
        "functionCode": "return [\n  {\n    json: {\n      headline1: 'row 1',\n      headline2: 'row 1',\n    }\n  },\n  {\n    json: {\n      headline1: 'row 2',\n      headline2: 'row 2',\n    }\n  }\n];"
      },
      "name": "Function",
      "type": "n8n-nodes-base.function",
      "typeVersion": 1,
      "position": [
        400,
        300
      ]
    },
    {
      "parameters": {
        "operation": "toFile",
        "options": {}
      },
      "name": "Spreadsheet File",
      "type": "n8n-nodes-base.spreadsheetFile",
      "typeVersion": 1,
      "position": [
        550,
        300
      ]
    }
  ],
  "connections": {
    "Function": {
      "main": [
        [
          {
            "node": "Spreadsheet File",
            "type": "main",
            "index": 0
          }
        ]
      ]
    }
  }
}

Hey Jan,
thanks for your quick answer.

Your example does work. BUT:
I tried it as “array of objects” before but can’t get it to work.
I process my object somewhere else and just want to pass it on.
If i return it as “array with json” it turns out as described above already. Just altered your example slightly.

array = [
  {
headline1: 'row 1',
headline2: 'row 1',
  },
  {

headline1: 'row 2',
headline2: 'row 2',
  }
];
return [{json: array}];

Turns out like this

|0.headline1|0.headline2|1.headline1|1.headline2|
|row 1|row 1|row 2|row 2|

How can i Pass an array of objects to behave as your example?

You can convert it to different items like this:

{
  "nodes": [
    {
      "parameters": {
        "functionCode": "items[0].json.dataArray = [\n  {\n    headline1: 'row 1',\n    headline2: 'row 1',\n  },\n  {\n\n    headline1: 'row 2',\n    headline2: 'row 2',\n  }\n];\nreturn items;"
      },
      "name": "Function",
      "type": "n8n-nodes-base.function",
      "typeVersion": 1,
      "position": [
        400,
        300
      ]
    },
    {
      "parameters": {
        "functionCode": "return items[0].json.dataArray.map(item => { return {json: item} });"
      },
      "name": "Function1",
      "type": "n8n-nodes-base.function",
      "typeVersion": 1,
      "position": [
        550,
        300
      ]
    }
  ],
  "connections": {
    "Function": {
      "main": [
        [
          {
            "node": "Function1",
            "type": "main",
            "index": 0
          }
        ]
      ]
    }
  }
}

The data it’s not returned the right way. Check the example below. You are putting the array on the JSON property, but each row of the array should be under an individual JSON property.

{
  "nodes": [
    {
      "parameters": {},
      "name": "Start",
      "type": "n8n-nodes-base.start",
      "typeVersion": 1,
      "position": [
        250,
        300
      ]
    },
    {
      "parameters": {
        "functionCode": "const result = [];\n\nconst data = [\n  {\nheadline1: 'row 1',\nheadline2: 'row 1',\n  },\n  {\n\nheadline1: 'row 2',\nheadline2: 'row 2',\n  }\n];\n\nfor (const d of data) {\n  result.push({ json: d })\n}\n\nreturn result\n"
      },
      "name": "Function",
      "type": "n8n-nodes-base.function",
      "typeVersion": 1,
      "position": [
        500,
        300
      ]
    }
  ],
  "connections": {}
}
1 Like

Thank you both!
Learned again a bit more. The message from @RicardoE105 solved entirely!

1 Like