Every Sunday I define with my girlfriend a menu for the coming week. We use Google Sheets for that: each cell is a comma-separated list of products to prepare a meal. We enter those data manually. Then I manually create a shopping list of products to buy, going through the menu. How can I automate this process?
I was thinking about the following algorithm:
- Concate products from selected cells into an array.
- Split each element of the array by comma, so that the result is an array of arrays of single products.
- Flattenize the output of the previous step, i.e., turn the array of arrays into 1-dimensional array of products.
- Remove duplicate products or at least sort them lexically.
- Join all products into a single string with comma-separated elements, so that the final output can be displayed in a single cell.
Here's an example of how it works with input (selected menu) and output (shopping list). Click the screenshot to go to the original document.
I have programming background (Java), but I am not familiar with Google Sheets abstractions, e.g., whether there's a concept of an array.
Is my algorithm implementable in Google Sheets? What functions should I use?