The UserRole function

The UserRole function is used in a Competence Wrap to temporarily assign a dynamic Role to a User from inside the Wrap, with an expiration timestamp.

Most Roles are static, i.e. permanent, and assigned to Users on the Wrapsite’s Administration dashboard. The UserRole allows you to temporarily assign a User a Role that is dynamic, from inside a Wrap. This allows you to create so-called Competence Wraps that keep track of your Users, their skills, and the related Roles.

If you are new to the concepts of Users and Roles, you may first want to learn a bit more about the ExcelWraps permissions model.

The Competence Wrap

Screenshot of a Competence Wrap

The Competence Wrap above can be used at the end of a certification or course. Users have new skills and will be rewarded with a new Role.

Each UserRole function in the Wrap can assign a single Role to a single User.

  • Column A contains UserSelector widgets. When the Wrap is open in a web browser, one User can be chosen for each row in the table.
  • Column B contains the dynamic Role to temporarily assign to the respective User.
  • Column C contains the expiration timestamp. The User will automatically lose the dynamic Role at the designated instant. If you don’t want the Role to be temporary, use a date very far in the future.
  • Column D contains the UserRole functions.
  • Cell D9 contains a WrapSignOff signature field, which must be for a Freeze signature.

When the Competence Wrap is signed, the User that is selected in the dropdown for column A will be temporarily assigned the role in column B until the expiration date in column C.

Static and Dynamic Roles

If a User has been assigned a dynamic Role, it has an expiration date instead of the tick in the box that the permanent Roles have. The User in the screenshot below has the dynamic Editor Role until the expiry date, and the static Author, Contributor, and C-Weld Roles until they are revoked.

Screenshot of User definition with a temporary role

You are not expected to tick the checkbox for a dynamic Role. If you do, you disable the automatic expiration and the Role becomes permanent – the User retains the Role at least until you untick the box. If you tick the box and then untick it, the User still retains the same dynamic Role until it expires automatically.

To edit or completely revoke a dynamic Role before it expires, you need to locate the instance of the Competence Wrap that you used to assign the Role, and Unsign it. This immediately revokes the dynamic Roles assigned by this Wrap. You can then make changes to the Wrap and sign it again, e.g. to change the Roles or the expiration dates. We recommend that you do not delete an instance of a Competence Wrap, at least not without unsigning the instance first, since it is the source for the dynamic Role.

If you have more than one instance of a Competence Wrap and you have assigned the same Role to the same User from more than one of the instances, you must unsign all the instances to revoke or change the dynamic role.

Only the latest expiration date is saved

Two or more Competence Wraps can assign the same Competence to the same User but with different expiration dates. In this case, only the latest expiration date is saved. Example: In January, a Competence Wrap assigns an Editor role to a user until June 30. In February, a second Competence Wrap assigns the same Editor role to the same user, but with a May 31 expiration date. The expiration date is not changed by the second assignment and the role is still retained up to and including June 30.

If you specifically want to shorten the duration of a temporary Role, you must unsign all the affected Competence Wraps so that the dynamic Role is unassigned, then assign it to the same user again but with the new expiration date.

Function reference

Screenshot of a Competence Wrap


In the example above, up to three Users can be selected using the UserSelector widgets in column A. They will all be temporarily assigned a dynamic Client role until the expiration date.

This is what this UserRole function call will look like:


Format and parameters

=UserRole(user_name, role_name, expiration_timestamp)


Specify the name of the User that is to be temporarily assigned a dynamic Role. You may refer to a cell containing a UserSelector widget, like in the example above. You can also specify the name as a text string, e.g. “JaneDoe”.


Specify the Role you want to assign, either as a string or as a cell reference.


Specify the date and time when the user automatically loses the dynamic Role, in ISO 8601 format. You can calculate the expiration date dynamically, e.g. as thirty days in the future, using this formula in B8:


You can then format this as an ISO 8601 date like this:


The string “-05:00” at the end sets your local time zone, e.g. -5 is Eastern Standard Time.

For Western European Time, use +00 or the letter “Z”.

Special considerations

The cell containing the UserRole function must be named.

The Wrap containing the UserRole function must be signed with a Freeze signature for the dynamic roles in the Wrap to be assigned.

If another Competence Wrap has already assigned the same temporary Role to the same User, but with a longer duration, the duration for the temporary Role is not shortened by a new assignment of the same role with an earlier expiration date. Read more above.