Json arrays : split "comma separated string" into google sheets rows

Hi

As a result of email parsing, my HTML extract node output a json that has a multidimensional array separated with a comma like this :

image

I’d like to update or append or create new sheet on a google spreadsheet that has for 2 columns reference and price and each of the values from the json in its own row :

My current workflow :

image

Thank you in advance for your help.

Hi @Uness, welcome to the community!

So essentially, you want to turn one item looking like this:

{
  "reference": ["foo", "bar"],
  "cost": ["$1.99", "$2.99"]
}

into multiple items like this:

[{
  "reference": "foo",
  "cost": "$1.99"
}, {
  "reference": "bar",
  "cost": "$2.99"
}]

This would be possible using the Function node and a JavaScript snippet like this:

// Create array to hold our newly created items
let results = [];

// Loop through all input items (which have array values in the reference and cost fields)
for (item of items) {
  // Loop through the number of items in the reference array for the current item
  for (let i = 0; i < item.json.reference.length; i++) { 
    // Check if we also have a corresponding value in the cost field    
    if (item.json.cost[i]) {
      // Push the result to our results
      results.push({
        json: {
          reference: item.json.reference[i],
          cost: item.json.cost[i]
        }
      });
    }
  }
}

// Return the results to the next node
return results;

If you only have one field which needs splitting up, you could also use the Split Out Items operation of the Item Lists node.

I hope this helps! Please do give me a shout if you run into any trouble with this.

2 Likes

Thank you so much @MutedJam for taking your time to replay.

The function node you have sent me works perfectly
Now i’m getting my data in a way that is understood by google sheet.

image

And the result is :
image

Thank you soo much for your help. You are awesome.

1 Like

You are most welcome, I am glad to hear this works :slight_smile:

3 Likes

Hi @Uness, I hope you’re well? I quickly wanted to follow up on this as I was discussing your task with @harshil1712 a few minutes back. He suggested using multiple Item Lists nodes here to replace the JavaScript snippet. So if you prefer an approach without using the Function node, this would be a possible way to achieve this:

{
  "nodes": [
    {
      "parameters": {
        "functionCode": "return [{\n  json: {\n    \"reference\": [\"foo\", \"bar\"],\n    \"cost\": [\"$1.99\", \"$2.99\"]\n  }\n}];"
      },
      "name": "Set Sample Data",
      "type": "n8n-nodes-base.function",
      "typeVersion": 1,
      "position": [
        450,
        300
      ]
    },
    {
      "parameters": {
        "fieldToSplitOut": "reference",
        "options": {}
      },
      "name": "Item Lists",
      "type": "n8n-nodes-base.itemLists",
      "typeVersion": 1,
      "position": [
        650,
        200
      ]
    },
    {
      "parameters": {
        "fieldToSplitOut": "cost",
        "options": {}
      },
      "name": "Item Lists1",
      "type": "n8n-nodes-base.itemLists",
      "typeVersion": 1,
      "position": [
        650,
        400
      ]
    },
    {
      "parameters": {
        "mode": "mergeByIndex"
      },
      "name": "Merge",
      "type": "n8n-nodes-base.merge",
      "typeVersion": 1,
      "position": [
        850,
        300
      ]
    }
  ],
  "connections": {
    "Set Sample Data": {
      "main": [
        [
          {
            "node": "Item Lists",
            "type": "main",
            "index": 0
          },
          {
            "node": "Item Lists1",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Item Lists": {
      "main": [
        [
          {
            "node": "Merge",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Item Lists1": {
      "main": [
        [
          {
            "node": "Merge",
            "type": "main",
            "index": 1
          }
        ]
      ]
    }
  }
}

Hope this helps :slight_smile:

1 Like

Thank you @MutedJam
The solution from @harshil1712 is also working.

2 Likes