无法运行绑定到新空白文档的微不足道的应用程序脚本 - 获取“此应用程序已被阻止"； [英] Can not run trivial app script bound to new blank document - get "This app is blocked"

问题描述

我无法在我的 Google 帐户中运行任何应用程序脚本 - 即使是我创建的应用程序脚本也绑定到了我创建的新文档.当我这样做时，我收到此应用程序已被阻止"的消息.错误.请注意，这与为此应用程序暂时禁用登录"不同.

然后，我从该电子表格的菜单栏中打开了 Tools->Script 编辑器，并输入了以下简单的脚本...

然后，我从脚本编辑器菜单栏中单击运行图标，并使用函数myFunction".从下拉菜单中选择.

我得到了需要授权"；弹出窗口，所以我点击查看权限"...

然后我得到一个选择一个帐户"弹出并单击我的帐户(唯一登录的帐户，与我创建工作表和脚本时登录的帐户相同)...

然后我收到应用程序被阻止"的消息弹出.查看执行日志发现脚本没有运行.

是什么导致了这个弹出窗口，我该如何防止它以便我可以运行应用脚本?

注意:我看到许多其他问题描述了这个问题的更复杂版本，但没有一个有有用的答案.我希望这个问题的超简单版本将有助于缩小范围并获得解决方案.

更多意外行为:

如果我将代码放入 onLoad() 函数中，它就可以正常工作，甚至从不要求我授权.

如果我输入这个代码:

...然后退出并重新加载工作表，然后我在执行日志中得到它...

同样，这根本没有授权弹出窗口.工作表不间断地加载，然后条目在执行日志中.因此，这似乎不是某个地方没有正确权限的情况，因为代码显然可以访问工作表.

旧版编辑器

旧版编辑器下的相同行为...

其他帐户

我可以在不同的 google 帐户上重复这些完全相同的步骤，并且工作正常，所以这个问题似乎与我的帐户有关.互联网上的许多其他人也注意到了同样的发现.似乎有一些隐藏的(可能是无意的)附加到帐户的设置以某种方式被切换，然后该帐户无法手动授权脚本运行.

改变项目

我检查了一下，这个脚本在默认"中项目按预期进行...

根据此

单击解决此问题"链接将我带到此页面...

...这似乎是说我没有权限对自己的帐户进行问题排查.

这似乎再次表明我在 Google 服务器上的帐户配置错误.:/

解决方案

不幸的是，那里没有好的答案.许多像我这样的人在一个随机的早晨醒来，发现他们无法再在他们的帐户中运行任何新的应用脚本.

我找到了一种非常有效的解决方法......但会让你非常难过.

这个问题不会影响自动运行的代码，所以像 onOpen() 和 onEdit() 这样的函数运行得很好，并且可以完全访问绑定的文档.你听说过，谷歌会阻止用户明确请求运行以保护他们的数据的代码，但是在打开或修改工作表时自动静默运行的代码可以自由运行和访问(和更改!) 任何它想要的数据.

因此，为了让用户运行您的代码，您可以在电子表格中选择一个单元格，该单元格的值可以在您希望代码运行时随时更改，或者创建一个名为编辑此单元格以运行程序"的特殊单元格;.

然后将代码放入 onEdit() 并(如果需要)检查特殊单元格是否已更新.如果是这样，则运行您的任意代码.它拥有对电子表格的完全访问权限，可以随意读取和更新单元格，也可以写入日志.

请注意，您必须关闭工作表并重新打开它才能使代码生效.

这是我的演示电子表格的样子...

...这里是演示代码...

function onEdit(e) {var 范围 = 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("代码运行于"+日期);Logger.log( date + ": B1=" + rangeB1.getValue() );}}

这是一个演示视频...

https://youtu.be/ypuLaUWn1R8

我之前说过，我再说一遍 - 如果您正在考虑将 Google 云服务用于任何目的，请再想一想.这是建立在谷歌没有人理解的垃圾之上的垃圾，它偶尔会突然和灾难性地中断，而且没有人甚至可以告诉你发生了什么，更不用说它是如何、何时或是否会得到修复.

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...

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

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"...

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)...

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

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:

...and then quit out and reload the sheet, then I get this in the execution logs...

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...

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...

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...

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

...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. :/

解决方案

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...

...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.

这篇关于无法运行绑定到新空白文档的微不足道的应用程序脚本 - 获取“此应用程序已被阻止"；的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！