In a GAS published as webapp bound to a spreadsheet in a public (free) Gmail account, I'm compiling information from many unrelated users into that spreadsheet, where any Gmail user account can access the webapp.
But when script is run as the user, the user can access the app (after user grants permissions to the app), but it stops at point where script accesses the spreadsheet. The user is told to request access. I have to "share" the spreadsheet to the user for the app to function when run as that user, but that is problematic--too many potential users.
If another version of the script is run instead under my account, then user has access to the webapp, but the script cannot access the user's Gmail address. Big problem because the webapp must have the user's Gmail address for security/application access controls.
(The webapp is in development and Google "approval" has not yet been sought.)
How do I have a webapp (a) that modifies content in a central spreadsheet, (b) that any Gmail user can access, (c) where the script has access to Session.getActiveUser().getEmail(), and (d) the user does not need to have edit permissions for that central spreadsheet? The only user-account information that the script needs is the user Gmail address--again for application access control and security. (Since Google provides me a link that gives anyone full access to the spreadsheet (if they also have its URL), can't I give the bound script access to the spreadsheet when run under any Gmail account, where the integrity of the spreadsheet content is managed through the app's functions and internal access controls?)
If there is no solution except to run the webapp under the user's Gmail account and give that user edit rights to the spreadsheet, would that create significant risk (where I don't intentionally/overtly disclose the spreadsheet's URL) that a user could access and alter the spreadsheet beyond the insert/edit functions performed by the webapp? If that risk is low, is there a function I could use in the webapp to automatically grant spreadsheet edit rights to a Gmail account when its Gmail address is pre-registered in the user list for the webapp?
Thank you!
CodePudding user response：
Let me start with c): there is no way to make that Session.getActiveUser().getEmail() works for free Google accounts (i.e. gmail.com accounts) other than for the container / script owner.
Regarding a) b) and d) you might use the Google Sheets API making the calls to it by using UrlFetch Service and a service account instead of the Spreadsheet Service or the Advanced Sheets Service.
Regarding allowing anyone to edit a central spreadsheet, once they open it either by using the Google Sheets apps or by means a web app, they will be able to find the spreadsheet in Google Drive > Shared with me among other places... IMHO the risk in terms of how likely is to have issues is big but in terms of the the impact depends on several factors, i.e. some changes might be reverted by using the version history but spreadsheet might become corrupted. If you have not used Google Sheets version history intensively the best is to get deeply familiar with it before relying on it as a backup / disaster recovery main tool .
From https://developers.google.com/apps-script/reference/base/session#getactiveuser
getActiveUser()
Gets information about the current user. If security policies do not allow access to the user's identity, User.getEmail() returns a blank string. The circumstances in which the email address is available vary: for example, the user's email address is not available in any context that allows a script to run without that user's authorization, like a simple onOpen(e) or onEdit(e) trigger, a custom function in Google Sheets, or a web app deployed to "execute as me" (that is, authorized by the developer instead of the user). However, these restrictions generally do not apply if the developer runs the script themselves or belongs to the same Google Workspace domain as the user.
Related