If your sheet has an approval flow, you can apply the formulas below to return certain values related to the approval process.
Currently, we support two types of approval formulas:
| Formula | Description |
|---|---|
| APPROVAL.COUNT() | Returns the number of approval steps |
| APPROVAL.STATUS() | Returns approval status.
N: New P: Processing REJ: Rejected F: Finish |
| APPROVAL.SUBMITDATE([true]) | Returns the date and time an approval process is started. Supported in Date Fields. The [true] argument is optional, the time value will be displayed using UTC+0 timezone when applied. Otherwise, the time value will display using the account timezone. (Front-end recalculation will be based on browser timezone; Back-end recalculation will be based on company timezone) |
| APPROVAL.SUBMITTER() | Returns the email address of the user who starts the approval process. Supported in Select User Fields. |
| APPROVAL.SUBMITTERNAME() | Returns the name of the user who starts the approval process. |
| APPROVAL.FINISHDATE([true]) | Returns the date and time an approval process ends. An approval ends when all of the approvers approve or when one of them rejects. Supported in Date Fields. The [true] argument is optional, the time value will be displayed using UTC+0 timezone when applied. Otherwise, the time value will be displayed using the account timezone. (Front-end recalculation will be based on browser timezone; Back-end recalculation will be based on company timezone) |
| Formula | Description |
|---|---|
| APPROVAL.CURRENTSTEPINDEX | Returns the index value representing the next step in the approval process.
Index 0 means the approval process has not yet been started. Index 1 means the approval process has been started but no approver has approved yet. Whenever an approver approves, 1 will be added to the index. When the approval process ends (all approve/ 1 rejects/ canceled), the index returns to 0. |
| APPROVAL.STEP([stepIndex]) | Uses index value to point to a certain step in the approval process. The [stepIndex] argument is optional, with its default argument being APPROVAL.CURRENTSTEPINDEX.
APPROVAL.STEP(-1) : Last step APPROVAL.STEP() : Next step. Equivalent to APPROVAL.STEP(APPROVAL.CURRENTSTEPINDEX)) APPROVAL.STEP(0) : Incorrect usage APPROVAL.STEP(1) : First step APPROVAL.STEP(2) : Second step |
| APPROVAL.STEP([stepIndex]).NAME() | Returns the name of this step. |
| APPROVAL.STEP([stepIndex]).STATUS() | Returns the status of this step.
N: New F: Finish |
| APPROVAL.STEP([stepIndex]).ISMULTI() | Returns True if this step has multiple approvers. |
| APPROVAL.STEP([stepIndex]).THRESHOLD() | Returns the threshold number of this step, or 1 if this step only has a single approver or no threshold was set. |
| APPROVAL.STEP([stepIndex]).USERS() | Returns all approvers.
E.g., Jessica Jones|Nick Fury|Steve Rogers Supported in Select Multiple Users Fields. |
| APPROVAL.STEP([stepIndex]).ACTIONDATE([email],[true]) |
Returns the approve or reject time of a specific approval step. The [true] argument is optional, the time value will be displayed using UTC+0 timezone when applied. Otherwise, the time value will be displayed using the account timezone. (Front-end recalculation will be based on browser timezone; Back-end recalculation will be based on company timezone) This formula needs to be applied to a date field. Similar to NOWTZ() and TODAYTZ() formula, the system will use the browsers time zone when recalculating on the frontend (entering the record’s editing mode) and use the time zone set in company settings when recalculating on the backend (Recalculations executed in the design mode, from related sheets, or by action buttons) [email] is an optional argument. It can be an email string or a select user field. This argument allows you to get a specific user’s approval or a reject time in an approval step with multiple users. One approver in an approval step (No [email] argument needed) Returns the approver’s approval or reject date and time. Multiple approvers in an approval step: If the [email] argument is not applied, the system will return the date and time when one approver has rejected this approval or when the step is completely approved (the number of approved approvers meets the approval threshold settings). If the [email] argument is applied, the system will return the respective user’s approve or reject time. (Note: The [email] argument in the formula is only applicable for approval steps created after 2021/07/13) |
| APPROVAL.STEP([stepIndex]).UNSIGNEDUSERS() | Returns the approvers who haven't approved in this step. Supported in Select Multiple Users Fields. |
| APPROVAL.STEP([stepIndex]).SIGNEDUSERS() | Returns the approvers who have already approved this step.
E.g., Jessica Jones|Nick Fury|Steve Rogers Supported in Select Multiple Users Fields. |
| APPROVAL.STEP([stepIndex]).SIGNEDCOUNT() | Returns the number of the approvers who have already approved this step. |
| APPROVAL.STEP([stepIndex]).SIG([email]) | Returns the digital signature of the approver in this step.
E.g., base64 image URL. Supported in Upload Image Fields. The [email] argument can be an email address or a Select User Field. Single approver: The [email] argument is optional. Returns the digital signature of the approver in this step.
Multiple approvers: The [email] argument is required. Returns the digital signature of a certain approver. |
| APPROVAL.STEP([stepIndex]).SIGIMG([email], [width], [height]) | Returns the digital signature of the approver in this step in a predetermined image size. The [width] and [height] arguments are optional , with default values being 300px x 150px. This formula can be applied to field description with BBCode [formula]. |
| APPROVAL.STEP([stepIndex]).COMMENT([email]) | Returns comments of the approver(s), or null if there is no comment.
Single approver: The [email] argument is optional. Returns the comment of the approver. Multiple approvers: The [email] argument is required. Returns the comment of a certain approver. |
| APPROVAL.STEP([stepIndex]).COMMENTDATE([email], [true]) | Returns the date and time left comments of the approver(s). The [true] argument is optional, the time value will be displayed using UTC+0 timezone when applied. Otherwise, the time value will be displayed using the account timezone. (Front-end recalculation will be based on browser timezone; Back-end recalculation will be based on company timezone)
Single approver: The [email] argument is optional. Returns the date and time the comment was left by the approver. Multiple approvers: The [email] argument is required. Returns the date and time left the comment was left by a certain approver. |