google-sheets - w3toppers.com

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

Google Sheet API v4

I believe you are missing API in the url. This is from google spreadsheet api doc. To acquire an API key: Open the Credentials page in the API Console. API keys: A request that does not provide an OAuth 2.0 token must send an API key. The key identifies your project and provides API access, … Read more

In ARRAY_LITERAL, an Array Literal was missing values for one or more rows – indirect formula error in google spreadsheet

Number of columns in both sheets must be the same: Try ={Sheet1!A2:D10;Sheet2!A2:D10}

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

Count cells that contain any text

You can pass “<>” (including the quotes) as the parameter for criteria. This basically says, as long as its not empty/blank, count it. I believe this is what you want. =COUNTIF(A1:A10, “<>”) Otherwise you can use CountA as Scott suggests

Col BY causing parse error in Google sheets query

BY column needs to be escaped with `: =QUERY(‘Applications received’!B:DL, “select B,CT,CU,CV,CW,CX,`BY` where B is not null order by CX”) https://developers.google.com/chart/interactive/docs/querylanguage#reserved-words

New Google spreadsheet Concatenate limit 50000 characters

Don’t know if this is working, but might be worth a shot. Someone posted this on Google Docs help forum. /…/ If you want more than 50,000 characters in a single cell, you can use QUERY’s header clause. Example: =ArrayFormula(query(row(A1:A70000),,100000)) This creates a cell with 408,893 characters. You can verify by using the LEN function. … 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