How to bulk map an array of values into multiple rows in same column in Google Sheet

[Ecco]

my objective is to map the following array from the http make a request module into a column in google sheets. whenever I map the output value from the http module into google sheets, it returns the whole array in a single cell. I want one value to be inserted into a single cell one by one.

output from the http make a request module is an array. I’ve used the aggregate to json, parse json / iterator, array aggregator and other methods but I’m doing something wrong because I can’t seem to get the results I want.

output from the http make a request module

[
    {
        "statusCode": 200,
        "headers": [
            {
                "name": "server",
                "value": "nginx"
            },
            {
                "name": "date",
                "value": "Wed, 24 Jul 2024"
            },
            {
                "name": "content-type",
                "value": "application/json; charset=utf-8"
            },
            {
                "name": "transfer-encoding",
                "value": "chunked"
            },
            {
                "name": "connection",
                "value": "keep-alive"
            },
            {
                "name": "x-frame-options",
                "value": ""
            },
            {
                "name": "x-xss-protection",
                "value": ""
            },
            {
                "name": "x-content-type-options",
                "value": "nosniff"
            },
            {
                "name": "x-download-options",
                "value": "noopen"
            },
            {
                "name": "x-permitted-cross-domain-policies",
                "value": "none"
            },
            {
                "name": "referrer-policy",
                "value": "strict"
            },
            {
                "name": "vary",
                "value": "Accept"
            },
            {
                "name": "access-control-allow-origin",
                "value": "*"
            },
            {
                "name": "access-control-allow-methods",
                "value": "GET, OPTIONS"
            },
            {
                "name": "access-control-allow-headers",
                "value": "*"
            },
            {
                "name": "access-control-max-age",
                "value": "1728000"
            },
            {
                "name": "etag",
                "value": "W/\"0682f6\""
            },
            {
                "name": "cache-control",
                "value": "max-age=0, private, must-revalidate"
            },
            {
                "name": "x-request-id",
                "value": "02ff68b9"
            },
            {
                "name": "x-runtime",
                "value": "0.002953"
            }
        ],
        "cookieHeaders": [],
        "data": [
            "1d8bd6a1-6500-4242-96a6-87f36f12d68f",
            "ac930a96-8e8e-4f12-a09f-eeeebf30c030",
            "dd20ccf5-358e-4132-bb1a-7042283d8d7b",
            "3fdccf60-6afe-4964-84c5-e8d1d8a4f2fb",
            "28c6e4f5-2e48-4ed9-9612-308f7b9c7e70",
            "07e95e1f-6a69-4c23-bf77-4e91da0baffc",
            "87360e45-97f0-4589-9d2d-5a25c68c0955",
            "720d1ef9-53e1-4fdd-86be-241bd5e3743e",
            "1b785974-9b9b-405c-8067-d3573d72d3e0",
            "18937891-0331-47b2-bc73-1346fee953c0",
            "a43c0702-7194-4bb0-90ca-438fa504bf1e",
            "16feaf61-0b81-4dac-86ab-2860badc9e1d",
            "b134dc28-800e-4ac1-afa8-043733c58092",
            "5415c328-bfa0-4c5f-88d4-d6065b700bb8",
            "e4a4f8a8-6e28-448a-9140-68eee1cc5463",
            "e6e83136-39ee-4915-a311-76644903683e",
            "7590e3ed-7e76-4f07-b74c-73e6ba9f41c5",
            "4c9c7ac1-8d11-42c0-8881-b2f3779536a5",
            "78136994-d34e-4961-8a25-abe3a90ad376",
            "04818721-b23a-407d-8ac4-11510cfdd65e",
            "a74b2e8c-4faa-4788-9b59-015363b5dcfd",
            "a8076a03-2754-4ad7-bfed-ea30a12905b9",
            "1ba249b4-c636-4797-9935-4103096aa242",
            "41b6322d-7964-4456-9a7d-dc0975ec96d8",
            "b7b97c42-343e-4503-bcf9-95c46bc7a194",
            "147d4932-7d12-45a8-980d-d73f9c649468",
            "b8153183-c232-4d2f-a70b-db76a084a847",
            "27f0c0fb-9fa6-4fe7-abc5-d1109658304c",
            "c86c1384-7936-4128-99a7-7720311437ae",
            "ecc41fa6-b185-4e11-8d53-e6fe362e2c40",
            "b09d240d-3ba3-4eed-b442-328f8feb1b4a",
            "cb076f6e-cb06-4722-9cb6-0e2da1a2fdd8",
            "0e61ae39-1574-45f7-a69a-803ca4c7976c",
            "f60b9f99-934c-4a3f-bf81-c6d08ca6e0be",
            "b7774504-c97e-4bdc-8177-0e8bce4e382c",
            "91f29b54-ebc0-4326-8e86-dffe66c51aa6",
            "eb550d0e-da8d-451b-b4f2-7a1fc852703c",
            "94538eb4-7573-44b3-a752-64bbf9e9cdce",
            "c742916d-1614-4a37-9355-5a15150b0484",
            "fbf69060-c8e8-4a38-89d5-f854de0bd963",
            "c7b12b9c-5115-4089-b4ec-90b7883d3cc8"
        ],
        "fileSize": 1600
    }
]

[samliew]

Welcome to the Make community!

Yes, that is possible. You’ll need a minimum of three modules (excluding your HTTP module):

Output

Give it a go and let us know if you have any issues!

Here is the “trick”:

1. Everytime you see an ARRAY, think ITERATOR

2. Then aggregate to “Target Structure Type”. More information found below:

Every result (item/record) from an ITERATOR module will output a bundle. To “combine” them into a single structure, 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 has applies to many use-cases.

There are other types of aggregator modules, click the below links to find out more:

• Array aggregator
• Text aggregator
• Numeric aggregator
• Table aggregator
• JSON aggregator
• CSV aggregator
• Archive (ZIP) aggregator
• other app-specific aggregators

Read This

The Array Aggregator module allows you to build a complex array of collections for a later module’s field to map multiple items (collections) to it.

Here is an example of using the “Target structure type” of an Array Aggregator module:

As you can see, the “Map” toggle on fields are used when you have an array. You can easily build an array variable to map to a field, by using an Array Aggregator module and select the “Target Structure Type” as the future field you want to map the array into.

samliew – request private consultation

Join the Make Fans Discord server to chat with other makers!

[samliew]

Breakdown of steps

1. Iterate Array from HTTP Module

2. Map Array from Array Aggregator, into “Rows” field

3. Map the “Value” from Iterator into the Column

samliew – request private consultation

Join the Make Fans Discord server to chat with other makers!

[Ecco]

thanks for your reply! I just have one question, is it possible to choose which column you want to bulk add rows to? I notice your example inputs the values into ‘Column 1’. I want to input the values into column # 13 ‘M’. Is this the correct way to do it?

[samliew]

Yes, but this will add NEW rows, not update existing rows.

If you want to update a range, you can use the other Bulk module:

In the above example, we want to start from row 2, and end up at the length of the array + (start_row_number - 1)

start_row_number was “2”, seen above (M2)

so (start_row_number - 1) = 1

samliew – request private consultation

Join the Make Fans Discord server to chat with other makers!

[Ecco]

thanks! yeah, let me try the second option. the first bulk add rows worked as expected but it added rows that I did not need and for some odd reason the process erased the original formatting on the whole spreadsheet lol. luckily I made a copy of the spreadsheet before running the scenario because I’ve learned from my past mistakes. Everything is back to normal now. I do want to update existing rows and not create new ones so let me try the second method you have provided.

edit 1 : second method worked perfectly. thanks for the help!