Google Apps Script: Array Reference in SQL Query (Javascript/AlaSQLGS)-CodePudding

I cannot get my query to accept my WHERE entry:

const query = "SELECT MATRIX Col1 FROM ? WHERE Col1 IN (name) ";
var res = alasql(AlaSQLGS.transformQueryColsNotation(query), [values]);
console.log(res.length);
console.log(res[0][0][0]);

res.length returns 0 and I get the following error for res[0][0][0]: "TypeError: Cannot read property '0' of undefined"

Entry for name is coming from an array but after trying everything I could possibly think of, I created a simple var name = "Tribiani, Joe". Didn't work. I can't even get this to work typing in the name:

const query = "SELECT MATRIX Col1 FROM ? WHERE Col1 IN ('Tribiani, Joe') ";

The only way I get this to work is: const query = "SELECT MATRIX Col1 FROM ? WHERE Col1 LIKE ('Tribiani, Joe%') "; but I can't use the variable 'name' in this case either, has to be written in.

Anybody has any ideas what I am doing wrong? And how do I correctly point to an array element in a AlaSQL query?

CodePudding user response：

Does this work?

var name = "Tribiani, Joe";
const query = `SELECT MATRIX Col1 FROM ? WHERE Col1 IN (${name})`;

I would have put it into a comment but the backticks do not show up properly.

CodePudding user response：

The syntax is alsql(query, [query params list]). Add name to the param list:

const query = "SELECT MATRIX Col1 FROM ? WHERE Col1 IN ? ",
  name = ['Tribiani', 'Joe'],
  res = alasql(AlaSQLGS.transformQueryColsNotation(query), [values, name]);
console.log(res);