Have a list of ingredients saved as a list inside a single google sheet cell. After importing into Glide appears as a paragraph. How can I import as a list?
Hi Debra, this is how I would do it.
I need to make a few assumptions:
- Your list of ingredients is in a single Google Sheet cell (as you said).
- The ingredients are separated by a separator, let’s say a comma.
- We can expect a maximum number of ingredients. I chose 10, but it could be 100 for instance.
In the Data Editor, in the Google Sheet table:
- Add a computed Split Text column to turn your comma-separated list of ingredients into an array of ingredients.
- Note 1: A comma-separated list is seen in the data editor as one element that happens to have commas in it, like a sentence. By splitting the comma-separated list into an array, you are telling Glide that you actually have multiple elements. Now you have something to work with to create a collection/list.
- Note 2: Notice that you now have an array of elements in one cell. It’s a good start but collections/lists in Glide are built based on rows in a table, not array elements in a cell, so we need to transpose this array of elements to a table. We affectionately call this @Robert_Petitto’s Miracle Method.
Now onto Bob’s Miracle Method. In the Data Editor:
-
Create a new table you can call IngredientsList or IngredientsCollection. I made it a Glide Table. Add as many rows as you think your ingredients collections will need. I chose 10 (since I cook with a limited number of ingredients ; ). Add a basic RowID column, it will come in handy.
-
Create row indexes thanks to the RowID column. Step 1: Create a computed lookup column and look up the RowID column. Step 2: Create a computed “Find element index” column and find the RowID of each row in the RowIDsArray column, this will return the index of each row.
-
Note: If you don’t care if the row indexes will dynamically add themselves when you add a row (this is what we just did, try adding a row), you can always simply input the indexes by hand. Make sure you start your indexes at 0.
- Now bring in your array of ingredients from the Ingredients table. Create a computed Single Value column and pull in the first row of the IngredientsArray column we create previously.
- With the array of ingredients in each row and an incrementing integer in each row, all we have to do now is extract the 1st, 2nd, 3rd, etc. element of the array, dynamically for each row. Create a computed Single Value column, select the IngredientsArray at the root level of the dropdown, and configure as follows (From start: RowIndex).
This is what your table could look like:
Now, in the the layout editor, you can create a collection whose source will be IngredientsList and you can filter the collection by setting “column IngredientsList is not empty”.
That’s it. To test, back in Google Sheet, try adding “Water” to your ingredients. Don’t forget the comma. In the data editor, “Water” will be added to your comma-separated list, then to the array in the same table, pulled to the IngredientsList table, appear on the 4th row and finally in your collection in the layout editor (or directly in the live version of your app).
Should you need more than 10 ingredients, just add more rows to your IngredientsList table.
2 Likes