Google Sheets Query – How to Evaluate Date Inside a Query

google sheetsgoogle-sheets-queryimportrange

I am attempting to perform a query in Google Sheets, and one of the things I need to match is a date. I have seen other posts on this topic, but I am not having success. The advice that isn't working for me involves this phrase: (Col4=date '"&TEXT(A14,"yyyy-mm-dd")&"'

Here is the whole formula: 

ARRAYFORMULA(IMPORTRANGE(
"https://docs.google.com/spreadsheets/d/1Wyewpz0j_IiKK8ESOapX5L4AedjvHccLZOOKS2tZGUA/",
"UsuallyAnotherSpreadsheetFile!B2:K")),"Select Col4, sum(Col8), sum(Col9) where  
(Col4=date '"&TEXT(A14,"yyyy-mm-dd")&"' AND (Col3='" & $F$1 & "')  AND (Col1 = '" & 
ClientInfo!$H$1 & "') group by Col4")

I get the error: Unable to parse query string for Function QUERY parameter 2:

PARSE_ERROR: Encountered "group" at line 1, column 124. Was expecting one of: "and" … "or" … ")" …

I will share my sheet here. Please see if you are able to get it to match the date based on what is in A14 on the sheet called Paste.

Best Answer

The error message is due to a missing closing parenthesis before the first AND.

Once solving this, you will get as result the following

            sum     sum 
10/12/2016  296.1   2.5

To avoid the headers, before the last " add 

 label sum(Col8) '', sum(Col9) ''

the final formula is

=QUERY(ARRAYFORMULA(IMPORTRANGE(
"https://docs.google.com/spreadsheets/d/1Wyewpz0j_IiKK8ESOapX5L4AedjvHccLZOOKS2tZGUA/",
"UsuallyAnotherSpreadsheetFile!B2:K")),"Select Col4, sum(Col8), sum(Col9) where  
(Col4=date '"&TEXT(A14,"yyyy-mm-dd")&"') AND (Col3='" & $F$1 & "')  AND 
(Col1 = '" & ClientInfo!$H$1 & "') group by Col4 label sum(Col8) '', sum(Col9) ''")
Related Topic