Google Sheets: Query function can't copy data

Question

I attached a sample Google Sheet data (Link).

It contains 2 sheets. The Response sheet contains the response of the Google Form. Since the Google form has a lot of repeating questions, I have to transform the data using the Query function (sheet Transformed cell B2). The query command is

=QUERY(
  {
    Response!E2:H,Response!B2:D;
    Response!I2:L,Response!B2:D;
    Response!M2:P,Response!B2:D;
    Response!Q2:T,Response!B2:D;
    Response!U2:X,Response!B2:D;
    Response!Z2:AC,Response!B2:D;
    Response!AD2:AG,Response!B2:D;
    Response!AH2:AK,Response!B2:D;
    Response!AL2:AO,Response!B2:D;
    Response!AP2:AS,Response!B2:D;
    Response!AU2:AX,Response!B2:D;
    Response!AY2:BB,Response!B2:D;
    Response!BC2:BF,Response!B2:D;
    Response!BG2:BJ,Response!B2:D;
    Response!BK2:BN,Response!B2:D;
    Response!BP2:BS,Response!B2:D;
    Response!BT2:BW,Response!B2:D;
    Response!BX2:CA,Response!B2:D;
    Response!CB2:CE,Response!B2:D;
    Response!CF2:CI,Response!B2:D;
    Response!CK2:CN,Response!B2:D;
    Response!CO2:CR,Response!B2:D;
    Response!CS2:CV,Response!B2:D;
    Response!CW2:CZ,Response!B2:D;
    Response!DA2:DD,Response!B2:D
  },
  "select * where Col1 <> '' Order By Col6"
)

However when you look at the sheet Transformed, some data in Col F (Corresponding to Response sheet Col B) didn't get transferred. How could this problem be fixed?

Thanks in advance

JPV JPV · Accepted Answer · 2017-12-18T07:45:46

It is often noted that users are tempted to mix data types within a column. The query() function will give undesirable output. If a column is intended for numeric values then only numerical values must reside in that column. Date columns must only contain dates and text columns only contain text values.

This does not mean that numbers cannot appear in a text column as long as they are in a text format. Generally, the query() function will assume the greater number of cell types in a column to be that data type. For example, if there are 100 numbers and 20 text values in the same column then a numeric value will be assumed for that column. There is a good chance the text values will just be ignored. A slight change in your formula will convert all values to text.

See if this helps

=ArrayFormula(QUERY(to_text({
Response!E2:H,Response!B2:D;
Response!I2:L,Response!B2:D;
Response!M2:P,Response!B2:D;
Response!Q2:T,Response!B2:D;
Response!U2:X,Response!B2:D;
Response!Z2:AC,Response!B2:D;
Response!AD2:AG,Response!B2:D;
Response!AH2:AK,Response!B2:D;
Response!AL2:AO,Response!B2:D;
Response!AP2:AS,Response!B2:D;
Response!AU2:AX,Response!B2:D;
Response!AY2:BB,Response!B2:D;
Response!BC2:BF,Response!B2:D;
Response!BG2:BJ,Response!B2:D;
Response!BK2:BN,Response!B2:D;
Response!BP2:BS,Response!B2:D;
Response!BT2:BW,Response!B2:D;
Response!BX2:CA,Response!B2:D;
Response!CB2:CE,Response!B2:D;
Response!CF2:CI,Response!B2:D;
Response!CK2:CN,Response!B2:D;
Response!CO2:CR,Response!B2:D;
Response!CS2:CV,Response!B2:D;
Response!CW2:CZ,Response!B2:D;
Response!DA2:DD,Response!B2:D}),"select * where Col1 <> '' Order By Col6"))

Google Sheets: Query function can't copy data

1 Answers