CODEWITHSUNDEEP
arraysgoogle-sheetsgoogle-query-language
Karl Reyka
Karl Reyka

Reputation: 1

Concatenate two Google spreadsheets

I have two Google spreadsheets I want to concatenate to make a third. I get the lines of one spreadsheet with this formula:

=QUERY(ImportRange("1IBOBd5LPuN4f2oLnHAVasmAiCgjjtC_WC8uH79RC7wq"; "Suivi cours!A1:K521"); 
 "Select Col1,Col2,Col3,Col4,Col5,Col6,Col7,Col8,Col9,Col11 Where month(Col9) = 1"; 0)

This works well. But I have to get the lines from another spreadsheet the same way. And I don't know what should I do to get the lines of the first Sheet followed by the lines of the second Sheet. How do I place these sequentially into a new spreadsheet?

Example:

Sheet 1: 

Line 1 Sheet 1
Line 2 Sheet 1
Line 3 Sheet 1

Sheet 2:

Line 1 Sheet 2
Line 2 Sheet 2

Then: I need to get in Sheet 3:

Line 1 Sheet 1
Line 2 Sheet 1
Line 3 Sheet 1
Line 1 Sheet 2
Line 2 Sheet 2

Upvotes: 0

Views: 640

Answers (1)

Douglas Gaskell
Douglas Gaskell

Reputation: 10100

Following the same concept as my answer here: https://stackoverflow.com/a/36852051/3547347

You can use embedded arrays to solve this problem.

Assuming you want to combine your IMPORTRANGES:

Combine your import ranges like so:

{{IMPORTRANGE("mySheet", "myRange")};{IMPORTRANGE("mySheet", "myRange")}}

And stick them back into your query function:

QUERY({{IMPORTRANGE("mySheet", "myRange")};{IMPORTRANGE("mySheet", "myRange")}} ; 
"Select Col1,Col2,Col3,Col4,Col5,Col6,Col7,Col8,Col9,Col11 Where month(Col9) = 1 " ; 0)

If you instead want to combine two ranges from two worksheets:

Same concept as above, but replace the IMPORTRANGE() with your sheet references, ie: 

{{Sheet1!A:C1000};{Sheet2!A:C500}}

Note: Both ranges must have the same width. You cannot use embedded arrays on ranges with varying widths.

Upvotes: 1

Related Questions