New to expressions and formulas? Try Expressions: The Essentials.
You can also learn more about other types of expressions.
Expressions may be used in various AppSheet features--app formulas, column constraints (Valid_If, Show_If, Required_If). initial values, and virtual columns--to customize app behavior and provide advanced functionality.
Expressions in this article align with the Other Expressions section of Expression Builder in the app editor.
Other expressions impact a range of scenarios and don't fit into any of the previous yes/no, math, time, columns, or list expression categories.
Functions
- CONCATENATE(text-expression1[, text-expression2]...) combines two or more text values.
- CONTAINS(text, keyword) returns TRUE if keyword is found in text.
- CONTEXT(option) returns a Text value that indicates where in the app this expression is being used. It's useful for "meta-data" operations and currently has very limited functionality. There are currently only three allowed options. CONTEXT("Host") returns one of the following: "Browser", "Device", or "Server" to indicate where the expression is being evaluated. CONTEXT("View") returns the name of the current view or null if the expression is not run in the context of a view. CONTEXT("ViewType") returns the type of the current view (table, chart, map, etc) or null if the expression is not run in the context of a view.
- DISTANCE(location, location) returns the approximate distance in kilometers between two locations expressed as LatLong values. DISTANCE() does not work with Address values.
- HERE() returns the current LatLong of the user.
- IF(condition, then-expression, else-expression) returns either then-expression or else-expression, depending on condition. Both then-expression and else-expression must be of the same type (e.g., text, number, etc).
- FIND(fragment, text) returns the position at which fragment first occurs within text, case-sensitive.
- LEFT(text, number) returns the left-most number of characters from text.
- LEN(text-expression) returns the length of text-expression.
- HYPERLINK(address, text) returns a hyperlink to address with the clickable text. For example, the formula HYPERLINK("http://en.wikipedia.org", "Click here to visit Wikipedia") will create a link that looks like this: Click here to visit Wikipedia. If you use the HYPERLINK() formula in a non-virtual column, make sure to mark IsHyperLink as TRUE in the Type Qualifier to ensure the value of this column is written as HYPERLINK() formula in the spreadsheet.
- INDEX(list, number) returns the value of item number in list.
- INITIALS(name) returns the first letter of each component of name. For example, INITIALS("Bruce Lee") returns "BL".
- LAT(LatLong) returns the latitude component of a LatLong value
- LATLONG(lat, long) returns a LatLong value from component latitude and longitude (both Decimal values)
- LINKTEXT(hyperlink) returns the link text of hyperlink. For example, calling LINKTEXT(HYPERLINK("http://google.com", "Google")) will return "Google".
- LINKURL(hyperlink) returns the URL part of hyperlink. For example, calling LINKURL(HYPERLINK("http://google.com", "Google")) will return "http://google.com".
- LONG(LatLong) returns the longitude component of a LatLong value
- RIGHT(text, number) returns the right-most number of characters from text.
- SUBSTITUTE(original-text, text-to-replace, replace-with) returns original-text with all occurrences of text-to-replace replaced with replace-with.
- TEXT_ICON(text) returns a Thumbnail image containing text. This is particularly useful in constructing images from text data for better presentation in gallery or deck views. A common use case is, e.g., TEXT_ICON(INITIALS([Student Name])).
- UNIQUEID() returns a unique Text value suitable for use as a key.
- USEREMAIL() returns the Email of the current user.
- USERLOCALE() returns the user's locale string, as defined by the user's browser settings. You can learn more about the locale strings here.
- USERNAME() returns the Name of the current user if known.
Legacy Operators
For backwards compatibility, we also support the function syntax below for a set of functions that have been supported from the earliest AppSheet release.
- @(_HERE) for the current LatLong of the user.
- @(_UNIQUE) for a unique Text value suitable for use as a key.
- @(_USEREMAIL) for the Email of the current user.
- @(_USERNAME) for the Name of the current user if known.
Common and Complex Expressions
Common Expressions
- LEN("AppSheet") returns 8.
- CONCATENATE([First Name], " ", [Last Name]) returns a full name.
- IF(([Status] = "Open"), "Green", "Red") returns "Green" when [Status] equals "Open"; otherwise, returns "Red".
Example: Column AppName with a value of "Sales-10305"
- LEFT([AppName], 5) returns "Sales".
- RIGHT([AppName], 5) returns "10305"
- LEFT([AppName], FIND("-", [AppName])) returns "Sales"
Use the following expressions in the Initial Value feature of the app editor:
- UNIQUEID(): Use to generate a unique Text value, e.g. a unique Invoice ID.
- HERE(): Use to identify the user's current LatLong.
- USEREMAIL(): Use to populate record value based on user login
- USERNAME(): It's generally a good idea to avoid USERNAME() because it's unreliable: providers like Google only return the user's name if that user has enabled Google+ on their account. If you wish to retrieve the user's name, do the following:
- Create a table having UserEmail as its key field followed by a UserName field.
- Populate the table manually by adding one entry for each UserEmail address.
- To obtain the user name, use a reference expression based on USEREMAIL().
Complex Expressions
- LEN([_THIS]) <= 10: Use this expression in the Valid_If constraint to restrict form field input to a maximum of 10 characters.
- IF(([Status] = "Open"), "Green", IF(([Status] = "Closed"), "Red", IF(([Status] = "Not Started"), "Blue", "Purple"))): Returns "Green" when Status equals Open; returns "Red" when Status equals Closed; returns "Blue" when Status equals Not Started; otherwise, returns "Purple".
Other Expression Patterns and Examples
In Expression Builder, follow the pattern below for a null, numeric, text, email, name, or LatLong result. See examples for further clarity.
Pattern Result Example
[{ref_column}], null [Ref].[Ref]
[{lookup_column]
LEN({*}) Number LEN([ChangeTimestamp])
CONCATENATE({*}, {*}) Text CONCATENATE([ChangeTimestamp], "value_1")
LEFT({Text},{Number}) Text LEFT([TEXT], 1)
RIGHT({Text},{Number}) Text RIGHT([TEXT], 1)
FIND({Text},{Text}) Text LEFT([TEXT], "text value")
IF({Yes/No},{*},{*}) * IF([TEXT], 1)
USERNAME() Name USERNAME()
USEREMAIL() Email USEREMAIL()
UNIQUEID() Text UNIQUEID()
HERE() LatLong HERE()