I have multiple collections with data that represent marketing report pulled by one of the services from their API. The structure is on the screenshot. I basically have an Array called “items” . Each item contains of multiple collections under “dimensions” and “metrics” (similar to old Google Analytics)
I want them eventually to populate rows in Google Sheet with the first row being the header row (dimension names followed by metrics names) and then there should be the values of dimensions and metrics - each row is 1 item from the source. In the source metrics collections are numbered 1,2,3 etc while dimensions are named by some names (dynamic)
Every result (item/record) from iterator/list/search/match modules will output a bundle. This can result in multiple bundles, which then trigger multiple operations in future modules (one operation per bundle). To “combine” multiple bundles into a single variable, you’ll need to use an aggregator of some sort.
Aggregators are modules that accumulate multiple bundles into one single bundle. An example of a commonly-used aggregator module is the Array aggregator module. The next popular aggregator is the Text Aggregator which is very flexible and can apply to many use-cases like building of JSON, CSV, HTML.
There are other types of aggregator modules, click the below links to find out more:
Array Aggregator – mapping multiple bundles into a complex field
The Array Aggregator module is very powerful because it allows you to build a complex array of collections for a later module’s field to map multiple items (collections) to it.
This is done using the “Target structure type” of an Array Aggregator module.
As you can see, the “Map” toggle on complex fields are used when you have an array. You can easily build an array variable to map to a future module’s field, by using an Array Aggregator module and select the “Target Structure Type” as the future module’s field you have mapped the array into.
Hope this helps! Let me know if there are any further questions or issues.
@Anton5, are you using ‘update in bulk’ module or ‘add rows in bulk’ ? header row kinda goes with this, you have to input target range in sheet module. you could create an array of headers if it’s not included in input data, and merge with this data. like :
Regarding the module name - you were right)) I used bulk add instead of update.
As for the header row it’s not that simple. The format is really tricky for this Google Sheets module.
The Rows array (output of Array Aggregator) looks like this
it’s not a simple array. It’s array consisting of collections. Each collection is an array named values.
So I have to somehow add 1 item at the top of this array which should be a collection of 1 item, this 1 item being an array called values, something like this
{“values”:[“marker_level_1”,“marker_level_2”,“visits”,“leads”,“sales”,“profit”,“marketing_cost”,“roi”]}
The question is - how to merge this into the Rows array?
This will probably be a bundle (collection) consisting of array named “values”. You need to append this object to existing Array. probably add(newjson;oldarray) will work. Else might have to fiddle with toArray() toCollection() and merge() and tweak the json a little bit. ask chatgpt maybe.
Thank you brother! I finally achieved the desired result by using JSON Parser module to create header row. It consists Array included in Collection included in another Array
This array I now can merge to data array in Google Sheets module
Don’t forget to bookmark this topic so you can get back to it easily in future!
I also recommend completing the Make Academy if you haven’t yet.
Here are some useful links and guides you can use to learn more on how to use the Make platform, apps, and app modules. I found these useful when I was learning Make, and hope they might benefit you too —
