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?

score 24 · Accepted Answer · edited May 03 '21 at 12:11

24

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.

edited May 03 '21 at 12:11

doubleunary

13,842
3
18
51

answered Jul 11 '16 at 22:59

When you say "add a column" do you mean manually in the sheet, or only programmatically as shown in your example? Is this for the Google Visualization API or Google Sheets API? – ariestav Feb 01 '20 at 05:23
This is a Google Sheets spreadsheet formula that includes the built-in `query()` function, which uses the [GViz query language](https://developers.google.com/chart/interactive/docs/querylanguage#Language_Syntax). – doubleunary May 02 '21 at 07:04

score 8 · Answer 2 · edited May 03 '21 at 08:07

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.

Get row number with Google spreadsheet query language

2 Answers2

Linked