Extract data from different google sheets using QUERY, IMPORTRANGE and TRANSPOSE?

user_15002748 0
Viewed 263

enter image description here

enter image description here

enter image description here

Hi everyone,

I have 3 testing files as shown in the screenshot above. I want to import the data from these 3 files into a master google sheet. This is the formula that I'm using:

=TRANSPOSE(QUERY(TRANSPOSE({IMPORTRANGE(NameList!B3,"Sheet1!B26:L29");IMPORTRANGE(NameList!B4,"Sheet1!B26:L29");IMPORTRANGE(NameList!B5,"Sheet1!B26:L29")}),"where Col1 is not null"))

The outcome output of this formula is: enter image description here

It works perfectly fine when all the data are filled in 3 source files. However, if the first file (testing) is empty, then the output will be #N/A in cell B5 in the master google sheet. May I know how to avoid this situation where the data only come out when the first file is filled with data? I want the formula able to skip the first file if it is empty and continue for the 2nd and the 2rd file.

Any advise will be greatly appreciated!

google-sheets-formula transpose google-sheets jquery
2 Answers

When your range is empty you got error message that is a single cell. Stacking tables {table;table;table;} is possible only when all the tables have equal number of columns. If you have 11 columns (from B to L) you should get a row of 11 empty cells in case of error. So each importrange should be wrapped with iferror or ifna formula that returns empty cells:

iferror(IMPORTRANGE(NameList!B3,"Sheet1!B26:L29");{""\""\""\""\""\""\""\""\""\""\""})

I've built an example file: enter image description here

Here I try to importrange from the same file. In a first row I try to reference non-existing sheet. Second reference is correct. Iferror makes 2 empty cells to prevent from error.

File is here: https://docs.google.com/spreadsheets/d/14AnNAlV7mQmXo9vJ6YQugSm2-PS-RQPoND27wwv5f5k/edit#gid=0

user_109670950

EDIT

I did not understand from your question what you wanted. I am sorry. You made it not clear.
That is what I asked in comment to read this.

Now I see you can use this formula with 2 QUERY

=QUERY(QUERY({IMPORTRANGE(NameList!B3,"Sheet1!B26:L29");
              IMPORTRANGE(NameList!B4,"Sheet1!B26:L29");
              IMPORTRANGE(NameList!B5,"Sheet1!B26:L29")},
                       "where 1=1"), 
          "where Col1 is not null")

It gives you results exactly like you like.
No need for IFERROR.
No need for {""\""\""\""\""\""\""\""\""\""\""}.
I imagine 10 importrange and 20 columns Sheet1!B26:T29 and IFERROR for every importrange.
10 times this:

iferror(IMPORTRANGE(NameList!B3,"Sheet1!B26:L29");{""\""\""\""\""\""\""\""\""\""\""\""\""\""\""\""\""\""\""\""\""\""})

I go crazy

First answer
You also try this trick

=TRANSPOSE(QUERY(TRANSPOSE({IMPORTRANGE(NameList!B3,"Sheet1!B26:L29");IMPORTRANGE(NameList!B4,"Sheet1!B26:L29");IMPORTRANGE(NameList!B5,"Sheet1!B26:L29")}),"where Col1 is not null or 1=1 "))

You see i add or 1=1
This will stop error #N/A in cell B5 in the master google sheet.

user_154256230
Related