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:

Hope this helps :slight_smile:

1 Like

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

2 Likes