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:
  1. Create a table having UserEmail as its key field followed by a UserName field.
  2. Populate the table manually by adding one entry for each UserEmail address.
  3. 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()

Did this answer your question?