Google-sheets – How to use FLATTEN function in query function

formulasgoogle sheetsgoogle-sheets-arrayformulagoogle-sheets-query

Below in my query result:

In the above table "Form Response" table is the response received in Google Sheets from Google Form.
I want to convert "Form Response" table to "Desired Result" Table.
I used FLATTEN & SPLIT functions to convert column Card data with one name in one cell.
I manually entered Timestamp date in Desired Result Table.

I need the Timestamp information automatically in Desired Table

Best Answer

You can use

={"Timestamp","Card"; 
  INDEX(FLATTEN(SPLIT(REPT(QUERY(A2:A," where A is not null ",0)&"@",3),"@"))),
  INDEX(FLATTEN(SPLIT(QUERY(B2:B," where B is not null ",0),",")))}

Functions used:

Related Solutions

Google-sheets – Using Query with IMPORTRANGE and comparison of dates

Several issues here.

When using query with importrange, the imported columns must be referred to as Col1, Col2, Col3, etc, according to their positions in the imported range. Your reference "ET" will not work.
You have a mess of quotation marks going on. Remember that the query string can contain single quotes. Instead of "WHERE ET>=date """&text(... it is easier to write "WHERE ET>=date '"&text(... because single quotes inside of double quotes need not be escaped.
You are missing a quotation mark in text(B1,yyyy-mm-dd").
The very end of query string is supposed to be an escaped quotation mark enclosed by quotation marks on both ends. Your """ is not enough for that. Save yourself headache and use "'", as advised by 2.
When a query is formed by concatenation like this, it is highly advisable to put it in a separate cell. E.g., your cell C1 can contain just the query string

="WHERE Col2>=date '"&text(A1,"yyyy-mm-dd")&"' and Col2<=date '"&text(B1,"yyyy-mm-dd")&"'"

and then some other cell has =query(importrange(...), C1). This approach simplifies debugging because you get to see what the query is before its attempted execution.

Google Sheets – Forms Data Manipulation

Short answer

Add a new sheet.
Add the following formulas to the new sheet

Cell A2 -> Headers

=OFFSET('Form responses 1'!$A$2,ceiling((row()-1)/5,1)-1,0)

This could be repeated several times, one for each header column, just change the last parameter accordingly.

Cell B2 -> data

=OFFSET('Form responses 1'!$A$2,ceiling((row()-1)/5,1)-1,mod(row()-2,5)*2+1,1,2)

Fill down as necessary

Explanation

OFFSET() is used to get the values from the source data.
'Form responses 1'!$A$2 is the start point to count columns and rows.
row() is used to increment the row and column iterators.
ceiling((row()-1)/5,1), row iterator, is used to set the row from the source data.
mod(row()-2,5)*2+1, column iterator, is used to set the column from the source data.
In the data formula the last parameter, indicates the number of data columns.

In case that are several "header data" columns, add 1 for each of them. I.E. if there are five "header data" columns, and six "data columns" the column iterator should be

mod(row()-2,5)*6+5

The final data formula to be added to the F2 cell is:

=OFFSET('Form responses 1'!$A$2,ceiling((row()-1)/5,1)-1,mod(row()-2,5)*6+5,1,6)

In the row and column iterators, 5 is used because there are 5 columns for "record data".

Demo

Spreadsheet