CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formulagoogle-forms
YangTegap
YangTegap

Reputation: 431

Dynamically add rows to expand QUERY

I have a Google Sheet linked to a Google form to record user input in a way that is easily manipulatable. For my example purposes, let's say this is simply a form to get user comments.

In this example, I have the sheet titled "Data" that retrieves the form submissions. This has the following headers:

|-----------|--------|-----------|
| Timestamp |  User  |  Comment  |
|-----------|--------|-----------|

New form submissions are actively being added to this sheet.

I am pulling this data to another sheet, titled "Report," via a QUERY FUNCTION. I would like to sort these comments under their select user. Here is the layout of my Report sheet:

|----------|---------|
|  User 1  |         |
|----------|---------|
|          |  QUERY  |
|----------|---------|
|  User 2  |         |
|----------|---------|
|          |  QUERY  |
|----------|---------|
|  User 3  |         |
|----------|---------|
|          |  QUERY  |
|----------|---------|

The cells with "QUERY" will have something along these lines:

=QUERY(Data!A1:C,"Select * Where B = 'User 1'", 1)

What I want to do is have new rows dynamically added to the associated query as each user sends in multiple submissions; that way there is enough room for the Query data and I won't get an error.

How can this be done?

Upvotes: 0

Views: 1351

Answers (1)

Wicket
Wicket

Reputation: 38357

If you are open to slighly change the report structure in order to keep things simple use the ORDER BY clause of Google Query Language in a single formula using the QUERY function

The formula will be something like this:

=QUERY({Data!B1:B,Data!A1:C},"Select * Order By Col1", 1)

Then you could use conditional formatting to set the font and background color to be the same if the cell value is the same of the above cell.

If you really need to have a stepped report, then add a set of rows having only the User name. Example:

=ArrayFormula(QUERY(
{Data!B:B,Data!A:C;UNIQUE({Data!B2:B,IF(LEN(Data!B2:B),{"","",""})})},
"Select * Where Col1 is not null Order By Col1, Col2", 1
))

Resources

Upvotes: 1

Related Questions