8

I have a Google Apps Script that I am currently using to successfully open a Google Sheets spreadsheet (read-only shared with my account) and perform some reading functions.

I received another spreadsheet that I would like to run similar processes on. If I try: SpreadsheetApp.openById("_______") (which works on the other sheet), I get Exception: Service Spreadsheets failed while accessing document with id _____" with this sheet. This spreadsheet is actually an xlsx on Google Drive (but appears it can still open in Google sheets) rather than a native Google sheet so I figured maybe that was the issue...?

So now I'm trying to instead access it as a Google Drive file using DriveApp.getFileById("____"). However now I get an authorization request to allow my script to access Google Drive. When I authorize it, I get:

This app is blocked
This app tried to access sensitive info in your Google Account. To keep your account safe, Google blocked this access.

I haven't found a way to fix this. I'm on a private Google account, not using a company's Apps. Any ideas?

Royal2000H
  • 393
  • 2
  • 12
  • 1
    Do you have advanced protection enabled in your Google account? – TheMaster Dec 16 '20 at 04:01
  • 1
    Your first issue is the common behaviour when trying to retrieve xlsx files through SpreadsheetApp, since this is not supported. See [this related feature request](https://issuetracker.google.com/issues/174231588). About your second issue, I cannot reproduce this. Have you checked whether this happens for other files? Did you notice this to work before? Have you made any change to your account settings? (enable [Advanced Protection](https://support.google.com/accounts/answer/7519408) as mentioned before, or what have you). – Iamblichus Dec 16 '20 at 09:32
  • I do not have Advanced Protection enabled. – Royal2000H Dec 17 '20 at 04:56
  • It does not appear to be an issue with the file itself, rather an overall issue. If I try ```var files = DriveApp.getFiles();``` instead, it still has the same message after trying to click Authorize – Royal2000H Dec 17 '20 at 04:57
  • 1
    Have you checked whether you can reproduce this in another project? Are you using a [default project](https://developers.google.com/apps-script/guides/cloud-platform-projects#default_cloud_platform_projects)? – Iamblichus Dec 17 '20 at 10:08
  • Yes I've tried a new blank project and I still get this issue. – Royal2000H Dec 24 '20 at 03:09
  • Hi there! To access your `.XLSX` by its ID, could you please try to convert it to Sheets beforehand? – Jacques-Guzel Heron Dec 28 '20 at 08:12
  • Unfortunately this is a .xlsx shared with me (read access) so I cannot go ahead and change it to Sheets. – Royal2000H Dec 29 '20 at 10:59
  • @Royal2000H to discard a permission issue, could you please check if this behaviour originates from your own `.XLSX` too? If the script continues returning some error, please share it to take a look. – Jacques-Guzel Heron Dec 31 '20 at 12:48
  • @Jacques-GuzelHeron I mentioned in a comment above to @Iamblichus, if I try a brand new script project and simply put in the line `var files = DriveApp.getFiles();`, I get the same error as in my original post: `This app is blocked. This app tried to access sensitive info in your Google Account. To keep your account safe, Google blocked this access.` As you can see, no specific .xlsx used but even just the use of Drive itself. – Royal2000H Jan 02 '21 at 05:34
  • Check if you still facing the same issue https://stackoverflow.com/a/66040219/6769935 – Firozzz Feb 05 '21 at 09:54

1 Answers1

10

Found a solution to the permission issue.

To work with Apps Script, I've modified this from @alper's solution which sources @tellowkrinkle's comment.

Do the following:

  1. Go to https://console.developers.google.com and create a new project for yourself
  2. Search for the Google Drive API in the search box at the top, and turn it on
  3. Click OAuth consent screen on the left and set it up.
    • Assuming your account isn't part of an organization, you'll have to say your app is for external users and in testing
    • In the required scopes section, add .../auth/docs and .../auth/drive (I'm not sure which needed, it's probably only one of those). This will probably not be available if you didn't complete (2)
    • Add the accounts you want to use with your copy of gdrive as testers of your app. Only these accounts will be able to use your copy of gdrive, so everyone will have to compile their own (unless someone goes and gets theirs reviewed by Google). I only added my own account because I am the only one using my App Script which uses my Drive.
  4. Click the 3 dots on the top right and select "Project settings". Make note of the "Project number".
  5. Go to your Apps Script. Go to Resources > Cloud Platform project. Insert the project number from (4).
  6. Next time you try to run your Apps Script, you'll be asked to Authorize but this time you'll have the chance to actually do it successfully.
Royal2000H
  • 393
  • 2
  • 12
  • [Aidan Jalili](https://stackoverflow.com/users/5511314) posted an [Answer](https://stackoverflow.com/a/65751842) saying: ..Just note to Royal200H's answer -- you need to actually type out `https://www.googleapis.com/auth/drive` and `https://www.googleapis.com/auth/docs` to add the scopes – Scratte Jan 16 '21 at 22:18
  • Unfortunately this solution is only a half-solution. It seems the permissions are lost after a week and need to be reauthorized at that point. Any scripts that are being run as "set-and-forget" will stop working until you open the script editor and run a function which prompts reauthorization. Any thoughts? – Royal2000H Jan 23 '21 at 01:18
  • Thank you so much - this worked for me !!! One suggestion to update step 5. In the apps script editor, I found this under "Project Settings" > Google Cloud Platform (GCP) Project. I did not find a dialog or menu "Resources" – Bram Luyten Aug 10 '22 at 07:32