CODEWITHSUNDEEP
sqlgoogle-apps-scriptgoogle-sheets
jason
jason

Reputation: 4439

google spreadsheet query function column id other than letter of column

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?

Upvotes: 3

Views: 15942

Answers (5)

David Salomon
David Salomon

Reputation: 849

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

Upvotes: 1

Tyson Cecka
Tyson Cecka

Reputation: 11

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.

Upvotes: 1

ynki9
ynki9

Reputation: 21

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

Upvotes: 2

legine
legine

Reputation: 11

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 :(

Upvotes: 1

AdamL
AdamL

Reputation: 24609

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

Upvotes: 6

Related Questions