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.
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.
- 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).
- GETX(xy) returns the X portion of an XY value as a decimal value in the range 0.000000 to 100.000000.
- GETY(xy) returns the Y portion of an XY value as a decimal value in the range 0.000000 to 100.000000.
- 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.
- 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)
- 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
- 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.
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
- IF(([Status] = "Open"), "Green", "Red") returns "Green" when [Status] equals "Open"; otherwise, returns "Red".
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().
- 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".