Dynamically update cell contents by merging incoming Google Form submissions

Question

I have a google form where multiple students will anonymously submit comments/feedback for another peer.

Currently I am using the formula below to essentially concatenate all of the student comments specific to the individual receiving feedback.

 =(join(" 

 ",transpose(iferror(filter($L$3:$L, $C$3:$C=$C3,$B$3:$B=$B3)))))

• The line break within the "join" function is to separate the individual comments by a line break.
• Column L: The original comment submitted by the students.
• Column C: Filters by the name of the student being evaluated.
• Column B: Secondary filter criteria.

I am currently using the add-on "CopyDown" to apply said formula to each new submission. However, I notice that the newer submissions do not concatenate prior entries even though the filter conditions are met; the formula works correctly when I manually copy/paste it into the cell.

Is there a way to alter the original formula to dynamically update the cell contents as new submissions are incoming? Perhaps through arrayformula?

Answer 1

Functions like JOIN and FILTER can't be used in an array formula (ARRAYFORMULA function ) the way you are thinking (hope?).

A formula using & (concatenate operator) could be used instead of JOIN but I don't think that could be possible to effectively replace FILTER by an ARRAYFORMULA "supported" function, so I think that you best bet is to use Google Apps Script. If you are't familiar with it, please read https://developers.google.com/apps-script/guides/sheets.

Depending on your workflow, you should use an on form submit event, a time driven trigger or execute it manually.