ARRAY_LITERAL error when Query Array Function stacking multiple google form responses

Question

So I have a unique problem. I have a Google form used for site inspections that allows up to 25 "issues" to be entered per Form submission. As normal, when submitted the issues are recorded in a single row in the spreadsheet. To more easily be able to sort through each individual issue, I have to first break them out of each submission and stack them, rather than having to go through each row individually to find the unique issues. The below function repeats the first 4 cells of data (A:D), we use those columns as constants to help filter the data based on location, once we're in our actual inspection portal. The problem is, since its just an array of Queries, it stacks the data even if the relevant data (anything not A:D) is empty. This means that if we only had 2 issues, the breakout has 2 rows of issues and 23 rows where it's just the repeated Col A:D.

={query(Inspection!A2:FJ,"Select A,B,C,D,E,F,G,H,I where E is not null");
query(Inspection!A2:FJ,"Select A,B,C,D,K,L,M,N,O where K is not null");
query(Inspection!A2:FJ,"Select A,B,C,D,Q,R,S,T,U where Q is not null");
query(Inspection!A2:FJ,"Select A,B,C,D,W,X,Y,Z,AA where W is not null");
query(Inspection!A2:FJ,"Select A,B,C,D,AC,AD,AE,AF,AG");
query(Inspection!A2:FJ,"Select A,B,C,D,AI,AJ,AK,AL,AM");
query(Inspection!A2:FJ,"Select A,B,C,D,AO,AP,AQ,AR,AS");
query(Inspection!A2:FJ,"Select A,B,C,D,AU,AV,AW,AX,AY");
query(Inspection!A2:FJ,"Select A,B,C,D,BA,BB,BC,BD,BE");
query(Inspection!A2:FJ,"Select A,B,C,D,BG,BH,BI,BJ,BK");
query(Inspection!A2:FJ,"Select A,B,C,D,BM,BN,BO,BP,BQ");
query(Inspection!A2:FJ,"Select A,B,C,D,BS,BT,BU,BV,BW");
query(Inspection!A2:FJ,"Select A,B,C,D,CE,CF,CG,CH,CI");
query(Inspection!A2:FJ,"Select A,B,C,D,CK,CL,CM,CN,CO");
query(Inspection!A2:FJ,"Select A,B,C,D,CQ,CR,CS,CT,CU");
query(Inspection!A2:FJ,"Select A,B,C,D,CW,CX,CY,CZ,DA");
query(Inspection!A2:FJ,"Select A,B,C,D,DC,DD,DE,DF,DG");
query(Inspection!A2:FJ,"Select A,B,C,D,DI,DJ,DK,DL,DM");
query(Inspection!A2:FJ,"Select A,B,C,D,DO,DP,DQ,DR,DS");
query(Inspection!A2:FJ,"Select A,B,C,D,DU,DV,DW,DX,DY");
query(Inspection!A2:FJ,"Select A,B,C,D,EA,EB,EC,ED,EE");
query(Inspection!A2:FJ,"Select A,B,C,D,EG,EH,EI,EJ,EK");
query(Inspection!A2:FJ,"Select A,B,C,D,EM,EN,EO,EP,EQ");
query(Inspection!A2:FJ,"Select A,B,C,D,ES,ET,EU,EV,EW");
query(Inspection!A2:FJ,"Select A,B,C,D,EY,EZ,FA,FB,FC")
}

I try to fix this by adding where COL is not null"); but I keep getting an error message In ARRAY_LITERAL, an Array Literal was missing values for one or more rows. when I get to the 4th query function. Could anyone assist with why I'm getting this error? I'm sure there's a better way of setting this up, or even a script that would be more efficient, so if anyone has any suggestions on doing it another way, I'm game to learn. Thanks.

score 1 · Answer 1 · answered Dec 19 '22 at 21:32

1

It is actually a very common problem!

try this formula instead:

=ARRAYFORMULA(QUERY(SPLIT(FLATTEN(Inspection!A2:A&"|"&Inspection!B2:B&"|"&Inspection!C2:C&"|"&Inspection!D2:D&"|"&FILTER(Inspection!E2:EY&"|"&Inspection!F2:EZ&"|"&Inspection!G2:FA&"|"&Inspection!H2:FB&"|"&Inspection!I2:FC,MOD(COLUMN(Inspection!E2:EY),5)=0)),"|",0,0),"where Col5 is not null",0))

answered Dec 19 '22 at 21:32

MattKing

7,373
8
13

Huh, never used Flatten() before, I'll have to learn more about that. Appreciate the assistance! – J.Raab Dec 20 '22 at 14:25

score 1 · Accepted Answer · answered Dec 19 '22 at 22:06

try:

=QUERY({
 query(Inspection!A2:FJ,"Select A,B,C,D,E,F,G,H,I");
 query(Inspection!A2:FJ,"Select A,B,C,D,K,L,M,N,O");
 query(Inspection!A2:FJ,"Select A,B,C,D,Q,R,S,T,U");
 query(Inspection!A2:FJ,"Select A,B,C,D,W,X,Y,Z,AA");
 query(Inspection!A2:FJ,"Select A,B,C,D,AC,AD,AE,AF,AG");
 query(Inspection!A2:FJ,"Select A,B,C,D,AI,AJ,AK,AL,AM");
 query(Inspection!A2:FJ,"Select A,B,C,D,AO,AP,AQ,AR,AS");
 query(Inspection!A2:FJ,"Select A,B,C,D,AU,AV,AW,AX,AY");
 query(Inspection!A2:FJ,"Select A,B,C,D,BA,BB,BC,BD,BE");
 query(Inspection!A2:FJ,"Select A,B,C,D,BG,BH,BI,BJ,BK");
 query(Inspection!A2:FJ,"Select A,B,C,D,BM,BN,BO,BP,BQ");
 query(Inspection!A2:FJ,"Select A,B,C,D,BS,BT,BU,BV,BW");
 query(Inspection!A2:FJ,"Select A,B,C,D,CE,CF,CG,CH,CI");
 query(Inspection!A2:FJ,"Select A,B,C,D,CK,CL,CM,CN,CO");
 query(Inspection!A2:FJ,"Select A,B,C,D,CQ,CR,CS,CT,CU");
 query(Inspection!A2:FJ,"Select A,B,C,D,CW,CX,CY,CZ,DA");
 query(Inspection!A2:FJ,"Select A,B,C,D,DC,DD,DE,DF,DG");
 query(Inspection!A2:FJ,"Select A,B,C,D,DI,DJ,DK,DL,DM");
 query(Inspection!A2:FJ,"Select A,B,C,D,DO,DP,DQ,DR,DS");
 query(Inspection!A2:FJ,"Select A,B,C,D,DU,DV,DW,DX,DY");
 query(Inspection!A2:FJ,"Select A,B,C,D,EA,EB,EC,ED,EE");
 query(Inspection!A2:FJ,"Select A,B,C,D,EG,EH,EI,EJ,EK");
 query(Inspection!A2:FJ,"Select A,B,C,D,EM,EN,EO,EP,EQ");
 query(Inspection!A2:FJ,"Select A,B,C,D,ES,ET,EU,EV,EW");
 query(Inspection!A2:FJ,"Select A,B,C,D,EY,EZ,FA,FB,FC")}, 
 "where Col5 is not null", )

Wow, that's actually incredibly simple. I didn't even think about nesting all my array queries inside a master query like that. Much appreciated. — J.Raab, Dec 20 '22 at 14:24

ARRAY_LITERAL error when Query Array Function stacking multiple google form responses

2 Answers2