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

Answer 2

No, there is no API for the Query function that allows it to be called from Google Apps Script. (There is no way to call ANY spreadsheet function in this way, in fact.)

You can get some similar functionality without writing it all yourself, though. The 2D Arrays Library includes a variety of "filter" functions that let you retrieve matching rows.