4

I created a script for Google Spreadsheets, this script just adds a new menu item as described here Custom Menu Items in a Spreadsheet. Then I deploy this script as a Web App and I want all users who install the app to be able to see the new menu item. And I'm stuck at this point.

As I understand, when you deploy a script as a Web App, onOpen functions looses it's meaning. So, inside doGet I create custom trigger for onOpen event, attach myOnOpen handler to it and inside myOnOpen I add a menu item, but the item doesn't show up.

Here's my code: 

function doGet() {
    var newSheet = SpreadsheetApp.create("new sheet");
    var newId = newSheet.getId();
    ScriptProperties.setProperty('newId', newId); 

    ScriptApp.newTrigger("myOnOpen")
        .forSpreadsheet(newId)
        .onOpen()
        .create();
};

function myOnOpen() {
    var newId = ScriptProperties.getProperty('newId');
    var sheet = SpreadsheetApp.openById(newId);

    var entries = [ { name : "Show bingo", functionName : "Bingo" } ];
    sheet.addMenu("My Menu", entries);
};

function Bingo() {
    Browser.msgBox("Bingo!");
};

So, when a user who installed the app opens "new sheet" spreadsheet, he doesn't see the "My Menu". What am I doing wrong here? Why the menu item doesn't show up? At the end of the day I want to create a Web App which extends Google Spreadsheets UI with additional menus and dialogs.

Any advice is welcome. Thanks!

xman85
  • 67
  • 1
  • 2
  • 8
  • I can't figure out how the function Bingo can end up in the new spreadsheet 's script... neither can I for the 'myonopen' function... how do you imagine this process? My opinion is that this is simply not possible... why not create a template with the script already included and after that create the trigger? Could you clarify please? I'm really curious... – Serge insas Dec 17 '12 at 20:32
  • @Sergeinsas When a user executes the app, a custom trigger gets created in his GDrive. The trigger is attached to a particular spreadsheet in user's GDrive. Every time a user opens this spreadsheet, myOnOpen gets called. It works, I created test account, installed my app, opened "new sheet", myOnOpen was invoked. I can even modify the spreadsheet to some extent from within myOnOpen function (e.g. `sheet.setColumnWidth(1, 200)`), but I can't create custom menus there. Template doesn't work for me, I want users to upload their .xls files, and I want to add custom UI for editing these files. – xman85 Dec 17 '12 at 22:31
  • How did you test that the myonOpen did run when a user opened the SS ? And how could Bingo work ? it has no link with the spreadsheet ... That doesn't correspond to the "script and container" definition. – Serge insas Dec 17 '12 at 22:49
  • @Sergeinsas I used `MailApp.sendEmail()`. myOnOpen is supposed to create a menu item, when the item is clicked, Bingo is invoked. – xman85 Dec 17 '12 at 23:01
  • What you explain about users uploading their .xls files is not the same situation as creating new files like in your test... templates would also work, just use a script to copy the xls imported data (in a google ss) into a copy of the template and you're all set, the template copy will have a script attached and you can add the triggers just the way you do it now... – Serge insas Dec 17 '12 at 23:02
  • Thanks for the explanations ;-) I'm still wondering how this can work but I'll stop questioning you... good luck – Serge insas Dec 17 '12 at 23:05
  • @Sergeinsas This might work if copying data from .xls to a template isn't a tedious process. Can I do it with a couple of simple API calls? Could you please point me to some documentation on this? – xman85 Dec 17 '12 at 23:09
  • It is quite straightforward ; use [sheet.copyTo()](https://developers.google.com/apps-script/class_sheet#copyTo) using the xls import ss as source and the template copy as destination, do that for each sheet in a loop and that's all. – Serge insas Dec 17 '12 at 23:26
  • @Sergeinsas Thank you Serge, it works. This solution may have it's own drawbacks though, we'll see... – xman85 Dec 18 '12 at 20:39
  • glad it helped, this was a looooong comment, apologies to sto ;-) – Serge insas Dec 18 '12 at 21:19

1 Answers1

0

When you deploy the script as a web app, users of the web app will only see content you "return" from the doGet function. You can create content using UiApp or HtmlService and return this content to be rendered in a browser. Your new menu items are attached to a spreadsheet, so they can only be displayed when the user goes to the spreadsheet itself (activating the onOpen trigger, etc).

Kalyan Reddy
  • 1,132
  • 6
  • 5
  • In my scenario the following happens: user installs the app, after that user opens newly created "new sheet" in his GDrive, myOnOpen function runs, but the menu doesn't get added. – xman85 Dec 17 '12 at 20:12