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 record 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 '18 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 Dec 10 '18 at 16:56
  • 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 Dec 10 '18 at 18:37
  • Gotcha. Here is a GSheet -> docs.google.com/spreadsheets/d/… – John Faig Dec 10 '18 at 19:39

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.


       COUNTA(QUERY(DataTable, "Select A", 1))-1)))}, 
 ARRAYFORMULA(QUERY(DataTable, "Select *", 1))}


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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