Spreadsheet sheet protection (via webapp?)

[Stephen Schlager]

Hello,

I have a spreadsheet with a number of sheets. Each sheet is controlled by a different user and the users should not be able to see each other's sheets.

I understand that Protection for sheets and ranges only protects editing not viewing and anyone who has been given access to a spreadsheet can view all the data in that spreadsheet.

I began trying to get around this by developing a webapp where the user's can see and edit their data on the webapp, however I think in order for this to work I must share access to the sheet for the user and then they have access all over again. By looking in their "Shared with me" section in Drive they can find the sheet and go into it and see all the sheets, and I've accomplished nothing.

Is there an established method for this besides separating all the sheets out into different spreadsheets?
Maybe through a webapp or another mechanism that hides the original spreadsheet?

One idea I had was to run the webapp as the creator of the document rather than as the Google user making the request (The choice you are given when you deploy)...but I'd still need to know which user the request originated from to get the right data...Hmmm.

Any other ideas?

Thank you,

Steve

[Martin Molloy]

You could just set up a set of child spreadsheets. Then create a master sheet and use the importrange function to import data from your child spreadsheets into the master. Then you don't need to worry about protecting sheets just share each one with the relevant user

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/e22c05b7-7dcc-482c-b7e3-a87dcef6f0a8n%40googlegroups.com.

[Laurie Nason]

I think Martin's idea is one solution -you can also have the master sheet generate the individual ones from a template and then in the script personalise and share the sheet with the appropriate person.

Then you retain access to the original, and use the importrange function to bring in each of the child sheets however you want and do your work there.

Laurie

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CAO8s2Pi7MHXFSxdgP8-0wb3v0zKm8gY73vZnnLZD2%3DSHx2D2hQ%40mail.gmail.com.

[Clark Lind]

There isn't any way to hide a tab from some and not all unfortunately. It is either visible to all, or none. Visibility is set at the file level, not the tab level, so the suggested solutions by others is the way to go. 

[Bruce Mcpherson]

Here's a technique to encrypt all or parts of various sheets and allow certain users to unlock what's relevant to them by using their own personalized private key. Might be overkill, but maybe not.

https://ramblings.mcpher.com/apps-script/add-ons/merging-encrypting/

--

You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/31630297-bbe2-4fce-8a70-2c2c308d254fn%40googlegroups.com.

[Stephen Schlager]

Thanks for the ideas. Yes, I've thought about these solutions (with the exception of Bruce's). They are definitely something I'm considering.

However splitting the sheets out into separate spreadsheets would make some other scripts I have running more complicated and also take more processing time.

Additionally users change sheets every so often and the access could be automated on a nightly script, but it's a bit of a user annoyance.

I'd like the users to just always go to the same place and the data is served to them from wherever, hence my webapp idea.

I've been looking at the below post and will let you guys know if it leads anywhere fruitful and share my approach once I make a decision.

https://stackoverflow.com/questions/59972996/get-user-info-when-someone-runs-google-apps-script-web-app-as-me

Bruce - your idea is interesting and I'll keep it in my back pocket, but isn't the immediate favorite. Thanks for sharing it...

Steve

[Витя Коледа]

вт, 14 июн. 2022 г., 18:16 Bruce Mcpherson <br...@mcpher.com>:

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CANijH0UheAhEQoO07%2B2zCDcR1EnmZ1YD%2BToChzOk_vcb7etLGQ%40mail.gmail.com.