Data from GSheets to OpenAI as JSON string – Mission Impossible?

Andy4 · April 16, 2025, 3:06pm

Hi everyone,
I’m facing a technical challenge with an integration on Make.com and I’m hoping someone here can help.

Use case:
I’m using the GSheets > Make an API call module to extract data from two columns in a Google Sheets file. My end goal is to generate a JSON object structured as follows:

json

CopiaModifica

{
  "output": [
    { "key": "colA", "value": "colB" },
    { "key": "colA", "value": "colB" },
    ...
  ]
}

Where:

Values from column A in Google Sheets are the "key"
Values from column B are the "value"

Planned strategy:
The JSON creation is meant to be handled directly by the OpenAI module (trying to keep things simple on my end).

Issue encountered:
The OpenAI module only accepts string inputs, while the data coming from the GSheets module is returned as a nested array.

I tried introducing an Iterator to process the data, but it only returns the first key-value pair, and fails to loop through all the elements from GSheets. I suspect the issue lies in how the array is structured or how the iterator handles it.

Goal:
Properly convert the extracted data from GSheets into a string formatted according to the JSON structure above, and pass it correctly to the OpenAI module.

Bonus:
Calling for help from the legendary @samliew (you know who you are!) — I could really use a hand solving this before my brain explodes

Thanks in advance for any suggestions or assistance!Preformatted text

Andy4 · April 17, 2025, 2:28pm

please guys, i don’t know how to get out of this
@samliew
@Msquare_Automation
@Donald_Mitchell
@alex.newpath

Donald_Mitchell · April 17, 2025, 2:52pm

I would say use an Iterator on your Array coming out of Google Sheets, followed by an aggregator.
If you want to pass text to OpenAI, use Text Aggregator.
If you want to pass JSON, use JSON aggregator.
You might even try using a Transform to JSON module on the array Instead of an Iterator-Aggregator combo.

It’s a bit difficult to say since it’s not 100% clear how your data needs to be formatted going in to OpenAI.

Also, just curious why are you using API call on google Sheets and not a built-in function?

Andy4 · April 17, 2025, 3:44pm

I had already tried using the iterator, but it only extracts the first ‘key’ and ‘value’ of the nested array.

It doesn’t matter how the data is extracted from Google Sheets because I process it with OpenAI afterwards.

I’m using the “Make an API call” module because I want to automate the process, and with the preconfigured modules, I would have to manually select the different spreadsheets every time I create a new one.
So I can’t map all the fields of the nested array.

If I try using the Text Aggregator directly instead, it doesn’t work—it gives me an error.

Donald_Mitchell · April 18, 2025, 3:34am

You need to map Body.Array from Google Sheets into your Iterator.
10.Body[] is a Collection, and you cannot Iterate that. You must iterate on the nested array.

Yes, it matters, because you want to convert what’s coming out of Google Sheets into something compatible for input into OpenAI.

So let’s say OpenAI needs to accept JSON. In that case you need:
Google Sheets → Iterator → JSON Aggregator → OpenAI

Alternate way that might work, if OpenAI accepts it:
Google Sheets → Transform to JSON → OpenAI

Andy4 · April 18, 2025, 8:16am

I iterated over 10.Body[], but it still only extracts the first array. I believe there’s an issue on Make.com’s end.

I’ve been trying for days, but it seems unsolvable.
If you ever wanted to try it yourself (you’re definitely better at this than me), it’s a simple procedure:

Create a Google Sheets document like this:

image296×187 1.47 KB
Use Gsheets → Make an API call
Add an Iterator

If you managed to do it, you’d be a hero

Thank you for your time.

Donald_Mitchell · April 18, 2025, 10:21am

10.Body is a collection, you do not want to iterate over that.

Iterate over 10.Body.array.
Find your array in the Google Sheets output and drag the 10.Body.array array into the Iterator.

Andy4 · April 18, 2025, 10:28am

Unless you are mistaken, it does not exist.

Sorry if I’m not understanding

samliew · April 18, 2025, 2:11pm

Hi Andy, thanks for the ping and apologies for the late response. I have been busy with my clients for the past couple of days, but I should have some time mid-next week to take a look above.

If you require urgent assistance, my profile has a couple of links to resources that may help, including an AI trained on all my 10k+ answers on this forum, or you can search my previous posts on my profile for answers to similar questions like these.

Thanks,
— @samliew

Andy4 · April 18, 2025, 2:17pm

I have already searched but I have not found any answer.
Can you help me?

Donald_Mitchell · April 18, 2025, 2:32pm

Weird, I was referring to “values” array that you had highlighted in your original post

So maybe just try {{10.body.values}} ?

Andy4 · April 18, 2025, 2:42pm

I had tried but nothing.
Is it impossible?

Donald_Mitchell · April 19, 2025, 3:25am

Maybe it is a display bug in Make. Did you try this in the Iterator?
{{10.body.valueRanges[1].values}}

And in your aggregator, get the values like this:
{{get(##.value; #)}}
Where ## = module number of your Iterator and
# = column number you want to get from the row you’re currently iterating, where 1 = Column 1.

Andy4 · April 22, 2025, 6:37pm

Nothing to do, it doesn’t work

It’s crazy !

samliew · April 23, 2025, 3:11am

To allow us to assist you with your scenario, please provide the following:

1. Relevant Screenshots

Please share screenshots of your scenario, any error messages, relevant module fields, and filters in question? It would really help other community members to see what you’re looking at.

2. Scenario Blueprint

Please export the scenario blueprint file to allow others to view the mapped variables in the module fields. At the bottom of the scenario editor, you can click on the three dots to find the Export Blueprint menu item.

3. Output Bundles of Modules

Please provide the output bundles of the modules by running the scenario (or get from the scenario History tab), then click the white speech bubble on the top-right of each module and select “Download input/output bundles”.

Providing the input/output bundles will allow others to replicate what is going on in the scenario even if they do not use the external service.

Andy4 · April 24, 2025, 2:57pm

Hi @samliew, of course I report the required information below.
blueprint (1).json (120.2 KB)

Google Sheets | Make an API call

[
    {
        "body": {
            "range": "Foglio1!A2:B1019",
            "majorDimension": "ROWS",
            "values": [
                [
                    "modello",
                    "Dreame L40 Ultra"
                ],
                [
                    "aspirazione_massima",
                    "11000 Pa"
                ],
                [
                    "capacita_batteria",
                    "5200 mAh"
                ],
                [
                    "durata_batteria",
                    "194 minuti"
                ],
                [
                    "capacita_sacchetto_polvere",
                    "3.2 L"
                ],
                [
                    "altezza_superamento_ostacoli",
                    "22 mm"
                ],
                [
                    "dimensioni_robot",
                    "350 x 350 x 103.8 mm"
                ],
                [
                    "dimensioni_base_ricarica",
                    "340 x 456.7 x 590.5 mm"
                ],
                [
                    "spazzola_laterale_estensibile",
                    "presente"
                ],
                [
                    "tecnologia_mopextend",
                    "presente"
                ],
                [
                    "altezza_sollevamento_moci",
                    "10.5 mm"
                ],
                [
                    "moci_rimovibili",
                    "presente"
                ],
                [
                    "rilevamento_ostacoli",
                    "presente"
                ],
                [
                    "controllo_vocale",
                    "presente"
                ],
                [
                    "manutenzione_senza_intervento",
                    "presente"
                ],
                [
                    "pulizia_a_65_gradi",
                    "presente"
                ],
                [
                    "garanzia",
                    "3 anni"
                ],
                [
                    "spedizione_gratuita",
                    "presente"
                ]
            ]
        },
        "headers": {
            "content-type": "application/json; charset=UTF-8",
            "vary": "X-Origin, Referer, Origin,Accept-Encoding",
            "date": "Thu, 24 Apr 2025 14:50:10 GMT",
            "server": "ESF",
            "x-xss-protection": "0",
            "x-frame-options": "SAMEORIGIN",
            "x-content-type-options": "nosniff",
            "alt-svc": "h3=\":443\"; ma=2592000,h3-29=\":443\"; ma=2592000",
            "accept-ranges": "none",
            "x-l2-request-path": "l2-managed-6",
            "connection": "close",
            "transfer-encoding": "chunked"
        },
        "statusCode": 200
    }
]

Iterator (seems not to iterate)

[
    {
        "range": "Foglio1!A2:B1019",
        "majorDimension": "ROWS",
        "values": [
            [
                "modello",
                "Dreame L40 Ultra"
            ],
            [
                "aspirazione_massima",
                "11000 Pa"
            ],
            [
                "capacita_batteria",
                "5200 mAh"
            ],
            [
                "durata_batteria",
                "194 minuti"
            ],
            [
                "capacita_sacchetto_polvere",
                "3.2 L"
            ],
            [
                "altezza_superamento_ostacoli",
                "22 mm"
            ],
            [
                "dimensioni_robot",
                "350 x 350 x 103.8 mm"
            ],
            [
                "dimensioni_base_ricarica",
                "340 x 456.7 x 590.5 mm"
            ],
            [
                "spazzola_laterale_estensibile",
                "presente"
            ],
            [
                "tecnologia_mopextend",
                "presente"
            ],
            [
                "altezza_sollevamento_moci",
                "10.5 mm"
            ],
            [
                "moci_rimovibili",
                "presente"
            ],
            [
                "rilevamento_ostacoli",
                "presente"
            ],
            [
                "controllo_vocale",
                "presente"
            ],
            [
                "manutenzione_senza_intervento",
                "presente"
            ],
            [
                "pulizia_a_65_gradi",
                "presente"
            ],
            [
                "garanzia",
                "3 anni"
            ],
            [
                "spedizione_gratuita",
                "presente"
            ]
        ],
        "__IMTINDEX__": 1,
        "__IMTLENGTH__": 1
    }
]

–
…

Summary
Use case:
I’m using the GSheets > Make an API call module to extract data from two columns in a Google Sheets file. My end goal is to generate a JSON object structured as follows:

{
  "output": [
    { "key": "colA", "value": "colB" },
    { "key": "colA", "value": "colB" },
    ...
  ]
}

Where:

Values from column A in Google Sheets are the "key"
Values from column B are the "value"

Planned strategy:
The JSON creation is meant to be handled directly by the OpenAI module (trying to keep things simple on my end).

Issue encountered:
The OpenAI module only accepts string inputs, while the data coming from the GSheets module is returned as a nested array.

I tried introducing an Iterator to process the data, but it only returns the first key-value pair, and fails to loop through all the elements from GSheets. I suspect the issue lies in how the array is structured or how the iterator handles it.

Goal:
Properly convert the extracted data from GSheets into a string formatted according to the JSON structure above, and pass it correctly to the OpenAI module.

Thank you for your time.

Andy4 · April 28, 2025, 3:40pm

@samliew @Donald_Mitchell Have you read?

Andy4 · April 30, 2025, 2:00pm

It is incredible how such a ‘simple’ scenario turns out to be unsolvable.
Disappointed

Donald_Mitchell · April 30, 2025, 3:27pm

Hi @Andy4,

Please remember this is a community forum and everyone here is volunteering their time to help others.

Your iterator is incorrect, it should be 10.body.values because “values” is the array on which you need to iterate. The way you’re querying the values returns them in nested array or arrays and Make doesn’t number them for easy mapping. So you have to type this in exactly: {{10.body.values}}

Your Iterator should read just what’s in black, get rid of the green.

Then, the Iterator will output each row (as an array) which you’ll be passing into the aggregator. Access the column values from each row like this in the aggregator according to their position relative to the starting column you specified, which is A (1) in this case: {{49.value.1}} {{49.value.2}} where 1 and 2 are columns 1 and 2 from that row, respectively, relative to/starting from A.

Since 49.value is an array, you can also use array functions on it, like this:
{{join(49.value; ",")}}
This will join all the values by comma if you don’t need to access them individually for any reason.

If you were using the built-in modules (like Get Range Values), you optionally specify the Header row so Make is able to capture those and label the output for easier mapping in downstream modules. Since you’re not using it, all that work falls on you, hence all this frustration.

You can use Get Range Values modules and select Search Method = Enter Manually and specify your spreadsheet ID, then map or type in your Sheet Name and Range. All of this you’re already doing with your Make an API Key module so I’m still not sure why you insist on using Make an API Key module instead of Get Range Values?

Hope that makes sense and hope it helps!

alex.newpath · May 1, 2025, 2:19pm

Book a call with me for $99USD at Make.com Hero - NewPath Consulting

I don’t have time to provide free support any longer, sorry! But there is an answer.

Topic		Replies	Views
Transform response open ai How To open-ai	4	1275	December 7, 2023
Open ai generated json to google sheet rows How To iterator , json , google-sheets	6	582	February 17, 2024
Array Itterator to Individual Variables How To arrays , json	6	544	December 22, 2023
Parse JSON to Google Sheets How To error	5	77	January 5, 2025
Open Ai Transform Response module to Google Sheet Add new row module How To arrays , google-sheets , open-ai	7	470	July 2, 2024

Data from GSheets to OpenAI as JSON string – Mission Impossible?

1. Relevant Screenshots

2. Scenario Blueprint

3. Output Bundles of Modules

Related topics