Get row number with Google spreadsheet query language

Question

I'd like to get the row number of the data I need with query language, i.g: select A,ROW_NUMBER() where (B=1).

But it seems the query language doesn't provide function like: ROW_NUMBER()?

How do I do that, other than query the whole column and count it in JavaScript?

Answer 1

You can include a row number in a query() by using an { array expression }, like this:

=arrayformula( query({A2:B, row(A2:B)}, "select Col1, Col3 where Col2 = 1", 0) )

In the query statement, Col1 is column A, Col2 is column B, and Col3 is the "virtual" column that contains row numbers. These "ColX" type column references need to be used whenever the data is not a spreadsheet range or a reference, but a computed array such as an arrayformula() result or an { array expression }.

The arrayformula() wrapper is required here to evaluate the row() function over a range of cells rather than just the top left cell.

Answer 2

Note that when the spreadsheet is in a locale that uses commas as decimal separators, you will need to change the comma in the { array expression } to a backslash \ and the rest of the commas to semicolons ; like this:

=arrayformula( query({A2:B \ row(A2:B)}; "select Col1, Col3 where Col2 = 1"; 0) )

From the array help page:

Note: For countries that use commas as decimal separators (for example €1,00), commas would be replaced by backslashes (\) when creating arrays.

To set the spreadsheet locale, choose File > Spreadsheet settings.