7

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. – Rubén Dec 28 '15 at 18:48
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());

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')")  
| improve this answer | |
  • 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). – Douglas Gaskell Dec 22 '15 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 Dec 22 '15 at 21:28
  • 1
    That said, I added my suggestion for dealing without a Script. – user79865 Dec 22 '15 at 22:01
0

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

| improve this answer | |
0

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

}

Query using curly brackets

Query without using curly brackets

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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