GAS – Google Sheets – Query the whole table ? No you can’t !

You would think that this should be straight-forward, but it is not.

A table full of data, with many columns, and everything to the right of Column C will have come from a list of items, but this will be different for each row, and i do not know how many columns there may eventually be.

I want to query the table, and return every row that contains an item from the list. No worries I thought I have the data tool chest of functions query, filter or vlookup to help me. But no, with query(), there is no option to select the whole table and query all the columns for a match, likewise with filter, and it just got silly with vlookup. Crazy but true. Perhaps someone reading this will point out the way to do this with a query?

I turned to google apps script to help me out.

My thanks to Serge Insas whose answer on stackoverflow got me there:

Now for the function (which incidentally I added to an onEdit(e) function so that everytime I changed a specific criteria cell, the search function ran)

First up, I needed to pull in the spreadsheet, the sheets, the search value, then ALL the values from the table

Next, and this is one of the clever bits, I iterated through each array element of the data values (converting it to a string) and finding any rows that matched with the search value. Each success was added to a new array, but recording the person’s name

This next bit just clears the range where the previous results were

I then had to do some work on the new array, as it was not in the format required for writing back to the spreadsheet, essentially getting the data into the correct array structure:

My output was to go into a vertical list, so i created the range for this using the length of my new array, then set all the values from it

Here is the script in full:

Here it what it looks like on the page:



This work ties in with a related post on how I then added the last training date to each of the names

3 thoughts on “GAS – Google Sheets – Query the whole table ? No you can’t !

  1. Pingback: Google Sheets – replace filter with vlookup for array formula | METRIC RAT

  2. Pingback: GAS – Google Sheets – Query All Columns with a Script | METRIC RAT

  3. 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 *