Formatting JSON API response into an array for table

Hi all - I'm very much new to Retool, but trying my best to learn! I was able to configure my resource correctly, but it looks like my API query response is formatted as JSON, so I know i need to turn the response into an array of values that can work with a table.

However, after looking at Query JSON with SQL, JS Transformer, and this community post, I'm stuck.

Here's what my API query returns:

Hi @ch126, you can add a transformer to the query to transform the response data to your desired format.

Thank you for the help @Harry_Doan!

I'm using this transformer in the query:
return {{ formatDataAsArray( }}

But the response I'm getting now is an empty array [].

@ch126 Here is the API for formatDataAsArray. I don't think it works with your response data format. You may need to do the transformation yourself, depending on the shape of your response data

1 Like

Yeah, I it's looking like that is the route I'll need to take. Any advice on that would also be much appreciated! Just trying to learn as much as I can. Thanks for the help thus far.

@ch126 Welcome!

It looks like the SmartSheet API endpoint you are using only returns a single sheet object. A table is probably not the best way to display the data returned from the API in this case. Have you looked at the new Key/Value component? When you query for a specific sheet it'll display results like this:

If you can offload all of the sheet data to a Retool DB or your own local server then you would be better served by a table displaying that data, or you might want to use a List View component instead.

1 Like

Hi there,

@pyrrho is right and that would most likely be your best bet.

If you still want to use the initial response to populate a table, though, you can simply return it as an array like so:

  • return [ {{ formatDataAsArray( }} ]

This would make your data populate the first row of a table so maybe not the most visually pleasing, but it would work.

Hope this helps!


1 Like