CODEWITHSUNDEEP
google-apps-script
wjp79
wjp79

Reputation: 149

google sheet script filter and query based on checkbox and date

I have 2 sheets. 1 sheet with 3 columns: 1 column with dates, 1 column with data and one with checkboxes

The other sheet I want to apply the query on If a checkbox is checked that copy a row with specific column based on the date of 2 cells on the target sheet. (one cell contains month (januari, second cell contains year 2023)

The query part works:

=QUERY(Data!A1:D, "select B,A,C where D=TRUE")

But then howto integrate the date filter. I need some guidance, sometimes I cant get my head around it.

=QUERY(filter(Data!A1:D100, "select B,A,C,D where D=TRUE",TEXT(Data!A1:A100,"yyyMMMM") = $D$1 & $B$1))

Latest code I came up with, but does not work.

Suggestions are welcome

Upvotes: 0

Views: 477

Answers (1)

doubleunary
doubleunary

Reputation: 18794

Use filter() with two criteria, like this:

=filter( 
  { Data!B1:B, Data!A1:A, Data!C1:C }, 
  Data!D1:D, 
  text(Data!A1:A, "MMMyyyy") = left(B1, 3) & D1 
)

...where column Data!A1:A contains numeric date values, cell B1 contains a month name as a text string and D1 contains a year as a number.

Upvotes: 1

Related Questions