Query an Array in Google Script

Question

I have learned how to use the Query formula in Google Sheets. I'm wondering if there is a comparable function in Google App Script. I would like to look at a 2D array and find a value where to variables intersect.

Consider an array where each row has a name, and each column has a month. The intersection has some dollar amount. I need programmatic language to say, What is the value for row(Nate), column(november)?

https://docs.google.com/spreadsheets/d/1vd7hgRVNTtIvKrr2ssLzVoDw5IVaXjOjF39CvVm2cYQ/

I know how to do this with for loops. I am wondering if there's just a simpler way. I wish there was array.FindValueAtCoordinates(rowkey,colkey).

Answer 1

Try this:

This creates an associative array between names in first column and row numbers and between column headers and column numbers.

function testIntersection(){
  var r='Ken';
  var c='Dec18';
  Logger.log(intersection(r,c));
}

function intersection(r,c){
  var ss=SpreadsheetApp.getActive();
  var sh=ss.getSheetByName('Sheet18');
  var rg=sh.getDataRange();
  var vA=rg.getValues();
  var colObj={};
  var rowObj={};
  for(var i=0;i<vA.length;i++){
    if(i==0){
      for(var j=0;j<vA[0].length;j++){
        if(vA[i][j] && !colObj.hasOwnProperty(vA[i][j])){
          colObj[vA[i][j]]=j+1;
        }
      }
    }
    if(vA[i][0] && !rowObj.hasOwnProperty(vA[i][0])){
      rowObj[vA[i][0]]=i+1;
    }
  }  
  return sh.getRange(rowObj[r],colObj[c]).getValue();           
}

Make sure your top row of column headers is formatted as plain text. Otherwise they maybe interpreted as dates in auto and there is no function getRange(string, class).

This is just some code I was playing around with to test the above function. Uses a table similar to yours. It generates a modeless dialog with all of the possible intersections listed.

function playingWithIntersection(){
  var ss=SpreadsheetApp.getActive();
  var sh=ss.getSheetByName('Sheet18');
  var rg1=sh.getRange(1,2,1,sh.getLastColumn()-1);
  var rg2=sh.getRange(2,1,sh.getLastRow()-1,1);
  var ckeys=rg1.getValues();
  var rkeys=rg2.getValues();
  var colkeys=[];
  var rowkeys=[];
  for(var i=0;i<ckeys[0].length;i++){
    colkeys.push(ckeys[0][i]);
  }
  for(var i=0;i<rkeys.length;i++){
    rowkeys.push(rkeys[i][0]);
  }
  var hl='<style>th,td{border:1px solid #000;}</style><table><th>rowkeys</th><th>colkeys</th><th>Value</th><tr></tr>';
  for(var i=0;i<rowkeys.length;i++){
    for(var j=0;j<colkeys.length;j++){
      hl+=Utilities.formatString('<tr><td>%s</td><td>%s</td><td>%s</td></tr>', rowkeys[i], colkeys[j], intersection(rowkeys[i],colkeys[j]));
    }
  }
  hl+='</table>';
  var ui=HtmlService.createHtmlOutput(hl);
  SpreadsheetApp.getUi().showModelessDialog(ui, 'Intersections')
}