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

Other Expressions

Expressions to modify text, create unique IDs, and work with location.
Madeline Clarke avatar
Written by Madeline Clarke
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.

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?