How to use WHERE clause in a query when number of columns is dynamic?

Question

I have searched this, but can't seem to find an answer:

I use the query function in a spreadsheet to collect comments from work assosiates. There is 17 commenters and I query from this week and ten weeks ahead. BUT, I only want the comments and not empty fields in my end result. I am almost there, but with the formula I use now, I have to manually update number of columns in the last part of the query, because the number of columns vary acording to number of comments. Here is my formula:

=transpose(query(transpose(query(QUERY(
IMPORTRANGE("1oQVZDEKLqx6ruz2yzIzUgppkWvBEOB_Eo-a4NW1WTSQ";"Comments!A1:U");
"select Col2, Col3, Col4, Col5, Col6, Col7, Col8, Col9, Col10, Col11, Col12, Col13, Col14, 
Col15, Col16, Col17, Col18, Col19 where todate(Col1) > date '"&text(today()-7;
"yyyy-mm-dd")&"' limit 10");"select * where Col2<>'' or Col3<>'' or Col4<>'' or 
Col5<>'' or Col6<>'' or Col7<>'' or Col8<>'' or Col9<>'' or Col10<>'' or Col11<>'' or 
Col12<>'' or Col13<>'' or Col14<>'' or Col15<>'' or Col16<>'' or Col17<>'' or Col18<>''"));
"select * where Col2<>'' or Col3<>'' or Col4<>''"))

Is there any way to get this formula to work no matter how many columns the two "innermost" querys returns?

Here is the spreadsheet with the queried comments. It collects comments from this sheet (in real life this is collected from 19 sheets with the importrange-formula).

I hope my question is understandable, and most of all, my problem solvable!

Answer 1

This function removes empty columns and rows from a range that has row and column headers. You can call it like this:

=FILTERROWSANDCOLS(IMPORTRANGE("1oQVZDEKLqx6ruz2yzIzUgppkWvBEOB_Eo-a4NW1WTSQ";"Comments!B1:U"))

Please note that I excluded column A for this.

function FILTERROWSANDCOLS(input) {
  function rowIsBlank(row) {
    return row.slice(1, row.length).join("") !== "";
  }

  return input.filter(rowIsBlank).transpose().filter(rowIsBlank).transpose();
}


Object.defineProperty(Object.prototype, "transpose", {value: function(){
  var output = [];
  for (var row = 0; row < this.length; row++) {
    for (var col = 0; col < this[row].length; col++) {
      if (row === 0){
        output.push([this[row][col]]); 
      } else {
        output[col].push(this[row][col]); 
      }
    }
  }
  return output;
}});