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

Can I ask you about your question? 1. You want to retrieve a value from the column header (Row 1) and the row header (Column A) using Google Apps Script. Is my understanding correct? 2. Can you provide the detail information of ``array.FindValueAtCoordinates(rowkey,colkey)``? 3. It seems that the script is not included in your shared spreadsheet. If you have the script, please show us. — Tanaike, Dec 05 '18 at 22:16
You can still use the Query formula after a fashion. The Query formula leverages Google's Visualization API under the hood. I wrote a Google Apps Script module that does the same. You can check it out [at this link](https://stackoverflow.com/questions/51327982/using-bound-google-scripts-to-generate-a-query-object/51328419#51328419). — TheAddonDepot, Dec 05 '18 at 23:13
Possible duplicate of [Using Bound Google Scripts to Generate a Query Object](https://stackoverflow.com/questions/51327982/using-bound-google-scripts-to-generate-a-query-object) — TheAddonDepot, Dec 05 '18 at 23:20

Cooper · Accepted Answer · 2018-12-06T06:42:22.437

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

Query an Array in Google Script

1 Answers1