No need for a script. This answer uses a series of stacked queries.
=query({ IFERROR(Query({A1:A,B1:J,arrayformula("a"&K1:K)},"Select Col1, Col2, Col11, Col3, Col4 where Col1 is not null and Col3 is not null and Col4 is not null label Col3 'region',Col4 'user', Col11 'program'",1),{"","","","",""}); IFERROR(Query({A1:A,B1:J,arrayformula("a"&K1:K)},"Select Col1, Col2, Col11, Col3, Col5 where Col1 is not null and Col3 is not null and Col5 is not null ",0),{"","","","",""}); IFERROR(Query({A1:A,B1:J,arrayformula("a"&K1:K)},"Select Col1, Col2, Col11, Col3, Col6 where Col1 is not null and Col3 is not null and Col6 is not null ",0),{"","","","",""}); IFERROR(Query({A1:A,B1:J,arrayformula("b"&K1:K)},"Select Col1, Col2, Col11, Col7, Col8 where Col1 is not null and Col7 is not null and Col8 is not null ",0),{"","","","",""}); IFERROR(Query({A1:A,B1:J,arrayformula("b"&K1:K)},"Select Col1, Col2, Col11, Col7, Col9 where Col1 is not null and Col7 is not null and Col9 is not null ",0),{"","","","",""}); IFERROR(Query({A1:A,B1:J,arrayformula("b"&K1:K)},"Select Col1, Col2, Col11, Col7, Col10 where Col1 is not null and Col7 is not null and Col10 is not null"),{"","","","",""})} ,"select Col1,Col2,Col3,Col4,Col5 where Col1 is not null order by Col1 format Col1 'd/M/yyyy'")
SAMPLE OUTPUT

There are six stacked queries
- #1: Program A - First choice
- #2: Program A - Second choice
- #3: Program A - Third choice
- #4: Program B - First choice
- #5: Program B - Second choice
- #6: Program B - Third choice
These have a consistent format:
IFERROR(Query({A1:A,B1:J,arrayformula("a"&K1:K)},"Select Col1, Col2, Col11, Col3, Col5 where Col1 is not null and Col3 is not null and Col5 is not null ",0),{"","","","",""})
Points to note:
IFERROR
is required in case the query returns an empty output. Normally an empty output returns #N/A
, but the IFERROR
enables us to return an empty array: {"","","","",""}
.
arrayformula("a"&K1:K)
: the desired output returns a lower case value for the "Program". This is the method used to create program name. Column K is used as a help column.
The stacked queries are wrapped in a final query with this clause:
"select Col1,Col2,Col3,Col4,Col5 where Col1 is not null order by Col1 format Col1 'd/M/yyyy'"
select Col1,Col2,Col3,Col4,Col5
: return the columns of data
where Col1 is not null
: this drops any of the empty output queries
order by Col1
: Order by date
format Col1 'd/M/yyyy'
: format the date
- When the
query()
function is wrapped in the iferror()
function, the format clause in the query() function will only work if the column is formatted as Automatic. But the iferror()
overrides the column Automatic format and returns a number value. So, the final query (which is NOT wrapped in IFERROR()
includes a format clause for the data values. HT: Ben Liebrand in Sheets query function loses date format when inside 'IFERROR()'