Search code examples
arraysgoogle-sheetsfilterarray-formulasgoogle-query-language

Query multiple google sheets in different sheets with sheet name in column1

I need help to show column 1 to get where sheet it came from, my current formula contains this:

=ARRAYFORMULA(QUERY({
{Product1!$AZ2:$AZ&"Product1",Product1!$A2:$AS};
{Product2!$AZ2:$AZ&"Product2",Product2!$A2:$AS}},"SELECT Col2,Col3,Col4
WHERE Col1 is not null 
LABEL Col2 'Date', 
Col3 'Data1', Col4 'Data2'"))

what's the code I am missing? Column 1 should contain either Product 1 and Product 2 depending on which row. Thanks

Solution

  • add Col1 into select:

    =ARRAYFORMULA(QUERY({
{Product1!$AZ2:$AZ&"Product1",Product1!$A2:$AS};
{Product2!$AZ2:$AZ&"Product2",Product2!$A2:$AS}},"SELECT Col1,Col2,Col3,Col4
WHERE Col1 is not null 
LABEL Col2 'Date', 
Col3 'Data1', Col4 'Data2'"))