Google Sheets: I have a list of URLs between 1 and 30 items long in Sheet 1 that I want to importrange if not blank

userRaLl1 0
Viewed 29

In Sheet 1, I have a list of URLs, but it's not a set list. It's a list that gets updated all the time and on one day could have a list of 5 URLs, and the next, 20.

See screenshot

I want to importrange all of these URLs into another sheet and used the following formula:

=QUERY({importrange('Sheet 1'!A16,"Tab1!A14:K80");IMPORTRANGE('1.Sheet1'!A17,"Tab1!A14:K80")},"where Col1 is not null")

which worked to import the data from the two URLs in Sheet1 A16 and A17.

However, I would like this whole thing to be dynamic, i.e. if a URL gets added in A18,19,20 and so on, I would like to importrange those too. So my question is, is there a way to do similar to what I did above, but insert a sort of IF condition? Like if A16:A36 is not blank/has a URL, importrange, and if not don't importrange, and for multiple URLs?

Thank you!

google-sheets-formula google-sheets
1 Answers

the old way:

=QUERY({
 IFERROR(IMPORTRANGE('Sheet 1'!A16, "Tab1!A14:K80"), IFERROR(SEQUENCE(1, COLUMNS(A1:K1))/0)); 
 IFERROR(IMPORTRANGE('Sheet 1'!A17, "Tab1!A14:K80"), IFERROR(SEQUENCE(1, COLUMNS(A1:K1))/0)); 
 IFERROR(IMPORTRANGE('Sheet 1'!A18, "Tab1!A14:K80"), IFERROR(SEQUENCE(1, COLUMNS(A1:K1))/0)); 
 IFERROR(IMPORTRANGE('Sheet 1'!A19, "Tab1!A14:K80"), IFERROR(SEQUENCE(1, COLUMNS(A1:K1))/0)); 
 IFERROR(IMPORTRANGE('Sheet 1'!A20, "Tab1!A14:K80"), IFERROR(SEQUENCE(1, COLUMNS(A1:K1))/0))},
 "where Col1 is not null", )

the new way (not yet supported on all spreadsheets but it's rolling out soon):

=BYROW('Sheet 1'!A16:A, LAMBDA(r, IMPORTRANGE(r, "Tab1!A14:K80")))
player00
Related