How to use QUERY and IMPORTRANGE function together to extract and transpose data in google sheet?

1
votes

enter image description here

Hi everyone,

I have 3 range of data (F8:G23,F31:G46,F54:G69) in one google sheet as shown above, I want to export this 3 range of data into another google sheet by using IMPORTRANGE. At the same time, I want to transpose the data and remove the blank cell between 3 range of data (F24:G30,F47:G53). This is the formula that I had tried:

transpose(Query({IMPORTRANGE("https://docs.google.com/spreadsheets/d/1DD9qniGyi9QSE9TYw-EnH-3Cug--BwqyViPW2nrrPLM/edit", "Sheet1!F8:G133")},"select * where Col1 is not null"))

The screenshot below is the expected output after using the formula above:

enter image description here

However, what I get from the formula is not what I expected. All letter had gone and the range size is 2 rows x 25 columns instead of 6 rows x 11 columns. Besides, the S and B also attached to the name Group 1 and Group 2. I attached a screenshot below to show what I got from the formula above:

enter image description here

What should I do to my formula so that I can get the expected output? Any help will be greatly appreciated!

1
jquerygoogle-sheetsgoogle-sheets-formula

1 Answers

2
votes

use:

=INDEX(TRANSPOSE(QUERY(TO_TEXT({
 IMPORTRANGE("1DD9qniGyi9QSE9TYw-EnH-3Cug--BwqyViPW2nrrPLM", "Sheet1!F8:G23"),
 IMPORTRANGE("1DD9qniGyi9QSE9TYw-EnH-3Cug--BwqyViPW2nrrPLM", "Sheet1!F31:G46"),
 IMPORTRANGE("1DD9qniGyi9QSE9TYw-EnH-3Cug--BwqyViPW2nrrPLM", "Sheet1!F54:G69")}),
 "where Col1 is not null", )))