9

I am unable to run any app script in my google account - even one created by me bound to a new document created by me. When I do, I get an "This App is Blocked" error. Note that this is different from the the "Sign in temporarily disabled for this app" failure mode mentioned elsewhere, and the solution for that problem has no effect on this one.

To test, I created a new spreadsheet while logged into my personal google account and put the value ONE in cell A1...

enter image description here

I then opened the Tools->Script editor from the menu bar of this spreadsheet and entered the following trivial script...

enter image description here

I then click on the run icon from the script editor menu bar with the function "myFunction" selected from the pulldown.

I get an "authorization required" popup and so I click "Review Permissions"...

enter image description here

I then get a "Choose an account" popup and click on my account (the only logged in account, and the same account I was logged into when I created the sheet and the script)...

enter image description here

I then get an "App Blocked" popup. Checking the execution log shows that the script did not run.

enter image description here

What is causing this popup and how can I prevent this so I can run app scripts?

NOTE: I see many other questions describing more complicated versions of this issue but none have useful answers. I am hoping this ultra simple version of the issue will help narrow it down and get a resolution.

More unexpected behavior:

If I put the code into the onLoad() function it works fine and never even asks me for authorization.

If I enter this code: enter image description here

...and then quit out and reload the sheet, then I get this in the execution logs... enter image description here

Again, this is with no authorization popups at all. The sheet loads without interruption and then the entry is in the execution logs. So this would seem to not be a case of not having the right permissions somewhere since code clearly can access the sheet.

Legacy editor

Identical behavior under the legacy editor...

enter image description here

Other accounts

I can repeat these exact same steps on a different google account and it works fine, so this problem appears to be linked to my account. Many others on the internet have noted the same finding. Seems like there might be some hidden (probably unintional) setting attached to the account that gets switched somehow and then thereafter the account is not able to manually authorize scripts to run.

Changing Project

I check and this script is in the "Default" project as expected...

enter image description here

According to this page,

For most applications and scripts, you never need to see or adjust this default GCP project—Apps Script handles all the necessary interactions with the Google Cloud Platform automatically.

Since I have nothing to lose, tried creating a new project in the Google Cloud Platform console, and then tried assigning this script to that new project. Unfortunately when I tried, I got the normal "Authorization needed" popup which lead to this opdd page...

enter image description here

Clicking on the "Troubleshoot this problem" link takes me to this page...

enter image description here

...which seems to say that I do not have the permissions to troubleshoot problems on my own account.

This again seems to suggest there is something misconfigured about my account on google servers. :/

bigjosh
  • 1,273
  • 13
  • 19
  • Did you enable "Advanced protection" on your Google account? – TheMaster Dec 22 '20 at 04:46
  • I don't think so. I don't even have 2-step verification enabled for this account. Do you know where I can check the Advanced Protection settings, please? I tried: https://landing.google.com/advancedprotection/ but it wants me to enroll. – Nelie Dec 22 '20 at 11:37
  • If "Advanced protection" is not the issue, You might have to create a issue in the issuetracker. See [tag info page](https://stackoverflow.com/tags/google-apps-script/info) for more details. Add the issue link here for more stars/wider audience, if you do create a issue. – TheMaster Dec 22 '20 at 12:22
  • 1
    Check here if this can help you https://stackoverflow.com/a/66040219/6769935 – Firozzz Feb 05 '21 at 06:28
  • Have you tried use the "Run" capability from the Legacy Apps Script code editor? I'm curious to know if that makes any difference. – Alan Wells Mar 15 '21 at 17:37
  • Also, try copying the entire script to a new project. Does the popup still appear? – Martí Mar 16 '21 at 14:04
  • @Martí yes, this happens on every script. It is dependant on my account rather than the document or project. – bigjosh Mar 16 '21 at 22:47
  • @AlanWells I just tried with the legacy editor and same results. Answer updated above. – bigjosh Mar 16 '21 at 22:53
  • This may be a stupid idea, but I'm wondering if deleting the cookies in your browser would do anything? Maybe try a different browser? – Alan Wells Mar 17 '21 at 00:55
  • @alan Not stupid, but tried clearing cookies, different browser, and different computer with different OS. Seems very likely linked to some config info related to the account on google side. – bigjosh Mar 17 '21 at 01:51
  • 1
    I think that I read somewhere that someone created a standard Google Cloud Platform (GCP) project and associated it with their Apps Script project, and they were able to authorize their script. *All* Apps Script projects are tied to the Google Cloud Platform no matter what. Every Apps Script project gets a "default" GCP project linked to it. It's done automatically. Many people have no reason to visit their GCP account, so don't realize it or understand it. Maybe creating the new GCP project and associating it with the Apps Script project could "reset" something. – Alan Wells Mar 17 '21 at 02:31
  • @AlanWells Interesting idea. To be sure, I went into Google Cloud console and created a new project from scratch and then manually assigned the app script to that new account. Unfortunately this just lead to a different permissions blocked alert with some puzzling commentary (details added above). Please LMK if any other ideas for this I should try. Thanks! – bigjosh Mar 17 '21 at 03:20
  • 1
    Could you try [turning "Allow less secure apps" on](https://myaccount.google.com/lesssecureapps)? ([Also see help](https://support.google.com/accounts/answer/6010255?hl=en#zippy=%2Cif-less-secure-app-access-is-off-for-your-account)) – Martí Mar 17 '21 at 09:05
  • @Martí I can not - Google will not let me! :) "This setting is not available for accounts with 2-Step Verification enabled. Such accounts require an application-specific password for less secure apps access. Learn more". But others have tried changing this settings without success. – bigjosh Mar 18 '21 at 20:00
  • 1
    with all due respect @bigjosh, I don't think the statements in your last paragraph are accurate. While GCP may also be less than perfect, we can infer the very fundamental security reasons for these GCP design choices. Your workaround for the apparent onEdit() security bug as you describe is elegant and commendable. Also please see my previous comment for a solution: [https://issuetracker.google.com/issues/145162820#comment169](https://issuetracker.google.com/issues/145162820#comment169) – Andrew R. Paullin Mar 18 '21 at 13:19
  • 1
    @Martí I am having this problem even on an account with 2FA on, AND I also have turned on "Allow less secure apps". Still no solution. I can't even run Google's own "Your First Script" code because of this issue. – rabbid Aug 09 '21 at 02:56

3 Answers3

6

Unfortunately there are no good answers there. Lots of people like me wake up one random morning to find that they can not run any new App Scripts in their account any more.

I've found a workaround that works great... but will make you very sad.

This issue does not affect code that runs automatically, so functions like onOpen() and onEdit() run just fine and have full access to the bound document. You heard that right- google blocks code that the user explicitly requests to run to protect their data, but code that runs silently and automatically anytime a sheet is opened or modified is free to run and access (and change!) whatever data it wants.

So to let the user run your code, you pick a cell inside the spreadsheet that either has a value that changes whenever you want your code to run, or you make a special cell called "Edit this cell to run the program".

Then you put your code inside the onEdit() and (if desired) check to see to see if the special cell was updated. If so, then run your arbitrary code. It has full access to the spreadsheet and can read and update cells at will and can also write to the log.

Note that you must close the sheet and re-open it for the code to take effect.

Here is what my demo spreadsheet looks like...

enter image description here

...and here is the demo code...

function onEdit(e) {
  var range = e.range;
  const triggerCell = "B2";
  if( e.range.getA1Notation() === triggerCell){
    var sheet = SpreadsheetApp.getActiveSheet();
    var data = sheet.getDataRange().getValues();
    var rangeB1 =sheet.getRange("B1");
    var rangeB2 =sheet.getRange("B2");
    var date = Utilities.formatDate(new Date(), Intl.DateTimeFormat().resolvedOptions().timeZone, "HH:mm:ss"); 
    rangeB2.setValue("Code ran at " + date );
    Logger.log( date + ": B1=" + rangeB1.getValue() );
  }
}

Here is a demo video...

https://youtu.be/ypuLaUWn1R8

I've said it before, I'll say it again - if you were thinking about using Google Cloud Services for anything then think again. This is crap built on top of crap that no one at google understands and it occasionally breaks suddenly and catastrophically, and there is no one who can even tell you what is going on much less how or when or if it is going to get fixed.

TheMaster
  • 45,448
  • 6
  • 62
  • 85
bigjosh
  • 1,273
  • 13
  • 19
  • This is barely a solution. There aren't many things you can do with just a simple trigger(like changing other spreadsheets, accessing databases or drive). `onEdit` is severely limited in what it can access – TheMaster Oct 15 '21 at 12:22
  • @TheMaster Agreed! This is a HORRIBLE work-around, but the best I could come up with! – bigjosh Oct 15 '21 at 18:01
  • This is not related to 145162820. I edited it out. If you think it is still related, re add it with additional explanations. – TheMaster Oct 24 '21 at 23:25
  • Unfortunately didn't work for me. Got a console error complaining about unauthorized scopes, even though the scopes were added to script. – Twitch Apr 20 '22 at 00:29
  • +1 because this helped a bit and because you're right about how disappointing and maddening this is. Unfortunately, it only helped the first part of my function work, and now I'm getting `Exception: You do not have permission to call UrlFetchApp.fetch. Required permissions: https://www.googleapis.com/auth/script.external_request` and I haven't solved it yet even after finding https://stackoverflow.com/a/62791834/470749 I didn't use to have so much trouble with Apps Scripts. – Ryan May 17 '22 at 00:19
  • "Unlike most other types of Apps Scripts, custom functions never ask users to authorize access to personal data. Consequently, they can only call services that do not have access to personal data..." https://developers.google.com/apps-script/guides/sheets/functions#using_apps_script_services Making it a custom function and then calling it as a formula from a sheet worked. I just can't trigger the execution manually from the code editor. – Ryan May 17 '22 at 00:32
4

I ran into this for the first time today but my issue seems to be due to:

  • created a sheet on a shared drive owned by a different organization
  • my organization uses the legacy apps script interface, the shared drive organization uses the new apps script interface

Using an account from the shared drive organization to create the script solved my issue.

Onjuku
  • 71
  • 4
  • 5
    This happens to me on a personal Google account (@gmail.com) even for newly created Spreadsheets where I'm the owner. – Nelie Dec 22 '20 at 11:39
  • I'm having the same issue on a personal account just linking to Drive on my own account. https://stackoverflow.com/questions/65316819/unable-to-open-google-xlsx-spreadsheet-also-google-drive-permission-blocked – Royal2000H Jan 06 '21 at 02:26
  • I'm having this issue on my personal account too, although in my case, my account is enrolled in advance protection so it's at least possible that's the issue. But this is a case of Google Apps authorizing a Google app to access a Google app, you'd think it would be trusted. – Steve Estes Jan 06 '21 at 04:55
  • I get this on my personal account – nadavkav Feb 05 '21 at 13:20
3

This error

This app is blocked

seems to be a new error affecting certain Google accounts. This is reported to Google. Kindly star(add a star ★ to it on top left) to the following issues and comment to get Google developers to prioritize this issue:

Some workarounds:

This issue does NOT seem to be related to another issue 145162820:

"Sign in temporarily disabled for this app"

The error messages are different.

TheMaster
  • 45,448
  • 6
  • 62
  • 85
  • 2
    I own a spreadsheet which is shared with others. They ran into the above issues. Creating a new GCP seemed solving the problem. But I've tried with my personal gmail account, it worked without any issues!! My collaborators for whom it didn't work they need their phones to sign in into their Gmail (i.e. they need to confirm the login by choosing the correct displayed number). So maybe this is the main issue here, and the solution would be to Disable: Use your phone to login. – Nabnub Nov 01 '21 at 20:25