Stack multiple columns on top of eachother on Google Sheet?-CodePudding

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?

CodePudding user response：

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")}

CodePudding user response：

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")

CodePudding user response：

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)