0

I am trying to add a yes/no message box to a google sheet that will display on an iPad. I have tried all the things below but none of them display the message. My goal is if the user changes a field the macro will change other fields if the user says it is ok. I can get this to work on a windows machine with no issues but when I try it on an ipad the message never appears. At this point I am just trying to get a message box to appear. This is what I have tried

1.

function onEdit(e) {
     Browser.msgBox("test");
}

Result: The macros times out

2.

function onEdit(e) {
     var html = HtmlService.createHtmlOutputFromFile('Page')
      .setWidth(400)
      .setHeight(300);
  SpreadsheetApp.getUi() // Or DocumentApp or SlidesApp or FormApp.
      .showModalDialog(html, 'My custom dialog'); 
}

Page.html:
<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
  </head>
  <body>
    Hello, world! <input type="button" value="Close" onclick="google.script.host.close()" />
  </body>
</html>

I enabled the trigger for onEdit to get it to work and it works on the windows machine without issues but when run on the iPad I get: Exception: You do not have permission to call Ui.showModalDialog. Required permissions: https://www.googleapis.com/auth/script.container.ui

So next I tried:

3.

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

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

Using the same page.html, this time in executions it looks like it runs but no box appears on the ipad(it does work on windows). I also tried spreadsheetApp.getUi().alert and prompt, these had the same results. And Yes I have to use the iPad, I can not use another type of tablet.

kgsw
  • 95
  • 1
  • 10
  • 1
    You say `I enabled the trigger for onEdit`. If by that you mean that you created an installable trigger for the onEdit(e) function then that's a problem because simple triggers are there all of the time by default. If you create an installable trigger for the function onEdit(e) then currently it's firing on both the simple trigger and the installable trigger. When you create an installable onEdit trigger you should name the function different than the ones for simple triggers otherwise you will get those functions triggered twice. – Cooper Apr 01 '21 at 17:49

1 Answers1

0

Issue:

"Exception: You do not have permission to call Ui.showModalDialog. Required permissions: googleapis.com/auth/script.container.ui"

Fix:

What you can do is installing the trigger instead of using a simple trigger. The image below shows the difference between an installed (above) and a simple (below) trigger using the same code.

Output:

output

Note:

  • Don't use reserved function names on installed triggers to avoid it being executed twice like what's shown above. Change onEdit to something like promptUI or something else.
  • If the above fix isn't enough to show the dialog box, then we need to confirm if the issue might just be on the safari browser (if you are using one) since it worked on your other devices which I assumed are not using safari. You can try using an alternative browser in your device such as Google Chrome and see if the same issue persists. I recommended doing this since i'm seeing a number of issues between showModalDialog and safari

EDIT:

  • I have seen these related posts pointing out that there are some limitations on mobile apps. See the references below.

Also, some answers suggests that you need to access the spreadsheet via browser (google chrome desktop mode) and then trigger the script there.

References:

NightEye
  • 10,634
  • 2
  • 5
  • 24
  • 1
    I did name my trigger wrong so it is called twice but that doesn't help, it still doesnt show the dialog box. I do not use a browser, I use the sheets app on the ipad – kgsw Apr 01 '21 at 18:10
  • Hi @kgsw, since I don't have IOS right now, I tried it on the android counterpart. I did test a script with dialog that works on the browser but not on the app. I tried doing simple `setValue` on a specific cell but still doesnt work, even if the function is linked into a button. Script functions just doesn't get executed in the mobile app. – NightEye Apr 01 '21 at 18:49
  • I have modified my answer and provided some references regarding the issue @kgsw, kindly check them out. – NightEye Apr 01 '21 at 19:18