Combining multiple spreadsheets in one using IMPORTRANGE

Question

I would like to aggregate the data of multiple spreadsheets into one spreadsheet.

• Spreadsheet 1 has a Row of Strings A2:A500
• Spreadsheet 2 has a Row of Strings A2:A500
• Spreadsheet 3 is supposed to have a Row of both (Spreadsheet1!A2:A500 AND Spreadsheet2!A2:A500).

Duplicates shall not be handled differently. I would like them to appear as often as they appear in the different sheets.

Is it possible to do this without writing a script or using jQuery, e.g. by using IMPORTRANGE?

What does not work: I have tried using IMPORTRANGE as follows:

ARRAY{IMPORTRANGE("key-of-spreadsheet1","list!A2:A500"), IMPORTRANGE("key-of-spreadsheet2", "list!A2:A500")}

This causes an error.

Answer 1

You should be able to use a vertical array in the Spreadsheet 3:

={IMPORTRANGE("Sheet1Key","SheetName!A2:A500");IMPORTRANGE("Sheet2Key","SheetName!A2:A500")}