Unable to update google sheet row with parsed JSON

How To
, , , ,
1

Hey fellow makers,

My automation is based around a an API call made to a recipe generator and stores it in a google sheet cell, in which I have used the HTTP module to make a GET call to the API with the following parameters:

timeFrame - week
targetCalories - <caloriemap
diet - <dietmap
exclude <foodallergymap

The output is a JSON string, and my main goal is to have it formatted and easily readable. The next step that I took with JSON string output was to JSON parse it, so instead of parsing it with a separate JSON parser module, I parsed it within the HTTP module as there is an option for it. Here is the output:

Capture
Capture526Ă—912 42.4 KB

I was one step closer to my goal in order for me to have a formatted output in my google sheet, I done some research online and came to the conclusion that once a JSON has been parsed it needs to be reiterated using the iterator module in order for you to map certain values in a string output or so. I click the iterator module, and in the array I mapped it to “week” the output seemed successful is what I thought:

2

3
3468Ă—897 29.5 KB

Then when I went to go create a final template for the formatted output and map the values, the iterator only showed two values which was “Total number of bundles” and “Bundle order position”. There was a solution to this issue on the iterator info/doc on make.com which was to do with the data structure in the JSON parser, I then attempted to try a separate JSON parser and create data structure which still did not work, I tried fiddling with values etc, still the same result.
4

Although when I map the array parameter in the iterator to “meals” of one of the days, it returned the single values correctly, although I was far from my end goal because I need to get the single values of all “meals” and “nutrients” from Monday to Sunday

5
5434Ă—535 28.7 KB

  • I tried ChatGPT to format the JSON string raw, but it was to much data to analyse at once and would cut the response short
  • I also tried to use multiple iterators to see if that would work but ended up taking 1000s of my operations

I’ve been stuck for multiple hours now, yes this is the reality of creating automations sometimes and i’m well aware, I just need a hand out of the mud.

1 Like
2

Did you put a text aggregator after the iterator?

I did it like this:

image
image779Ă—171 17.4 KB

I am now able to use the JSON string into the Google Sheets module.

1 Like
3

Hi @Koda_Sleiman

noticed that the iterator you’ve used requires an array as input, but it seems to have been mapped with a collection.
image
This is why it’s not displaying correctly.
Please try with converting the collection to array using to {{toArray()}}

image
image701Ă—269 25.5 KB

If you require additional assistance, please don’t hesitate to reach out to us.
MSquare Support
Visit us here
Youtube Channel

1 Like
4

@Msquare_Automation . It did output 7 bundles!, although it doesn’t show me the singular values, only “value”
Capture

1
1466Ă—937 35.8 KB

5

Hi @Koda_Sleiman

From the image I could see that you have getting value as array. To get a particular value from the array please try to use map function.
eg: {{73. value.id}}

If you require additional assistance, please don’t hesitate to reach out to us.
MSquare Support
Visit us here
Youtube Channel

2 Likes