GAS – Google Sheets – Query All Columns with a Script

Came across a problem using query on a table, where I wanted to query all the columns. No easy way to write this into a function if you have 10 + columns, so set about writing a google apps script to handle this. The premise is simple enough, a data table with a list of people’s names in the first column, then a set of columns with colour names assigned to each person:


On the search page a straight forward drop down list of all the colours (using data validation from the datatable range!)


I created a named range for the datatable called datarange.

The script:

Set all the variables, including the search and result cell locations, along with a formula for a single column datatable, and the building blocks for the formula required

Count the number of columns, and create an array that assigns column letters instead of column names using a helper script (columnToLetters() – see below)

An if/elseif/else statement next:
If: the search box is blank then just make the results blanks (reduces load on spreadsheet).
Elseif: there is only one column to return results from, simply paste a the full formula to the results cell.
Else: more than one column, builds the formula adding a where “column” contains “search, for each column, then pastes the formula to the results cell

Here is the outcome when searching for “Azure”


and the formula generated:

Here is the helper script that converts column numbers to letters:

For ease of use, I then created an onEdit(e) script, so that any change to the search box would run the script:

and a link to the spreadsheet itself. Make a copy if you want to play around with it and access the script editor.

I also attach a text file containing all the scripts used:


Would be much easier if you could just:

One thought on “GAS – Google Sheets – Query All Columns with a Script

  1. Pingback: Google Sheets – Using Query to Find a Value in any Column | METRIC RAT

Leave a Reply

Your email address will not be published. Required fields are marked *