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.