Spread single row into multiple in Google Sheets

Question

I have data in a google sheet in the following format

group_id	id_1	id_2	id_3	id_4
1	b2260c55-c4a2-4f16-840f-6e0a572861ee	e63c6878-1ea9-4715-ae11-82d09e788a2d	2e381820-1b30-49d5-9e04-d5eff03a0341
2	9a6dfdd4-204d-46b6-95bb-a1fb2323ea79	3f5d47c4-971a-4dfc-b05e-e664b96584e8
3	4e9e58b4-1eae-4a2c-bc7d-cfe90099d154	e19c3aba-3c50-483a-9363-dfc538458bd4	93b8534b-902b-4404-8a10-0cbb9bd1db35	02ffa092-a305-4783-8c51-7cf71e7384d0

Each group_id has anywhere from 2-15 IDs listed next to it. What I want to do is take each row and split it into multiple rows based on the number of IDs. So taking just group_id 1 as an example the below is my desired output

group_id	id
1	b2260c55-c4a2-4f16-840f-6e0a572861ee
1	e63c6878-1ea9-4715-ae11-82d09e788a2d
1	2e381820-1b30-49d5-9e04-d5eff03a0341

Is there a way I can do this using formulas or other Gsheet tools other than scripting? I need to do this as a one-time thing so even a multi-step process works as long as I do not need to do this super manually. Thanks in advance!

Answer 1

Assuming the table is in A1:E, you can use the following formula:

=ARRAYFORMULA(QUERY(SPLIT(TOCOL(A2:A&"|"&B2:E),"|"),"where Col2<>''"))

Answer 2

Here's one approach you may test out:

=let(Σ,tocol(,1), reduce(Σ,indirect("Sheet!B2:"&index(match(,0/(Sheet!A:A<>"")))),lambda(a,c,
                   vstack(if(iserror(a&""),Σ,a),if(len(c),hstack(index(Sheet!A:A,row(c)),c),Σ)))))

• Assumed input tab name as Sheet which you may need to change accordingly