Query importrange HUGE multiple sheets and turns out error

Question

so I have 5 separate google sheets, each one with the same title and format enter image description here. There are 8000-10000 rows each so far, as it's a daily sales updating data, yearly may finish with 20000-40000 rows each.

So what I want to do is to merge all of them into another single sheet, and I've tried the following formulas: enter image description here

Formula A

={IMPORTRANGE(B2,C2);IMPORTRANGE(B3,C3);IMPORTRANGE(B4,C4);IMPORTRANGE(B5,C5);IMPORTRANGE(B6,C6)} Results:

#VALUE! "In ARRAY_LITERAL, an Array Literal was missing values for one or more rows."

Formula B (reduced the data to only one month)

=QUERY({IMPORTRANGE(B2,C2);IMPORTRANGE(B3,C3);IMPORTRANGE(B4,C4);IMPORTRANGE(B5,C5);IMPORTRANGE(B6,C6)},"SELECT * Where Col2 > DATE'2022-5-1' AND Col2 <= DATE'2022-5-31' ",0)

Result:

#VALUE! "In ARRAY_LITERAL, an Array Literal was missing values for one or more rows."

Formula C (check if is the problem of the code)

={IMPORTRANGE(B2,C2);IMPORTRANGE(B3,C3)}

Results: It works.

So my question is that, there is anyway via google sheet let me merge all the 5 sheets in one (5sheets*40000rows into one sheet) , or it's not the place to manage that volumn of data?

Answer 1

every importrange needs to be run separately before you use it in your joint formula. the array error you are getting is a result of some importranges importing those 7 columns and those importranges that are not connected output one single cell. make sure you allow access to all your importranges.