36

Reading the documentation for the =QUERY function1, 2, 3, some of it seems to imply that I should be able to use column headers directly in my query. For example, the =QUERY function takes a third optional parameter, HEADERS, which lets you specify a number of header rows.

Most of my queries would be a lot prettier if I could use column headers, thus not having to use column indexes, but I'm not able to make it work.

Example:

A      B         C
---------------------
Name   Phone     City
Vidar  12345678  Oslo
Rupert 32165487  Berlin

I'm able to query this by using column indexes:

=QUERY(Sheet1!A1:C; "select A, B, C where A = 'Vidar'"; 1)

... but not using column headers:

=QUERY(Sheet1!A1:C; "select A, B, C where Name = 'Vidar'"; 1)

... this gives me Error: Invalid query: Column [Name] does not exist in table

Is it at all possible to use column headers like this? If not, what is the purpose of the HÈADERS parameter?

13 Answers 13

6

The third parameter you speak of in the QUERY function, is meant to control the headers. If set to be -1, then the Google Spreadsheet will depict its own choice in choosing headers, based on the data available: enter image description here

I've used the following data set: enter image description here

If set to be 0, then no header will be used, leaving: enter image description here

If set to be 1, then the first row will be used, leaving: enter image description here

If set to be 2 or -1 or left blank, then the first two rows will be used, leaving: enter image description here

Using the headers, the way you did in your example, isn't possible. The closest thing would the usage of the QUERYfunction, described in this answer. There a quasi column name is used.

4
  • Ah, OK, so if HEADERS > 0 then the given number of header rows will be returned by =QUERY on top of the query results, right? That makes sense - I thought HEADERS only specified rows to ignore in the query. Feb 26, 2014 at 17:35
  • @Vidar The OFFSET option can be used to ignore first rows.
    – Jacob Jan
    Feb 26, 2014 at 18:00
  • 1
    In my experience setting the headers parameter to 0 does not do what Google docs describe or what is depicted here. It basically does nothing (i.e., same as -1) as far as I can tell. Has anyone else noticed this?
    – user24601
    May 13, 2018 at 13:29
  • 16
    I believe the intention of the original poster was not about getting headers in the report but rather about using header names in the query statement rather than having to use column indexes (as in A or B or C).
    – Farrel
    Jun 10, 2018 at 16:51
11

Is it at all possible to use column headers like this?

Yes, it is possible. First, you need to use MATCH to get the column number of the column whose value matches 'Name'. Then you need to use ADDRESS to get the cell reference. Finally, you need to use SUBSTITUTE to remove the row number from the cell reference.

=QUERY(Sheet1!A1:C,"SELECT A, B, C where "&SUBSTITUTE(ADDRESS(1,MATCH("Name",Sheet1!A1:C1,0),4),1,"")&" = 'Vidar'")
3
  • 8
    Please see the OP "Most of my queries would be a lot prettier if I could use column headers". Do you truly believe that your answer makes the query prettier?!? Sure your solution WORKS, but it doesn't address the OP's intent at all - that's a hot mess you're proposing there.
    – Tom Auger
    Feb 10, 2021 at 20:47
  • 2
    Seems pretty clear that the OP wants to use in a "where" clause the values of the cells in the first row (which he refers to as "headers") in lieu of the column indices. See his example: =QUERY(Sheet1!A1:C; "select A, B, C where Name = 'Vidar'"; 1) I welcome your suggestions for improving my solution. Please demonstrate how you would achieve the desired result in a "prettier" way. Feb 12, 2021 at 2:10
  • 2
    Helpful even if it's not prettier than column headers... Might not be prettier but it sure is a lot more portable when you add more columns to your data.
    – blak3r
    Apr 28, 2021 at 14:27
8

I was searching for a suitable answer to this question, also, 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"))

You use it with a name range like this:

=query(RangeDataName,QueryByName(RangeDataName, "SELECT `Contract`","RangeDataName"))

Here is a real-world use case with labels and format.

=QUERY(
    Data!A1:Z, 
    QueryByName(
        Data!A1:Z1,
        "
        SELECT `Epic name`,
        SUM(`Time Spent (Minutes)`)/60, 
        SUM(`Original Estimate (Minutes)`)/60
        where `Epic name` is not null and `Original Estimate (Minutes)` > 0 and `Time Spent (Minutes)` >0
        group by `Epic name`
        label SUM(`Time Spent (Minutes)`)/60 'Time Spent (hours)', SUM(`Original Estimate (Minutes)`)/60 'Time Estimated (hours)'
        format SUM(`Time Spent (Minutes)`)/60 '0.00', SUM(`Original Estimate (Minutes)`)/60 '0.00'
        "
    )
)

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() functionx
 * 
 * Example:
 * =QUERY(A1:C13, QueryByName(A1:C1, "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, nameRange = false, firstColumnIndex = 0) {
  // Lowercase all heading names so that case errors are accepted
  const headings = columnHeadings[0].map(item => item.toLowerCase());

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

  // Find column number by range
      if (nameRange) {
        firstColumnIndex =  findColumnNumberByRangeName(nameRange)-1;
        //let columnLetter = columnNumberToLetter(columnNumber);
      }
    // Loop over each one and replace it with the column letter
    for (let [nameWithTicks, name] of fieldNamesInQuery) {
      let columnLetter = columnNumberToLetter(headings.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;
  }

  function findColumnNumberByRangeName(name) {
  var Range = SpreadsheetApp.getActiveSpreadsheet().getRangeByName(name);
    return Range.getColumn();
  }
}



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

5
  • 4
    This is by far the cleanest approach here. The one tweak I made was to create a shorter function alias (QBN(...)) for brevity. Another thought (which I haven't tried yet), would be to make the range an optional second argument that if undefined defaults to the first row in the current sheet that it would have to query via the Sheets API.
    – Digicrat
    Dec 8, 2021 at 16:25
  • This doesn't work with named ranage. Aug 24, 2022 at 0:42
  • @Adel-MageBinary I haven't tested it with named ranges, so you may be correct, although in theory it should work. You need to ensure that you provide TWO separate ranges: the first is the complete table, and the second is the range of just the heading row only, so double-check that you've provided those correctly.
    – Simon East
    Aug 24, 2022 at 5:01
  • I have modified your code a bit. Will edit your answer. Aug 24, 2022 at 22:00
  • Hi, get error i.imgur.com/kaYs4p7.png Mar 21 at 18:46
5

Most of already provided answers requires string concatenation, which is quite inconvenient to use. Some answers involves custom script function, which is better approach. Here's a bit similar approach, however without using App Scipt, with very simple function that does the same.

The assumptions are:

  • the data range provided contains headers in the first row. If that's not the case, you can mimic this by combining row with headers with range with data using {headersRow; dataRange}. Number of columns must match of course.
  • to indicate columns, you provide header names in back-quotes (``), which is a correct way of providing complex identifiers, as defined in the docs (https://developers.google.com/chart/interactive/docs/querylanguage#identifiers)

Option 1) defined Named Function

Define new Named function, e.g. QUERY_BY_HEADERS, with two arguments: data and query_text. Here's a little info about Named Functions: https://support.google.com/docs/answer/12504534

For "Formula Definition" provide:

QUERY({data}; LAMBDA(text; columns;
  REDUCE(text; FILTER(columns; NOT(ISBLANK(columns))); LAMBDA(res; col;
    REGEXREPLACE(res; "`" & col & "`"; "Col" & MATCH(col; columns; 0))))
  )(query_text; ARRAY_CONSTRAIN(data; 1; COLUMNS(data)));
1)

And that's it. It works with ranges like A:F, named ranges, results from other functions etc. To use it, just type in the cell e.g.

=QUERY_BY_HEADERS(A:F; "select `first name`, `age`")

Option 2) formula straight in the cell

If for some reason you don't want to define Named Functions, you can provide whole definition straight into any cell, with a little help of LAMBDA function. Just put text of form =LAMBDA(data; query_text; [formula-definition-from-option1])([parameter values]).

So, the whole cell content will look like this:

=LAMBDA(data; query_text;
  QUERY({data}; LAMBDA(text; columns;
    REDUCE(text; FILTER(columns; NOT(ISBLANK(columns))); LAMBDA(res; col;
      REGEXREPLACE(res; "`" & col & "`"; "Col" & MATCH(col; columns; 0))))
    )(query_text; ARRAY_CONSTRAIN(data; 1; COLUMNS(data)));
  1)
)(A:F; "select `first name`, `age`")

As you can see, first and last lines are the lambda "wrapping", with last line containing also parameter values, like in option 1. And the middle lines are exactly the same as well.

Sample spreadsheet

Here's the spreadsheet to see all above in the action: https://docs.google.com/spreadsheets/d/1Pg9REUaTBU1uyx6DNnuA0ncdSb08KAU0RnKyo-OWEdU

It also contains a bit extended version of QUERY_BY_HEADERS, which takes also 3rd parameter to control visibility of headers in the generated result.

To see the named functions definitions and play with it, click this link to copy the spreadsheet: https://docs.google.com/spreadsheets/d/1Pg9REUaTBU1uyx6DNnuA0ncdSb08KAU0RnKyo-OWEdU/copy

1
  • Thanks! This is a very nice solution. I already had my own named function that worked, but this is simpler than mine. I ended up making a couple of very minor tweaks to simplify it further and make the column name matching case insensitive: =QUERY(data, LAMBDA(columns, REDUCE(query_text, FILTER(columns, NOT(ISBLANK(columns))), LAMBDA(res, col, REGEXREPLACE(res, "(?i)`" & col & "`", "Col" & MATCH(col, columns, 0)))))(INDEX(data, 1)), 1)
    – HexAndBugs
    Nov 11, 2023 at 12:48
4

While an old post, I wanted to add my solution to the mix. You can use column names which I find to be more useful. This way you don't have to edit your query function calls when you insert or remove columns from the source data.

I have seen others use the match and substitute, I implemented something a little different to simply the query function call.

First - Create a lookup table of all of your column names like this. My lookup table starts in Column E of a 'Config' sheet only because i have other items on the sheet, it could easily be placed in its own sheet.

  • Column 1 (Column Name)

    =TRANSPOSE( 'Source Data'!1:1 )
    
  • Column 2 (Column #)

    =arrayformula( row( E2:E ) - 1 )
    
  • Column 3 (Column Letter)

    =arrayformula( if( int( F2:F / 26.5 ) > 0, char( int( F2:F / 26.5 )  + 64), "" ) & char( (F2:F - (int( F2:F / 26.5 ) * 26 ) ) + 64 ) )
    

    I'm open to refinements to convert a column number to a letter. This formula is limited in that it only handles 78 columns. More than enough for me though.

Now your Query function call would look something like this:

=query( 'Source Data'!$A:$L,
"Select " & " " &
vlookup( "Date", Config!$E:$G, 3, false ) & ", " &
vlookup( "Dev Query Engine Conn Count", Config!$E:$G, 3, false ) & ", " &
vlookup( "Dev Conn Limit Retry Count", Config!$E:$G, 3, false ) & ", " &
vlookup( "Dev Max Sequential Retry in One Minute", Config!$E:$G, 3, false ) & ", " &
vlookup( "Dev Conn Limit Errors", Config!$E:$G, 3, false ) & " " &
"where " & vlookup( "Display in Graph.", Config!$E:$G, 3, false ) & "=TRUE " &
"order by " & vlookup( "Date", Config!$E:$G, 3, false ) & " desc "
)

Keep it nicely formatted and it isn't bad to manage at all. The only thing you will suffer is if you change the column index of your vlookup range. But this should not change often, if at all, considering it is a helper range.

2
  • This answer is what I was looking for, but I needed it a little longer, so I wrote a query that supports up to 3 letters =arrayformula( if(F2:F < 702, "", CHAR(MOD((F2:F / 26 / 26) - 1, 26) + 65)) & if(F2:F < 26, "", CHAR(MOD((F2:F / 26) - 1, 26) + 65)) & CHAR(MOD(F2:F, 26) + 65) ) In my case, I start at index 0, so I had to have the 2nd column actually be - 2
    – JRJurman
    May 27, 2020 at 2:33
  • Using a named range for Config!$E:$G would tidy this up a little further. And you could add another named range to look up the 3.
    – yoyo
    Nov 28, 2021 at 21:48
4

Old question, but I think this solution may be worth it.

You can use a custom script function which retrieves the column index (i.e. A, B, C...) using a header name, allowing to do something like:

=query('MySheet'!A2:Z; 
   "select "&colIndex("'MySheet'!A1:Z1"; "Car name")&"  
    where "&colIndex("'MySheet'!A1:Z1"; "Car color")&"='Blue'"))

With colIndex function:

function colIndex(a1NotationStr, header){
  var range = SpreadsheetApp.getActiveSpreadsheet().getRange(a1NotationStr)
  var row = range.getValues()[0]

  //parse the header row and stop at first header matching our search
  //use the related cell A1Notation and remove all numbers (i.e. 'A45' becomes 'A')
  for(var i=0; i<row.length; i++){
    var currHeader = row[i]
    if(currHeader == header){
      return range.getCell(1, i+1).getA1Notation().replace(/[0-9]/g, '');
    }  
  }
}

Which may be easier to use and read.

3

Here is my solution:

if you have a column called "city" create a new cell somewhere with the index of that cell:

   A          B               C        ...         Y               Z        
 --------- ------------ ------------ ------ --------------- --------------- 
  Name        Phone        City        ...    Column Name     Column Index  
  Vidar      12345678     Oslo         ...    Name           A              
  Rupert     32165487     Berlin       ...    Phone          B              
  Sahid      32165487     Colombo      ...    City           C              

You can then create a named range for cell Z:2 called : "name" , Z:3 = "phone", Z:4 = "city"

in your query you can then write:

=QUERY( Sheet1!A1:C,"SELECT A, B, C where "&name&" = 'Vidar'"

You can combine this with the address match solution, to not have to keep track of rearranged columns.

1
  • 2
    To clarify, this answer shows that you can use Named Ranges somewhat cleanly in the query, so use them to map a nice name to the column letter that QUERY() requires. You must create a named range for each column name, and since named range names are global this is best left for small workbooks. FYI, column Y in this answer has no meaning, except to maybe use it to populate Column Z via the SUBSTITUTE, ADDRESS, MATCH method.
    – Adam
    Dec 30, 2020 at 1:57
1

The two cleanest ways to implement this:

  1. Use {} around the query range so you can use columns instead of letters. E.g.:
    =Query({A:B},"Select Col"&Match("Header Name",1:1,0))
    Where A:B is where your query data lives and 1:1 is the where the headers live.

  2. Name the range you want to pull from. You can reference the named range within the query.

This site has a pretty great breakdown of how to implement: https://francofolini.com/2020/08/04/reference-columns-by-name-in-google-sheets-query/

1

You can try the below Named Function. Import from here

Name

_BETTERQUERY

Usage example

=_BETTERQUERY(A1:C10,"select `name` where `age` > 18",1)

Formula description

Runs a Google Visualization API Query Language query across data. It supports the usage of column headers.

Argument placeholders

  • range
  • better_query
  • headers

Formula definition

=QUERY({range},REGEXREPLACE(REDUCE(better_query,
REGEXEXTRACT(better_query,REGEXREPLACE(REGEXREPLACE(better_query,
"([()\[\]{}|^.+*$?])","\\$1"),"`(.*?)`","`($1)`")),LAMBDA(acc,cur,
SUBSTITUTE(acc,cur,"Col"&MATCH(cur,INDEX(range,1),0),1))),
"`(Col\d+)`","$1"),headers)

Notes

  1. This function is built on top of QUERY, so you can use it exactly like QUERY. When referring to the columns with their header, make sure that the first row of range is the header and in better_query enclose the column header between two backticks `col_header`. (See example usage above)

  2. The headers parameter is not optional since Named Functions do not currently allow optional parameters.

If you want to understand more about how this works. See How to Use Column Names in QUERY

2
1

To add to @SimonEast's answer. The code works but returns an error when you have many columns in the initial data, including the BY column.

Since BY is part of SQL it will throw an error on the final querystring.

To avoid it, this line should be modified from:

let columnLetter = columnNumberToLetter(headings.indexOf(name.toLowerCase()) + firstColumnIndex + 1)

to:

let columnLetter = "`" + columnNumberToLetter(headings.indexOf(name.toLowerCase()) + firstColumnIndex + 1) + "`"

So the complete code is then:

/**
 * Custom sheet function for allowing the use of column headings 
 * instead of column labels inside the QUERY() functionx
 * 
 * Example:
 * =QUERY(A1:C13, QueryByName(A1:C1, "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, modified by Dušan Vrban
 * <https://webapps.stackexchange.com/a/155456/20364>
 */

function QueryByName(columnHeadings, queryString, nameRange = false, firstColumnIndex = 0) {
  // Lowercase all heading names so that case errors are accepted
  const headings = columnHeadings[0].map(item => item.toLowerCase());

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

  // Find column number by range
      if (nameRange) {
        firstColumnIndex =  findColumnNumberByRangeName(nameRange)-1;
        //let columnLetter = columnNumberToLetter(columnNumber);
      }
    // Loop over each one and replace it with the column letter
    for (let [nameWithTicks, name] of fieldNamesInQuery) {
      let columnLetter = "`" + columnNumberToLetter(headings.indexOf(name.toLowerCase()) + firstColumnIndex + 1) + "`"
      console.log(name + ": " + columnLetter);
      if (!columnLetter)
        throw new Error('The field named ' + nameWithTicks + ' was not found in the header range you specified.');
      queryString = queryString.replace(nameWithTicks, columnLetter);
    }

  console.log(queryString);

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

  function findColumnNumberByRangeName(name) {
  var Range = SpreadsheetApp.getActiveSpreadsheet().getRangeByName(name);
    return Range.getColumn();
  }
}
0

You can actually get around to using column headers by splitting the Query formula and using other formula's to automatically get the desired column names from a list.

For example if you have a table in range A1:E15 with headers "H1, H2, H3, H4, H5", and you'd like to only get columns H3 & H5:

  1. Store the desired headers (H3 & H5) in another table/range as a list - lets say this range is G1:G2

  2. Use MATCH formula along with TextJoin formula to generate an concatenated string like Col3, Col5

    =TextJoin(", ",TRUE,ArrayFormula(IFERROR("Col"&MATCH(G1:G6,$A$1:$E$1,0),"")))
    
    • Lets say this was in cell H1
  3. You can refer to this cell in your Query formula like below

    =QUERY({A1:E20},"SELECT "&H1&" WHERE Col2='w'")
    

You can see it in action in below screenshot:
Sample Screenshot

0

Combining the answers of Tina, ZygD, and Gangula:

  1. Follow the steps in the post of Franco Folini but stop just before manually creating the named ranges. The result is a sheet "data map" with vertical list of Column IDs and Column Labels where the latter contains only those labels you wish to retrieve from your source data.

  2. Then use Gangula's suggestion: turn the vertical list of Column IDs into a comma-separated string using TEXTJOIN and insert that into the query() function as per his post.

The above combination keeps Franco Folini's benefit that you simply type the column names you wish to keep, whilst avoiding the drawback that you have to manually define a named range for each such name.

0

Is it at all possible to use column headers like this?

This is only an improvement of Dave Meindl's answer. I suggest using SPLIT, instead of SUBSTITUTE. This shortens the formula 8 chars for each use.

=QUERY(Sheet1!A1:C,"SELECT A, B, C where "&SPLIT(ADDRESS(1,MATCH("Name",Sheet1!A1:C1,0),4),1)&" = 'Vidar'")

I'm also against using an Apps Script function for this purpose as it will slow down your calculations, specially if you have a lot of queries.

Note: This answer is only focused on improving Dave's answer. The above QUERY has maintenance problems (e.g. adding columns; changing column name) that other answers offer solutions. My approach has always been on zero maintenance, as I'm forgetful. So, If you are looking for a "maintenance free" version, use the following formula instead of a column id:

SPLIT(ADDRESS(1,COLUMN(Sheet1!$A$1),4),1)     instead of A

making your QUERY formula

=QUERY(Sheet1!A1:C,"SELECT "
 &SPLIT(ADDRESS(1,COLUMN(Sheet1!$A$1),4),1) &","
 &SPLIT(ADDRESS(1,COLUMN(Sheet1!$B$1),4),1) &","
 &SPLIT(ADDRESS(1,COLUMN(Sheet1!$C$1),4),1) 
 &" where "
 &SPLIT(ADDRESS(1,COLUMN(Sheet1!$A$1),4),1)&" = 'Vidar'")

Not pretty, but plays nice with changes to Sheet1!

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.