Adding a header row between vertically joined queries (Or how to pad an array / create a fixed size array)

Question

I am trying to make a google sheet that has a list of employees grouped by work area, where employees can be in multiple sections.

In our data structure we have a column G that is all the different groups they are approved to work in "Discord", "Live Monitoring", etc etc etc... That is just line feed separated text.

I have combined some queries like this that works from a data standpoint:

={QUERY('Active Unique'!A3:P,"Select * where G contains 'Discord'");QUERY('Active Unique'!A3:P,"Select * where G contains 'Live Monitoring'")}

I want a divider / header row between each query that just says "Discord", or "Live Monitoring". For it to work in this syntax the number of columns in that header has to match the return of the query (in this case the width of the sheet). I thought array_constrain would be my savior, but it only reduces the size of an array, it does not expand them. I am thinking something like {"Discord","","",""....} at the start of each query with the number or columns equal to the width of the sheet. Easy enough to get with col(1:1) but I don't know how to construct a fixed size array or preferably how to pad one out. I could make a custom function for it. That is sort of the nuclear option and I would prefer not doing that so that the formula is more portable.

We currently have columns out to P in this sheet and it would be a visual nightmare to have a manually made array like that not to mention upkeep when a column is added to the source data.

Any ideas?

Answer 1

try:

=ArrayFormula({"Discord", IFERROR(COLUMN(B:P)/0)})