We do a lot of difficult things for clients - from complex integrations to crazy mapping and data visualizations. But a recent project required field-level encryption in the new Google Tables product as well as Airtable.
User fills out a form and reveals confidential information in one or more form fields targeted for encryption.
The user also enters a “passkey” - think pin-code like “2998” or a password like “good1ucktryingt0geu$$this”.
The form is submitted and an action script immediately transforms the entry in many ways.
The target encrypted fields are combined into a collection of strings and encrypted using a 256-bit AES algorithm resulting in a single cypher key.
All targeted encryption fields are set to null values.
The cypher created as a result of step #4 is then transformed into a GUID and placed into a designated field in the record
With this process, which occurs in about 800ms, the record entered by the user is now obscured; the sensitive data is stored in the cypher key which looks like any GUID (i.e.,
Now imagine a Script Block (or a custom app, or an API process) that can access and display the sensitive information. It is able to prompt for the pass key -
Successful entry of the passkey displays the designated sensitive information.
Alternatively, the passkey is sustained in a session allowing the user to browse sensitive data as demonstrated in this video.
Does anyone care about scenarios and encryption like this?
sidebar - it’s important to note that the record creation action stores a version of the unencrypted data, thus making it possible for anyone with read access to see historical versions. As such, the true layout of this process in production requires an additional abstraction from the consumable data records. I didn’t want to complexify the scenario, but it’s also an important design choice to make it impossible to expose sensitive data that arrives via Airtable or Google Tables forms.