Conditionally allow column edits (Editable_If)

The Editable_If column constraint may be used to limit the user's ability to edit a column value for a row.

Editable_If is commonly used to only allow column value changes:

  • By administrators or specific users, but not others.
  • When another column has a specific value.
  • When adding a new row, but not when editing an existing one.
  • When no value has yet been provided.

New to expressions and formulas? See also Expressions: The Essentials.

The Editable_If Expression

The Editable_If column constraint is a Yes/No expression: if the expression evaluates as TRUE, the user is allowed to edit the column value; if FALSE, the user is not allowed.

As in all column constraint expressions, the Editable_If expression may refer to the current column value using the column value expression, [_THIS]. For instance, if the current column is Employee Name, [Employee Name] and [_THIS] both refer to the same value.

Examples

("Admin" = USERROLE()) - Allow only designated app admins. See also: USERROLE()

IN(USEREMAIL(), LIST("joe@example.biz", "mary@example.biz")) - Allow only specific users. See also: LIST(), USEREMAIL()

(USEREMAIL() = [Email]) - Allow only if the Email column value matches the current user. See also: USEREMAIL()

NOT(IN([Employee ID], Employees[Employee ID])) - Allow only for a new row, but not an existing row. See also: IN(), NOT()

ISBLANK(LOOKUP([_THISROW].[Employee ID], "Employees", "Employee ID", "Name")) - Allow only if the Name has not been previously set for this Employee ID. See also: ISBLANK(), LOOKUP()

(NOW() < ([Timestamp] + "024:00:00")) - Allow only if the row is less than a day old. See also: NOW()

AND((TIMENOW() >= "08:00:00"), (TIMENOW() < "18:00:00")) - Allow only during business hours. See also: AND(), TIMENOW()

(WEEKDAY(TODAY()) = 2) - Allow only on Mondays. See also: TODAY(), WEEKDAY()

FALSE : Prohibit all user changes while allowing changes by app formulas and initial values, and actions.

Setting Editable_If

To set a column's Editable_If column constraint, go to the Data > Columns tab in the app editor and click on the edit icon next to the desired column:

Edit icon in the Columns page of the Data section.

Scroll down to locate (and, if necessary, expand) the Update Behavior section, which contains the Editable? setting. Clicking the flask icon to the right will replace the ON/OFF switch with an expression box.

Click in the expression box to enter your Editable_If expression. To remove the Editable_If column constraint, click the X to the right of the expression box.

Editable field with an expression defined.

Scope of Editable_If

The Editable_If column constraint only affects the user's access to the column value, such as when the user interacts with a Form view containing the column. The constraint is also checked before allowing access to Quick Edit columns (such as, in a Detail or Table view).

Editable_If does not affect the application of app formulas and initial values, the performance of actions, or any other app behaviors.

Editable_If is consulted only if all of the following are true:

Editable_If can only be used to reduce access; it cannot be used to expand access beyond that allowed by the underlying slice or table.

Was this helpful?
How can we improve it?
Search
Clear search
Close search
Google apps
Main menu
Search Help Center
false
false