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

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.