Encrypt / Decrypt with Private Tables Question

Frank1000 · 02-20-2021 04:36 AM

Hi,
while using private tables for the app, I want to encrypt data automatically when entering data to the private table, and decrypt it again, based on user permission, when reading back from the private table.

So far when entering data to the private table, a Google App Script takes care of the encryption OnChange.

Question, how would my Google App Script recieve recognizeable info from AppSheet, when and what is about to be read, so it would hand over decrypted data to the AppSheet app (in case the user is permitted).

Regards,
Frank

MultiTech

Hmmm… you’d have to create some way for the app to signal that X user is using the app - then the system could check for permissions and such and decrypt the info.

So you’d change some value in a table, then have another decrypt script run to check things, unscramble the data, then the app would just read it like normal data.

Frank1000

thx for reply.
Yes I also was thinking that something should identify the read request, but question is how to fiddle it together so the data in the private tables stay encrypted.

Frank1000

… is there a way or a hack I can trigger a Google App Script “OnRead” ?
Have not found something yet.

WillowMobileSys

Ummm, I am a little confused.

It has been my understanding, that Google already encrypt’s data in two ways - data in transit and data at rest on its servers. (See article below).

Now, I have read that some don’t want to depend on Google security so they will encrypt the data again on their own. Is that what you are attempting to do here?

Frank1000

… thx for reply. The aim is to encrypt content within the GSheet cells when saved and de-crypt when read back. Independent of the transfer encryption.

WillowMobileSys

As I pointed out, data in the Google Sheets sitting (at rest) on the Google servers is already encrypted with 128-bit encryption.

Once the data is requested, such as for viewing in your browser or by AppSheet for use in the app, Google will decrypt the data from its servers and then re-encrypt it at 256-bit encryption for transmission over the Internet to your browser or to AppSheet servers.

Frank1000

yes I see your point. But what my goal is, when someone logs into the GDrive and opens a table, they see encrypted content in the cells. Content that does not make sense for that person when reading it. This way I mean.
This can be achived by Google App Scripts with a “OnChange” triggering the script. So this part is no problem.
The open question for me right now is, how to achive the next part of my goal, to decrypt such previously encrypted content (encrypted for the reason that logged in people to the GDrive not be able to read the content of the table), based on visibility options I created for each content.
The problem rt now is, that there is no “OnRead” trigger with Goggle App Script. Meaning the Google App Script is not able to take the date, decrypt it based on the visibility-options settings, and handing such decrypted data to the AppSheet app.
Question is how I’d be able to solve this step of the goal.

WillowMobileSys

Understood. Google does have the ability to specify a whitelist of users who have access to each file. But I realize this may not be enough protection.

What you want is in fact what I eluded to above - a 2nd layer of encryption at the Google servers. I don’t know enough about it to help with applying the function. The article above does talk about applying encryption in this manner. And there are other articles online that should help.

Frank1000

ok i c.

Question, is there a way to run decrypt code on AppSheet … I mean like a behavior ?

This way I can encrypt on GDrive level and decrypt data on AppSheet level, based on a permission-code that is available to the permitted user.

WillowMobileSys

Not within AppSheet’s native functionality. You may be able to use AppSheet’s webhook capability in some way to call out to a service to perform the decryption.

Frank1000

ok good, and how is AppSheet then able to display the encrypted data instead of the original data in the private table ?

WillowMobileSys

I don’t understand this question.

Frank1000

what’s on my mind is

data is encryptet by GAS (Google Apps Script) for some columns in GSheet private table on save … so when someone opens that table in GDrive, they can’t make sense of what is stored in such columns
when AppSheet reads the data, it does not just display it, but first decrypts it
since AppSheet itself does not run custom code, now this decrypt process needs to be performed by another connected app over webhook, which decrypts the data and then sends the data back to AppSheet so the user will see the decrypted data within the AppSheet app
It’s crucial, that the decrypted data at no time is present in the private table.
The user can edit the data, which updates the private table, again with encrypting the update with GAS within the private table.

If my assumptions of the process is not how it goes, please let me know the correct process flow.

Steve

Not gonna work.

Frank1000

k i c, is there any other idea how it can work to leave the data in the private tables visibly encrypted ?

Steve

Nope. AppSheet is not the right solution for you.

Koichi_Tsuji

I m not perfectly sure if it fits your needs, but better to check PowerApps or Amazon Honey Code? to see they fill your needs?

Ami

So I have probably the same problem. My idea was to setup an action on the appsheet side that "encrypts" the data that is saved in the Google sheet. If it's a numeric value it puts it in a function that scramble the number, similarly for text values. However, in order to run a search on such data would be difficult at best and may even crash the app in search for some data as I would have to use the reverse method to search