Select columns by name rather than letter in Google Query Language (GQL) with Google Spreadsheets?

Question

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.

Answer 1

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