Google Sheets OnEdit() - You do not have permission to call a service

Since the beginning of this month this code has not more been working

function onEdit(e){    
              var html = HtmlService.createTemplateFromFile( 'MyHtmlFile' )
              .evaluate()
              .setWidth( 800 )
              .setHeight( 400 );
              SpreadsheetApp.getUi() // Or DocumentApp or FormApp.
              .showModalDialog( html, 'My page title' ); 
}

When I edit a Spreadsheet's cell NO pop up is shown but I got this error in the console:

Google Apps Script: You do not have permission to call showModalDialog


Solution 1:

The onEdit() will have to be an Installable Trigger because it is using a service that requires authorization.

note: rename onEdit() when using it as an installable trigger to avoid possible issues

Like simple triggers, installable triggers let Apps Script run a function automatically when a certain event, such as opening a document, occurs. Installable triggers, however, offer more flexibility than simple triggers: they can call services that require authorization, ...

Managing triggers manually:

To manually create an installable trigger through a dialog in the script editor, follow these steps:

  1. From the script editor, choose Edit > Current project's triggers.
  2. Click the link that says: No triggers set up. Click here to add one now.
  3. Under Run, select the name of function you want to trigger.
  4. Under Events, select either Time-driven or the Google App that the script is bound to (for example, From spreadsheet).
  5. Select and configure the type of trigger you want to create (for example, an Hour timer that runs Every hour or an On open trigger).
  6. Optionally, click Notifications to configure how and when you will be contacted by email if your triggered function fails.
  7. Click Save.

Solution 2:

This answer serves documentation purposes.

  • You can also create the installable onEdit trigger via a script if you want to avoid the manual work of installing the trigger.

In order to create an installable onEdit trigger for myFunction, execute only and once the create_onEdit function below:

function myFunction(){    
     var html = HtmlService.createTemplateFromFile( 'MyHtmlFile' )
              .evaluate()
              .setWidth( 800 )
              .setHeight( 400 );
              SpreadsheetApp.getUi() // Or DocumentApp or FormApp.
              .showModalDialog( html, 'My page title' ); 
}

function create_onEdit(){
    ScriptApp.newTrigger('myFunction')
    .forSpreadsheet(SpreadsheetApp.getActive())
    .onEdit()
    .create(); 
}

References:

  • Class TriggerBuilder