Unable to parse query string for Function QUERY parameter

Question

Trying to run this query:

=query(IMPORTRANGE("XXXXXXX","'XX'!A:H"), "where Col3 = '666' and select Col1, Col2, Col3, Col4, Col5, Col6, Col7, Col8 and order by Col5 desc",1)

And keep on getting weird errors such as this one

Unable to parse query string for Function QUERY parameter 2: PARSE_ERROR: Encountered " "select" "select "" at line 1, column 24. Was expecting one of: "true" ... "false" ... "not" ... "date" ... "timeofday" ... "datetime" ... "timestamp" ... "min" ... "max" ... "avg" ... "count" ... "sum" ... "no_values" ... "no_format" ... "is" ... "null" ... "year" ... "month" ... "day" ... "hour" ... "minute" ... "second" ... "millisecond" ... "with" ... "contains" ... "starts" ... "ends" ... "matches" ... "like" ... "now" ... "dateDiff" ... "quarter" ... "lower" ... "upper" ... "dayOfWeek" ... "toDate" ... ... <INTEGER_LITERAL> ... <DECIMAL_LITERAL> ... <STRING_LITERAL> ... <QUOTED_ID> ... "(" ... "-" ...

Not sure why the error is referring to column 24 where I clearly didn't ask for it :)

Any thoughts?

Column24= character 24. First character is w from where. Select cannot come after where. — TheMaster
– TheMaster, Commented Jun 26, 2020 at 20:01

player0 · Accepted Answer · 2020-06-29 18:56:10Z

3

should be:

=QUERY(IMPORTRANGE("XXXXXXX", "'XX'!A:H"), 
 "select Col1,Col2,Col3,Col4,Col5,Col6,Col7,Col8 
  where Col3 = '666'
  order by Col5 desc", 1)

edited Jun 29, 2020 at 18:56

answered Jun 27, 2020 at 17:02

player0

131k14 gold badges91 silver badges149 bronze badges

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

7 Comments

user13708028 Over a year ago

Thanks, this is indeed working now! One quick tweak: indeed of a fixed value ('666') I'd like the query to return values based on a value in another cell, on the same sheet the query is in. I tried to replace '666' with 'C1'. The good this is that it didn't return an error :) the bad this is that it returned only the header row from the sheet the "IMPORTRANGE" is pointing to. Any thought?

player0 Over a year ago

yeah, if C1 is numeric value use:

=QUERY(IMPORTRANGE("XXXXXXX", "'XX'!A:H"),   "select Col1,Col2,Col3,Col4,Col5,Col6,Col7,Col8   where Col3 = "&C1&"  order by Col5 desc", 1)

player0 Over a year ago

if C1 is text string use:

=QUERY(IMPORTRANGE("XXXXXXX", "'XX'!A:H"),   "select Col1,Col2,Col3,Col4,Col5,Col6,Col7,Col8   where Col3 = '"&C1&"'  order by Col5 desc", 1)

user13708028 Over a year ago

I'm coming back to this post after a few weeks during which it worked, but suddenly has some issue. The issue is that some columns aren't presented. I do see their header, but no data in the cells below some headers. Not sure if this is relevant, but these columns contain both numeric and text data. Cell C1 is string and the actual function still works and it pulls data as expected, apart from the missing data in some columns.

player0 Over a year ago

try:

=ARRAYFORMULA(QUERY(TO_TEXT(IMPORTRANGE("XXXXXXX", "'XX'!A:H"),   "select Col1,Col2,Col3,Col4,Col5,Col6,Col7,Col8    where Col3 = '666'   order by Col5 desc", 1)))

|

Collectives™ on Stack Overflow

Unable to parse query string for Function QUERY parameter

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related