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)