How I can start a HtmlService directly on file start (onOpen)?

Question

I have created this simple script in a Google Sheet:

function onOpen() {
  SpreadsheetApp.getUi()
      .createMenu("My Private Menue")
      .addItem('My html Dialog', 'StartMessage')
      .addToUi();      
    ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1").activate();

  var html = HtmlService.createHtmlOutputFromFile('Message');
  SpreadsheetApp.getUi().showModalDialog(html, 'TITELLEISTE');
}

But the HtmlService does not start directly. But when I click on my created menu, the HtmlService executes without problems.

Where is my fault? Here I have shard my file.

Try creating an On open installed trigger that will launch the dialog. onOpen does not launch a sidebar so I think it also prevents your custom dialog from opening. — TheWizEd, Jun 14 '19 at 21:12
@TheWizEd, true, since installable triggers run in `authMode=FULL`, he will be able to show the dialog — Oleg Valter is with Ukraine, Jun 14 '19 at 21:27
@TheWizEd, btw, I think you'd better post it as an answer and take credit for the suggestion - your solution works (I even tested it just to make sure). — Oleg Valter is with Ukraine, Jun 15 '19 at 00:25

Oleg Valter is with Ukraine · Answer 1 · 2019-06-15T05:43:01.520

0

There is nothing wrong with your script, but there is a problem. If you open your execution transcript, you'll see that the onOpen(e) trigger fails due to "You do not have permission to call showModalDialog" error. Also, please, take note of additional restrictions that you need to account for.

Your trigger runs with authMode=LIMITED (you'll see that if you log() the trigger's event object). According to restrictions table, Access to user interface is limited to Add menu items, thus the failure (the table is in editor add-ons auth lifecycle guide, but in terms of authMode=LIMITED restrictions I believe this is the only mention of simple triggers being restricted to adding menu items).

This also means that if you want to show users a dialog each time they open your Spreadsheet, you are out of luck in terms of simple triggers (please, see the TheWizEd's comment proposing using installable triggers for solution), as triggers run don't run authMode=LIMITED when a user clicks on the menu item (and this is why you are able to show dialog after click).

UPD: the answer was updated after discussion with TheMaster to avoid confusion.

edited Jun 15 '19 at 05:43

answered Jun 14 '19 at 20:58

Oleg Valter is with Ukraine

9,619
8
37
59

1

OP never mentioned using a add-on. – TheMaster Jun 15 '19 at 05:04
1

Yes, but doesn't the authorization lyfecycle apply to the container-bound scripts with simple triggers, like `onEdit()`/`onOpen()`? From tests I've run - this is exactly the case (maybe I should modify the answer to avoid confusion) – Oleg Valter is with Ukraine Jun 15 '19 at 05:14
1

I don't think so. There is no "Enabled"/"Installed" mode. There is no `onInstall()` trigger. And there's no mention of how to create a installable onOpen trigger, which is the actual answer/comment made by WizEd – TheMaster Jun 15 '19 at 05:20
1

Agreed, sorry about that - my actual point is that simple `onOpen()` triggers run in `authMode=LIMITED` (which is the case) - and that lead to the error, poor assumption on my side about the add-on. Will edit accordingly! – Oleg Valter is with Ukraine Jun 15 '19 at 05:35

score 0 · Answer 2 · edited Jun 15 '19 at 00:05

From what I understand that the HTML service can display a dialog or sidebar in Google Sheets, or Forms if your script is container-bound to the file. Meaning that if it was created from that document rather than as a standalone script. I can see that you already have Bound-Scripts methods in your code like getActiveSpreadsheet().

Why not use the following:

function onOpen() {
  SpreadsheetApp.getUi()
      .createMenu("My Private Menu")
      .addItem('My html Dialog', 'StartMessage')
      .addToUi();

//Return sheet object by name
function getSheet(name){
 return SpreadsheetApp.getActiveSpreadsheet().getSheetByName(name);
}  

function StartMessage(){  
  var html = HtmlService.createHtmlOutputFromFile('Message').setWidth(400).setHeight(300);  
  SpreadsheetApp.getSheet().getUi().showModalDialog(html, 'TITELLEISTE');
}

I hope this helps!

References:
https://developers.google.com/apps-script/guides/html/
https://developers.google.com/apps-script/guides/dialogs
https://ctrlq.org/code/19954-html-service-google-scripts

@N Ali - as far as I understood, @J Doe 's problem is that the `HtmlService` isn't triggered on Spreadsheet being *open* and executing it on menu click is not a problem. See my answer for info on authorization lifecycle for add-ons. Besdes, TheWizEd proposal deals with the issue in one simple modification... — Oleg Valter is with Ukraine, Jun 15 '19 at 00:26

How I can start a HtmlService directly on file start (onOpen)?

2 Answers2

Linked