Google Sheets Import Range if specific columns not blank

Question

I have a Google Sheet tab ("Data") with columns A:Z and need to create a tab that pulls in all rows from ("Data") if columns A:N are NOT null in any of these columns.

I've tried query importrange, query+importrange, but can't crack the code:

Attempt #1: =IF(isblank("Data!A1:N1"),Importrange("https:... ","Data!A1:Z100"),,))

Attempt #2: =QUERY(IMPORTRANGE("https:...", " Data!A1:Z1"), "select Col1 where Col1 is not Null", 0). <<this pulls the data in for that specific cell but I can't drag it down or across to get the other rows to populate

Help!

player0 player0 · Accepted Answer · 2021-07-01T20:14:04

join columns A:N into one column and use TRIM

=INDEX(TRIM(FLATTEN(QUERY(TRANSPOSE(IMPORTRANGE("url", "Data!A1:N")),,9^9))))

next, add this column to the range A:Z as the last column of array {} and QUERY it

=INDEX(ARRAY_CONSTRAIN(QUERY({
 IMPORTRANGE("url", "Data!A1:Z"), TRIM(FLATTEN(QUERY(TRANSPOSE(
 IMPORTRANGE("url", "Data!A1:N")),,9^9)))}, 
 "where Col27 is not null"), 9^9, 26))

Google Sheets Import Range if specific columns not blank

1 Answers