Column Identifiers as Described in Query Language Reference (Version 0.7) not working

Question

In the Query Language Reference document found at https://developers.google.com/chart/interactive/docs/querylanguage#Table_used_in_Examples it indicates the following as properties of data tables.

"Identifier (or column ID). Used to reference columns within the query. Note that you should never try to reference a column by label in a query, only by identifier. Tip: Try not to use any IDs that include spaces; spaces are hard to manage and can cause you to make small, but hard to find mistakes, in your coding. Additionally, an ID that includes spaces must be surrounded by back-quotes."

Further on it shows the following example: "select email address, name, date"

Finally at the very end of the document it says the following about identifiers: "Identifiers Identifiers (or IDs) are text strings that identify columns.

Important: If your identifier

Has spaces, Is a reserved word, Contains anything but alphanumeric characters or underscores ([a-zA-Z0-9_]), or Starts with a digit it must be surrounded by back-quotes (not single quotes).

Otherwise, your identifier does not need to be quoted. (Note that not all keywords defined by the syntax are reserved words; so, for example, you can use "max" as an identifier, without having to back-quote it.)

Examples: col1 employee_table start date 7 days traffic select"

I've searched for examples where someone was able to make this work and I haven't found any yet. As an interim solution to get my columns to move dynamically I've resorted to the following scheme.

This would be the original text. =query(Thurston!D1:AP41248,"select * where G>=18 and G<19",1)

Here is the replacement. =query(Thurston!D1:AP41248,"select * where " & char(column(Thurston!G1)+64) & ">=18 and " & char(column(Thurston!G1)+64) & "<19",1)

I put a reference in to the column in question and then computed what letter it would represent. Then if the column is moved the letter is updated automatically. It's not pretty but it works. It would be really convenient if I could get the identifiers as described to work.

Answer 1

Apples and Oranges

You are referencing the Query Language documentation for the Google Visualization API, and expecting to apply it to the QUERY function in the Google Sheets. The application programming interface (API) is intended for programmers wishing to visualize data. For example, generate charts from an external data source, such as Google Spreadsheets.

You can use a Custom Function

This Google Support Community answer Use column names in Sheets QUERY explains an approach using Google Apps Script and Google Sheets. It includes a linked sample spreadsheet that you can copy to your own account.

TLDR

In Sheets' QUERY function, QUERY(data, query, [headers]), use the custom function, MYSELECT, as the query argument:

=QUERY(data, MYSELECT(sqlstring, headersrange), [headers])

Normal QUERY Version

Formula

=QUERY(A:D, "Select A, D where D is not Null", 1)
# or
=QUERY(A:D, "Select  Col1, Col4 where Col4 is not Null", 1)

query Argument

"Select A, D where D is not Null"
# or
"Select  Col1, Col4 where Col4 is not Null"

Sample Data

	A	B	C	D
1	name	salary	age	dept
2	John	1000	35	Eng
3	Sally	600	30	Eng
4	Mike	800	24	Marketing

Sample Result

	E	F
1	name	dept
2	John	Eng
3	Sally	Eng
4	Mike	Marketing

Custom Function Version

1. Replace column letters/numbers by headings in the QUERY function's query argument:

   • "Select B, D where D is not Null"
     becomes
     

     "Select [name], [dept] where [dept] is not Null"
     

2. Populate custom function:

   • =MYSELECT(sqlstring, headersrange)
     becomes
     

     =MYSELECT("Select [name], [dept] 
        where [dept] is not Null", A1:D1)
     

     and returns
     

     "Select  Col1, Col4 where Col4 is not Null"
     

3. Populating QUERY with custom function,

   • =QUERY(data, query, [headers])
     becomes
     

     =QUERY(A2:D,
        MYSELECT("Select [name], [dept] 
           where [dept] is not null", A1:D1),1)
     

Script

// Source: Google Docs Editors > Community >  
// https://support.google.com/docs/thread/179627889?hl=en&msgid=179851086
// @James/mreighties (https://support.google.com/profile/6320) 
// Originally Posted: 2022-09-18
// Script below edited for length.  Unedited script linked 
// in original Google post linked above and at:
// https://docs.google.com/spreadsheets/d/1QFBT0JJj1a_YmbbaI3NSxGTQd_ncqcRZetNa4yKUBdc/copy  

function MYSELECT(sqlstring,headersrange) {
  
  let text = sqlstring
  let headers = headersrange[0]
  
  let results = text.matchAll(/\[.*?\]/g)
  let matches = [...results].map(match => match[0])
  
  let uniqueMatches = [...new Set(matches)]
  //console.log(uniqueMatches)
  
  let pos
  let regextext
  
  uniqueMatches.forEach(match=>{
    //console.log(match)
     
    pos = headers.indexOf(match.slice(1,-1))
    //console.log(pos)
    
    if(pos !== -1){
      regextext = `\\[${match.slice(1,-1)}\\]`
      console.log(regextext)
      text = text.replace(new RegExp(regextext,"g"),`Col${pos+1}`)
    }
                        
  })
  
  return text 
                
}