电子表格:“您无权调用setNumberFormat" [英] Spreadsheets: "you do not have permission to call setNumberFormat"

问题描述

我在Google Spreadsheet上运行了以下脚本:

I've got the following script running on a Google Spreadsheet:

function FORMATCURRENCY(value, currency) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheets()[0];
  
  var range = sheet.getRange("D19");
  
  range.setNumberFormat("$#,##0.00;$(#,##0.00)");
  
  return range 
}

但是，当我运行它时，出现错误消息您没有运行setNumberFormat的权限".

But when I run it I get an error saying "You do not have permission to run setNumberFormat".

我已经在此功能的文档中找到了，并且我已按照所有说明进行操作，包括在脚本区域中的作用域属性上添加了授权依赖项，但仍然出现相同的错误.

I've looked into the documentation for this function here and I've followed all the instructions, including adding the authorization dependencies on the scope properties in the script area and I still get the same error.

我的清单文件在脚本中看起来像这样:

My manifest file looks like so in the scripts:

{
  "timeZone": "Europe/London",
  "dependencies": {
  },
  "exceptionLogging": "STACKDRIVER",
  "runtimeVersion": "V8",
  "oauthScopes": [
    "https://www.googleapis.com/auth/spreadsheets",
    "https://www.googleapis.com/auth/spreadsheets.currentonly",
    "https://www.googleapis.com/auth/spreadsheets.readonly",
    "https://www.googleapis.com/auth/userinfo.email"
  ]
}

这是什么问题?

推荐答案

自定义功能不能与需要授权的服务一起使用.

Custom functions can not be used with services that require authorization.

所有Set方法都属于此类别，在您的情况下为setNumberFormat:

All Set methods belong to this category, in your case setNumberFormat:

有关自定义功能，请参见官方文档:

See the official documentation regarding custom functions:

如果您的自定义函数抛出错误消息，您没有 允许调用X服务.该服务需要用户授权 因此无法在自定义函数中使用.

If your custom function throws the error message You do not have permission to call X service., the service requires user authorization and thus cannot be used in a custom function.

要使用除上面列出的服务以外的其他服务，请创建一个自定义菜单 运行Apps脚本功能而不是编写自定义 功能.从菜单触发的功能将询问用户 必要时进行授权，因此可以使用所有应用程序 脚本服务.

To use a service other than those listed above, create a custom menu that runs an Apps Script function instead of writing a custom function. A function that is triggered from a menu will ask the user for authorization if necessary and can consequently use all Apps Script services.

或者，您可以从脚本编辑器中运行该函数，但请记住，您需要传递参数才能执行它或在函数中定义它们.

Alternatively, you can run the function from the script editor but keep in mind that you need to pass the arguments to be able to execute it or define them within your function.

在这种情况下，由于您没有在FORMATCURRENCY函数中使用value和currency，因此可以直接从脚本编辑器或自定义菜单执行此操作，但不是公式:

In this case, since you are not using value and currency within the FORMATCURRENCY function, you can execute this directly from your script editor or from a custom menu but not as a formula in your sheet:

function FORMATCURRENCY() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheets()[0];
  
  var range = sheet.getRange("D19");

  range.setNumberFormat("$#,##0.00;$(#,##0.00)");
  
}

这篇关于电子表格:“您无权调用setNumberFormat"的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！