I have Google Sheets running the add-on formmule to automatically send emails with the responses to a Google Form. Using the following formula, I was able to get a nice HTML table to show up in the body of each email.


This returns a table that looks like this:

Blank cells and a single response cell

I want to try and filter the cells with nothing in them, out, so that only cells with values in them are returned.

  • Now the question doesn't make sense. If you selectively drop cells from each row, that's not a table anymore. – user79865 Jul 28 '16 at 19:09

You can include multiple conditions in a filter by using multiplication (which means logical AND). For example,

FILTER(D:AH, (A:A=A2) * LEN(E:E) * LEN(H:H))

requires A to be equal to A2 and E to be nonempty and H to be nonempty.

Conditions can also be combined using addition, which stands for logical OR:

FILTER(D:AH, (A:A=A2) * (LEN(E:E) + LEN(H:H)))

requires A to be equal to A2 and that either E or H be nonempty.

If you have a lot of columns (like D:AH), one of which must be nonempty, the formula will have to be somewhat long. Instead of adding lengths as above, one can use concatenation & to shorten it a bit:

FILTER(D:AH, (A:A=A2) * LEN(E:E & F:F & G:G & H:H & I:I & J:J & K:K & L:L & M:M & N:N & O:O & P:P & Q:Q & R:R & S:S & T:T & U:U & V:V & W:W & X:X & Y:Y & Z:Z & AA:AA & AB:AB & AC:AC & AD:AD & AE:AE & AF:AF & AG:AG & AH:AH))`

Long, but you only have to do it once.

By the way, I used a spreadsheet to generate the long string above, by making a column of letters (say, in A1:A30) and then using =join(" & ", arrayformula(A1:A30 & ":" & A1:A30)))

  • So could I just do: =RANGETOTABLE(FILTER(D:AH,A:A=A2 * LEN(D:AH)),$D$1:$AH$1) in order to only return responses that are non-empty? Did I place it correctly? – KlavierKatze Jul 28 '16 at 17:47
  • Unfortunately, LEN won't work with a two-dimensional array like that. See my edit. – user79865 Jul 28 '16 at 18:06
  • Well it worked....sort of. I typed it all out by hand before I saw your edit. The sheet is now throwing up an error. #VALUE: Error. Text result of RANGETOTABLE is longer than the limit of 50000 characters. The HTML for the table should be getting shorter as the empty cells are removed. Would adding some sort of binary logic be better? Is there a SHOWIF sort of function? – KlavierKatze Jul 28 '16 at 18:21
  • To isolate the problem, try the FILTER formula on its own, without Rangetotable. – user79865 Jul 28 '16 at 18:23
  • #Error: Formula parse error – KlavierKatze Jul 28 '16 at 18:25

You can include in your formula something like A:A<>"" that way it will ignore values in column A that are blank.


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.