How to evaluate a Sheet cell function in Google Apps Script

Question

I almost had this small freelancing project, and it was a tiny bit of a technical wake-up call.

Basically, there is this master Sheet the client wanted to keep for her own reference, and my plan to solve her problem was to create this Google Web App that uses it as backend.

The sheet's columns roughly looked like this:

• Last Name
• First Name
• Period
• Start Time
• End Time
• Reason for Leaving

The plan for the web app was simple:

• create some initial screen consisting of just first name, last name, period, and a "Next" button.
• when next button was hit, we fetch the latest record for that student, where their start time is not null but their end time is
  • if such record exist, spawn the "Welcome Back!" screen, with an "End Time" button, or something similar
  • else, spawn the "Start time" screen, with an autofill/input for reason for leaving, and a "Start Time" button
• whatever button they click will post data to the back end, show some info/finished screen, and then return to the main screen

I know for a fact that Google Sheets has some QUERY() function you can use in a cell. If we were to use it in a temp Sheet (which is a hacky way to solve this problem), we could say something like

=QUERY('master'!A2:Z, "SELECT * WHERE A=:lastName AND B=:firstName AND C=:period AND D IS NOT NULL AND E IS NULL")

// yes, that's prepared statement syntax in the second argument of that QUERY() :p

My question is, how do I do something like that straight from the Google Apps Script code?

Answer 1

Explanation:

It's a long-asked problem, but until now, there is no evaluate() method in Apps Script that can be used to compute or evaluate a formula without putting it into a cell.

See: How to use a formula written as a string in another cell [evaluate for Google Spreadsheet]

I can only suggest these workarounds:

1. Since you already have a formula, you can use setFormula() to put it into a cell and execute it from the Sheet.

Sample Code:

Assuming the parameters are string variables:

var lastName;
var firstName;
var period;

/* some code here to define range */

range.setFormula("=QUERY(\'master\'!A2:Z, \"SELECT * WHERE A=" + lastName " AND B=" + firstName + " AND C=" + period + "AND D IS NOT NULL AND E IS NULL\")");

References:

Class Range | setFormula()

The only other option is to code your logic from scratch.