尝试打开其他电子表格时出现自定义函数的错误消息。 “您无权执行该操作。” [英] Error msg from custom function when trying to open another spreadsheet. "You do not have permission to perform that action."

问题描述

我有一个自定义函数用于从另一个电子表格中检索表格中的单元格值。函数的两个参数是 row 和 col ，它们确定要引用的单元格。每当我尝试使用该函数时，都会收到错误您无权执行该操作。，指向带有openByID方法的行。这是我的自定义函数：

pre $ function TEST（row，col）{
var formDataSpreadsheet = SpreadsheetApp.openById（FORM_DATA_SPREADSHEET_ID ）;
SpreadsheetApp.setActiveSpreadsheet（formDataSpreadsheet）;
var formDataSheet = formDataSpreadsheet.getSheetByName（WEBSITE_FORM_DATA_SHEET_NAME）;
formDataSpreadsheet.setActiveSheet（formDataSheet）;
返回formDataSpreadsheet
}


我需要发布FORM_DATA_SPREADSHEET吗？

解决方案

对不起，不能在自定义函数中做到这一点：

Google文档引用：
$ b

如果您的自定义函数抛出错误消息您没有权限调用X服务，该服务需要用户授权，因此不能用于自定义函数中。

Google文档 - 自定义功能

I have a custom function that I want to use to retrieve the cell value in a sheet from another spreadsheet. The two parameters of the function are row and col and they determine the cell to reference. Whenever I try to use the function, I get the error "You do not have permission to perform that action.", pointing to the line with the openByID method. Here is my custom function:

function TEST(row,col) {
    var formDataSpreadsheet = SpreadsheetApp.openById(FORM_DATA_SPREADSHEET_ID);
    SpreadsheetApp.setActiveSpreadsheet(formDataSpreadsheet);
    var formDataSheet = formDataSpreadsheet.getSheetByName(WEBSITE_FORM_DATA_SHEET_NAME);
    formDataSpreadsheet.setActiveSheet(formDataSheet);
    return formDataSpreadsheet  
}

Do I need to publish the FORM_DATA_SPREADSHEET? Any help is greatly appreciated.

解决方案

Sorry, can't do that in a custom function:

Quote from Google documentation:

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.

Google Documentation - Custom functions

这篇关于尝试打开其他电子表格时出现自定义函数的错误消息。 “您无权执行该操作。”的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！