3

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?

player0
  • 124,011
  • 12
  • 67
  • 124
user13708028
  • 315
  • 3
  • 13

1 Answers1

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)
player0
  • 124,011
  • 12
  • 67
  • 124
  • 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? – user13708028 Jun 29 '20 at 12:08
  • 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 Jun 29 '20 at 15:00
  • 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)` – player0 Jun 29 '20 at 15:01
  • 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. – user13708028 Jul 21 '20 at 11:07
  • 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)))` – player0 Jul 21 '20 at 12:14
  • If I undertand currently, you only changed this part =QUERY( with this =ARRAYFORMULA(QUERY(TO_TEXT(. I get this error Wrong number of arguments to TO_TEXT. Expected 1 arguments, but received 2 arguments. – user13708028 Jul 21 '20 at 12:49
  • fixed... 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))` – player0 Jul 21 '20 at 19:56