Audience: Intermediate
In AppSheet, expressions are the “engine” of your app and they allow you to calculate values, filter data, and control your app’s behavior.
1. What are AppSheet Expressions?
Think of expressions as the AppSheet version of Google Sheets or Excel formulas. They are used to:
-
Calculate Values: Automate math or text concatenation.
-
Filter Data: Show only specific rows to specific users.
-
Define Logic: Determine when a button should be visible or when an automation should trigger.
2. Where Expressions Live
Here are common areas where you will use expressions:
-
Initial Values: Setting a starting value (like TODAY()) when a user opens a new form.
-
App Formulas: Calculating values (like [Price] * [Quantity]).
-
Show_If / Editable_If: Using YES/NO logic to hide or lock fields based on conditions.
-
Virtual Columns: Creating “calculated fields” that don’t exist in your data but are vital for the values that are computed on-the-fly using expressions based on other columns in the same row.
3. Fundamental Syntax Rules
There are a few Golden Rules for writing correct expressions:
-
Square Brackets [ ]: Always use these for Column names (e.g., [Status]).
-
Quotes " ": Use these for text strings (e.g., “Completed”).
-
Parentheses ( ): Every function must have these to contain its arguments (e.g., SUM(Select(…))).
-
Dot Notation .: Used for “Dereferencing” to pull data from a linked table (e.g., [Customer ID].[Phone]).
4. Key Expression Categories
- Math Category
-
Example: [Subtotal] + [Tax]
-
Use Case: Used for performing standard arithmetic operations.
- Text Category
-
Example: CONCATENATE([First], " ", [Last])
-
Use Case: Ideal for joining strings together.
- Logical Category
-
Example: IF([Status]=“Late”, “Red”, “Green”)
-
Use Case: Used for creating conditional results.
- List Category
-
Example: SELECT(Invoices[Total], [Status]=“Unpaid”)
-
Use Case: Specifically designed for retrieving a list of values from another table.
- Datetime Category
-
Example: TIMENOW() or TODAY()
-
Use Case: Essential for capturing timestamps.
5. The Expression Assistant
Within the app editor, you can access the Expression Assistant anywhere you’re able to enter an expression.
To access the Expression Assistant:
-
Click the flask icon next to an input field to toggle to the expression format (if available). For example:
-
Click in the expression field indicated with an equals sign (=). For example:
Note: The equals sign indicates that the expression’s result will be used as the setting’s value.
The Expression Assistant opens. Build and test your expressions as described in the following sections.
Build expressions
Build expression using the Expression Assistant as shown in the following figure.
As highlighted in the previous figure, the Expression Assistant enables you to:
-
Build your expression in the text area
-
Confirm the valid syntax and function of the expression
-
View and insert column references (explained in subsequent sections) or predefined patterns (explained in subsequent sections) into the expression using the tabs at the bottom of the dialog
-
Test the expression (explained in subsequent sections)
-
Format the expression to add spacing, indenting, and so on
-
Expand the text area by:
-
Dragging the handle in the lower-right corner to accommodate more complex expressions that span several lines
-
Collapsing the tabs at the bottom of the dialog
-
-
Save the expression by clicking Save
The Expression Assistant provides the following features to assist you with AppSheet function syntax:
-
Syntax highlighting to display components of an expression in different colors
Color key:
-
Green: AppSheet functions
-
Pink: Data references, such as column names
-
Blue: Text
-
Orange: Numbers
-
Syntax of current function with contextual highlighting
-
Auto-completion for columns (when cursor is positioned within an expression or column definition)
Note: You can use the Ctrl + Space keyboard shortcut to display the auto-completion suggestions.
-
Documentation panel for quick access to the description and syntax for the current function
View and insert column references into expressions
View and insert column references into expressions by clicking the Data Explorer tab, as shown in the following figure.
The Data Explorer tab enables you to:
-
View all tables and columns in your app
-
Insert a column reference into an expression, as described below
-
View Ref associations (references to other tables)
-
Expand and collapse table, and pin their display in the Expression Assistant
To insert a column reference into an expression:
-
Click within the expression text area where you want to insert the column reference.
-
Expand the table that contains the column you want to add.
-
Position your cursor over the column name and click Insert.
View and insert predefined patterns into expressions
View and insert predefined patterns into expressions by clicking the Examples tab, as shown in the following figure.
The Examples tab enables you to:
-
Select an expression type
-
Insert a predefined pattern into an expression, as described below
To insert a predefined pattern into an expression:
-
Click within the expression text area where you want to insert the example pattern.
-
Select the tab based on the expression type that you want to add.
-
Position your cursor over the example pattern and click Insert.
Test expressions
To test the results of the expression for each row in the table, click Test in the Expression Assistant. The Expression Test page displays.
The first column shows the Expression Result for each row in the table, as shown below:
Expand the Expression Result by clicking the expand icon to view the results of each part of the expression:
For more information, refer to the Support article or use the Introduction to AppSheet Expressions video tutorial.