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?
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.
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.
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 nth 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.
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.
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.