Google Sheets Query() function returns unexpected character error

Question

I am trying to create a list of rows in a larger table that are missing data. to do this I wrote

=QUERY(Sheet1!A2:J361,"Select A:E, I where J is null")

to retrieve rows without data in column J. This returned the error

 Unable to parse query string for Function QUERY parameter 2:
 PARSE_ERROR: Encountered " <UNEXPECTED_CHAR> ": "" at line 1, column 9.

sample data:

|Lab # |Site|Block|Plot|Rep |Treatment| Lower Depth|Upper Depth|Depth (cm)  |Sample bag wet (g)|
|:-----|:---|:----|:---|:---|:--------|:-----------|:----------|:-----------|:-----------------|
3100|SFREC/WLIC|1|1|1|Compost + Biochar|    0|  10| 0-10|   622.17|
3101|SFREC/WLIC|    1|  1|  1|  Compost + Biochar   |10 |20 |10-15| 157.58
3102|SFREC/WLIC|1|1|1|Compost + Biochar|20|30|20-30|    
3103|SFREC/WLIC|1|1|2|Compost + Biochar|0|10|0-10|430.18
3105|SFREC/WLIC|1|1|2|Compost + Biochar|20|30|20-30|

expected result:

|Lab # |Site|Block|Plot|Rep |Depth (cm)|    
|:-----|:---|:----|:---|:---|:--------|
3102|SFREC/WLIC|1|1|1|20-30|    
3105|SFREC/WLIC|1|1|2|20-30|

For some reason stack overflow refuses to let me format the sample data as a table, instead insisting that it is improperly formatted code. — Adrianne K Seiden
– Adrianne K Seiden, Commented Jul 7, 2022 at 18:40
The error tells you. You can't use : in the query. Use A, B, C, D, E instead. Or build the string dynamically. — General Grievance
– General Grievance, Commented Jul 7, 2022 at 18:46

Talia Smiley · Accepted Answer · 2022-07-07 19:24:46Z

1

As General Grievance suggests, you can't use a range of cells in the query. Instead, you must name each column you wish to use. For example, the following formula should produce the result you desire:

=QUERY(Sheet1!A2:J361,"Select A,B,C,D,E,I where J is null")

Alternatively, if you wanted it to be easier to expand the range of columns you are selecting, you could avoid the use of the =QUERY function entirely and use =FILTER instead. Here is an example that produces the same results as the query above:

=FILTER({Sheet1!A2:E361,Sheet1!I2:I361},Sheet1!J2:J361="")

answered Jul 7, 2022 at 19:24

Talia Smiley

1,1817 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Google Sheets Query() function returns unexpected character error

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related