Encrypt / Decrypt with Private Tables Question

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

1 Like

Hmmm… :thinking: 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.

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.

1 Like

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

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?

… 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.

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.

1 Like

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.

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.

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.

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.

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

I don’t understand this question.

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.

Not gonna work.

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

Nope. AppSheet is not the right solution for you.

2 Likes

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?

1 Like