Is it possible to add a column of numbers to the results of a query? It would be nice to have a number (from 1 to n) next to each records returned from a query.

  • John, I'm not clear on what your wanting. Are you just wanting to number the items in your QUERY list (i.e., first item has "1" in a column to the left of it, second item has "2" in that column, etc.)? If so, yes, this is possible. However, it's always hard (and sometimes impossible) to explain how to do something without seeing the actual sheet. If you'd like to share a link to your sheet (or a copy of sample data) here, with permissions set to "Anyone with the link can Edit," I'm sure I or someone else here can help you get the result you want. – Erik Tyler Dec 8 at 15:13
  • Thanks for clarifying. Yes, I am looking to number each row returned from a query. I prefer a formula, but I am happy to use a script if I can obtain the size of the query results. This request is not related to a specific query as much as I want to understand how to do it. – John Faig 2 days ago
  • John, even if you don't have a specific formula use in mind, it always helps to have a sheet to SHOW a formula, rather than explain the "ifs." If you'd just create any old sheet and share the link here, I'll set up an example QUERY for you there to show how to do the numbering. – Erik Tyler 2 days ago
  • Gotcha. Here is a GSheet -> docs.google.com/spreadsheets/d/… – John Faig 2 days ago

OK, John, I've looked at your sheet. It gets a little complicated due to the fact that you're using named ranges. But here is what I placed in your sheet (a single formula in A1):

=ArrayFormula({QUERY({"Number";ROW(INDIRECT("A1:A"&COUNTA(QUERY(DataTable,"Select 'Last_Name'"))-2))}),QUERY(DataTable,"Select * Order by 'Last_Name'")})

You'll recognize your original QUERY second in a list here. The QUERY before that is running the number column.

The "A1:A" in the INDIRECT function is arbitrary. We're just creating an array of "row numbers" that start in row 1, to get the count from 1 to n.

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.