0

My web app access to a shared spreadsheet via a Google Service Account was working perfectly. This was enabled by adding the service account email (@.iam.gserviceaccount.com) as a user email in the doc sharing dialog.

At some point opening the spread sheet in google drive popped up a sharing dialog with a message about external access being turned off. The admin says they made no changes yet my app code to access the spreadsheet now fails with a "access denied " error.

How can I restore access to this spreadsheet from my service account? I've search the web with no answers forthcoming. idon;t have admin access to so need ot explain to the admin.

Is it possible to do so without enabling global external access? We want to remain secure.

Steve Lee
  • 5,191
  • 5
  • 17
  • 18
  • I'm not expert in workspace, but you should be able to whitelist some domain to grant them on your Workspace document. Your admin have activated an option, with your issue as side effect, work with them to solve it! – guillaume blaquiere Aug 09 '21 at 14:41
  • There was a similar situation in the past and a solution was also provided. You can check for the solution [here](https://stackoverflow.com/a/27196484/15745106). – Bakul Mitra Aug 10 '21 at 09:34
  • @guillaumeblaquiere Yes that's what I thought but they swear they have not. Did Google tighten security like they will do in Sept? I can find no good google admin or Service docs on what to do to manage external sharing so I can tell them what I require. – Steve Lee Aug 10 '21 at 15:17
  • @BakulMitra that's no help. The code is all correct and was working. It's simply needed to regrant permission to spreadsheet. Typical Google docs are no help. I'm going to have to search the Google admin UI to find what might be possible. – Steve Lee Aug 10 '21 at 15:18
  • @Steve Lee can you explain a little bit about your issue? – Bakul Mitra Aug 11 '21 at 13:43
  • @BakulMitra Thanks 1) access to a sheet from a Service Account needs account email added to the sheet 2) The service email was added to the sheet and everything worked perfectly. My web app could update the sheet 3) Suddenly stopped working. Opening sheet showed sharing box with error that service account email it is external and an admin setting is blocking. 4) admin not changed any external sharing options. I can find no good docs on what settings. 5) So either a) the service account email is now treated as external , or b) the external sharing admin settings was changed automatically. – Steve Lee Aug 12 '21 at 14:34
  • @BakulMitra So now we need to figure out how to share this spread sheet to the service account email and ideally no more than that. Whatever, a pointer to the admin docs that apply would be a good start. I'm going through the admin console with the admin tomorrow. So hope we can get it working, even if we have to open the securiy right up. Ideally we could alow only the service account access to the specific sheet and no more. Is that clear enough? – Steve Lee Aug 12 '21 at 14:34
  • Hello @Steve Lee. I think i have found something which can be helpful [here](https://stackoverflow.com/a/35041760/15745106). Please do let me know if this helps. – Bakul Mitra Aug 13 '21 at 09:22
  • OK so if we enable Global sharing to any domain then I can add the service account to she sheet and my app works. If we try to use the "whitelist" feature to restrict it for security it fails. The error is the usual verbosity that might mean it is as it is not a Google domain - well obviously it is a service account D'oh This never happened : (Optional) To allow visitor sharing only to trusted domains: "Known issue: If you choose this option, you get this warning: "Incompatible with whitelisted domains." However, your users can still share with people in the trusted domain." – Steve Lee Aug 13 '21 at 13:58
  • @Steve Lee is it solved? – Bakul Mitra Aug 13 '21 at 15:57
  • @BakulMitra Not really, as we want to get the whitelisted non Google domain working as the docs almost explain how to do but fails to work. But sure, it's one of those that unless you can talk to a Google dev whom works on it you'll only solve by luck. Thanks for your support. – Steve Lee Aug 14 '21 at 10:49

1 Answers1

0

Unfortunately you cannot restore the access to the spreadsheet.

The permissions don't change automatically, so most likely some changes have been made regarding the spreadsheet.

There are also other things to take into account - if the spreadsheet is in a shared drive for instance and the permissions for the shared drive have been changed by the owner, then these changes are reflected in the spreadsheet permissions.

Another thing is, even though the admin may have not made any changes, the original owner of the spreadsheet might have changed these permissions which in the end lead to the "Permission denied" error.

How to fix this?

The owner of the spreadsheet will have to grant access to the service account again in order for this to work.

Reference

ale13
  • 5,679
  • 3
  • 10
  • 25
  • Thanks for the details. It's actually only myself as developer and the admin who might have change any of those things, and of us did so. The error we get tis he service account suddenly can't have access due to security settings. Whatever, we want to grant access to the service account but the only way we found that works is to enable the option to allow all users to share outside the Google domain. We never changed that option either. – Steve Lee Sep 10 '21 at 17:07
  • The docs indicate that there is a whitelist feature for external email addresses but after going round and round following the documation references for external access to non google suite emails and trying to get the whitelist to work we gave up. It really seems like some global security feature changed and broke our ability to provide external access to a specific service to a particular spreadsheet. Never mind. I've lost interest in finding a high security option and will stick to the wider access option that is the only one that works. – Steve Lee Sep 10 '21 at 17:07