Get a JSON from two arrays and few singe values

Questions
, , , ,
1

Hi everyone,

I’m working on a scenario where I need to extract data from a Google Sheet and transform it into a structured JSON representing product variants (combinations of sizes and colors) along with the correct price per variant.

Here’s a simplified version of my sheet:

Sizes Colors Price S-XL Price XXL Price XXXL Low Price S-XL Low Price XXL Low Price XXXL
S, M, L, XL, XXL, XXXL White, Pepper, Black, Ivory, Mustard, Moos, Blue Jean, Berry, Violet, Blossom 32.96 34.88 38.20 27.68 29.59 32.91

Goal:

Generate a JSON with all possible combinations of sizes and colors, each with the correct price.

The logic for pricing is as follows:

  • If the color is “Mustard”, then apply the respective Low Price:

    • If size is XXL → use Low Price XXL

    • If size is XXXL → use Low Price XXXL

    • Else → use Low Price S-XL

  • For all other colors:

    • If size is XXL → use Price XXL

    • If size is XXXL → use Price XXXL

    • Else → use Price S-XL

Example Output Format (shortened):

[   
{     
"size": "S",     
"color": "White",     
"price": 32.96   },   
{     
"size": "XXL",     
"color": "Mustard",     
"price": 29.59   
},   
... ]

Question:

What’s the best way to implement this logic using a Google Sheets module and possibly iterators/routers? I’m a stuck since two days at how to loop through each combination and apply the conditional logic cleanly.

Any ideas, examples, or pointers would be super appreciated!

Thanks in advance