Info below provided as-is, you may need to tweak and adjust things to suit your situation and please take note of the security concern outlined below.
There are 2 apps needed to make this solutions (explained below):
- Approve from Email (primary app)
- Keygap for Approve from Email (required to mask the primary app’s access key)
Currently there is no default AppSheet solution available to generate emails where an individual can execute AppSheet actions directly from the email itself. Typically they must click on a link to open the app and then click on an action within the app. In this example, a simple app was created that allows users to submit new requests into an app and email these requests to an approver. This solution solves the problem of needing to open the app to approve or reject a request allowing users to execute the Approve or Reject action directly from the email body.
Why are there 2 apps?
There is a limitation with AppSheet in that in order to receive API calls you need to use a static app access key. Normally this is not a problem when accomplishing machine to machine calls, but in this case the access key needs to be embedded in the email’s html code. This exposes a security vulnerability and creates an opportunity for someone who receives the email to copy the app’s key and send calls to update the app through the and bypass any of the app’s security filters that may be in place.
The second AppSheet app (Keygap for Approve from Email) acts as a security gap and it is this app’s access key that is sent in the email body. This is done so you only expose an app that does only one thing… send calls for the desired email actions you are wanting to create. This creates additional security because even if a user finds the keygap access key by reading the html code of an email, they would only be able to act on requests that are in the email action queue and would need to know the primary key of the records to execute the action.
There are 2 tables in this example, requests and keygap.
The requests table is where all new requests are logged and tracked to approval or rejection. This follows a typical approval workflow setup using appsheet. This only appears in the primary app.
The keygap table is used by both apps. where all pending requests are queued for the keygap app to act on when the keygap app receives a call from an email. This also has the benefit of shrinking the size of a potential attack surface to only specific pending records in this table. Records are then deleted by the keygap app once the action is executed.
Potential Security Concerns
As highlighted above the purpose of the keygap app and separate keygap table is to shrink the attack surface to only requests that are currently pending. However this does not remove all risk. If the email itself is forwarded to other individuals they will have the ability to approve/reject the request even if they cannot approve the request in the app. If the risk is a high enough concern for an organization, one could add a pin number system to the app and capture a pin number in the email’s post form as an additional verification that the approver is the person clicking the approve or reject button.
How the App Works
- The main app sends a formatted email via Notify approver workflow to an email address. The email body contains html form tags that generate the approve or reject actions with the reference to the primary key of the record to be updated. This makes use of the Invoking the AppSheet API service.
- The main app also adds the request to the keygap table with a pending status.
- The approver receives an email that summarizes the request and has 2 options to approve or reject.
- When Approve/rejected button is clicked an post call is sent to the keygap app to update the respective record in the keygap table with the approvers intent.
- When the record is updated with the approvers intent in the keygap table, a workflow called Update Record is triggered that sends a post call to the Main app to trigger either Approve or Disapprove for specific record
- This update in the main app triggers a workflow called Approved or Rejected that sends an email to the approver to notify them that the request was successfully approved or rejected.