AppSheet

https://support.google.com/appsheet
Admin
AppSheet is a no-code platform for creating, automating, and managing applications with AI...
Tokens:845,950
Snippets:5,928
Trust Score:10
Update:1 week ago
Show doc for...
Tokens:
Raw
### Include If expressions in Start expressions

Source: https://support.google.com/appsheet/answer/11568425

Use <<If>> inside a <<Start>> block to conditionally display a field. The example shows quantity only when it is greater than 1.

```appsheet
Order Detail Records

<<Start: [Related OrderDetails]>>

Product: <<[Product]>>
<<If: ([Qty] > 1)>>Qty: <<[Qty]>><<EndIf>>
Amount: <<[Amount]>>
<<End>>

```

--------------------------------

### TOP

Source: https://support.google.com/appsheet/table/10104782

Returns the initial list of items.

```APIDOC
## TOP

### Description
Initial list of items.

### Signature
`TOP( _list_, _how-many_)`

### Parameters
- **list** - The list to take from.
- **how-many** - The number of items to take.

### Returns
The first N items of the list.
```

--------------------------------

### Include Start expressions in If expressions

Source: https://support.google.com/appsheet/answer/11568425

Use <<If>> to conditionally include a <<Start>> block that iterates over related records. The example checks if there are any related order details.

```appsheet
<<If: COUNT([Related OrderDetails])>0>>

Order Detail Records

<<Start: [Related OrderDetails]>>

Product: <<[Product]>>
Qty: <<[Qty]>>
Amount: <<[Amount]>>

<<End>>

<<EndIf>>

```

--------------------------------

### CONTEXT

Source: https://support.google.com/appsheet/table/10104782?hl=en&ref_topic=10099895

Returns information about the running app.

```APIDOC
## CONTEXT

### Description
Returns information about the running app.

### Syntax
`CONTEXT( _option_)`

### Returns
Information about the running app.
```

--------------------------------

### CONTEXT(option)

Source: https://support.google.com/appsheet/answer/10107885?hl=en&ref_topic=10104782

Returns contextual information about the running app based on the keyword option specified. The option must be one of the valid keywords listed in the documentation.

```APIDOC
## CONTEXT(option)

### Description
Returns contextual information about the running app based on the keyword option specified. The option must be one of the valid keywords listed in the documentation.

### Syntax
`CONTEXT(_option_)`

### Parameters
- **option** (string) - Required - Must be set to one of the valid keywords: `AppName`, `Device`, `Host`, `OwnerEmail`, `Table`, `View`, `ViewType`.

### Return Value
The return value depends on the keyword provided. If an invalid option is provided, a null value is returned.

### Keyword Options
- **AppName** - Returns the unique name of the app, including account ID, as used in app URLs. For example, `MyApp-123456`.
- **Device** - Returns the universally unique identifier (UUID) of the device, intended to identify the mobile device running the native app. Used from a browser, the UUID is tied to the browser's local storage, not the device itself, and may change if the browser's cache is cleared. In the emulator of the app editor, the text `expression-assistant-device-id` is returned.
- **Host** - Returns one of the following values:
  - `Server` when the expression is evaluated remotely on the server. Remote evaluation would include things like automation, security filters, or virtual columns computed during sync.
  - `Browser` when the expression is evaluated locally within the app running in a web browser or the app preview in the editor.
  - `Device` when the expression is evaluated locally within the Android or iOS AppSheet apps or branded apps.
- **OwnerEmail** - Returns the email of the app owner.
- **Table** - Returns the name of the table used by the current view.
- **View** - Returns the name of the view currently displayed to the user, or blank if no view is currently displayed (for example, in an automation).
- **ViewType** - Returns the type of the view currently displayed to the user, or blank if no view is currently displayed (for example, in an automation).

### Sample Usage
- `IN(CONTEXT("View"), LIST("Orders_Detail", "Orders_Form"))` returns a `Yes/No` value that answers the question, is the currently-displayed view named `Orders_Detail` or `Orders_Form`? This might be used in a Show_If column constraint to display a column only in specific views but hide it elsewhere.
- `SWITCH(CONTEXT("Host"), "Device", "Tap below to:", "Browser", "Click below to:", "")` returns different directions depending on whether the app is running from the native app on a device or from a browser. This might be used to generate a display name for a gallery view of launchers.
- `CONTEXT("VIEWTYPE")="Detail"` returns `Yes` if the current view is of type Detail.

### Notes
- The `View` and `ViewType` options are not supported for Chat apps or embedded app view emails (dynamic emails).
- May not work as expected if used in a virtual column's App formula expression. Except when editing a row (for example, in a form or with an action) and other limited circumstances, virtual column values are computed by the AppSheet server during a sync.
- In each case, the server is unaware of the view currently visible to the user, so cannot provide the view name or type.

### See Also
- `HERE()`
- `NOW()`
- `USEREMAIL()`
- `USERLOCALE()`
- `USERNAME()`
- `USERROLE()`
```

--------------------------------

### Start expression with reverse reference: <<Start:[Related Order Details]>>

Source: https://support.google.com/appsheet/answer/11541779?hl=en&ref_topic=11445504

A common Start expression is the name of a reverse reference virtual column. The column name must be enclosed in square brackets. This example uses the reverse reference virtual column 'Related Order Details' from the Orders table.

```appsheet
<<Start:[Related Order Details]>>
```

--------------------------------

### ANY and TOP equivalent for preferred value

Source: https://support.google.com/appsheet/answer/10107336

Equivalent alternative to the preferred-value example, using ANY and TOP to get the first non-blank phone number.

```appsheet
ANY(
  TOP(
    (
      LIST([Mobile Phone], [Office Phone], [Home Phone])
      - LIST("")
    ),
    1
  )
)
```

--------------------------------

### LIST

Source: https://support.google.com/appsheet/table/10104782

Creates a new list.

```APIDOC
## LIST

### Description
New list.

### Signature
`LIST([ _value_...])`

### Parameters
- **value** (optional) - One or more values to include in the list.

### Returns
A list containing the given values.
```

--------------------------------

### TOP()

Source: https://support.google.com/appsheet/answer/10107704?hl=en&ref_topic=10104782

Returns a list containing the specified number of items at the beginning of a list. If the count exceeds the list length, the full list is returned; if the count is less than 1, an empty list is returned.

```APIDOC
## TOP()

### Description
Returns a list that contains the specified number of items at the beginning of a list. If the number of list items to be returned is greater than the length of the list, returns the full list. If the number of list items to be returned is less than 1, returns an empty list.

### Syntax
`TOP(_list_, _how-many_)`

### Parameters
- **list** (List of any type) - Required - The list from which to extract items.
- **how-many** (Number) - Required - The number of item values from `list` to be returned.

### Sample Usage
`TOP(LIST("Red", "Yellow", "Green"), 2)` returns a list of 2 items: `Red`, `Yellow`

`TOP({"Red", "Yellow", "Green"}, 4)` returns a list of 3 items: `Red`, `Yellow`, `Green`

### Examples

#### Days of the month
A list of days of a month, accounting for the difference in months:
```
TOP(
  {
    1, 2, 3, 4, 5, 6, 7, 8, 9,
    10, 11, 12, 13, 14, 15, 16, 17, 18, 19,
    20, 21, 22, 23, 24, 25, 26, 27, 28, 29,
    30, 31
  },
  EOMONTH([Date], 0)
)
```

1. `{1, 2, 3, ..., 31}` constructs a list of possible month day numbers.
2. `EOMONTH([Date], 0)` identifies the number of the last day of the month specified by the `Date` (or `DateTime`) value in the `Date` column.
3. `TOP(..., ...)` returns a list of only the day numbers from 1 to the last day of the given month.

See also: `EOMONTH()`

#### Lowest values
The 5 earliest employee hire dates:
```
TOP(SORT(Employees[Hire Date]), 5)
```

1. `Employees[Hire Date]` retrieves the list of all values from the `Hire Date` column of the `Employees` table.
2. `SORT(...)` orders the list of dates chronologically in ascending/low-to-high order (the default sort order), putting the earliest dates at the beginning.
3. `TOP(..., 5)` returns the first 5 values from the sorted list, the 5 earliest hire dates.

See also: `SORT()`

#### Rows with highest values
The rows of the 3 students with the highest GPAs in Mr Sandwich's class:
```
TOP(
  ORDERBY(
    FILTER("Students",
      AND(
        ISNOTBLANK([Teacher]),
        ([Teacher] = "Mr Sandwich")
      )
    ),
    [GPA], TRUE
  ),
  3
)
```

1. `FILTER("Students", ...)` returns a list of key values from the `Students` table that match a condition.
2. `AND(..., ...)` limits the filter to only those rows that match all of the given sub-conditions.
3. `ISNOTBLANK([Teacher])` requires the `Teacher` column value not be blank.
4. `[Teacher] = "Mr Sandwich"` requires the `Teacher` column value be exactly `Mr Sandwich`.
5. `ORDERBY(..., [GPA], TRUE)` orders the filtered keys by the values of their corresponding `GPA` column value in descending/high-to-low order (`TRUE`), putting high GPAs first.
6. `TOP(..., 3)` returns the first 3 items in the ordered list, the keys of the rows having the 3 highest GPAs.

See also: `AND()`, `FILTER(),` `ISNOTBLANK()`, `ORDERBY()`

### See also
- `ANY()`
- `INDEX()`
```

--------------------------------

### Example JSON template file

Source: https://support.google.com/appsheet/answer/11575775?hl=en&ref_topic=11445504

An example JSON template file for AppSheet. It includes template expressions like <<Start: Select(MyTable[MyKey], TRUE)>> and <<End>> to iterate over rows, and placeholders like <<_UPDATEMODE>>, <<_APPNAME>>, <<[LastName]>>, and <<[Qty]*[Price]>>. Save this content in a file with a .json extension.

```json
{
  "MyTable": [
  <<Start: Select(MyTable[MyKey], TRUE)>>
      {
         "UpdateMode": "<<_UPDATEMODE>>",
         "Application": "<<_APPNAME>>,
         "TableName": "<<_TABLENAME>>",
         "UserName": "<<_USERNAME>>",
         "LastName": "<<[LastName]>>",
         "FirstName": "<<[FirstName]>>",
         "Age": "<<[Age]>>",
         "Computed Total": "<<[Qty]*[Price]>>",
         "ComputedName": "<<_ComputedName>>"
      },
   <<End>>
   ]
}
```
Tokens:
Raw