Using IMPORTRANGE() with an array of spreadsheet_key

Question

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?

There is a 3rd party script which (I believe) allows you to do this: productforums.google.com/forum/#!topic/docs/6lczh2QEEXQ — Tim
– Tim, Commented Aug 2, 2014 at 7:12

pnuts · Accepted Answer · 2014-08-02 00:30:44Z

1

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

answered Aug 2, 2014 at 0:30

pnuts

59.7k11 gold badges92 silver badges141 bronze badges

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

Collectives™ on Stack Overflow

Using IMPORTRANGE() with an array of spreadsheet_key

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