访问电子表格时，Google Doc onOpen将不会触发 [英] Google Doc onOpen will not fire when spreadsheet is accessed

问题描述

我的用例是，我将拥有电子表格中定义的流程规则列表.那是后端的东西.最终用户将复制一个Google文档，该文档将具有一个基于规则电子表格内容的动态菜单.我编写了一个onOpen函数，该函数从电子表格中读取并创建以工作表命名的菜单项.这些菜单项将触发处理表单中找到的规则的功能.

My use case is that I will have a list of rules for a process defined in a spreadsheet. That's backend stuff. End users will copy a Google doc which will have a dynamic menu based on the contents of the rules spreadsheet. I've written an onOpen function that reads from the spreadsheet and creates menu items named for the sheets. Those menu items will fire a function that processes the rules found in the sheet.

我的问题是，从脚本编辑器触发时，onOpen函数可以正确运行，但是在打开文档时不会触发.通过玩弄注释位，我确定SpreadsheetApp.openById()命令导致onOpen函数在初始文档加载时失败.当不使用该命令时，它将运行.

My problem is that the onOpen function runs correctly when triggered from the Script Editor, but will not fire when the document is opened. By playing around with commenting bits out, I have determined that the SpreadsheetApp.openById() command causes the onOpen function to fail on initial doc load. It will run when that command is not used.

我在函数内部和外部都设置了工作表定义，结果没有差异，因此，我认为下面的代码应该可以工作.确实如此，只是在打开文档时不会自动.有趣吧?因此，这是我的文档，其中包含代码:

I've had the sheet definitions set both inside and outside the function, and there's no difference in the result, so I believe the code below should, hypothetically, work. And indeed it does, just not automatically when the doc is opened. Interesting, right? So here is my doc with the code:

https://docs.google.com/documents/d/1dQb5RYntMsbTIxDCh6uEoNo3 /a>

https://docs.google.com/document/d/1dQb5RYntMsbTIxDCh6uEoNur3oOlVisxP7hD_sK3Fsk/edit

这是定义菜单项的电子表格:

And here is the spreadsheet that defines the menu items:

https://docs.google.com/spreadsheet/ccc?key=0AjR3e-R75aP8dHR0WWpGNF9vdEhvcy12eHJTMmF3aXc#gid=0

    // from this spreadsheet, I want function names based on the sheet names
    var ss = SpreadsheetApp.openById("0AjR3e-R75aP8dHR0WWpGNF9vdEhvcy12eHJTMmF3aXc");
    var sheets = ss.getSheets();

    function onOpen() {
       var menu = DocumentApp.getUi().createMenu("Menu Title");

       // dynamic menu based on tabs in spreadsheet
       for (sheet in sheets) {
          var thisCaption = sheets[sheet].getName();
          var thisFunction = "sheet_" + sheet;
          menu.addItem(thisCaption, thisFunction);
       }

       menu.addToUi();
    }


    // I precreate dummy functions based on sheet number
    function sheet_0() { process_sheet(0); }
    function sheet_1() { process_sheet(1); }
    function sheet_2() { process_sheet(2); }
    function sheet_3() { process_sheet(3); }
    function sheet_4() { process_sheet(4); }
    function sheet_5() { process_sheet(5); }
    function sheet_6() { process_sheet(6); }
    function sheet_7() { process_sheet(7); }
    function sheet_8() { process_sheet(8); }
    function sheet_9() { process_sheet(9); }


    function process_sheet(sheetNum) {
       var thisSheet = sheets[sheetNum];

       // at this point I do some processing based on the contents of the sheet
       // for the sake of example, I'll just set the document name to the sheet name
       var sheetName = thisSheet.getName();
       DocumentApp.getActiveDocument().setName(sheetName);
    }

推荐答案

您无法通过onOpen触发的函数访问外部文件(电子表格或其他文件).由于此功能自动运行，不需要任何用户的授权.它无法访问可能需要授权的API.

You can not access an external file (spreadsheet or else) from an onOpen triggered function. Since this function runs automatically, without requiring authorization from any user. It can not access APIs that potentially require authorization.

我不知道任何解决方法，我想您只需要更改操作方式即可.也许可以通过用户单击从onOpen创建的固定菜单的功能来设置此动态菜单.因为当用户单击某个功能时，将向他显示一个授权弹出窗口，然后您的脚本可以在他的权限下运行，并且可以执行他所授权的所有操作，这意味着要打开此电子表格，必须与他共享该电子表格，仅当您拥有自己以外的其他用户时才有意义:)

I do not know any workaround for this, I guess you just have to change the way you do it. Maybe set up this dynamic menu from a function the user click on a fixed menu you create from onOpen. Because when the user clicks a function, an authorization popup will be presented to him, and then your script can run under his permissions and can do everything that he's authorized, meaning that to open this spreadsheet, it will have to be shared with him, only makes sense if you have other users than yourself :)

这篇关于访问电子表格时，Google Doc onOpen将不会触发的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！