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

bumped to the homepage by Community 2 days ago

This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.

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

0

This can be done without hardcoding the columns in the formula. I am going to show you how to make a formula that can work with any number of columns without any change to the formula.

We are going to have the filter checkboxes in the first row. I filled the whole first row with checkboxes.

Sheet

Note that in Google Sheets we can refer to the whole first row as 1:1, we will rely on this.

For the QUERY formula we need to be able to compute the column letters. Google Sheets does not provide a direct way to get just that, so we have to get creative. We can get the column indexes (1, 2, 3, ..), and then we can convert them to single cell addresses ($A$1, $B$2, ..), and then we can filter out the non letter characters, and we got ourselves some clean column letters:

Sheet

We reuse the previous formula, and for all unchecked checkboxes, we replace (with IF) the column letter with an empty string, and TEXTJOIN whatever remains:

Sheet

(The TRUE in TEXTJOIN ensures that the empty strings are ignored.)

We get the needed query by just appending "SELECT " to the previous formula:

Sheet

And the whole formula comes together like this:

Sheet

That's it.

Make sure to always provide the "header" parameter to the QUERY function, you don't want it guessing.

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.