Using IMPORTRANGE as an array formula

Question

I have a URL in one cell which is the criterion for the IMPORTRANGE. e.g.:

=IMPORTRANGE(B2,"sheet1!$A$1")

I found a formula to collect it as criterion, but not in array version.

A sample can be found here.

Answer 1

I see you have column B filled with Spreadsheet Ids, and you are trying to execute

=ArrayFormula(IMPORTRANGE(B2:B,"sheet1!$A$1")) 

Not every spreadsheet function supports arrays as argument. In particular, importrange does not. You need separate importrange formulas for separate spreadsheets you are importing.

Indeed, importing data from another spreadsheet is not a batch operation; each import has to be authorized by clicking a prompt the first time it's called. Also, it's a very slow operation; you will get a serious performance hit if trying to import a lot of other spreadsheets.

Generally, if you find yourself doing a lot of importrange, it may be time to rethink the overall data organization.

Answer 2

While you cannot use IMPORTRANGE() in an arrayformula, as detailed in the other answer, you can use it in an array literal. In your case, for the first five cells in your column B that contain spreadsheet URLs:

={
  IMPORTRANGE(B2,"sheet1!$A$1");
  IMPORTRANGE(B3,"sheet1!$A$1");
  IMPORTRANGE(B4,"sheet1!$A$1");
  IMPORTRANGE(B5,"sheet1!$A$1");
  IMPORTRANGE(B6,"sheet1!$A$1")
}

So you'll have to write IMPORTRANGE() multiple times and you can only use this technique if you know before how many sheets there are to import. But the advantage is that this also works where IMPORTRANGE() returns multiple rows each – in that case, copying the formulas down is no option, as IMPORTRANGE() creates an error in cases where its results would overwrite cell contents further down.