Combining Multiple Spreadsheets In One Using IMPORTRANGE

Answer :

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

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

Of course, it is also possible to combine several IMPORTRANGE() functions with the QUERY() function, which gives us a greater control over the results we import.

For example, we can use such a construction:

=QUERY(   {     IMPORTRANGE("key-or-url-of-spreadsheet-1", "'sheet-name-1'!A2:Z100");     IMPORTRANGE("key-or-url-of-spreadsheet-2", "'sheet-name-2'!A2:Z100");     IMPORTRANGE("key-or-url-of-spreadsheet-3", "'sheet-name-3'!A2:Z100");     IMPORTRANGE("key-or-url-of-spreadsheet-4", "'sheet-name-4'!A2:Z100")   },   "SELECT * WHERE Col1 IS NOT NULL ORDER BY Col3 ASC" ) 

Explanation:

The above query removes blank lines from imported ranges:

SELECT * WHERE Col1 IS NOT NULL 

and sorts ascending all data collected together in relation to the third column:

ORDER BY Col3 ASC 

For descending, just use DESC in place of ASC.

Of course, we can also arrange any other criteria, or omit them displaying everything without modification:

"SELECT * " 

Note:

In order to use the above constructed query, we first need to call a single IMPORTIMAGE() method for each of the spreadsheets we want to refer:

=IMPORTRANGE("key-or-url-of-spreadsheet-1", "'sheet-name-1'!A2:Z100") 

We have to do this even if we refer to the same spreadsheet in which we write this formula, but for every spreadsheet it is enough to do it once.

This is to be able to connect these sheets and allow access to the sheets (to which we have the access rights anyway):

                                                    

After giving permission for all spreadsheets, we can use the above query.