Group 65Go to AppSheet
All Collections
EXPRESSIONS
Expression Types
Other Expressions

Other Expressions

Expressions to modify text, create unique IDs, and work with location.
Santiago Uribe Montoya avatar
Written by Santiago Uribe Montoya
Updated over a week ago

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?