Query call via Apps Script returning 0 (zero) instead of Data Range

Question

For some reason, historyQuery2 returns the proper data range, but historyQuery1 returns 0.

function getEventHistory(cS,findAcct) {
// copy filtered values from Events sheet to Client sheet (cS)
  var historyCell = cS.getRange("E5");
  var historyQuery1 = "=query(Events!A1:AH,\"select * where A = " & findAcct & "\")";
  var historyQuery2 = "=query(Events!A1:AH,\"select * where A = 808998\")";
  historyCell.setFormula(historyQuery2);
}

I know I can use filters but I find queries run far faster (the Events worksheet is around 12000 rows).

I was expecting a subset of the Events sheet that met the query criteria (Column A equals findAcct).

What I get is a zero (0).

I've tried eval (ick) but no difference. I've tried forcing the findAcct variable (which is an integer) into a string. I've tried passing the query string to the function. I've tried declaring historyQuery as a const. Still no luck.

The problem seems to lie in the historyQuery1 string. When I try to output that string using Browser.MsgBox as a debug, I also get zero (0), but historyQuery2 query string appears in the message box.

Answer 1

Sometimes the act of writing this all out seems to unblock the flow.

I recalled the old concat function. Here is the solution:

var historyQuery = "=query(Events!A1:AH,\"select * where A = ";
historyQuery = historyQuery.concat(findAcct.toString());
historyQuery = historyQuery.concat("\")");
historyCell.setFormula(historyQuery);

The above works. I probably can clean up the concat statements, but don't have time...

Hope this helps someone else out there!

Answer 2

Use a template literal, like this:

function getEventHistory(cS, findAcct) {
  const historyQuery = `=query(Events!A1:AH, "where A = ${findAcct}", 0)`;
  cS.getRange('E5').setFormula(historyQuery);
}

See template literals.