google-sheetsgoogle-sheets-formulaarray-formulasimportrange

ARRAYFORMULA with IMPORTRANGE

In column B are listed IDs of Google Sheets. In column C are listed cells, from which I want to import data.

Screenshot of the table

In column D is shown the result of using IMPORTRANGE() by simply dragging it. e.g. for D1 it looks like:

=IMPORTRANGE(B1;C1)

for D2:

=IMPORTRANGE(B2;C2)

and so on.

In column E I want to display the same result but using ARRAYFORMULA that looks like: 

=ARRAYFORMULA(IMPORTRANGE(B2:B4,C2:C4))

but the function displays only the data from the first spreadsheet.

Solution

  • ARRAYFORMULA doesn't work when importing data (I think it relates to permissions). You could use something like this, =IFERROR(IMPORTRANGE(B5:B7;C5:C7)) and pre-fill the column first, but still there would be the permissions issue. Each new imported sheet needs it's permissions granted by a user.