I have an online form where user can cite a list of plants blooming in their area, which appends this data to a Google spreadsheet.
The collected data is 1) the date, and 2) a comma separated list of plant names.
I would like to convert this data into a dynamic calendar, which will continue to evolve as new plants are added at different dates.
edit: This is a link to a copy of the spreadsheet.
The resulting spreadsheet will look like:
| Submitted On | What is blooming in your area right now |
|---|---|
| 08/19/2023 21:37:44 | crape myrtle, orange cosmos, white clover |
| 08/27/2023 21:37:44 | crape myrtle, orange cosmos, white clover |
| 09/04/2023 21:37:44 | crape myrtle, white clover |
| 08/20/2023 8:06:16 | crape myrtle, sunflower,garden zinnia,melampodium,Rose of Sharon, hibiscus |
In a second sheet, I use a QUERY/ARRAYFORMULA to transmogrify the data to a two column range showing the blooming plant (A) and the ISOWEEKNUM of the date submitted (B).
The formula is:=unique(query(ArrayFormula(split(trim(flatten(split(filter( flatten(Sheet1!B2:B), len(flatten(Sheet1!B2:B)) ),",")&"•"&isoweeknum(Sheet1!A2:A))),"•")), "select * where Col2 is not null order by Col1"))
The result looks like: |Plants Bloomin|Week of Year| |---|---| |crape myrtle |33| |crape myrtle |34| |crape myrtle |36| |garden zinnia |33| |hibiscus |33| |melampodium |33| |orange cosmos |33| |orange cosmos |34| |Rose of Sharon |33| |sunflower |33| |white clover |33| |white clover |34| |white clover |36|
What I would ultimately like to do is to populate a dynamic calendar (or table) with the plant names on the left and the appropriate calendar item toggled on (as I am using the checkbox for visual readability). The calendar/table needs to be dynamic as the list of plants will continue to grow as new species are added.
As it is, my existing calendar/table uses a formula in each box which looks at the column heading for a week number and the row heading for the plant name, and then looks for a corresponding match.
For example, the formula in "H2" is =ISTEXT(filter($A:$B,$A:$A = $G2,$B:$B = H$1))
Depending on the result, the box is either true or false.
However, using this per-box formula does not allow for dynamic data growth as new plants are added, requiring me to constantly add additional rows when they become needed. A current workaround would be to generate a few hundred pre-formatted rows with the formula pre-populated.
What I would like is a smart QUERY and/or ARRAYFORMULA that will toggle the weekly boxes, thus constructing the calendar for all the plants. The only way I can imagine this at the moment is to come up with a QUERY which would return 54 columns (one for the plant name and fifty-three for the weekly blocks) ... and no, I have not come up with that query yet.
Any thoughts on how this might be reasonably achieved (without resorting to scripting)? Thank you in advance, my fellow spreadsheet intelligentcia.
1 Answer
Here's one approach you may test out:
=let(row,unique(tocol(map(B2:B,lambda(Σ,index(trim(split(Σ,","))))),3)), col,sequence(1,53), vstack(hstack(,col),hstack(row,makearray(counta(row),53,lambda(r,c,if(ifna(xmatch(index(col,,c),filter(weeknum(A:A),search(index(row,r),B:B)))),image(""),))))))
