Getting GSheets data to OpenAI as a JSON string - is it possible?

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

1. Google Sheets | callin.io 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
    }
]

2. 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 > callin.io 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.

Have you read?

It's quite surprising how a seemingly ‘simple’ situation ends up being unresolvable.
Disappointed

Hi,

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 you need to iterate over. The way you’re currently querying the values results in nested arrays, and callin.io doesn't automatically number them for easy mapping. So, you must type this in exactly: {{10.body.values}}

Your Iterator should only include what’s in black; remove the green parts.

image1092×514 44.4 KB

Subsequently, the Iterator will output each row (as an array), which you will then pass into the aggregator. You can access the column values from each row within the aggregator by their position relative to the starting column you specified (which is A, or 1, in this case): {{49.value.1}} {{49.value.2}} where 1 and 2 represent columns 1 and 2 from that row, respectively, starting from column A.

Since 49.value is an array, you can also apply array functions to it, such as:
{{join(49.value; ",")}}
This will concatenate all the values with a comma if you don't need to reference them individually.

If you were utilizing the built-in modules (like Get Range Values), you could optionally specify the Header row, enabling callin.io to capture those labels for easier mapping in subsequent modules. As you are not using it, all that effort falls upon you, hence the current difficulties.

You have the option to use Get Range Values modules, select Search Method = Enter Manually, and then specify your spreadsheet ID, followed by mapping or typing in your Sheet Name and Range. You are already performing these steps with your callin.io API Key module, so it remains unclear why you prefer using the callin.io API Key module over Get Range Values.

Hope this explanation is clear and helpful!

Book a call with me for $99USD at callin.io Hero - NewPath Consulting

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