How to resolve "This app is blocked" error for shared Google Apps Script library?

0
votes

I'm trying to create a Google Apps Script (GAS) library that I can reuse across my Google Sheets spreadsheets. Following these instructions, here's what I've done so far:

  1. Created a new project in Google Cloud Platform

  2. Enabled Google Sheets API for project in step 1

  3. Set up OAuth 2.0 authentication for project in step 1

    • Credential type = User data
    • Scopes = https://www.googleapis.com/auth/spreadsheets (I cannot use spreadsheets.currentonly because one method in this library requires the full spreadsheets scope)
    • OAuth Client ID = Web application

  4. Configured OAuth consent screen

    • Publishing status = Testing
    • User type = External
    • Test users = Gmail email I used to write the script

  5. In Apps Script editor:

    • Set the Google Cloud Platform (GCP) Project to Standard by associating it with the Project Number from step 1

    • Updated appsscript.json to look like this:

      {
   "timeZone": "America/Los_Angeles",
   "dependencies": {
     "enabledAdvancedServices": [
       {
         "userSymbol": "Sheets",
         "version": "v4",
         "serviceId": "sheets"
       }
     ]
   },
   "exceptionLogging": "STACKDRIVER",
   "runtimeVersion": "V8",
   "oauthScopes": [
     "https://www.googleapis.com/auth/spreadsheets"
   ]
}

    • Created a versioned deployment of my script following these instructions

    • Shared my GAS project as "Anyone on the internet with this link can view"

And here's the issue I'm facing:

  1. Created a new Sheets using the same Google account that I used to create the GAS library

  2. In the Scripts editor, I added my GAS library by entering the Script ID, then selected the HEAD version (it doesn't seem to matter -- all versions resulted in the same error)

  3. In Code.js, wrote a test function that calls a function from my GAS library

  4. Clicked the Run button to execute the test function; the following prompt appears:

    enter image description here

  5. Clicked "Review permissions" and selected the Google account I used to create the GAS library

ISSUE: This error page is displayed:

enter image description here

Now the strange part: if I repeat steps 1-5 above using a different Google account (i.e., not the same account I used to create the GAS library), then it works! I get the expected OAuth consent screen, and after granting permissions, I'm able to use my GAS library.

Has anyone figured out how to reuse a GAS library in a Sheets script that is under the same Google account as the one that was used to create the GAS library?

1
google-apps-scriptgoogle-sheets-api
If it works for one account (your gmail?) but not another (your work?) that is because of the way your domain permissions are set up. Is that what happened? Or are both accounts under one domain. You could also try different security settings than "everyone can view". – J. G.
@J.G. the Google account where the library works is also a personal account (not GSuite/Workspace). On the OAuth Consent screen setup page, I left the domain settings untouched. – thdoan

1 Answers

1
votes

If the app is blocked for your account, there is an easy approach to allow it. Just keep in mind that you would need to be an admin in the domain in order to run these changes. Follow this guide on allowing apps and complete its steps. Feel free to leave a comment if you need more help.