Join with importrange and Query

Question

I Need a query that returns Sheet_1

A | B | C 
1 | 2 | 3
4 | 5 | 6

Sheet_2

D | E | F | G | H | I | 
0 | 1 | 2 | 3 | 4 | 5 |
6 | 7 | 8 | 9 | 0 | 1 |
2 | 3 | 4 | 5 | 6 | 7 |
8 | 9 | 0 | 1 | 2 | 3 |
4 | 5 | 6 | 7 | 8 | 9 |
0 | 1 | 2 | 3 | 4 | 5 |

Join results

A | B | C | D | E | F | G | H | I |
1 | 2 | 3 | 0 | 1 | 2 | 3 | 4 | 5 |
4 | 5 | 6 | 6 | 7 | 8 | 9 | 0 | 1 |
  |   |   | 2 | 3 | 4 | 5 | 6 | 7 |
  |   |   | 8 | 9 | 0 | 1 | 2 | 3 |
  |   |   | 8 | 9 | 0 | 1 | 2 | 3 |
  |   |   | 4 | 5 | 6 | 7 | 8 | 9 |
  |   |   | 0 | 1 | 2 | 3 | 4 | 5 |

It's possible with query and importrange in Google Sheets?

Answer 1

use:

=QUERY({Sheet_1!A1:C100, Sheet_2!D1:I100}, "where Col4 is not null", 0)

---

UPDATE:

in your case, you will need 3 types of formulae because your dataset is not uniform enough. therefore, create a new spreadsheet and import (with IMPORTRANGE formula) all markets in separate sheets:

then create a MASTER sheet in which you will join all data from all markets

in A1 cell paste:

=ARRAYFORMULA({market1!A1:J2, TRANSPOSE(QUERY(TRANSPOSE({
 market1!K1:2, market2!K1:2}), "where Col2 is not null", 0))})

---

in A3 cell paste:

=QUERY({market1!A3:J; market2!A3:J}, "where Col2 is not null", 0)

---

and then paste this into K3 cell and drag it to the right:

=ARRAYFORMULA(IFERROR(VLOOKUP($B3:$B, QUERY({
 market1!$B3:$B, IFERROR(INDIRECT("market1!"&ADDRESS(3, MATCH(K1, market1!$A1:1, 0), 4)&":"&ADDRESS(ROWS(market1!$A:$A), MATCH(K1, market1!$A1:1, 0), 4)), REGEXREPLACE(market1!$B3:$B,".*",));
 market2!$B3:$B, IFERROR(INDIRECT("market2!"&ADDRESS(3, MATCH(K1, market2!$A1:1, 0), 4)&":"&ADDRESS(ROWS(market2!$A:$A), MATCH(K1, market2!$A1:1, 0), 4)), REGEXREPLACE(market2!$B3:$B,".*",))},
 "where Col1 is not null", 0), 2, 0)))