CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
tfchen
tfchen

Reputation: 589

Get row number with Google spreadsheet query language

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?

Upvotes: 15

Views: 24422

Answers (2)

user3717023
user3717023

Reputation:

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.

Upvotes: 25

paolo antonelli
paolo antonelli

Reputation: 101

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.

Upvotes: 10

Related Questions