Unable to open modal dialog in onOpen function of a spreadsheet copy using Apps Script

Question

I have a template spreadsheet which is supposed to execute some code on open.
Part of this code is to open a modal dialog asking the user to wait while the script is running.
I have manually created the onOpen trigger on the template.
Everything works as expected when opening this template spreadsheet.

However when I make a copy of the spreadsheet and open it, the onOpen code executes but the modal dialog is never showing.
The trigger that was manually installed on the first spreadsheet is not added to the copy. If I create the trigger manually on the copy, everything works as expected.

How can I create copies of the initial spreadsheet that show the modal dialog on open without having to create the trigger manually (or do anything else manually) on each copy?

EDIT 1: Here is a simple example.

function onOpen(event) {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu("Custom menu")
    .addItem('Show modal dialog', '_pleaseWait')
    .addToUi();

  _pleaseWait();
}

function _pleaseWait(){
  var htmlOutput = HtmlService
    .createHtmlOutput("Processing...")
    .setWidth(100)
    .setHeight(100);
  SpreadsheetApp.getUi().showModalDialog(htmlOutput, 'Hello');
}

Without the onOpen trigger created manually, the menu is added but the modal dialog never shows automatically. However it is displayed by clicking on the added menu item.
Copies of the template spreadsheet will be made and a manual trigger cannot be added on each copy.

I've thought of this solution but I need the trigger to be installed automatically before the first time the copy is opened — Mouz
Install a Trigger programatically onInstall. You have to install a trigger to have the authorization scope to do the other things you want to do. — Chris
It seems you have a problem with your code. However, we can't help unless we have code or information that can reproduce the problem. Otherwise, we are just blindly guessing. — Chris

Mark Mark · Accepted Answer · 2018-06-01T19:13:47

I had a similar issue. It would work perfectly when called via a custom menu; but the minute I would add it to the function onOpen(){} it would not work and, additionally, it would not execute anything after it. I even tried to add the code directly to the onOpen function (opposed to calling another function). This appears to be an issue explicitly with the ui.showModalDialog(); it would work as expected with ui.alert. I ended up adding a project trigger to run my function and it works with no issue. The following code would not work:

function onOpen(){    
    SpreadsheetApp.getUi().createMenu('Feedback').addItem('Submit Feedback','sendFeedback').addToUi();
    startUpNotification()
}
function startUpNotification(){
    var ui = SpreadsheetApp.getUi();
    var message = HtmlService.createHtmlOutputFromFile('reminders').setWidth(800).setHeight(250);
    var result = ui.showModalDialog(message,' ');
}

However, once I added startUpNotification() to the project trigger list via the 'Edit' menu in the Script Editor window, it worked as expected.

Unable to open modal dialog in onOpen function of a spreadsheet copy using Apps Script

2 Answers