Search code examples
google-sheetsgoogle-sheets-formula

How to use query to filter according to many checkbox and dropdown menus?

I'm trying to create a formula to perform queries based on the selected checkbox and the values in the dropdown menus. And after that, format the data to be displayed, in this manner:

I need to group all units done in a day per building in 1 row. But since I'm creating a invoice system, and each job type are billed differently, I need to make sure that on each row there is only 1 type of Job type.

But I don't know if its achievable with formulas, which would be much more convenient than app Script.

This is part of the spreadsheet shared bellow:

Date Client Unit Room Job Type Not completed
08/13 Monica Building1 1C Dpt TRUE
08/13 Monica Building1 D Dpt TRUE
08/13 Monica Building1 2 Dpt TRUE
08/13 Monica Building1 1* Dpt TRUE
08/13 Monica Building1 3 Dpt TRUE
08/13 Monica Building1 12 Dpt FALSE
08/13 Monica Building1 8 Dpt FALSE
08/13 Monica Building1 9 Dpt FALSE
08/13 Monica Building1 10 Dpt FALSE
08/13 Monica Building1 4 Dpt FALSE
08/13 Monica Building1 2 Dpt FALSE
08/13 Monica Building2 8 Dpt FALSE

Here's the spreadsheet with all tabs explained

I tried using a helper sheet to achieve that, but it's a mess and only does half of the job, I'll paste it just so you have an idea, but don't focus on it, as there will be conflicts with the shared sheet, and I didn't think it would worth your time to look into it:

Helper sheet A2:E

=IF('Units Report'!C4="Yes",filter('Unit Log'!A2:A,NOT(REGEXMATCH('Unit Log'!J2:J,"\!"))),ARRAYFORMULA('Unit Log'!A2:A))

Helper sheet AA2:AE:

=sort(let(a,unique({A2:INDEX(A:A,MATCH(3,1/(A:A<>""))),C2:INDEX(C:C,MATCH(3,1/(C:C<>""))),B2:INDEX(B:B,MATCH(3,1/(B:B<>"")))}),map(INDEX(a,,1),INDEX(a,,2),INDEX(a,,3),lambda(y,z,f,{to_date(y),f,z,join(", ",filter(D:D&IF(LEN(E:E)," ("&E:E&")",),A:A&C:C&B:B=y&z&f)),join(", ",filter(D:D,A:A&C:C=y&z))}))))

Units Report E2:

=QUERY('Helper sheet'!AA2:AG,
       "select "&IF(A4="Yes","AA","AA")&
       " where "&IF(A4="Yes","AA","AA")&" is not null"&
       IF(A2<>""," and AA >= date '"&TEXT(A2,"yyyy-mm-dd")&"'","")&
       IF(C2<>""," and AA <= date '"&TEXT(C2,"yyyy-mm-dd")&"'","")&
       IF(A6=True,," and AB matches '"&TEXTJOIN("|",TRUE,A7:A)&"'")&
       IF(C6=True,," and AC matches '"&TEXTJOIN("|",TRUE,C7:C)&"'")
       )

I could share a more complete spreadsheet, but to save your time, I shared only a specific doubt I have, so I can try to do the rest myself.

Solution

  • Here's one possible solution you may test out:

    Using your own query from above as step_1 base dataset

    =QUERY('Unit Log'!A2:F; "select *"&" where "&IF(A4="Yes";"E";"A")&" is not null"& IF(A2<>"";" and A >= date '"&TEXT(A2;"yyyy-mm-dd")&"'";"")& IF(C2<>"";" and A <= date '"&TEXT(C2;"yyyy-mm-dd")&"'";"")& IF(A6=True;;" and B matches '"&TEXTJOIN("|";TRUE;A7:A)&"'")& IF(C6=True;;" and C matches '"&TEXTJOIN("|";TRUE;C7:C)&"'"))

    formula:

    =let(Λ;{E3:E\G3:G\I3:I};Σ;unique(filter(Λ;index(Λ;;1)<>""));
     map(index(Σ;;1);index(Σ;;2);index(Σ;;3);lambda(date;unit;job;{date\unit\textjoin(", ";1;filter(H:H&" ("&I:I&")";E:E=date;G:G=unit;I:I=job))})))
    • Someone re-formatted the dates in Column_A of Unit Log tab to valid-date-format (thanks to them!!); which is something you might need to double check when re-using the formula(s) in your original sheet
    • Column_D of Unit Log tab Room is formatted to TEXT data-type since query has issues rendering mixed data types in a single column

    enter image description here