CODEWITHSUNDEEP
arraysgoogle-sheetsimportgoogle-sheets-formulagoogle-query-language
Trung Pham
Trung Pham

Reputation: 33

Stack multiple columns on top of eachother on Google Sheet?

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

However, the resulting 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 each other 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 combining them afterward 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?

Upvotes: 2

Views: 2789

Answers (4)

Rishabh Tripathi
Rishabh Tripathi

Reputation: 1

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

Upvotes: 0

player0
player0

Reputation: 1

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)

Upvotes: 1

Mr Shane
Mr Shane

Reputation: 636

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

Upvotes: 1

Trung Pham
Trung Pham

Reputation: 33

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

Upvotes: 1

Related Questions