Google sheets multiple queries, how do I append the sheet name-CodePudding

=sort({QUERY(Kraft!A2:K500, "SELECT A,G,H WHERE G!='' AND J=''", 1); QUERY(Beacon!A2:H500, "SELECT A,B,F WHERE B!='' AND G=''", 1)},3,true)

Comes out like this

Pro Number 1 Trailer 1 Date 1

Pro Number 2 Trailer 2 Date 2

Pro Number 3 Trailer 3 Date 3

Pro Number 4 Trailer 4 Date 4

I would like to append the sheet name, or even simply text (sheet names shouldnt change much, so I can manually update the queries as needed)

Kraft Pro Number 1 Trailer 1 Date 1

Beacon Pro Number 2 Trailer 2 Date 2

Kraft Pro Number 3 Trailer 3 Date 3

Beacon Pro Number 4 Trailer 4 Date 4

Also I should note that there will be about 10 queries in total

CodePudding user response：

Try this

=SORT({
 TRANSPOSE(SPLIT(REGEXREPLACE(REPT(","&"Kraft",
                              COUNTA(QUERY(Kraft!A2:K500, "SELECT A,G,H WHERE G!='' AND J=''", 1))),"^.",""),",")),
 QUERY(Kraft!A2:K500, "SELECT A,G,H WHERE G!='' AND J=''", 1);
 TRANSPOSE(SPLIT(REGEXREPLACE(REPT(","&"Beacon",
                              QUERY(Beacon!A2:H500, "SELECT A,B,F WHERE B!='' AND G=''", 1)),"^.",""),",")), 
 QUERY(Beacon!A2:H500, "SELECT A,B,F WHERE B!='' AND G=''", 1)},4,true)

CodePudding user response：

try:

=SORT({
 QUERY(Kraft!A2:K500,  
 "select 'Kraft', A,G,H where G is not null and J='' label'Kraft'''", 1); 
 QUERY(Beacon!A2:H500, 
 "select 'Beacon',A,B,F where B is not null and G='' label'Beacon'''", 1)}, 4, 1)

update:

=SORT({
 IFERROR(QUERY(Kraft!A2:K500,  
 "select 'Kraft', A,G,H where G is not null and J='' label'Kraft'''",  1), {"","","",""}); 
 IFERROR(QUERY(Beacon!A2:H500, 
 "select 'Beacon',A,B,F where B is not null and G='' label'Beacon'''", 1), {"","","",""})}, 4, 1)