Convert Google Sheets with numerous bundles to Json via aggregator

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
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.

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



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.


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?


1 Like

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”}


1 Like


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)



1 Like

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)

1 Like

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.


1 Like

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.


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 :sweat_smile:)

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

For the moment, your scenario should look like this

2024-06-17_16-12-51 (1)
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

2024-06-17_16-19-51 (1)
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”
2024-06-17_16-22-00 (1)

Go back to the Create Json and map the resulting Value of the Aggregator
2024-06-17_16-23-21 (1)

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



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.