Unable to open Google xlsx spreadsheet / Also Google Drive permission Blocked

Question

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?

Answer 1

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.