CODEWITHSUNDEEP
google-sheets-formula
Michael Mullins
Michael Mullins

Reputation: 3

Google Formula Query/Importrange

Using the below formula and receiving the error message "In ARRAY_LITERAL, an Array Literal was missing values for one or more rows". Is there an easy way to resolve this or an easier formula to pull?

=QUERY({IMPORTRANGE(Trackers!C27,"Tracker!D4:AC");IMPORTRANGE(Trackers!C28,"Tracker!D4:AC");IMPORTRANGE(Trackers!C29,"Tracker!D4:AC");IMPORTRANGE(Trackers!C30,"Tracker!D4:AC");IMPORTRANGE(Trackers!C31,"Tracker!D4:AC");IMPORTRANGE(Trackers!C32,"Tracker!D4:AC");IMPORTRANGE(Trackers!C33,"Tracker!D4:AC");IMPORTRANGE(Trackers!C34,"Tracker!D4:AC");IMPORTRANGE(Trackers!C35,"Tracker!D4:AC");IMPORTRANGE(Trackers!C37,"Tracker!D4:AC");IMPORTRANGE(Trackers!C38,"Tracker!D4:AC");IMPORTRANGE(Trackers!C39,"Tracker!D4:AC");IMPORTRANGE(Trackers!C40,"Tracker!D4:AC");IMPORTRANGE(Trackers!C41,"Tracker!D4:AC");IMPORTRANGE(Trackers!C42,"Tracker!D4:AC");IMPORTRANGE(Trackers!C43,"Tracker!D4:AC")},"select Col1, Col2, Col3, Col4, Col5, Col7, Col9, Col10, Col11, Col14 where Col9 = 'In Storage : MP3'AND (Col5 ='Awaiting Samples')")

I am not great with formulas, I have tried to make sure everything lines up and I believe it does!

Upvotes: 0

Views: 42

Answers (1)

rockinfreakshow
rockinfreakshow

Reputation: 29982

Try replacing this part of the formula:

{
 <importrange_1>;
 <importrange_2>;
 <importrange_3>;
 <importrange_4>;
 <importrange_5>
}

with

 ifna(vstack(
             <importrange_1>,
             <importrange_2>,
             <importrange_3>,
             <importrange_4>,
             <importrange_5>
      ))

& test

  • this suggestion is based on an assumption that one or more of your importrange data(s) could be blank maybe (null data) & the particular stacking you used via., braces { } will throw an error in such a scenario while the suggested ifna + vstack will be able to handle such a scenario

Also make sure to test each importrange() separately just to make sure they are linked perfect & is pullin' in data right...

Upvotes: 0

Related Questions