Skip to content

Expression — Chainable Filter Predicates

Expression builds filter conditions by chaining methods on a column name. Use it with BareFrame.filter by calling .toFunction() at the end.

JavaScript
// Build a condition, then convert to a predicate function
const cond = gaslamp.Expression.col("age").ge(18).toFunction();
const adults = df.filter(cond);

Note: df.filter() expects a plain function, not an Expression instance. Always call .toFunction() before passing to filter().

When to Use Expression vs Arrow Functions

Use Expression when:

  • Filtering by column value (comparisons, string matching, null checks)
  • Combining multiple conditions with AND / OR
  • Code clarity is important

Use arrow functions when:

  • Applying custom logic (calculations, date math)
  • Comparing values across multiple columns
  • Business rules that don't fit the predicate model
JavaScript
// Expression: clear intent for column filtering
df.filter(gaslamp.Expression.col("age").ge(18).toFunction());

// Arrow function: custom logic
df.filter(row => {
  const date = new Date(row.get("date"));
  return date > new Date("2024-01-01");
});

// Mix: Expression for first pass, arrow for complex logic
const active = df.filter(gaslamp.Expression.col("status").eq("active").toFunction());
const scored = active.filter(row => calculateScore(row) > 80);

For a full introduction to filter, see Filtering Rows.

Creating Expressions

Use Expression.col to create an expression for a column. new Expression is an equivalent alternative.

JavaScript
gaslamp.Expression.col("age")   // Recommended
new gaslamp.Expression("age")   // Alternative

Comparison Operations

Numeric Comparisons

gt, ge, lt, le accept a number value:

JavaScript
function example_numeric() {
  const df = gaslamp.BareFrame.fromSheet(sheet);

  const adults = df.filter(gaslamp.Expression.col("age").ge(18).toFunction());
  const highScores = df.filter(gaslamp.Expression.col("score").gt(50).toFunction());
  const underBudget = df.filter(gaslamp.Expression.col("amount").le(1000).toFunction());
}

Equality Comparisons

eq and ne accept any value (string, number, boolean, null):

JavaScript
function example_equality() {
  const df = gaslamp.BareFrame.fromSheet(sheet);

  const active = df.filter(gaslamp.Expression.col("status").eq("active").toFunction());
  const notDeleted = df.filter(gaslamp.Expression.col("status").ne("deleted").toFunction());
  const score100 = df.filter(gaslamp.Expression.col("score").eq(100).toFunction());
}

Available operators: gt, ge, lt, le (number only) | eq, ne (any value)

Note: Null/undefined values always return false for comparison operators. Use isNull() for null checking.

String Operations

Match string patterns in column values:

JavaScript
function example_stringOps() {
  const df = gaslamp.BareFrame.fromSheet(sheet);

  const validEmails = df.filter(gaslamp.Expression.col("email").contains("@").toFunction());
  const admins = df.filter(gaslamp.Expression.col("email").startsWith("admin").toFunction());
  const company = df.filter(gaslamp.Expression.col("email").endsWith("@company.com").toFunction());
}

Returns false for:

  • Non-string values (numbers, dates, etc.)
  • Null or undefined values

Returns true for:

  • Empty search string (every string contains empty string)

Null Checking

Explicitly check for null/undefined values:

JavaScript
function example_nullChecks() {
  const df = gaslamp.BareFrame.fromSheet(sheet);

  const noEndDate = df.filter(gaslamp.Expression.col("endDate").isNull().toFunction());
  const hasEmail = df.filter(gaslamp.Expression.col("email").isNotNull().toFunction());
}

Array Membership

Check if a column value is in a list:

JavaScript
function example_in() {
  const df = gaslamp.BareFrame.fromSheet(sheet);

  const validStatuses = df.filter(
    gaslamp.Expression.col("status").in(["active", "pending", "review"]).toFunction()
  );

  const highPriority = df.filter(gaslamp.Expression.col("priority").in([1, 2]).toFunction());
}

Logical Operators

Combine predicates with and(), or(), and not():

JavaScript
function example_logical() {
  const df = gaslamp.BareFrame.fromSheet(sheet);

  // AND: both conditions true
  const activeAdults = df.filter(
    gaslamp.Expression.col("status").eq("active")
      .and(gaslamp.Expression.col("age").ge(18))
      .toFunction()
  );

  // OR: at least one true
  const salesOrMarketing = df.filter(
    gaslamp.Expression.col("dept").eq("Sales")
      .or(gaslamp.Expression.col("dept").eq("Marketing"))
      .toFunction()
  );

  // NOT: negate condition
  const notArchived = df.filter(
    gaslamp.Expression.col("status").eq("archived").not()
      .toFunction()
  );
}

Note: AND stops evaluating if the left side is false. OR stops evaluating if the left side is true.

Practical Workflow

Read → filter with Expression → write:

JavaScript
function workflow_expressionFilter() {
  const book = SpreadsheetApp.getActiveSpreadsheet();

  const inputSheet = book.getSheetByName("Contacts");
  const df = gaslamp.BareFrame.fromSheet(inputSheet);

  const activeContacts = df.filter(
    gaslamp.Expression.col("status").eq("active")
      .and(gaslamp.Expression.col("email").contains("@"))
      .toFunction()
  );

  const outputSheet = book.getSheetByName("Active Contacts")
    ?? book.insertSheet("Active Contacts");
  activeContacts.toSheet(outputSheet);

  console.log(`Found ${activeContacts.length} active contacts`);
}

See Also