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

Question

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:

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:

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

player0 · Accepted Answer · 2021-02-08 09:14:14Z

2

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

edited Feb 8, 2021 at 9:14

answered Feb 8, 2021 at 8:19

player0

131k14 gold badges91 silver badges149 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related