CODEWITHSUNDEEP
google-sheetsgoogle-query-languageparse-error
Adrianne K Seiden
Adrianne K Seiden

Reputation: 35

Google Sheets Query() function returns unexpected character error

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|

Upvotes: 0

Views: 845

Answers (1)

Sebastian Smiley
Sebastian Smiley

Reputation: 1099

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="")

Upvotes: 1

Related Questions