Google Sheets IMPORTRANGE with multiples Sheets

Question

I would like to know how to handle a QUERY + IMPORTRANGE functions in order to combine data from several (40) google sheets, the links of which are in a table.

I cannot put them all in a single formula like so, it would be too long to type...

=QUERY({IMPORTRANGE(Link1,"A1:E500");IMPORTRANGE(Link2,"A1:E500");IMPORTRANGE(Link3,"A1:E500");...},"Select * where Col1 is not null")

I did not manage to do it with an INDIRECT function

If there is a simpler solution, I am also interested !

Thank you !

Answer 1

I get that you want to read two different Sheets and dump them all in a third Sheet using a formula. If that is correct, you are very close to solving this goal. You need to slightly modify your formula to match this:

=QUERY({IMPORTRANGE("{FIRST SPREADSHEET ID}","{SHEET RANGE}");IMPORTRANGE("{SECOND SPREADSHEET ID}","{SHEET RANGE}")},"SELECT * WHERE Col1 <> ''")

Before using that formula you need to approve the connection to both Sheets. To do it, you need to copy each IMPORTRANGE separately and accept the dialog box when running it. If you still have questions, feel free to ask me.