Google-sheets – Using Query with IMPORTRANGE and comparison of dates

google sheetsgoogle-sheets-queryimportrange

I created a Google Form that employees complete, The data goes into the main sheet in a spreadsheet. I then want to split that data into separate sheets according to the date in one of the columns. In my separate sheets, I've entered the dates that I want included in cells A1 and B1. This is where I"m stuck.

Can someone look at this and tell me where I went wrong?

=QUERY(IMPORTRANGE("https://docs.google.com/spreadsheets/d/1gYzU1Tt-
0BNCZDBST06ETNHoE0pgkUdTpQctMqDW-1A/edit#gid=1240808529","'Form Responses 10'!A:JQ"),
"WHERE ET>=date """&text(A1,"yyyy-mm-dd")&""" and ET<=date """
&text(B1,yyyy-mm-dd")&""")

Best Answer

Several issues here.

  1. When using query with importrange, the imported columns must be referred to as Col1, Col2, Col3, etc, according to their positions in the imported range. Your reference "ET" will not work.

  2. You have a mess of quotation marks going on. Remember that the query string can contain single quotes. Instead of "WHERE ET>=date """&text(... it is easier to write "WHERE ET>=date '"&text(... because single quotes inside of double quotes need not be escaped.

  3. You are missing a quotation mark in text(B1,yyyy-mm-dd").

  4. The very end of query string is supposed to be an escaped quotation mark enclosed by quotation marks on both ends. Your """ is not enough for that. Save yourself headache and use "'", as advised by 2.

  5. When a query is formed by concatenation like this, it is highly advisable to put it in a separate cell. E.g., your cell C1 can contain just the query string

="WHERE Col2>=date '"&text(A1,"yyyy-mm-dd")&"' and Col2<=date '"&text(B1,"yyyy-mm-dd")&"'"

and then some other cell has =query(importrange(...), C1). This approach simplifies debugging because you get to see what the query is before its attempted execution.

Related Topic