8

newbie question, is it possible to select columns by name rather than letter when using Google Query Language with Google Spreadsheets?

This works fine for me: "SELECT A, COUNT(B) GROUP BY A"

It'd be great if I could use the column headers in the first row, more like a database, as in:

"SELECT student, COUNT(detention) GROUP BY student"

I suspect it's not possible, but hope this yet another case where my Internet search skills failed me.

ross
  • 2,684
  • 2
  • 13
  • 22
prototype
  • 7,249
  • 15
  • 60
  • 94

4 Answers4

6

This is currently not possible. The GQL documentation states[1] "Columns are referenced by the identifiers (not by labels). For example, in a Google Spreadsheet, column identifiers are the one or two character column letter (A, B, C, ...)."

If you want to do this in a spreadsheet it is possible with the following formula to convert a column header name into a letter (some tweaking might be required +1 (might be +2)). It also relies on column headers being unique and not containing commas 

=REGEXEXTRACT(ADDRESS(1,COUNTA(SPLIT(LEFT(JOIN(",",TRANSPOSE(1:1)),FIND("your_column_name",JOIN(",",TRANSPOSE(1:1)))),","))+1,4);"[a-zA-Z]+")

[1] https://developers.google.com/chart/interactive/docs/querylanguage#Select

mhawksey
  • 2,013
  • 5
  • 23
  • 61
1

I found that when you use IMPORTRANGE function on external ranges it converts from a letter to a column number, and will help you in this matter.

I've wanted to select a column based on its field name, but the problem for me was that the column to look at was likely to change in the future. So what I did was use the MATCH function to identify the column, so it looks something like

=QUERY(IMPORTRANGE("spreadsheet url","NamedRange"),"SELECT Col"&MATCH("FieldName",FieldNameRowAddress/RangeName,FALSE)")

The funny thing is you have to allow permission to access itself.

I've named my ranges that I'm importing to make it even more future proof.

Paul Marshall
  • 11
  • 3
0

A little simpler:

SELECT "&SUBSTITUTE(ADDRESS(1,MATCH("student",Sheet1!A1:B1,0),4),1,"")&", COUNT("&SUBSTITUTE(ADDRESS(1,MATCH("detention",Sheet1!A1:B1,0),4),1,"")&") GROUP BY "&SUBSTITUTE(ADDRESS(1,MATCH("student",Sheet1!A1:B1,0),4),1,"")
Dave Meindl
  • 121
  • 3
0

if you really want you can fake it:

=LAMBDA(student, detention, QUERY({A1:B5}, 
 "SELECT "&student&", COUNT("&detention&") GROUP BY "&student, 1))
 ("Col"&MATCH("student",   A1:B1, ), 
  "Col"&MATCH("detention", A1:B1, ))

enter image description here

player0
  • 124,011
  • 12
  • 67
  • 124