# Convert Google Sheets with numerous bundles to Json via aggregator

**URL:** <https://community.make.com/t/convert-google-sheets-with-numerous-bundles-to-json-via-aggregator/42033>\
**Category:** Beginner Questions\
**Tags:** aggregators, google-sheets, json\
**Created:** [June 17, 2024, 8:56am UTC](https://community.make.com/t/convert-google-sheets-with-numerous-bundles-to-json-via-aggregator/42033 "2024-06-17T08:56:10Z")\
**Posts on this page:** 12\
**Page:** 1

<div class="post-metadata">

**Author:** ![Jean\_Bonneau](https://avatars.discourse-cdn.com/v4/letter/j/db5fbb/32.png) [@Jean\_Bonneau](https://community.make.com/u/Jean_Bonneau)\
**Post date:** [June 17, 2024, 8:56am UTC](https://community.make.com/t/convert-google-sheets-with-numerous-bundles-to-json-via-aggregator/42033/1 "2024-06-17T08:56:10Z")

</div>

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](http://make.com).  
My scenario looks like this.

 ![image](https://europe1.discourse-cdn.com/flex013/uploads/make/original/3X/d/b/dba35c1bf12922f812d0f9ef3ba3e68b2296988e.png)

Here’s what I get with my excel module.

 ![image](https://europe1.discourse-cdn.com/flex013/uploads/make/original/3X/1/d/1dca954587b8fa740c165f17095d40f46bdc5b8d.png)

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

 ![image](https://europe1.discourse-cdn.com/flex013/uploads/make/original/3X/9/5/95a1a8a5f0e89d526f162cfd7c9e808975ede25f.png)

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.

 ![image](https://europe1.discourse-cdn.com/flex013/uploads/make/original/3X/2/a/2a7453ab609bac7f661bf33a9f40e1433608ac4f.png)

---

<div class="post-metadata">

**Author:** ![Benjamin\_from\_Make](https://dub1.discourse-cdn.com/flex013/user_avatar/community.make.com/benjamin_from_make/32/37894_2.png) [@Benjamin\_from\_Make](https://community.make.com/u/Benjamin_from_Make)\
**Post date:** [June 17, 2024, 10:07am UTC](https://community.make.com/t/convert-google-sheets-with-numerous-bundles-to-json-via-aggregator/42033/2 "2024-06-17T10:07:43Z")

</div>

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 “T`arget 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

---

<div class="post-metadata">

**Author:** ![Jean\_Bonneau](https://avatars.discourse-cdn.com/v4/letter/j/db5fbb/32.png) [@Jean\_Bonneau](https://community.make.com/u/Jean_Bonneau)\
**Post date:** [June 17, 2024, 10:32am UTC](https://community.make.com/t/convert-google-sheets-with-numerous-bundles-to-json-via-aggregator/42033/3 "2024-06-17T10:32:23Z")

</div>

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.

 ![image](https://europe1.discourse-cdn.com/flex013/uploads/make/original/3X/4/1/4116d5b204e32efac62cf70b0a9060031c085e01.png)  
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.

---

<div class="post-metadata">

**Author:** ![Benjamin\_from\_Make](https://dub1.discourse-cdn.com/flex013/user_avatar/community.make.com/benjamin_from_make/32/37894_2.png) [@Benjamin\_from\_Make](https://community.make.com/u/Benjamin_from_Make)\
**Post date:** [June 17, 2024, 10:46am UTC](https://community.make.com/t/convert-google-sheets-with-numerous-bundles-to-json-via-aggregator/42033/4 "2024-06-17T10:46:45Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Jean\_Bonneau](https://avatars.discourse-cdn.com/v4/letter/j/db5fbb/32.png) [@Jean\_Bonneau](https://community.make.com/u/Jean_Bonneau)\
**Post date:** [June 17, 2024, 12:24pm UTC](https://community.make.com/t/convert-google-sheets-with-numerous-bundles-to-json-via-aggregator/42033/5 "2024-06-17T12:24:18Z")

</div>

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](https://community.make.com/uploads/short-url/6h6jATkkVDtHh5jOSXpZaLmRWeP.json) (24.2 KB)

Here’s what my sheets looks like.

 ![image](https://europe1.discourse-cdn.com/flex013/uploads/make/original/3X/7/1/71e182c47de16f12c563a8c2ab538096440d9e25.png)

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”}  
]  
},  
…  
…  
]

---

<div class="post-metadata">

**Author:** ![Benjamin\_from\_Make](https://dub1.discourse-cdn.com/flex013/user_avatar/community.make.com/benjamin_from_make/32/37894_2.png) [@Benjamin\_from\_Make](https://community.make.com/u/Benjamin_from_Make)\
**Post date:** [June 17, 2024, 12:57pm UTC](https://community.make.com/t/convert-google-sheets-with-numerous-bundles-to-json-via-aggregator/42033/6 "2024-06-17T12:57:46Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Jean\_Bonneau](https://avatars.discourse-cdn.com/v4/letter/j/db5fbb/32.png) [@Jean\_Bonneau](https://community.make.com/u/Jean_Bonneau)\
**Post date:** [June 17, 2024, 1:26pm UTC](https://community.make.com/t/convert-google-sheets-with-numerous-bundles-to-json-via-aggregator/42033/7 "2024-06-17T13:26:48Z")

</div>

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](https://community.make.com/uploads/short-url/aWOO1xlxw3TpHqFSx9ZcIsgMALr.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](https://community.make.com/uploads/short-url/e4pGL8255CTY7nKJeZWGwYvoSdG.json) (23.3 KB)

---

<div class="post-metadata">

**Author:** ![Benjamin\_from\_Make](https://dub1.discourse-cdn.com/flex013/user_avatar/community.make.com/benjamin_from_make/32/37894_2.png) [@Benjamin\_from\_Make](https://community.make.com/u/Benjamin_from_Make)\
**Post date:** [June 17, 2024, 1:36pm UTC](https://community.make.com/t/convert-google-sheets-with-numerous-bundles-to-json-via-aggregator/42033/8 "2024-06-17T13:36:52Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Jean\_Bonneau](https://avatars.discourse-cdn.com/v4/letter/j/db5fbb/32.png) [@Jean\_Bonneau](https://community.make.com/u/Jean_Bonneau)\
**Post date:** [June 17, 2024, 1:58pm UTC](https://community.make.com/t/convert-google-sheets-with-numerous-bundles-to-json-via-aggregator/42033/9 "2024-06-17T13:58:42Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Benjamin\_from\_Make](https://dub1.discourse-cdn.com/flex013/user_avatar/community.make.com/benjamin_from_make/32/37894_2.png) [@Benjamin\_from\_Make](https://community.make.com/u/Benjamin_from_Make)\
**Post date:** [June 17, 2024, 2:30pm UTC](https://community.make.com/t/convert-google-sheets-with-numerous-bundles-to-json-via-aggregator/42033/10 "2024-06-17T14:30:12Z")

</div>

Hey!

Here is one way to do.

 ![image](https://europe1.discourse-cdn.com/flex013/uploads/make/original/3X/f/e/feb1a2958d54a1f208352a6b29e193e562ed323a.jpeg)  
Use Search Rows to pick the entire document data

 ![image](https://europe1.discourse-cdn.com/flex013/uploads/make/original/3X/0/3/03b337cd7f1e1da1a892cca61e4f4608335963db.jpeg)  
It generates bundles with data

 ![image](https://europe1.discourse-cdn.com/flex013/uploads/make/original/3X/2/1/212226519528ad878e5f32dbadcdf36eea98d6e6.jpeg)  
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

 ![image](https://europe1.discourse-cdn.com/flex013/uploads/make/original/3X/b/f/bfd9db23840f15c4cdfea012b1f01c02b416e95b.png)  
For the moment, your scenario should look like this

![2024-06-17_16-12-51 (1)](https://europe1.discourse-cdn.com/flex013/uploads/make/original/3X/a/1/a14c20b6bd364dc0976a5e9281838256e9659f46.gif)  
Add the Array Aggregator between the 2 modules

 ![image](https://europe1.discourse-cdn.com/flex013/uploads/make/original/3X/a/4/a414cb37824849053fdc258f9012c37f96729af2.png)  
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)](https://europe1.discourse-cdn.com/flex013/uploads/make/original/3X/d/3/d3039c1c5b25957b58fc26b232141ebda7cfa1bd.gif)  
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)](https://europe1.discourse-cdn.com/flex013/uploads/make/original/3X/7/e/7e3cec7c56fb032fe067022506546364d36dd8f6.gif)

Go back to the Create Json and map the resulting Value of the Aggregator  
 ![2024-06-17_16-23-21 (1)](https://europe1.discourse-cdn.com/flex013/uploads/make/original/3X/3/d/3d35e55cc365b42ec92f7f15931c6108e2389b5e.gif)

Run the scenario to see the result

 ![image](https://europe1.discourse-cdn.com/flex013/uploads/make/original/3X/1/2/12c7522cab85f2ac077d2a0f17f8045d98e4dc7c.jpeg)  
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.

 ![image](https://europe1.discourse-cdn.com/flex013/uploads/make/original/3X/3/7/37cddc0334aca675ef800437cb72cdc183684719.jpeg)

 ![image](https://europe1.discourse-cdn.com/flex013/uploads/make/original/3X/5/0/5022cbc09a43576986438fbd3bd0304e245a2b6f.jpeg)

 ![image](https://europe1.discourse-cdn.com/flex013/uploads/make/original/3X/f/c/fceca9baae0f5bd7b19a55bd09953404bb228278.png)  
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

---

<div class="post-metadata">

**Author:** ![hugoassuncao](https://dub1.discourse-cdn.com/flex013/user_avatar/community.make.com/hugoassuncao/32/357_2.png) [@hugoassuncao](https://community.make.com/u/hugoassuncao)\
**Post date:** [June 17, 2024, 11:24pm UTC](https://community.make.com/t/convert-google-sheets-with-numerous-bundles-to-json-via-aggregator/42033/11 "2024-06-17T23:24:54Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Make\_Bot](https://dub1.discourse-cdn.com/flex013/user_avatar/community.make.com/make_bot/32/14661_2.png) [@Make\_Bot](https://community.make.com/u/Make_Bot)\
**Post date:** [July 1, 2024, 2:49pm UTC](https://community.make.com/t/convert-google-sheets-with-numerous-bundles-to-json-via-aggregator/42033/12 "2024-07-01T14:49:05Z")

</div>