Applying IMPORTRANGE formula in Google Sheets between tabs

Question

I have one Google sheet having three tabs, Sheet 1, Sheet 2, Sheet 3. I have another tab having the name "Sheet 4" in which I want to import all data from these three tabs, but the problem is data in these three tabs will keep on updating, that is dynamic.

={QUERY(IMPORTRANGE("1Cqf7ozYcrTb99X4-x1vzHcrs4pqXTBcNqLtaSZnihjE","sheet 1!A2:Q"),"SELECT *",1);
  QUERY(IMPORTRANGE("1Cqf7ozYcrTb99X4-x1vzHcrs4pqXTBcNqLtaSZnihjE","sheet 2!A2:Q"),"SELECT *",1)}

The result which I am getting is data from Sheet 1 is coming but data from sheet 2 is not coming.

Can anyone please help me how can I import data from all required tabs

Answer 1

As you are using open references, your first IMPORTRANGE is including all the sheets rows, including those that doesn't have data, so the data of the second IMPORTRANGE is not shown intimately after the data of the first but they are after some hundreds, maybe thousand rows

Alternatives

1. include a where clause with an appropriate condition left out those blank rows.
2. remove the blank rows from sheet 1 (it will be a good idea to do the same on sheet 2)

Answer 2

• you don't need IMPORTRANGE formula - all you need is:

  =QUERY({'sheet 1'!A2:Q;'sheet 2'!A2:Q;'sheet 3'!A2:Q}; 
   "where Col1 is not null and Col2 is not null and Col3 is not null"; 0)