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