Google Sheet Array Formula to automatically transpose responses from a Google Form into a single cell

0
votes

I want to tweak an Array Formula that I'm trying to use to transpose a set of responses from a Google Form into a single 'list' in a cell in the linked Google Sheet.

Here is a simplified mock-up of the Google Sheet: https://docs.google.com/spreadsheets/d/1BKgjGK2RbXC5FkCgBOU53dLEb3fDeQADujenrqRgnT0/edit?usp=sharing

As you can see, I have a working Array Formula 'hidden' in the header for Column A, which takes the content from columns B:K in each row and transposes them with line breaks to make a nice neat list in the first cell at the start of each row (A2, A3, A4, etc.) Here is the Array Formula for ease of reference: 

=ARRAYFORMULA({"Points to Develop";
transpose(query(transpose ($B$2:$B&CHAR(10)&$C$2:$C&CHAR(10)&$D$2:$D&CHAR(10)&$E$2:$E&CHAR(10)&$F$2:$F&CHAR(10)&$G$2:$G&CHAR(10)&$H$2:$H&CHAR(10)&$I$2:$I&CHAR(10)&$J$2:$J&CHAR(10)&$K$2:$K)
))})

The problem I'm having is that the formula stops working as soon as a new Google Form is submitted, as the new row of responses arrives at the top and 'pushes the formula down'/throws everything out of alignment, when all I want is for it to stay in place and run automatically every time a Form is submitted. As you can probably gauge by my limited technical terminology, I'm very much a novice at this!

How can I fix this formula, or is there a simpler or better alternative that will achieve the same results?

1
google-sheetstransposearray-formulasgoogle-forms
If I understand you correctly, you have a Form attached to the spreadsheet that is adding data to your sheet every time it is submitted, and the submitted data is messing with your formula. In this case, I would consider either (1) having the ARRAYFORMULA in a different sheet (tab) from the one that is getting submitted data, or (2) get rid of the formula altogether and have an Apps Script onFormSubmit trigger instead, which can be used to execute some actions when the Form is submitted without the need of any formula. In any case, how is the submitted data messing with your formula? - Iamblichus
Thanks for the suggestions. Having slept on the problem, I had suddenly realised that something like your option (2) might be the solution: I already use an Apps Script to grab the 'edit' links onFormSubmit so I can easily go back and add/edit things later. However, I have no idea what kind of Script I would need for the situation I've outlined here. Your Option (1) sounds promising - I'll look into that. Re. your final question, the submitted data messes with my formula by throwing all the cell references off/down: $B$2:$B becomes $B$3:$B, etc. so the formula doesn't run on the new/top row. - A Warde
I'm not sure what data is received by the form and how it should be managed when that happens. If you could clarify this a bit more, I'd be willing to post an answer. - Iamblichus
Thanks, Iamblichus. The Google Form is a pretty straightforward questionnaire-type form, with most of the questions (and answers) being text, either as open text (short answer / paragraph) or pre-set (drop-down / checkbox grid /etc.). As I’ve explained below, I’ve found a solution that works for my purposes (presumably regardless of the content of the cells being transposed) so I’m happy to work with and fine-tune that. Thanks for your helpful suggestions and questions. - A Warde

1 Answers

2
votes

As often happens in life, the nature of the problem turned out to be the source of the solution. Here’s a condensed version of my thoughts as I slowly found my way to an answer (you can almost hear the hamster wheels turning in my head!):

  • The problem with formulas in a Google Sheet linked to a Google Form is that each new entry/submission from the form always arrives in Row 2 …

  • In fact, the only thing that stays ‘in place’ in the Google Sheet is the Header Row/Row 1 …

  • So, rather than using a formula that refers to a ‘Row 2’ that will become ‘Row 3/4/5/etc.’ as new Google Forms are submitted, I should use a formula that always refers to Row 1, that is, by using OFFSET

The obvious (if inelegant) formula that resulted from this is [I've also placed this in the final column in the Google Sheet that I linked to in my original question]:

= ARRAYFORMULA({"Points to Develop [Working OFFSET Formula]"; transpose(query(transpose(OFFSET(A1:A,1,1)&CHAR(10)&OFFSET(A1:A,1,2)&CHAR(10)&OFFSET(A1:A,1,3)&CHAR(10)&OFFSET(A1:A,1,4)&CHAR(10)&OFFSET(A1:A,1,5)&CHAR(10)&OFFSET(A1:A,1,6)&CHAR(10)&OFFSET(A1:A,1,7)&CHAR(10)&OFFSET(A1:A,1,8)&CHAR(10)&OFFSET(A1:A,1,9)&CHAR(10)&OFFSET(A1:A,1,10)))) })

I am sure that there are neater ways of getting to the same result, but this is one that works for me … and is within my levels of comprehension, so I can fine-tune and fix it as needed.