Using Query with column headers instead of column letters

Question

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')")

Answer 1

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());

with

  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')")  

Answer 2

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},
    SUM({NetAmount})
GROUP BY {TradeGroup}, {Status}
"))

Querying without curly braces

=QUERY(PositionsFromHistory!1:120, test("
Select TradeGroup, Status,
    SUM(NetAmount)
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 ="";
  
  do{
    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;
    keysArr.forEach(function(k){
        const keyTemplate = "{" + k + "}";
        queryStr = replaceAll(queryStr,keyTemplate, dict[k]);
        queryStr = replaceAll(queryStr,k, dict[k]);
    });
    
    Logger.log(queryStr);
    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);

}

Answer 3

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+(?=[ ,])/);

to:

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...

Answer 4

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.

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.