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

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

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

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

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.

Tags:

Google Sheets

Google Apps Script

Google Query Language

Recent Posts