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 · Accepted Answer · 2021-07-01 20:14:04Z

1

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))

answered Jul 1, 2021 at 20:14

player0

131k14 gold badges91 silver badges149 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Google Sheets Import Range if specific columns not blank

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related