Convert Google Sheets with numerous bundles to Json via aggregator

[Jean_Bonneau]

Hello everyone,
I am trying to transform an excel sheet into a Json. The Sheets contains numerous info about numerous business partners. I’m new to make.com.
My scenario looks like this.

Here’s what I get with my excel module.

But I only get this input to my Create Json module:

I don’t understand how to get all the values inside of the one bundle output by the aggregator.
Here’s what I asked of the aggregator. It might related but I am unable to find the structure of the expected Json inside the aggregator.

[Benjamin_from_Make]

Hello! Welcome to the Make community!

The Array Agregator is very specific compared to other Make modules.
If you want to aggregate multiple bundles into an Array of your Json doc, you need to:

• First add the Json/Create a Json module, and create/select the Data Structure of your Json document,
• and only then, add an Array Aggregator between the 2 modules, just before the Json module.

It will automatically detect all Arrays in your JSON data structure, and you will be able to select the Array you want to convert to in the “Target Structure type” of the Array Aggregator.
Then, in the Create Json, for your array, you will use the little “map” switch; you will be able to map the output of the Array Aggregator.

If I’m not clear, leat me know, I will build a little example for you

Benjamin

[Jean_Bonneau]

I’m really sorry but I don’t understand what you ae trying to say. I understand the mapping box you are talking about.
For the structure type problem I am still unable to assign a structure type after creating a new scenario and placing the create JSON before the aggregator.

Once the assignation is done would I be able to get all my arrays of my aggregator inside my create JSON module?
I’d really like an example right now please if you can.

[Benjamin_from_Make]

Sure,

If you want it to work:

• the Array Aggregator has to be put after you added the Create Json. And sometimes, it’s better to unlink, save refresh page, and redo, because it doesn’t refresh correctly
• the JSON structure has to have one or more Arrays of collections. Since you want to aggregate into one single document.

If ever you are still stuck, can you give me an example Json document showing what document you want to generate?

Benjamin

[Jean_Bonneau]

I tried everything but my aggregator doesn’t find any data structure, even though I have some in my Json module. I’m starting to think that something more is needed. Should the name of the output keys be the same as the name of the keys of my Json?

Here what my scenario looks like :
blueprint(2).json (24.2 KB)

Here’s what my sheets looks like.

And how my Json should look like:
[
{
“offres_partenaire”: [
{ “reference”: “QFYI”, “allergenes”: [0,0,0,0,0,0,0,0,0,0,0,0,0,0], “accessoire”: “”, “id”: 0, “class”: “Animation”, “nom”: “1”, “titre”: “E…”, “description”: “…”, “cost”: 1.5, “qty”: 1, “unit”: “Animation”, “actif_from”: 1, “option_from”: -1, “option_cost”: 1, “type_prestation”: [1,1,1], “remove_price”: 0.5, “temp”: “”, “img”: “…jpg”},
{ “reference”: “QFYI”,“allergenes”: [0,0,0,0,0,0,0,0,0,0,0,0,0,0], “accessoire”: “”, “id”: 1, “class”: “Animation”, “nom”: “2”, “titre”: “…”,“description”: “…”, “cost”: 2.5, “qty”: 1, “unit”: “Animation”, “actif_from”: 1, “option_from”: -1, “option_cost”: 1, “type_prestation”: [1,1,1], “remove_price”: 0.75, “temp”: “”, “img”: “…jpg”}
]
},
{
“offres_partenaire”: [
{ “reference”: “QFYI”,“allergenes”: [0,0,0,0,0,0,0,0,0,0,0,0,0,0], “accessoire”: “verre_soft”, “id”: 1, “class”: “Spiritueux”, “nom”: “1”, “titre”: …", “description”: “…”, “cost”: 4, “qty”: 2, “unit”: “Verres”, “actif_from”: 50, “option_from”: -1, “option_cost”: 1.8, “type_prestation”: [1,1,1], “remove_price”: 0.9, “temp”: “”, “img”: “…jpg”},
{ “reference”: “QFYI”,“allergenes”: [0,0,0,0,0,0,0,0,0,0,0,0,0,0], “accessoire”: “verre_soft”, “id”: 2, “class”: “Boisson”, “nom”: “2”, “titre”: “…”, “description”: “…”, “cost”: 0.5, “qty”: 1, “unit”: “Verres”, “actif_from”: 50, “option_from”: -1, “option_cost”: 1.8, “type_prestation”: [1,1,1], “remove_price”: 0.9, “temp”: “”, “img”: “…jpg”}
]
},
…
…
]

[Benjamin_from_Make]

Thanks!

May I ask you a few questions:

• in Allergenes, we see an array of numbers. Where do you get this info from? In the Spreadsheet, we only see a 0, and it becomes [0,0,0,0…]
• If I get it right, the “offres_partenaires” is grouped by “reference”; which means you have a global array that contains “offres_partenaire”, and each “offres_partenaire” contains an array of collection with all the data coming from the column in Gsheet, right?
• Can you share a sample Google Sheet with data? (maybe a CSV file that contains the header and data)

Cheers

Benjamin

[Jean_Bonneau]

Thanks for your time.

• The “allergenes” key comes from the B to O rows (allergènes 1-allergènes 2-…3-…-allergènes 14)

• You’re right, this is part of a bigger Json and the offers all have a reference binding them to a producer.

• Here’s the csv with the 4 first offers.
  Tempo1.csv (1.6 KB)

reference	allergenes__001	allergenes__002	allergenes__003	allergenes__004	allergenes__005	allergenes__006	allergenes__007	allergenes__008	allergenes__009	allergenes__010	allergenes__011	allergenes__012	allergenes__013	allergenes__014	accessoire	id	class	nom	titre	description	cost	qty	unit	actif_from	option_from	option_cost	type_prestation__001	type_prestation__002	type_prestation__003	remove_price	temp	img
8F29	0	0	0	0	0	0	0	0	0	0	0	0	0	0		0	Animation	1 Colonne	Ensemble de légumes et fruits frais, sains ultra locaux cultivés en hydroponie	Légumes et fruits frais, sains, savoureux ultra locaux	1.5	1	Animation	1	-1	1	1	1	1	0.5		https://…/Niel.jpg
8F29	0	0	0	0	0	0	0	0	0	0	0	0	0	0		1	Animation	1 Colonne	Tenue d’un stand de légumes et fruits frais, sains ultra locauxcultivés en hydroponie	Tenue d’un stand de légumes et fruits frais, sains ultra locauxcultivés en hydroponie	2.5	1	Animation	1	-1	1	1	1	1	0.75		https://…/uploads/…/Niel.jpg
QFYI	0	0	0	0	0	0	0	0	0	0	0	0	0	0	verre_soft	1	Spiritueux	Colonne 1	Saké japonais (alcool à base de riz), authentique et élégant	Saké japonais, raffiné, authentique, élégant	4	2	Verres	50	-1	1.8	1	1	1	0.9		https://…/uploads/…/Niel.jpg
QFYI	0	0	0	0	0	0	0	0	0	0	0	0	0	0	verre_soft	2	Boisson	Colonne 2	Citronade et Thé glacé japonais, raffiné, élégant	Citronade et Thé glacé japonais, raffiné, élégant	0.5	1	Verres	50	-1	1.8	1	1	1	0.9		https://…fr/uploads/…/Niel.jpg

Here’s my scenario
blueprint(3).json (23.3 KB)

[Benjamin_from_Make]

Great thanks!
Let me give it a try.

Just one question; is there a reason why you used Google Sheet / Get Range Values? Why not use Google Sheet / Search Rows? It’s simpler since it generates names fields from the Header of the document.

Benjamin

[Jean_Bonneau]

No I used this not knowing your module.

I discovered the aggregate as Json module, and realise now I might be overthinking.
I replaced the 2 last modules by an aggregate as Json.
But I still don’t understand why I don’t find any data structure in my aggregate.

[Benjamin_from_Make]

Hey!

Here is one way to do.

Use Search Rows to pick the entire document data

It generates bundles with data

Then you add “Create Json” and select your data structure (sorry for the name that is not AT ALL what data you are handling )

Don’t change anything else (yet), and click OK

For the moment, your scenario should look like this

Add the Array Aggregator between the 2 modules

You should be able to see “Offres Partenaires”. (I generated the Data Structure from the example you gave me)

Map all fields. - Little Tips&trick bellow for the 2 arrays

you select “map”, you add the items, with a coma between each. When you click map again, it will separate into items.

And the most important is the “Goup By”

Go back to the Create Json and map the resulting Value of the Aggregator

Run the scenario to see the result

As you see, it generated 2 bundles, each one for each reference. With Make you will not directly be able to generate an Array with the 2 collections, since the Array is from the Root of the document, so, one way to do is to use a Text Aggregator and then use the result in your target API. Here is an example, but it will depend on waht you want to do with the data.

Here is the full document.

So the last 2 steps will depend on what you want to do with the generated data.

I hope if helps

Benjamin

[hugoassuncao]

Another option to this would be to use the Text Aggregator module, to write each array object and join them with a comma - you could then wrap that value with the open and closing tags for your json object.

But that is just an option to a problem already beautifully solved.