Checkbox to activate a column in QUERY

Question

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

Answer 1

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.

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:

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:

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

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

And the whole formula comes together like this:

That's it.

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

Answer 2

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, ",),", $"," "))

Answer 3

• 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))