SpreadsheetApp.openByUrl 和 openById 给出错误 [英] SpreadsheetApp.openByUrl and openById give errors

问题描述

所以我在使用谷歌电子表格和应用程序脚本编写的应用程序中遇到了问题.我已经整理了一个分步指南来重现它.

So I have encountered a problem in the application I am writing using google spreadsheets and app-script. I have put together a step by step guid to reproduce it.

总结:任何使用 SpreadsheetApp.openByUrl() 和 SpreadsheetApp.openById() 的脚本在从脚本编辑器内部调用时都可以正常工作，但从内部调用时会出现错误，即使是包含该脚本的同一个电子表格.

Summary: any script that uses SpreadsheetApp.openByUrl() and SpreadsheetApp.openById() works OK when called from inside the script editor but gives errors when called from inside even the same spreadsheet that has that script inside it.

错误消息:

Error
    You do not have permission to perform that action. (line ?).
    

重现它的步骤:

1. 制作新的 Google 电子表格
2. 打开它的脚本编辑器(工具菜单>>脚本编辑器...)
3. 选择空白项目

4. 粘贴此代码

1. Make a new Google Spreadsheet
2. Open its Script Editor ( Tools menu >> Script Editor... )
3. Choose Blank Project

4. Paste in this code

function demo1() {
  return "cat";
}

function demo2() {
  return [["bob", "fred"], ["x", "y"]];
}

function demo3() {
  Logger.log("demo3-1")
  ss = SpreadsheetApp.openByUrl("[==URL==]")
  name = ss.getName()
  Logger.log("demo3-2 " + name)
  return name
}

function demo4() {
  Logger.log("demo4-1")
  ss = SpreadsheetApp.openById("[==id==]")
  name = ss.getName()
  Logger.log("demo4-2 " + name)
  return name
}

将[==URL==]"和[==id==]"更改为引用您自己的文档

Change the "[==URL==]" and "[==id==]" to refer to your own document

保存脚本

从运行菜单中选择demo1.这会引发一个关于权限的问题.授予它所要求的特权.(要求访问您的 Google 云端硬盘)

From the Run menu choose demo1. This will trigger a question about privileges. Grant it the privileges it asks for. ( asks for access to your Google Drive )

返回到电子表格本身

设置以下单元格以使用这些公式:

Set the following cells to have these formulae:

A1 : =demo1()
C2 : =demo2()
F1 : =demo3()
h1 : =demo4()

注意前两个工作，所以电子表格看到脚本项目并能够从中运行代码(因此它有权从该脚本运行代码)但后两个不工作并抱怨它没有执行该操作的权限.

Note that the first 2 work, so the spreadsheet is seeing the script project and able to run code from it ( so it has permission to run code from that script ) but the last two do not work and complain that it does not have permission to perform that action.

那么我该如何解决这个问题?是我做错了什么还是有潜在的问题?

So how do I fix this? Am I doing something wrong or is there an underlying problem ?

推荐答案

此错误是由于 Google 最近推出的一项更改，禁止在自定义函数中使用 SpreadsheetApp.openByUrl()(或 SpreadsheetApp.openById()).您仍然可以在菜单项、触发器等其他上下文中使用此方法.出于安全原因，Google 不得不推出此更改，并且他们将无法恢复到旧行为.因此，唯一可能的解决办法很可能是将该函数重写为正常"的 GAS 函数，而不是自定义函数.

This error is due to a change Google recently rolled out prohibiting the use of SpreadsheetApp.openByUrl() (or SpreadsheetApp.openById() in custom functions.  You can still use this method from other contexts like a menu item, trigger, etc. Google had to roll out this change for security reasons and they won't be able to revert back to the old behavior. So the only possible fix may very well be to rewrite the function as a 'normal' GAS-function and NOT a custom function.

问题在问题跟踪器中列出.

这篇关于SpreadsheetApp.openByUrl 和 openById 给出错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！