0

Is there a way to activate/deactivate a column in QUERY function? I have a query to query and selection of several columns

Select Col1,Col31,Col32,Col33,Col25,Col26,

and want to have a checkbox for each column that could hide/show it. Hiding/showing the entire sheet column with the onEdit script is not an option as this sheet has another data above/under results of this query. So it should be inside the query

0

The default values for checkboxes are TRUE/FALSE. You could use several IF functions, the & operator and REGEXREPLACE to create the SQL argument for QUERY

Let say that A1 to A5 have the checkboxes, the the resulting formula will look like the following one:

=QUERY(something,"Select "&REGEXREPLACE(IF(A1,"Col1, ",)&IF(A2,"Col31, ",)&IF(A3,"Col32, ",)&IF(A4,"Col25, ",)&IF(A5,"Col26, ",),", $"," "))

  • hey! thanks a lot, it worked well but when I used it for the range that has a header - formula does not bring it to a new query - leaves the blank row. =QUERY(J13:P34, "select "&REGEXREPLACE(IF(J13, "J, ", )& IF(K13, "K, ", )& IF(L13, "L, ", )& IF(M13, "M, ", )& IF(N13, "N, ", )& IF(O13, "O, ", )& IF(P13, "P, ", ), ", $", " "), 1) how to deal with it? – Marchuk Anton Jun 14 at 21:00
  • I'm sorry, it's not clear to me what you mean. Please post a new question including sample input data and the expected result – Rubén Jun 14 at 23:00
0
  • for selectable columns do it like this where you can use offset 1 to hide values of those selected checkboxes

    =ARRAYFORMULA(QUERY(TO_TEXT({A1:AG3}), 
     "select "&REGEXREPLACE(IF(A1,  "Col1,  ", )&
                            IF(AE1, "Col31, ", )&
                            IF(AF1, "Col32, ", )&
                            IF(AG1, "Col33, ", )&
                            IF(Y1,  "Col25, ", )&
                            IF(Z1,  "Col26, ", ), ", $", " ")&"
      offset 1", 0))

    0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.