Database Design Documentation
Database Design Documentation
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, indicating the approval process has not yet started or has been canceled. P: Processing, indicating the approval process is ongoing and awaiting completion. REJ: Rejected, indicating rejection by anyone approver. F: Finish, indicating completion when all approvers agree. |
| 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 time zone when applied. Otherwise, the time value will display using the account time zone. (Front-end recalculation will be based on browser time zone; Back-end recalculation will be based on company time zone) |
| 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 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 time zone when applied. Otherwise, the time value will be displayed using the account time zone. (Front-end recalculation will be based on browser time zone; Back-end recalculation will be based on company time zone) |
| Formula | Description |
|---|---|
| APPROVAL.CURRENTSTEPINDEX | Returns the index value representing the current 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". |
The following formulas should be prefixed with APPROVAL.STEP([stepIndex]), which indicates a specific step in the approval process using an index value. This is not a standalone formula but can be used with other approval formulas. The [stepIndex] argument is optional; if not provided, APPROVAL.CURRENTSTEPINDEX will be used as the default, representing the current step. The arguments are explained as follows:
APPROVAL.STEP(-1): Last step.
APPROVAL.STEP(): Current step. Equivalent to APPROVAL.STEP(APPROVAL.CURRENTSTEPINDEX)).
APPROVAL.STEP(0): Incorrect usage.
APPROVAL.STEP(1): First step.
APPROVAL.STEP(2): Second step.
| Formula | Description |
|---|---|
| APPROVAL.STEP([stepIndex]).NAME() | Returns the name of this step. |
| APPROVAL.STEP([stepIndex]).STATUS() | Returns the status of this step.
N: New, indicating the approval process has not yet started or has been canceled. F: Finish. If this step involves a "single approver", it indicates either approval or rejection of the request. If there are "multiple approvers", it indicates that the number of approvers who have agreed equals or exceeds the designated threshold, or someone has rejected the request. |
| APPROVAL.STEP([stepIndex]).RESP([email]) |
Returns the response of this step. [email] argument is optional. It can be an Email string or a Select User Field. This argument allows you to get a specific user’s response in an approval step with multiple users. (1) One approver in an approval step: No [email] argument needed. N: Not yet, indicating that this step has not yet been approved or rejected and is still pending approval. A: Accepted, indicating that the approver has accepted the approval. A_D: Accept by Deputy, indicating that the deputy of the approver has accepted the approval. REJ: Rejected, indicating that the approver has rejected the approval. REJ_D: Reject by Deputy, indicating that the deputy of the approver has rejected the approval. (2) Multiple approvers in an approval step: Without the [email] argument: N: Not yet, indicating that the number of approvers who have accepted the approval is less than the designated number of approvers. A: Accepted, indicating that the number of approvers who have accepted the approval is greater than or equal to the designated number of approvers. REJ: Rejected, indicating that someone has rejected the approval. With the [email] argument: unsigned: The approver has not yet accepted or rejected the approval. signed: The approver has accepted the approval. signed_by_deputy: The approval has been accepted by the deputy of the approver. rejected: The approver has rejected the approval. rejected_by_deputy: The approval has been rejected by the deputy of the approver. |
| 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 Multiple Select Users Fields. (Due to the current limitation where formulas cannot be applied to Multiple Select Fields, you need to manually set the f attribute.) |
| APPROVAL.STEP([stepIndex]).ACTIONDATE([email],[true]) |
Returns the approve or reject time of a specific approval step. This formula needs to be applied to a Date Field. The [true] argument is optional, the time value will be displayed using UTC+0 time zone when applied. Otherwise, the time value will be displayed using the account time zone. (Front-end recalculation will be based on browser time zone; Back-end recalculation will be based on company time zone) [email] argument is optional. 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. (1) One approver in an approval step: No [email] argument needed. Returns the approver’s approval or reject date and time. (2) Multiple approvers in an approval step: Without the [email] argument: 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). With the [email] argument: The system will return the respective user’s approval 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. E.g., Jessica Jones|Nick Fury|Steve Rogers
Supported in Multiple Select Users Fields. (Due to the current limitation where formulas cannot be applied to Multiple Select fields, you need to manually set the f attribute.) |
| APPROVAL.STEP([stepIndex]).SIGNEDUSERS() | Returns the approvers who have already approved this step.
E.g., Jessica Jones|Nick Fury|Steve Rogers Supported in Multiple Select Users Fields. (Due to the current limitation where formulas cannot be applied to Multiple Select fields, you need to manually set the f attribute.) |
| APPROVAL.STEP([stepIndex]).SIGNEDCOUNT() | Returns the number of the approvers who have already approved this step. |
| APPROVAL.STEP([stepIndex]).SIG([email]) | Returns the signature of the approver in this step.
E.g., base64 image URL. Supported in Upload Image Fields. The [email] argument can be an Email string or a Select User Field and should be enclosed in " " in this formula. (1) One approver in an approval step: No [email] argument needed. Returns the signature of the approver in this step. (2) Multiple approvers in an approval step: The [email] argument is required. Returns the signature of a certain approver. |
| APPROVAL.STEP([stepIndex]).SIGIMG([email], [width], [height]) | Returns the 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 descriptions with BBCode [formula]. Please note that the [email] parameter of this formula should be enclosed in " ". To return a signature for a single approver in an approval process with dimensions 600px in length and 300px in width, use APPROVAL.STEP([stepIndex]).SIGIMG(null, 600, 300), where null should not be enclosed in " ". |
| APPROVAL.STEP([stepIndex]).COMMENT([email]) | Returns comments of the approver(s), or null if there is no comment.
(1) One approver in an approval step: No [email] argument needed. Returns the comment of the approver. (2) Multiple approvers in an approval step: 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 time zone when applied. Otherwise, the time value will be displayed using the account time zone. (Front-end recalculation will be based on browser time zone; Back-end recalculation will be based on company time zone) (1) One approver in an approval step: No [email] argument needed. Returns the date and time the comment was left by the approver. (2) Multiple approvers in an approval step: The [email] argument is required. Returns the date and time left the comment was left by a certain approver. |
About formula recalculation, please refer to this article.
If your sheet has already started or completed the approval process, and you subsequently add a field applying an approval formula that returns time, such as APPROVAL.SUBMITDATE([true]) or APPROVAL.STEP([stepIndex]).ACTIONDATE([email],[true]), to a sheet that already contains Date values with the "Display Using Browser’s Time Zone" setting, it may result in the displayed time being adjusted based on "your browser's time zone".
For example, if you have already approved a sheet on "August 1, 2023, at 12:00 PM", and the approval time field at that time was not set to "Display Using Browser’s Time Zone", the recorded time was according to the "company time zone". When you subsequently add a "New Approval Time" field and enable "Display Using Browser’s Time Zone", it will cause the approval formula to calculate based on the "company's time zone" first. Then, it will add the value displayed using the browser's time zone (e.g., UTC+8). Consequently, the time in the "New Approval Time" field will be further increased by 8 hours and displayed as "8:00 PM on August 1, 2023".
Hence, when you encounter time zone discrepancies in the approval time, simply uncheck the "Display Using Browser’s Time Zone" option for that field. This will ensure that the sheet calculates the time based on the "company time zone".
When you check or uncheck the "Display Using Browser’s Time Zone" option, the system will display a reminder indicating that the field's date values will be converted. Therefore, please pay attention to this setting and avoid checking or unchecking it for fields that already contain date values.
