Stack multiple columns on top of eachother on Google Sheet?

Question

I am using Google SpreadSheet, and I'm trying to import data from different sources together using a combination of query, importrange and curly brackets:

={Query(Importrange(SheetA),"select Col1,Col2,Col3,Col4 where Col1 is not null");
Query(Importrange(SheetB),"select Col1,Col2,Col3,Col4 where Col1 is not null")}

But the result formula only returns SheetA data.

Since I only need 4 particular columns from SheetA and SheetB, I'd want the columns from both sheets to stack on top of eachother like this:

Col1	Col2	Col3	Col4
Data SheetA	Data SheetA	Data SheetA	Data SheetA
Data SheetB	Data SheetB	Data SheetB	Data SheetB

I tried running each =query(importrange) individually and combine them afterwards using {A:D;E:H} but it gives Result was not automatically expanded, please insert more rows error.

I tried looking for other similar threads but they were mostly about stacking different columns into one singular column. Can you help me with my case?

Answer 1

Turns out the issue was due to the infinite blank rows when trying to combine 2 infinite ranges A:D and E:H,

By enclosing them with a query, remove null value condition, my formula works fine now:

=query({A:D;E:H},"Select * where Col1 is not null")

Nevertheless, are there any ways to stack those 4 columns using the original singular formula using query, importrange and curly brackets?

={Query(Importrange(SheetA),"select Col1,Col2,Col3,Col4 where Col1 is not null");
Query(Importrange(SheetB),"select Col1,Col2,Col3,Col4 where Col1 is not null")}

Answer 2

Where you have 2 source files (Source1, Source2), and you want to use stack the data using IMPORTRANGE into another file (Combined)

There are 2 parts to this solution, and you can do Part 1 and Part 2 in any order.

Part 1

Give approvals to each of the imported ranges.

On a temporary sheet (that can be removed later) in the Combined file.

Use a standard IMPORTRANGE formula like this for Source1 (example, where you must replace "Source1_ID" with the ID of your Source1 file):

=IMPORTRANGE("https://docs.google.com/spreadsheets/d/Source1_ID", "Sheet1!A2:D")

After you have given approval, delete the formula from the cell, and do it again for Source2 (example, where you must replace "Source2_ID" with the ID of your Source2 file)

=IMPORTRANGE("https://docs.google.com/spreadsheets/d/Source2_ID", "Sheet1!A2:D")

Part 2

In the Combined file, go to the sheet where you want the combined data, and enter in the complete QUERY formula (example, where you must replace "Source1_ID" and "Source2_ID" with the ID's of both of your source files)

=QUERY({IMPORTRANGE("https://docs.google.com/spreadsheets/d/Source1_ID", "Sheet1!A2:D"); IMPORTRANGE("https://docs.google.com/spreadsheets/d/Source2_ID", "Sheet1!A2:D")}, "Select Col1, Col2, Col3, Col4 Where Col1 Is Not Null")

Answer 3

first, you need to run every importrange separately as standalone formula to connect your sheets by allowing access

when done, only then, you can run this array:

=QUERY({IMPORTRANGE("url_or_id", "SheetA!A:D"); 
        IMPORTRANGE("url_or_id", "SheetB!A:D")}, 
 "where Col1 is not null", 0)

Answer 4

So we have 3 things to do:

• Transpose so the columns are below the other.
• Flatten so that it is ordered as necessary.
• Filter to remove extra blank columns we selected.

This will work wonders considering the columns are different lengths and are already next to each other as in a table.

If columns are at multiple places use {A:A;D:D} to select columns and remove flatten and transpose.

=filter(flatten(transpose(A1:D10)), len(flatten(transpose(A1:D10))))