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

Hello everyone, I'm having trouble with this and could use some guidance.

I'd suggest using an Iterator on your Array that comes from Google Sheets, followed by an aggregator.
If you need to pass text to OpenAI, use the Text Aggregator.
If you need to pass JSON, use the JSON aggregator.
You could also try using a Transform to JSON module on the array instead of the Iterator-Aggregator combination.

It's a bit challenging to give a definitive answer without a completely clear understanding of how your data needs to be formatted before it goes into OpenAI.

Also, I'm curious why you're using an API call for Google Sheets and not a built-in function?

I had previously attempted to use the iterator, but it only seems to extract the initial ‘key’ and ‘value’ from the nested array.

image747×650 50.3 KB

image502×617 24.9 KB

The method of data extraction from Google Sheets is not critical, as I will be processing it with OpenAI afterward.

I am utilizing the “Make an API call” module because my goal is to automate the process. Using the pre-configured modules would necessitate manually selecting the different spreadsheets each time a new one is created.
Therefore, I am unable to map all the fields within the nested array.

If I attempt to use the Text Aggregator directly, it results in an error.

image876×650 63.4 KB

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

I've been looping through 10.Body[], but it still only retrieves the initial array. I suspect there might be an issue on callin.io's side.

I've spent days on this, and it seems unresolvable.
If you're interested in trying it yourself (you're definitely more skilled at this than I am), the process is straightforward:

1. Create a Google Sheets document structured like this:

   

   image296×187 1.47 KB

2. Utilize Gsheets → Make an API call

3. Incorporate an Iterator

If you manage to get this working, you'd be a lifesaver!

Thanks for your help.

10.Body is a collection; you should not iterate directly over it.

Instead, iterate over 10.Body.array.
Locate your array within the Google Sheets output and then drag the 10.Body.array into the Iterator module.

Unless I'm mistaken, it doesn't seem to be there.

image863×680 62.4 KB

My apologies if I'm not grasping it correctly

Hi Andy, thanks for reaching out and apologies for the delayed reply. I've been occupied with client work over the last few days, but I anticipate having some availability mid-next week to review your request.

Should you need immediate assistance, my profile contains links to helpful resources. These include an AI trained on my 10k+ forum responses, or you can explore my past posts for answers to similar inquiries.

Thanks,
— @samliew

I've already searched but couldn't find a solution. Could you please assist me?

That's odd, I was referring to the “values” array that you had highlighted in your initial post.

image703×625 37.2 KB

Perhaps you could try {{10.body.values}}?

I attempted that, but it didn't yield any results. Is this not possible to achieve?

image890×406 39.4 KB

It might be a display issue within callin.io. Have you attempted this within the Iterator module?


{{10.body.valueRanges[1].values}}


And in your aggregator, retrieve the values using this syntax:


{{get(##.value; #)}}


Where ## refers to the module number of your Iterator, and # represents the column number you wish to extract from the current row being iterated. For instance, 1 corresponds to Column 1.

It's not functioning, and there's nothing to do.

image613×332 20.4 KB

image666×468 28.6 KB

This is unbelievable!

To help us understand your scenario, please provide the following:

1. Relevant Screenshots

Please share screenshots of your scenario, any error messages, relevant module fields, and filters you are concerned with. This will greatly assist other community members in understanding your situation.

2. Scenario Blueprint

Kindly export your scenario blueprint file. This will enable others to see the mapped variables within the module fields. You can find the "Export Blueprint" option by clicking the three dots at the bottom of the scenario editor.

3. Output Bundles of Modules

Please provide the output bundles from your modules. You can achieve this by running the scenario (or by retrieving them from the scenario History tab). Then, click the white speech bubble located in the top-right corner of each module and select "Download input/output bundles".

Sharing the input/output bundles will allow others to replicate your scenario's behavior, even if they don't use the external service.