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.

Functions

  • 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.

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

  • 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:
  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

  • 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".
Did this answer your question?