Google Sheets QUERY Function: Select Columns by Name

you can transpose it and header row becomes a column. then: =TRANSPOSE(QUERY(TRANSPOSE(A1:C), “where Col1 matches ‘bb header|student'”, )) where A1:C is your named range (including header row) update: =QUERY({AI1:AK6}, “select Col2,Col3 where Col1=’Jones'”, 1) dynamically: =LAMBDA(p, t, s, QUERY({AI1:AK6}, “select Col”&t&”,Col”&s&” where Col”&p&”=’Jones’ order by Col”&t&” desc”, 1)) (MATCH(“principal”, AI1:AK1, ), MATCH(“teacher”, AI1:AK1, ), MATCH(“student”, … Read more

How to Print sheet/range using .gs script in Google Sheets?

You can use the following script: var PRINT_OPTIONS = { ‘size’: 7, // paper size. 0=letter, 1=tabloid, 2=Legal, 3=statement, 4=executive, 5=folio, 6=A3, 7=A4, 8=A5, 9=B4, 10=B ‘fzr’: false, // repeat row headers ‘portrait’: true, // false=landscape ‘fitw’: true, // fit window or actual size ‘gridlines’: false, // show gridlines ‘printtitle’: false, ‘sheetnames’: false, ‘pagenum’: ‘UNDEFINED’, … Read more

How to make a range repeat n-times in Google SpreadSheet

I would use split() function instead of arrayformula() and rept() function to repeat cell positions. For example, if your n=4 the formula will look like this: =split(rept(C1&”;”,4),”;”) rept() repeats cell position C1+semicolon four times creating a string and split() function divides created string by semicolons to horizontal cells. You can rotate resulted horizontal table to … Read more

Stacking multiple columns on to one?

Updated Answer I was right there is a much better solution. It’s been posted below but I’m copying it here so it’s in the top answer: =unique({A:A;B:B}) Caveat: This will include one blank cell in certain scenarios (such as if there’s one at the end of the first list). If you’re not concerned with ordering … Read more

How to create all possible pair combinations without duplicates in Google Sheets?

google-sheets It is a hard task for native functions. Try a script and use it as a custom function: function getTournament(teams_from_range) { // teams_from_range — 2D Array var teams = []; // convert to list teams_from_range.forEach(function(row) { row.forEach(function(cell) { teams.push(cell); } ); } ); return getTournament_(teams); } function getTournament_(teams) { var start = 0; var … Read more