Search code examples
google-sheetsgoogle-sheets-formulaimportrange

Using IMPORTRANGE() with an array of spreadsheet_key

I am trying to import data from a few Spreadsheets using IMPORTRANGE(), however having every spreadsheet_key inline would make it a pain to read, a pain to add a new key. I am trying to load the spreadsheet_keys from a list, something like:

=IMPORTRANGE({Settings!A30:A31}, "Test!A2:V")

where Settings!A30 holds a key and Settings!A31 holds a second key.

I don't get any errors, but it only loads data from the first spreadsheet!

Is there a way to get this working?

Solution

  • The approach you are taking is sound, but the :A31 in your formula is being ignored. (Presumably the key that works for you is the one in A30 and not the one in A31.) This makes sense to me because I would not expect Google to know which data from which set to put in which cell where the two datasets are competing for occupation of the same range.

    You may just need to ensure the formulae are in cells not already occupied - and with enough room for expansion for the imported data. So maybe: 

    =IMPORTRANGE({Settings!A30},"Test!A2:V")

    in one cell and, once that data is imported, choose a cell below those then occupied and insert there:

    =IMPORTRANGE({Settings!A31},"Test!A2:V")