Google Sheets QUERY reference a single cell value as a static column

Question

I have the following example spreadsheet: https://docs.google.com/spreadsheets/d/1uv0Lj5sayraH4FOPE1qZ3coLA9A2incLuX2JYD5y7SQ/edit?usp=sharing

I have a query on Sheet 2 that references sheet1 and sheet2. I would like to have the query return 3 columns, Name, ID, and GroupName. GroupName should reference A1 from the sheet. I cannot find a way to do this, but it seems that it should be possible. Something like

=query({Sheet1!A:B; Sheet2!A:B},"Select Col1, Col2, A1 Col3 where Col1 is not null")

Answer 1

Try:

=arrayformula(query({iferror(Sheet1!A2:A/0,Sheet1!A1),Sheet1!A2:B; iferror(Sheet2!A3:A/0,Sheet2!A1),Sheet2!A3:B},"where Col2 is not null label Col1 'GroupName'",1))

You can alter the column order like this (select Col2, Col3, Col1):

=arrayformula(query({iferror(Sheet1!A2:A/0,Sheet1!A1),Sheet1!A2:B; iferror(Sheet2!A3:A/0,Sheet2!A1),Sheet2!A3:B},"select Col2, Col3, Col1 where Col2 is not null label Col1 'GroupName'",1))

Answer 2

I was able to get it to work by swapping the semi-colon for a comma

=query({Sheet1!A:B, Sheet2!A:B},"Select Col1, Col2, Col3 where Col1 is not null")

Col3 should be col A from sheet2