Query formula to replace filter

0
votes

I have a Google Sheets filter formula that I need to replace with something, I was thinking QUERY, but I can't seem to make that work?

=FILTER(Food!$B:$K,Food!$A:$A=true,TODAY()>=Food!$C:$C,(Food!$D:$D-TODAY())<=14,NOT(ISBLANK(Food!$C:$C)),NOT(ISBLANK((Food!$D:$D-TODAY()))))

I need to IMPORTRANGE the filtered results and they lose their table formatting. I got QUERY working but with fixed dates. I have tried numerous combinations and can't seem to nail down the syntax.

Here is a copy of the sheet and the formula I need help with is in the Specials tab, A2.

https://docs.google.com/spreadsheets/d/16bAAI-A_EMsy1noj8U-fhIuT4XTbfgOJuxvzi_0hYhk/edit?usp=sharing

Thank you!

2
google-sheets
Is this a problem before or after importrange? When you use importrange you import only values - never any formatting. – Krzysztof Dołęgowski
It's before the IMPORTRANGE sheet. This sheet in the above question is a sheet for restaurants to report their latest specials and then there is another sheet which aggregates the data and it's there where the IMPORTRANGE happens. Here is a link to a drawing. docs.google.com/drawings/d/… – Dave Lalande

2 Answers

1
votes

Does this formula do what you want?

=QUERY(Food!$A:$K,"select * 
  where A=TRUE 
    and (C > date '2000-01-01' and C <  date '" & TEXT(TODAY(),"yyyy-mm-dd") & "') 
    and (D > date '2000-01-01' and D <= date '" & TEXT(TODAY()+14,"yyyy-mm-dd") & "') ",0)

The first date test for C and D is to ensure they aren't blank. Using C <> '' or C <> 0 didn't work, I guess since it is a date field.

UPDATE

My mistake - I left out some of the logic I'd included at first. Try this:

=QUERY(Food!$A:$K,"select B,C,D,E 
  where A=TRUE 
    and (C > date '2000-01-01' and  C <= date '" & TEXT(TODAY(),   "yyyy-mm-dd") & "') 
    and (D > date '2000-01-01' and (D >= date '" & TEXT(TODAY(),   "yyyy-mm-dd") & "'
                               and  D <= date '" & TEXT(TODAY()+14,"yyyy-mm-dd") & "')) ",0)
0
votes

I got it working by wrapping the FILTER in a QUERY. I had to improve the logic for an End Date >=TODAY() but it worked the way I need after the additional argument.

=QUERY(FILTER(Food!$B:$K,
Food!$A:$A=true,
TODAY()>=Food!$C:$C,
(Food!$D:$D>=TODAY()),
(Food!$D:$D-TODAY())<=14,
NOT(ISBLANK(Food!$C:$C)),
NOT(ISBLANK((Food!$D:$D-TODAY())))))

I'm happy to learn that I can QUERY(FILTER( then IMPORTRANGE the results properly.

Thanks for the interest.