CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formula
Tom J Nowell
Tom J Nowell

Reputation: 9981

Use Google Sheets ARRAYFORMULA to create QUERY input

I have a QUERY that looks up values from various separate sheets, this is its input:

=QUERY( {
        IFNA( QUERY({'Week 1'!A2:Z133;'Week 2'!A2:Z133;'Week 3'!A2:Z133;'Week 4'!A2:Z133;'Week 5'!A2:Z133},"select Col26, Col1, Col2,  Col4,  Col5  where Col1 IS NOT NULL and Col4  IS NOT NULL", 0), { "","","","","" } );
        IFNA( QUERY({'Week 1'!A2:Z133;'Week 2'!A2:Z133;'Week 3'!A2:Z133;'Week 4'!A2:Z133;'Week 5'!A2:Z133},"select Col26, Col1, Col6,  Col8,  Col9  where Col1 IS NOT NULL and Col8  IS NOT NULL", 0), { "","","","","" } );
        IFNA( QUERY({'Week 1'!A2:Z133;'Week 2'!A2:Z133;'Week 3'!A2:Z133;'Week 4'!A2:Z133;'Week 5'!A2:Z133},"select Col26, Col1, Col10, Col12, Col13 where Col1 IS NOT NULL and Col12 IS NOT NULL", 0), { "","","","","" } );
        IFNA( QUERY({'Week 1'!A2:Z133;'Week 2'!A2:Z133;'Week 3'!A2:Z133;'Week 4'!A2:Z133;'Week 5'!A2:Z133},"select Col26, Col1, Col14, Col16, Col17 where Col1 IS NOT NULL and Col16 IS NOT NULL", 0), { "","","","","" } );
        IFNA( QUERY({'Week 1'!A2:Z133;'Week 2'!A2:Z133;'Week 3'!A2:Z133;'Week 4'!A2:Z133;'Week 5'!A2:Z133},"select Col26, Col1, Col18, Col20, Col21 where Col1 IS NOT NULL and Col20 IS NOT NULL", 0), { "","","","","" } )
}, "SELECT * WHERE Col1 IS NOT NULL ORDER BY Col1")

The {} gets repetitive and longwinded, and requires manually updating every time I add another week.

Recently I discovered I can generate a list of Week sheet names using this formula:

=ARRAYFORMULA(
    "Week " & 
    {1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21;22;23;24;25;26;27;28;29;30}
    & "!A2:Z133"
)

and then in another column list only those sheets that actually exist:

=IF(
        ISERROR(CELL("address",INDIRECT($U2))),
        "",
        $U2
)

Now I have a column with values such as Week1!A2:Z133, Week2!A2:Z133, etc. How can I use this column to create the QUERY formula source automatically?

Using this formula gets me the first range referenced but none of the subsequent ones in the column:

={ARRAYFORMULA(INDIRECT(AA:AA) )}

Upvotes: 0

Views: 126

Answers (1)

MattKing
MattKing

Reputation: 7783

Here within this sheet is a basic script for combining tabs that start with the word "Week".

function tabCombo(){
    var ss = SpreadsheetApp.getActive();

    //Filters sheets to just the ones that start with "Week"
    var sheets = ss.getSheets().filter(function (e){return e.getName().slice(0,4)=='Week'});

    //combines all tab values into one array and filters out the rows with a certain value in the first column
    var combo = sheets.map(e=>e.getDataRange().getValues()).flat().filter(e=>e[0]!='Header1');

    //writes that new value to a 'Master' tab.
    ss.getRange('Master!A2').offset(0,0,combo.length,combo[0].length).setValues(combo);
}

Take note of the word "Week" which is how it decides which tabs to grab.

Take note of the number 4 (which is how many letters "Week" has)

Take note of the range "Master!A2" which is the top left corner of where the combined data should go.

Take note of the term "Header1" which is how the combined array filters out the header rows from all the tabs.

Upvotes: 2

Related Questions