google-sheetsgoogle-sheets-formulagoogle-query-language

Google Sheets - how to show sheet name in SELECT result of QUERY() function


In this formula I need to show the Sheetname in the first column, either concatenated to Col1 or it could be the first column, like this.

SELECT SheetName,Col1,Col2,Col3,Col4,Col5

I am not even sure what to try. Just to test I tried hardcoding with CONCATENATE() like SELECT CONCATENATE("Jan",Col1), it throws error. Tried SELECT "Jan" & Col1, doesn't work.

I tried this too, it doesn't throw error but it doesn't show the word "Jan"

QUERY({ArrayFormula("Jan "&Jan!A3:E);Feb!A3:E

Formula:

=SORT(
    ARRAYFORMULA(IF(A2="[All]",
                    QUERY({Jan!A3:E;Feb!A3:E;Mar!A3:E;Apr!A3:E;May!A3:E;Jun!A3:E;Jul!A3:E;Aug!A3:E;Sep!A3:E;Oct!A3:E;Nov!A3:E;Dec!A3:E}, 
                            "SELECT Col1,Col2,Col3,Col4,Col5 WHERE 1=1 " 
                                    & IF(B2="[All]",""," AND LOWER(Col4)=LOWER('" & B2 & "')") 
                                    & IF(C2="[All]",""," AND LOWER(Col5)=LOWER('" & C2 & "')")),
                    QUERY(INDIRECT("'"&A2&"'!A3:E"), 
                            "SELECT Col1,Col2,Col3,Col4,Col5 WHERE 1=1 " 
                                    & IF(B2="[All]",""," AND LOWER(D)=LOWER('" & B2 & "')") 
                                    & IF(C2="[All]",""," AND LOWER(E)=LOWER('" & C2 & "')")
                            )
                    )
    ),IF(LEN(C4)>0,3,IF(LEN(B5)>0,2,1)),IF(C4="z",FALSE,TRUE)

)


Solution

  • Try this out:

    =SORT(IF(A2="[All]",
             QUERY({Jan!A3:E,IF(ROW(Jan!A3:E),"Jan");
                    Feb!A3:E,IF(ROW(Feb!A3:E),"Feb");
                    Mar!A3:E,IF(ROW(Mar!A3:E),"Mar");
                    Apr!A3:E,IF(ROW(Apr!A3:E),"Apr");
                    May!A3:E,IF(ROW(May!A3:E),"May");
                    Jun!A3:E,IF(ROW(Jun!A3:E),"Jun");
                    Jul!A3:E,IF(ROW(Jul!A3:E),"Jul");
                    Aug!A3:E,IF(ROW(Aug!A3:E),"Aug");
                    Sep!A3:E,IF(ROW(Sep!A3:E),"Sep");
                    Oct!A3:E,IF(ROW(Oct!A3:E),"Oct");
                    Nov!A3:E,IF(ROW(Nov!A3:E),"Nov");
                    Dec!A3:E,IF(ROW(Dec!A3:E),"Dec")}, 
                       "SELECT Col6,Col1,Col2,Col3,Col4,Col5 WHERE 1=1 " 
                       & IF(B2="[All]",""," AND LOWER(Col4)=LOWER('" & B2 & "')") 
                       & IF(C2="[All]",""," AND LOWER(Col5)=LOWER('" & C2 & "')")),
              QUERY({INDIRECT(A2&"!A3:E"),IF(ROW(INDIRECT(A2&"!A3:E")),A2)}, 
                       "SELECT Col6,Col1,Col2,Col3,Col4,Col5 WHERE 1=1 " 
                       & IF(B2="[All]",""," AND LOWER(Col4)=LOWER('" & B2 & "')") 
                       & IF(C2="[All]",""," AND LOWER(Col5)=LOWER('" & C2 & "')")
                                    )
                            )
            ,IF(LEN(C4)>0,3,IF(LEN(B5)>0,2,1)),IF(C4="z",FALSE,TRUE)
    )