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.
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;
}
=QUERY is a spreadsheet function. If you want that functionality in Apps Script you can leverage Google's Visualization API and Query Language. I wrote a custom module for it in GAS. Here's an extract:
(function(context) {
const Utils = (context.Utils || (context.Utils = {}));
/**
* Queries a spreadsheet using Google Visualization API's Datasoure Url.
*
* @param {String} ssId Spreadsheet ID.
* @param {String} query Query string.
* @param {String|Number} sheetId Sheet Id (gid if number, name if string). [OPTIONAL]
* @param {String} range Range [OPTIONAL]
* @param {Number} headers Header rows. [OPTIONAL]
*/
Utils.gvizQuery = function(ssId, query, sheetId, range, headers) {
var response = JSON.parse( UrlFetchApp
.fetch(
Utilities.formatString(
"https://docs.google.com/spreadsheets/d/%s/gviz/tq?tq=%s%s%s%s",
ssId,
encodeURIComponent(query),
(typeof sheetId === "number") ? "&gid=" + sheetId :
(typeof sheetId === "string") ? "&sheet=" + sheetId :
"",
(typeof range === "string") ? "&range=" + range :
"",
"&headers=" + ((typeof headers === "number" && headers > 0) ? headers : "0")
),
{
"headers":{
"Authorization":"Bearer " + ScriptApp.getOAuthToken()
}
}
)
.getContentText()
.replace("/*O_o*/\n", "") // remove JSONP wrapper
.replace(/(google\.visualization\.Query\.setResponse\()|(\);)/gm, "") // remove JSONP wrapper
),
table = response.table,
rows;
if (typeof headers === "number") {
rows = table.rows.map(function(row) {
return table.cols.reduce(
function(acc, col, colIndex) {
acc[col.label] = row.c[colIndex] && row.c[colIndex].v;
return acc;
},
{}
);
});
} else {
rows = table.rows.map(function(row) {
return row.c.reduce(
function(acc, col) {
acc.push(col && col.v);
return acc;
},
[]
);
});
}
return rows;
};
Object.freeze(Utils);
})(this);
Just drop that module into its own file in your GAS editor then you can call it as follows:
// result is an array of objects if header row is specified, otherwise it is an array of arrays
var result = Utils.gvizQuery(
"<YOUR_SPREADSHEET_ID>",
"<YOUR_QUERY_STRING>",
<SHEET_ID_IF_NEEDED>, // can be a number (the sheetId), or the name of the sheet; if not needed, but headers are, pass in undefined
<RANGE>, // specify range, ex: `A2:O`
<HEADER_ROW_INDEX_IF_NEEDED> // always a number
);
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.
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've managed to use Ala-SQL library.
Copy the library to your project: https://raw.githubusercontent.com/agershun/alasql/develop/dist/alasql.min.js
Read the documentation carefully and you are ready to go!
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;
}
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');
