I have built a google sheets query and I want to improve it by adding a date range. Any ideas on how I can improve my code so that it actually works so far it hasn't been working.
=QUERY(IMPORTRANGE("url", "Masterfile!A:AS"), "where Col35 is not null" & "where Col41 > date '"&TEXT($A$2,"yyyy-mm-dd")&"' and Col41 <= date '"&text($B$2,"yyyy-mm-dd")&"'",1))
It is always best to provide a copy of the sheet when asking for google sheet help. In this case I am working blind as cannot see the values of A2 and B2. Assuming they are both dates. The syntax is off for the 2nd parameter of QUERY() which happens quite often and sometimes takes some time to figure out the issue from the generic error the QUERY() function throws.
I typically break the 2nd parameter out into a formula to see how it evaluates when I get an error. Using the two dates I picked here are the results:
The formula is:
="where Col35 is not null" & "where Col41 > date '"&TEXT($D$49,"yyyy-mm-dd")&"' and Col41 <= date '"&text($D$50,"yyyy-mm-dd")&"'"
And this evaluates to:
where Col35 is not nullwhere Col41 > date '1959-10-21' and Col41 <= date '1960-10-21'
Now one can see the problem after the NULL keyword. In QUERY() we can use logical operators like AND, OR an NOT.
="where Col35 is not null AND where Col41 > date '"&TEXT($D$49,"yyyy-mm-dd")&"' and Col41 <= date '"&text($D$50,"yyyy-mm-dd")&"'"
Evaluates to:
where Col35 is not null AND where Col41 > date '1959-10-21' and Col41 <= date '1960-10-21'
So, putting it together should work:
=QUERY(IMPORTRANGE("url", "Masterfile!A:AS"), "where Col35 is not null AND where Col41 > date '"&TEXT($D$49,"yyyy-mm-dd")&"' and Col41 <= date '"&text($D$50,"yyyy-mm-dd")&"'",1))