3

alright... not sure if these could be done.

i'm in google spreadsheets with cell A1 = time.. the range is A1:C4.

i have a simple table as follows:

time   sit   stand 
1      bob   mike
2      fred  pat
3      chris mike

This my query:

=query($A$1:$C$4,"select A,B,C where C='mike'",0)

... pretty straight forward. however, I want the column reference to be dynamic. So i need to be able to query using the header. how do i do it? I've already tried the following:

=query($A$1:$C$4,"select 'sit ', 'stand' where 'stand' = 'mike' ",0)

=query($A$1:$C$4,"select sit, stand where stand = 'mike' ",0)

and per this page's suggestion: Google spreadsheet Query Error - column doesn't exist

I've also tried the following:

=query($A$1:$C$4,"select Col2, Col3  where Col3 = 'mike' ",0)

=query($A$1:$C$4,"select Col2, Col3  where (Col3) = 'mike' ",0)

=query($A$1:$C$4,"select (Col2), (Col3)  where (Col3) = 'mike' ",0)

=query($A$1:$C$4,"select 'Col2', 'Col3'  where 'Col3' = 'mike' ",0)

None of them work... does anybody know how to do it or know if it is possible?

https://developers.google.com/chart/interactive/docs/querylanguage

the examples here seems like you can do it, but is that for app script only? and not in the spreadsheet function?

Community
  • 1
  • 1
jason
  • 3,811
  • 18
  • 92
  • 147
  • 1
    Take a look at this question. http://stackoverflow.com/questions/7508477/select-columns-by-name-rather-than-letter-in-google-query-language-gql-with-go – Phil Bozak Feb 08 '13 at 17:04
  • 1
    Looks like the answer is no. There seems to be conflicting references to being able to use `nameOfColumn` and not being able to use it. So that typically means that you can't. – Phil Bozak Feb 08 '13 at 17:14

5 Answers5

6

Unfortunately there is no native way of referencing columns by their headers in the QUERY spreadsheet function select clause.

You can use the Colx notation if the first argument of the QUERY is anything other than an explicitly referenced range. One way to achieve this is wrap the range in parentheses, and invoking ArrayFormula:

=ArrayFormula(QUERY(($A$1:$C$4),"select Col2, Col3 where Col3 = 'mike'",0))

And it is rather ugly, but you can use the MATCH function to bolt in header references:

=ArrayFormula(QUERY(($A$1:$C$4),"select Col"&MATCH("sit";$A$1:$C$1;0)&", Col"&MATCH("stand";$A$1:$C$1;0)&" where Col"&MATCH("stand";$A$1:$C$1;0)&" = 'mike'",1))

AdamL
  • 23,691
  • 6
  • 68
  • 59
  • Hi AdamL, I put the first ArrayFormula in. It doesn't seem to work. – jason Feb 09 '13 at 23:36
  • It gives me "error: Invalid query: Column [Col2] does not exist in table." – jason Feb 10 '13 at 04:41
  • Did you put the exact formula from this post in? If not, could you post here the formula you used? Eg, did you wrap the range in parentheses as advised/shown – AdamL Feb 10 '13 at 04:46
  • i think so. =ArrayFormula(query($A$1:$C$4,"select Col2, Col3 where Col3 = 'mike'",0)) – jason Feb 10 '13 at 05:08
  • oops... i see the difference... thanks for the help. works now! – jason Feb 10 '13 at 05:10
  • Hey Adam. I got another question for you if you are up to it. http://stackoverflow.com/questions/14796620/google-spreadsheet-query-join-equivalent-function – jason Feb 10 '13 at 10:09
  • Adam have you tried the Colx notation in the new google spreadsheet? I can't seem to get it to work. Did the new google spreadsheet do away with supporting the Colx notation? – jason Feb 15 '14 at 01:57
  • 1
    Yes they changed the behaviour of "anything other than an explicitly referenced range"; ie it is now more difficult to generate an array that can be referenced with Colx notation. One method that works in new (and old) Sheets is `=QUERY(QUERY(A1:C4,"select *"),"select Col1 etc")` – AdamL Feb 15 '14 at 02:04
2

I have also came across this problem without a solution, so I've have written a script which will allow column references within a query. To use: 1. Create a separate sheet and set "[SHEET NAME]" to the name of the data sheet 2. Create a Name Range (from tools menu) which is the row which the columns ids are stored eg A1:K1 3. change[COLUMNIDs ROW REFERENCE] in the code to the named range. Now while querying simple prefix a $ character before the column id example: =QUERY([SHEET_NAME]!A4:F, _Select(" * WHERE $[COLUMNID] < $[COLUMNID2]")

function _Select(squery){
  var sheetName = "[SHEET NAME]";
  var sheet = SpreadsheetApp.getActive().getSheetByName(sheetName);
  var colIndex = sheet.getDataRange().getColumn();
  var colIndex2 = sheet.getDataRange().getLastColumn();

  var rangeString = sheetName+"!"+sheet.getRange(3, colIndex, 1, colIndex2).getA1Notation();

  return "SELECT "+yq(rangeString, squery);
}

function yq(range, sQuery) {
  var sheetName = SpreadsheetApp.getActiveSheet().getSheetName();
  if( (typeof range == "object") && (range !== null) ){
    sheetName = range.getSheet().getName();
    range = range.getA1Notation();
  }else{
    var tRange = range.split("!");
    if(tRange.length > 1){
      sheetName = tRange[0]
      range = tRange[1];
    }
  }

  var sheet = SpreadsheetApp.getActive().getSheetByName(sheetName);
  var range = sheet.getRange(range);

  var qInput = sQuery.split(" ");
  var outQuery = [];
  for(var i = 0; i < qInput.length; i++){
    if(qInput[i].charAt(0) == "$"){
      var colIndex = getHeaderValues(sheet, qInput[i].slice(1), range, "[COLUMNIDs ROW REFERENCE]");
      outQuery.push(colIndex.toString());
    }else{
      outQuery.push(qInput[i]);
    }
  }

  return outQuery.join(" ");
}


function getHeaderValues(sheet, columnName, range, columnHeaderRow){
  var columnHeaderRowIndex = range.getRowIndex() - 1;
  if(!isNaN(parseFloat(columnHeaderRow)) && isFinite(columnHeaderRow)){
    columnHeaderRowIndex = range.getRowIndex() + columnHeaderRow;
  }else if(typeof columnHeaderRow == "string"){
    columnHeaderRowIndex = SpreadsheetApp.getActive().getRangeByName(columnHeaderRow).getRowIndex();
  }  
  var numColumns = range.getLastColumn() - range.getColumn() + 1;
  var headersRange = sheet.getRange(columnHeaderRowIndex, range.getColumn(), 1, numColumns);
  var headers = headersRange.getValues()[0];
  var hIndex = null;
  for(var i = 0; i < headers.length; i++){    
    if(headers[i] == columnName){
      hIndex = headersRange.getColumn() + i;
      hIndex = sheet.getRange(headersRange.getRow(), hIndex).getA1Notation();
      return hIndex.charAt(0);
    }
  }
  return null;
}
ynki9
  • 21
  • 3
1

Hi I have another solution. I broke Lines that the hole thing can be read.

=query($A$1:$C$4,"select "

&CHAR(MATCH("time";1:1;0)+64)
&","
&CHAR(MATCH("sit";1:1;0)+64)
&","
&CHAR(MATCH("stand";1:1;0)+64)

&"where C='mike'",0)

Still not nice, and you are limmited to 24 Columns. Since after that you need to split. Dont like it at all :(

legine
  • 11
  • 1
1

There are two things I've found you can do to improve Query column references:

  1. Place column references searches in another cell (legend) and use

    =query(A:C,"select "&D2&" where "&E2&" starts with '"&E3&"' ")

    where for example D2 = A, E2 = C, E3 = foo

    This has the benefit of allowing you to change the Query terms by editing cells rather than formulas and also doesn't break when you add/move columns around. You can take it further and name the ranges to make it look like

    =query(A:C,"select "&cats&" where "&name&" starts with '"&search&"' ")

  2. Switch it to Col[n] reference mode by messing with the range

    =query({A:C},"select Col1 where Col3 matches 'foo' ")

    This gives you the ability to move the dataset around without breaking it, but will break down if you insert more columns into the range.

Tyson Cecka
  • 11
  • 1
1

I have found a workaround that is useful, you can name columns as named ranges and then you query those specific columns, some caveats is that you can't do that with large databases

=QUERY({employee,score},"select Col1,avg(Col2) group by Col1")
David Salomon
  • 804
  • 1
  • 7
  • 24