Fetch Data from API and display array result in Collection

[piratenkoch]

Hi folks,

I’m looking for a way to fetch real estate data from an external API and display the result in a collection list.

I’m able to fetch the data into the glide data editor, but now I’m struggling to make the array available for a collection. I think the first step would be to transpose the data from one the cell into multiple rows, but how can I do that without using Google Sheets? Is there any way?

Thank you in advance and for your help!

[Darren_Murphy]

You’ll need to expand the array into a single column that spans multiple rows.
Do you know in advance the maximum number of results that will be returned?

If you can provide a screenshot of what the data looks like in the Glide Data Editor, I can probably help.

[piratenkoch]

Thank you @Darren_Murphy !

I can specify the number of array items while calling the API, by default it’s 20.

Here’s a screenshot of the buying prices in an array. I would do the same for other data such as images, title, number of rooms etc.

[Darren_Murphy]

Okay, so here is the general approach:

• I’d recommend creating a new helper table to do the work
• Add enough rows to this table to cater for the maximum number of array elements (eg. 20, or whatever)
• Add a RowID column
• Add a Lookup column that targets the RowID column. This will give you an array of RowIDs
• Now use the Find Element Index array column. The values should be the array of RowIDs, and you need to find the RowID. This will give you a column of Row numbers, beginning at zero. Call this a Row Index column.
• Add your API column as shown in your screenshot. If you already have it in another table, you might need to create a relation + lookup, or use a Single Value column.
• Use a template column to remove the enclosing square brackets from the array. The result should be a comma separated list.
• Use a split text column to turn that into an array
• Now use a Single Value column. You need to target the Split Text column, and take the Row Index from start.

If you’ve done all that correctly, your array will have been transformed into a single column, with one value per row. And you can now use that column as the source of a Collection.

As it so happens, I was just doing something very similar today. Here’s a short video that walks through most of the above steps.

[piratenkoch]

Brilliant, that worked very well - thank you so much!
How would you now handle multiple fields? Repeat the process for every field?
Or is there a smarter way?

Example:

[Darren_Murphy]

@piratenkoch

How would you now handle multiple fields? Repeat the process for every field?

That’s the simple, brute force approach, yes. Of course, if you do it all in the same table then you only need to repeat the last few columns.

@piratenkoch

Or is there a smarter way?

There probably is, but it’s something I’d have to experiment with. I’d recommend having a look at the Transform JSON plugin. Just thinking out aloud, if you can use that to flatten an entire data structure into a single array, then you could probably target that same column with a series of single value columns, with each taking every n^th item of the array. You could use a math column to calculate n for each column. I guess it depends on how many distinct fields you have whether or not this would be worth the extra effort.

Just a note on your helper table. I can see that you didn’t actually use a RowID column, but rather just a text type column and added the row numbers manually. That’s fine, but it essentially makes the next two columns redundant. You could have just made that a Number type column, started your row numbers at zero, and then use that column in your Single Value column. The advantage of the approach that I described is that it’s dynamic - the row index numbers will automatically adjust themselves if/when you add or remove rows.

[ThinhDinh]

What I usually do when I have multiple fields is:

• Write a JQ query snippet to have a custom structure for the data I have returned. Instead of a JSON, it will be an array of arrays, each child array will contain the info for an item in the parent array.

.[] | [your fields here,"🍀"] | join("♠")

is being used as a custom separator between child arrays, while is the custom separator between items in the child array itself.

Then it’s just like your method above, transposing the child arrays, then I split out the info with a JS column. You might not need to use the JS column, just split the child array out and use single value column to point to the correct place.