Google-sheets – How to import/merge data from from 2 separate sheets from another spreadsheet

formulasgoogle sheetsgoogle-sheets-arrayformulagoogle-sheets-queryimportrange

I need to import data from "Spreadsheet A" to "Spreadsheet B" but all the data is in two separate sheets "MY" and "NY" in "Spreadsheet A"

I basically need exactly this but with a function to import from another spreadsheet, not only within the same one

=QUERY({{ARRAYFORMULA(ROW(INDIRECT("A1:A"&COUNTA(QUERY(MY!A2:A; "select A where A is not null")))))\QUERY(MY!A2:A; "select A where A is not null")};  {ARRAYFORMULA(ROW(INDIRECT("A1:A"&COUNTA(QUERY(NY!A2:A; "select A where A is not null")))))\QUERY(NY!A2:A; "select A where A is not null")}}; "select Col2 order by Col1")

Best Answer

  • first, run one IMPORTRRANGE to connect the sheets:

    =IMPORTRANGE("ID"; "MY!A2")

  • then try:

    =QUERY({{ARRAYFORMULA(ROW(INDIRECT("A1:A"&COUNTA(
 QUERY(IMPORTRANGE("ID"; "MY!A2:A"); "select Col1 where Col1 is not null")))))\
 QUERY(IMPORTRANGE("ID"; "MY!A2:A"); "select Col1 where Col1 is not null")};
        {ARRAYFORMULA(ROW(INDIRECT("A1:A"&COUNTA(
 QUERY(IMPORTRANGE("ID"; "NY!A2:A"); "select Col1 where Col1 is not null")))))\
 QUERY(IMPORTRANGE("ID"; "NY!A2:A"); "select Col1 where Col1 is not null")}}; 
"select Col2 order by Col1")

    0

  • also make sure that both spreadsheets have the same amount of rows

Related Topic