Google Doc onOpen will not fire when spreadsheet i

Question

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.

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/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);
    }

Answer 1

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.

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