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.

Answer 3

Perhaps, through a formula you can get something done than you need.

function testFormula() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheets()[1];
  var cell = sheet.getRange("A1");
  cell.setFormula("=QUERY('Sheet0'!A1:B5;\"SELECT A, B\"; 0)");
}

Answer 4

I've managed to use Ala-SQL library.

Step 1.

Copy the library to your project: https://raw.githubusercontent.com/agershun/alasql/develop/dist/alasql.min.js

Step 2.

Read the documentation carefully and you are ready to go!

Step 3.

Create your own translator to connect sheets with the library. Please see more info in my post.

I used sql in custom functions in order to show the way to use it:

=getAlaSql(sql_text, West!A:G, East!A:G, Central!A:G)

• sql_text use any supported syntax with "select" clause. Note: Ala-SQL also has update, insert, but not with anonymous functions.
• select Col1 from ?... union all ... select Col1 from ? is a proper syntax. I used Col1-notation. Question marks mean tables or variables from the rest of a function.
• West!A:G, East!A:G, Central!A:G is a list of tables. The library replaces question marks with this variables.

/*
  The code here uses http://alasql.org library:
  
  Downlaad it from here:
    https://raw.githubusercontent.com/agershun/alasql/develop/dist/alasql.min.js
  or here (not tested)
    https://cdn.jsdelivr.net/npm/alasql
    
    My sample sheet is here:
    https://docs.google.com/spreadsheets/d/1V0kHvuS0QfzgYTvkut9UkwcgK_51KV2oHDxKE6dMX7A/copy
*/

function test_AlaSqlQuery()
{
  
  var file = SpreadsheetApp.getActive();
  var sheet1 = file.getSheetByName('East');
  var range1 = sheet1.getDataRange();
  var data1 = range1.getValues();
  
  var sheet2 = file.getSheetByName('Reps');
  var range2 = sheet2.getDataRange();   
  var data2 = range2.getValues();
  
  var sql = "select a.Col1, a.Col3, reps.Col2, a.Col7 from ? a left join ? reps on reps.Col1 = a.Col3";
  var data = getAlaSql(sql, data1, data2);
  
  Logger.log(data);  

}

function getAlaSql(sql)
{
  var tables = Array.prototype.slice.call(arguments, 1);  
  var request = convertToAlaSql_(sql);
  var res = alasql(request, tables);
  //return JSON.stringify(res);
  return convertAlaSqlResultToArray_(res);
}



function test_AlaSqlSelect()
{
  var file = SpreadsheetApp.getActive();
  var sheet = file.getSheetByName('East');
  var range = sheet.getDataRange();
  var data = range.getValues();
  
  var sql = "select * from ? where Col5 > 50 and Col3 = 'Jones'"
  Logger.log(convertAlaSqlResultToArray_(getAlaSqlSelect_(data, sql)));
  /*
  [  
     [  
        Sun Jan 07 12:38:56      GMT+02:00      2018,
        East,
        Jones,
        Binder,
        60.0,
        4.99,
        299.40000000000003                                            // error: precision =(
     ],
    ...
  ]
  
  */

}


function getAlaSqlSelect_(data, sql)
{
  var request = convertToAlaSql_(sql);
  var res = alasql(request, [data]);
  // [{0=2016.0, 1=a, 2=1.0}, {0=2016.0, 1=a, 2=2.0}, {0=2018.0, 1=a, 2=4.0}, {0=2019.0, 1=a, 2=5.0}]
  return convertAlaSqlResultToArray_(res);
}


function convertToAlaSql_(string)
{
  var result = string.replace(/(Col)(\d+)/g, "[$2]");
  result = result.replace(/\[(\d+)\]/g, function(a,n){ return "["+ (+n-1) +"]"; });
  return result;
}


function convertAlaSqlResultToArray_(res)
{
  var result = [];
  var row = [];
  res.forEach
  (
  function (elt)
  {
    row = [];
    for (var key in elt) { row.push(elt[key]); }
    result.push(row);
  }  
  );
  return result;
}

Answer 5

If you already have the user's information you want to use in a cell in the sheet named : "filter"

You can put this kind of formula in a sheet named : "template"

in B1, this formula : =query(DataToFilter!A1:E,"select A,B,C,D where D contains '"&A1&"'",-1)

then with this code you have in result a sheet named : "newFilter" that receive your filtered data.

var templateToCopy = ss.getSheetByName('template');
template.copyTo(ss).setName('newFilter');
var editFilter = ss.getSheetByName('newFilter');
//assuming the user information needed is in A5
editFilter.getRange('A1').setValue('=filter!A5');