edit note: thanks to @imthenachoman's insight, there's a huge vulnerability with this setup where you risk exposing virtually every single data point from the sheet, should the user actually get to know the sheet ID or even the file to look for within their drives.
the ideal course for the makers would be to create the setup and not share the actual apps script codebase or any direct indicators of the raw sheet with anyone.
here's how to allow users to only view data related to their email ID - a good use-case would be that for teachers wanting students to view only their status updates / reports & not anyone else's.
disclaimer: this setup involves making your "master" spreadsheet publicly available (or domain-wide, if on G Suite); albeit, ensuring that the data is not viewable by non-owners/editors
coincidentally, i stumbled upon scenarios that seemed quite similar (at least to me) & were reported just a day apart from one another (also, on 2 totally different platforms) but more importantly, happened while i was on a break from work (still am, as of when i'm writing this post 😛) which allowed me to work on something that is a real-world problem and provide a solution that real-humans would use (hopefully) -
- 'mike feldman' on twitter wanted kids to be able to track their "standards" & not see everyone else's
- 'diana t' over at stack overflow wanted to create a report card for students - again, this was whilst not wanting students to access other's data
the similarities here were that the data was being stored in a spreadsheet and the limitations were that of sharing access with everyone involved (instructors/teachers & students).
let me start by saying that this is going to be fairly convoluted as i couldn't think of a more easier way to achieve what ought to be done.
while i'd originally proposed 2 plausible ways to achieve what was required (1st was to build an add-on & the 2nd was a web-app) - i'd later understood why a web-app would be preferred over an add-on.
the structure to implementing a web-app are as follows -
- preparing the sheet to display exactly what ought to be displayed to users over a web-app (similar to the header image from this post)
- if this requires us to create a new sheet and importing only certain ranges (in a specific order), we should rather get that done on a spreadsheet itself as opposed to handling it programatically (via apps script)
- writing code takes more effort and maintenance as opposed to building simple
- very carefully setting up public sharing option (on the sheet) & also deploying the web-app to be accessed in a very specific manner (full details have been listed under setup)
- what this does is disable non-owners/editors to either view or download the overall master data
you can make a copy of my script here or use the code from below -
the backend -
the frontend -
for sheet and script owners (makers)
- prepare the spreadsheet with all the necessary data
- create a dummy sheet (within the same spreadsheet) & hide the "master" sheet
- by doing this, folks would not be able to unhide the sheet (post implementing steps 3 & 4) & you can verify the same on my dummy sheet here
- setup link sharing where folks only get to have "view" access - & based on your preference, either select the sharing to be accessible by folks outside your domain or within
- while you're at it, check the options to disallow folks to download the sheet or change access (these would be unchecked by default)
- next, head over to the apps script and set it up by replacing all the required variables (refer codebase for the actual code)
- finally, go to Publish > Deploy as web app... and choose:
- Project version: New
- Execute the app as: User accessing the web app
- Who has access to the app: Anyone (or Anyone within your domain - based on your preference)
for end-users accessing the web-app
given that the web-app is configured to run as the user accessing it, the link would ask for certain permissions whilst throwing "dangerous" warnings - do not fear, its all part of the process and the google apps script web app setup experience 😌
here's what you'd be expected to see on first use (only) & ought to do in order to access the web app -
- press the review permissions button > login using your desired gmail / g suite account
- click on advanced from the next pop-up and then on "go to <whatever would be the app name> (unsafe)"
- finally, click on allow button for the web app to start showing data
read this if you're still unsure about allowing access to the web app via this setup.