google-query-language

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

Can the google spreadsheet ‘query’ function be used in google apps script?

I do not know whether there is a restriction on that … function test () { var req = query(“=QUERY(shopT!B2:E; \”select min(E) where (B=3 or B=4 and D=2) group by C, D\”)”); Logger.log(req); } function query(request) { var sheet = sp.insertSheet(); var r = sheet.getRange(1, 1).setFormula(request); var reply = sheet.getDataRange().getValues(); sp.deleteSheet(sheet); return reply; }

Can the google spreadsheet ‘query’ function be used in google apps script?

I do not know whether there is a restriction on that … function test () { var req = query(“=QUERY(shopT!B2:E; \”select min(E) where (B=3 or B=4 and D=2) group by C, D\”)”); Logger.log(req); } function query(request) { var sheet = sp.insertSheet(); var r = sheet.getRange(1, 1).setFormula(request); var reply = sheet.getDataRange().getValues(); sp.deleteSheet(sheet); return reply; }

How to compare dates or date against today with query on google sheets?

There’s no today() in Query. Use now() instead: =query(sheet1!A3:N, ” select I,M where I = ‘Singapore’ AND M > now() “,0) Or if you want now() without time(equivalent to TODAY()), use: todate(now()) For this to work, provided you have all the correct dates in M in any format, which Google sheets recognises (i.e., the formula … Read more

SUMIFS function in Google Spreadsheet

The simplest way to easily make SumIFS-like functions in my opinion is to combine the FILTER and SUM function. SUM(FILTER(sourceArray, arrayCondition_1, arrayCondition_2, …, arrayCondition_30)) For example: SUM(FILTER(A1:A10;A1:A10>5;B1:B10=1) Explanation: the FILTER() filters the rows in A1:A10 where A1:A10 > 5 and B1:B10 = 1. Then SUM() sums the values of those cells. This approach is very … Read more

ArrayFormula of Average on Infinite Truly Dynamic Range in Google Sheets

QUERY level 1: if all 5 cells in range C2:G have values: =QUERY(QUERY(C2:G, “select (C+D+E+F+G)/5”), “offset 1”, ) if not, then rows are skipped: if empty cells are considered as zeros: =INDEX(QUERY(QUERY({C2:G*1}, “select (Col1+Col2+Col3+Col4+Col5)/5”), “offset 1”, )) to remove zero values we use IFERROR(1/(1/…)) wrapping: =INDEX(IFERROR(1/(1/QUERY(QUERY({C2:G*1}, “select (Col1+Col2+Col3+Col4+Col5)/5”), “offset 1”, )))) to make Col references … Read more