I have a formula that I use to dynamically get the column letter for a 1-Dimensional array that are the column headers of a sheet. In order to use QUERY() with column names instead of letters.

SUBSTITUTE(ADDRESS(1,MATCH("Weight", Headers, 0),4), "1", "")

The syntax takes up a fair bit of space and can be difficult on the eyes when the queries get long. An example:

=QUERY(A3:F13, "select "&SUBSTITUTE(ADDRESS(1,MATCH("Type", Headers, 0),4), "1", "")&" where ("&SUBSTITUTE(ADDRESS(1,MATCH("Version", Headers, 0),4), "1", "")&" = 'Version 1') and ("&SUBSTITUTE(ADDRESS(1,MATCH("Type", Headers, 0),4), "1", "")&" <> 'Type')")

Does the same as

=QUERY(A3:F13, "select E where (A = 'Version 1') and (E <> 'Type')")

With the advantage of always selecting the row I want no matter what order they are in.

Is it possible to make a function that would operate like the following without recreating the wheel in apps script?:

GetHeaders(range, string)

=QUERY(A3:F13, "select "&GetHeader(Headers, "Type")&" where ("&GetHeader(Headers, "Version")&" = 'Version 1') and ("&GetHeader(Headers, "Type")&" <> 'Type')")
  • As was explained by @Normal Human in a comment in his answer, the only way to make a custom function is by using Apps Script. If you require answers without the use of it, "make a function" should be reworded (i.e. "make a formula") and mention the that using custom functions isn't desired. Commented Dec 28, 2015 at 18:48
  • This is similar to another question asked previously: Can I use column headers in a =QUERY?
    – Simon East
    Commented Jun 29, 2021 at 5:01

4 Answers 4


Without Apps Script

One cannot make a new function without Apps Script. The only way to streamline the process is to put more cells somewhere and reference those.

For example: under each header such as 'Weight', enter the command

=regexextract(address(1, column()), "[A-Z]+")

This will put the column letter(s) under the headers. Include the row with letters in the Headers named range. In the query string, use

hlookup("Weight", Headers, 2, 0)

which is easier on the eyes than

SUBSTITUTE(ADDRESS(1,MATCH("Weight", Headers, 0),4), "1", "")

With Apps Script

Passing a range (named or otherwise) into a custom function passes in the values, with no information on their location in the sheet. But I found a solution: infer the location of headers from the first argument of query. No need to include "Headers", which was itself repetitive.

Version 1: headers included in query range

I prefer to include the header rows in the range passed to query, and specify the number of header rows as the 3rd argument of query. This avoids misinterpretation of data as headers or vice versa. For example:

=QUERY(A1:F13, "select "&GetHeader("Type")&" where ("&GetHeader("Version")&" = 'Version 1') and ("&GetHeader("Type")&" <> 'Type')", 2) 

Here is the custom function to use with the above query:

function getHeader(name) {
  var sheet = SpreadsheetApp.getActiveSheet();
  var formula = SpreadsheetApp.getActiveRange().getFormula();
  var args = formula.match(/\w+:\w+(?=[ ,])/);
  var range = sheet.getRange(args[0]);
  var firstRow = range.offset(0, 0, 1, range.getWidth());
  var headers = firstRow.getValues();
  for (var i = 0; i < headers[0].length; i++) {
    if (headers[0][i] == name) {
      var notation = range.getCell(1, i+1).getA1Notation();
      var column = notation.replace(/\d/, '');
      return column;
  return 'Not found';

The function obtains the formula from the cell from which it was called. It extracts the first range argument of formula with a regular expression. Then it looks through the first row of this range, assuming it's the header row, in search of the given string. It obtains the A1 notation of the cell with the string, removes the row part of it, and returns the result.

Version 2: headers taken from Row 1

An alternative version, where the headers are taken from the first row of the sheet, regardless of the rows in the query argument. Just replace

  var firstRow = range.offset(0, 0, 1, range.getWidth());


  var firstRow = sheet.getRange(1, range.getColumn(), 1, range.getWidth());

With this version you can use

=QUERY(A3:F13, "select "&GetHeader("Type")&" where ("&GetHeader("Version")&" = 'Version 1') and ("&GetHeader("Type")&" <> 'Type')")  
  • Thanks Normal Human! I wanted to avoid using Apps Script for this though, since custom functions tend to only work intermittently when used in mass I need something that works every time, and in my experience custom functions do not work every time (The perpetual Loading... issue that is resolvable be deleting and re-pasting the function). Commented Dec 22, 2015 at 21:16
  • 1
    You asked to "make a function".... using Apps Script is the only way to make a new function. Otherwise, there is nothing but copy-pasting the same combination of built-in functions around. I do understand the concern about performance, but usually the number of queries is not very large, or you have performance issues related to queries themselves.
    – user79865
    Commented Dec 22, 2015 at 21:28
  • 1
    That said, I added my suggestion for dealing without a Script.
    – user79865
    Commented Dec 22, 2015 at 22:01

Example usage in cell (maybe you will want to customize the name/params of the test()):

Querying with curly braces

=QUERY(PositionsFromHistory!1:120, test("
Select {TradeGroup}, {Status},
GROUP BY {TradeGroup}, {Status}

Querying without curly braces

=QUERY(PositionsFromHistory!1:120, test("
Select TradeGroup, Status,
GROUP BY TradeGroup, Status

Copy paste this code to Tools -> Script Editor -> Code.gs

function replaceAll(string, search, replace) {
  return string.split(search).join(replace);

function colNumToName(colNum ){
  var retval ="";
    colNum -= 1;
    var q = Math.floor(colNum / 26);
    var r = colNum % 26;
    colNum = q;
    retval =  ( String.fromCharCode( r+65)) + retval;
  }while( colNum > 0 );
  return retval;

function rowToObject(sheet, rowNum,startColNum, lastColNum) {
    var startCol = colNumToName(startColNum);
    var lastCol = colNumToName(lastColNum);
    const range = `${startCol}${rowNum}:${lastCol}${rowNum}`;
    const dt = sheet.getRange(range).getValues();
    var vals = dt[0];
    return vals;

function getColumnHeaders(sheet,rowNum,startColNum,lastColNum){
    var colHdrs = rowToObject(sheet,rowNum,startColNum,lastColNum);
    // TODO: Check if any of the hdrs is null or empty and report an error instead.
    return colHdrs;

function getColumnHeaderMappings(sheet, rowNum, startColNum, lastColNum) {
    const colHdrs = getColumnHeaders(sheet, rowNum, startColNum, lastColNum);

    var dict = {};

    for (let i = startColNum; i <= lastColNum; i++) {
        dict[colHdrs[i-startColNum]] = colNumToName(i);

    return dict;

function genQueryString(queryStringTemplate, sheet, hdrsRowNum, firstColNum, lastColNum) {

    if (typeof hdrsRowNum === "undefined") {
        hdrsRowNum = 1;

    if (typeof firstColNum === "undefined") {
        firstColNum = 1;

    if (typeof lastColNum === "undefined") {
        lastColNum = sheet.getLastColumn();
    const dict = getColumnHeaderMappings(sheet, hdrsRowNum, firstColNum, lastColNum);
    const keysArr = Object.keys(dict).sort(function(a,b) { 
        return b.length - a.length || a.localeCompare(b);  
    var queryStr = queryStringTemplate;
        const keyTemplate = "{" + k + "}";
        queryStr = replaceAll(queryStr,keyTemplate, dict[k]);
        queryStr = replaceAll(queryStr,k, dict[k]);
    return queryStr;

function test(queryStringTemplate = "Select {TradeGroup}, {Status}, SUM({NetAmount}) GROUP BY {TradeGroup}, {Status}", sheetName = "PositionsFromHistory", rowNum = 1) {
    var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
    return genQueryString(queryStringTemplate, sheet, rowNum);


Query using curly brackets

Query without using curly brackets


I really like the answer provided by user79865. If you want to use it and have locale different than US (like my Polish one) you may have different separator for function arguments in Sheets. And instead of:

=QUERY(A3:F13, "select "&GetHeader("Type")&" where ("&GetHeader("Version")&" = 'Version 1') and ("&GetHeader("Type")&" <> 'Type')", 2)

you might have (; in place of ,):

=QUERY(A3:F13; "select "&GetHeader("Type")&" where ("&GetHeader("Version")&" = 'Version 1') and ("&GetHeader("Type")&" <> 'Type')"; 2)

In such case the regex in function (Version 1), line 4, should be changed from:

var args = formula.match(/\w+:\w+(?=[ ,])/);


var args = formula.match(/\w+:\w+(?=[ ;])/);

And finally if your data is in separate sheet (but in the same spreadsheet) you may modify the regex to look for sheet name and use it to search for column names:

function getHeader( name ) {
  var sheet = SpreadsheetApp.getActiveSheet();
  var formula = SpreadsheetApp.getActiveRange().getFormula();
  var args = formula.match(/\((\'*(.+(?=[!']))\'*!)*(\w+:\w+(?=[ ;]))/); // 1: "'sheet name'!", 2:"sheet name", 3:"A1:D100"
  if( args[2] ) 
    sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(args[2]);
  var range = sheet.getRange(args[3]);
  var firstRow = range.offset(0, 0, 1, range.getWidth());
  var headers = firstRow.getValues();
  for (var i = 0; i < headers[0].length; i++) {
    if (headers[0][i] == name) {
      var notation = range.getCell(1, i+1).getA1Notation();
      var column = notation.replace(/\d/, '');
      return column;
  return 'Not found';

I'd like to add comment, but as a novice member I can't comment...


As I posted in another, similar question here, I was also searching for a suitable answer to this question, since using column letters like A, B, C is not good for flexibility and becomes a maintenance hassle. Every time your source data columns are reordered, or a new column comes in, you have to update your QUERY().

So I came up with this solution. It does require a short Apps Script function, but the benefit is that you get quite a clean, readable, maintainable query.

Using Google Sheets QUERY with column headings/labels instead of letter

You use it like this:

=QUERY(A9:C13, QueryByName(A9:C9, "SELECT `Name`, `country` WHERE `age`>7"))

In order to use this function, open your Google Sheet, click Tools > Script Editor and paste in the following function.

 * Custom sheet function for allowing the use of column headings 
 * instead of column labels inside the QUERY() function
 * Example:
 * =QUERY(A1:C13, QueryByName(A1:A10, "SELECT `name`, `country`"))
 * The first range (in the QUERY() function) is your data source, 
 * and the range given in QueryByName() should be the header row
 * that lists out your headings. Consider giving both ranges
 * a name for easy reference.
 * The third parameter 'firstColumnIndex', is only used if your
 * source data does NOT start in column A. Otherwise you can leave
 * it blank. If it starts in column B, set this to 1, if it starts 
 * in C, set to 2, etc.
 * You can make this dynamic by setting it to COLUMN(x) where x is
 * the top-left cell of your source data (or even the entire range).
 * Function by Simon East
 * <https://webapps.stackexchange.com/a/155456/20364>
function QueryByName(columnHeadings, queryString, firstColumnIndex = 0) {

  // Lowercase all heading names so that case errors are accepted
  columnHeadings[0].map(item => item.toLowerCase());

  // Use a regular expression to get a list of all strings inside `backticks`
  let fieldNamesInQuery = queryString.matchAll(/`([^`]+)`/g);

  // Loop over each one and replace it with the column letter
  for (let [nameWithTicks, name] of fieldNamesInQuery) {
    let columnLetter = columnNumberToLetter(columnHeadings[0].indexOf(name.toLowerCase()) + firstColumnIndex + 1)
    if (!columnLetter)
      throw new Error('The field named ' + nameWithTicks + ' was not found in the header range you specified.');
    queryString = queryString.replace(nameWithTicks, columnLetter);

  return queryString;
  // Converts 1 to A, 2 to B, 27 to AA, etc...
  function columnNumberToLetter(column) {
    var temp, letter = '';
    while (column > 0) {
      temp = (column - 1) % 26;
      letter = String.fromCharCode(temp + 65) + letter;
      column = (column - temp - 1) / 26;
    return letter;


If you experience any bugs, feel free to post a comment here.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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