Can the google spreadsheet 'query' function be used in google apps script?

Question

I'm looking for a way to programmatically populate a spreadsheet that filters data from another spreadsheet based on the logged in user.

I am able to do this using the query function inside a spreadsheet. BUT, unable to figure out a way to call the query function from apps script?

Can this be done? Would appreciate sample code. Thanks.

Answer 1

I do not know whether there is a restriction on that ...

function test () {
  var req = query("=QUERY(shopT!B2:E; \"select min(E) where (B=3 or B=4 and D=2) group by C, D\")");

  Logger.log(req);
}

function query(request) { 
  var sheet = sp.insertSheet();
  var r = sheet.getRange(1, 1).setFormula(request);

  var reply = sheet.getDataRange().getValues();
  sp.deleteSheet(sheet);

  return reply;
}