0

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!

Rubén
  • 34,714
  • 9
  • 70
  • 166
DougMS
  • 45
  • 5

1 Answers1

0

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

Rubén
  • 34,714
  • 9
  • 70
  • 166
  • Thanks so much for your response. This webapp script must have access to the user's Gmail address, so the webapp needs to run under the user account. In that case, can the webapp script employ the UrlFetch service with the same (fixed) OAuth credentials for every user to connect to the Google sheet? If so, then (maybe) I see how reading and posting with the UrlFetch can replace the various SpreadsheetApp functions. Can you confirm that the same authorization for UrlFetch access to the spreadsheet will work when the webapp is run under different user accounts? Thanks again! – DougMS Feb 26 '22 at 17:29
  • Yes the same authorization will work for all user if it's set properly. I added a couple of links that someway are related. If you need further help I suggest you to post a new question focused only on the urlfetch + service account part. Don't forget to include your search efforts as is suggested in [ask]. – Rubén Feb 26 '22 at 17:45
  • Yes, thank you again. This looks like my option. I thought adding Google login button for the webapp would be a better approach for me because I only need the user Gmail address. But researching difficulties, I saw this post (https://stackoverflow.com/questions/67947721/what-is-the-authorized-javascript-origin-for-a-webapp-powered-by-google-script) which says that such client-side authentication is no longer possible for Google web apps. This seems unbelievable--can you also confirm this? – DougMS Feb 27 '22 at 04:17
  • @DougMS Please post a new question. – Rubén Feb 27 '22 at 04:20
  • Thanks- I've posted question: https://stackoverflow.com/questions/71286727/not-possible-to-use-google-login-authenticator-button-for-google-webapp – DougMS Feb 27 '22 at 16:59