How editors in google sheets can run scripts in installed by app script menu

Question

There is a code that examples type of my problem:

function testing(){
  let ss = SpreadsheetApp.create("Test Table");
  ss.addEditor("[email protected]");
  ScriptApp.newTrigger('button')
      .forSpreadsheet(ss)
      .onOpen()
      .create();
}

function button(){
  SpreadsheetApp.getUi()
      .createMenu("Scripts Menu")
      .addItem('Hello!', 'hello')
      .addToUi();
}

function hello(){
  SpreadsheetApp.getUi().alert("Hello World!");
}

In that code, that runs in google app script bounded to a SpreadSheet, I create a SpreadSheet, give an editor rights to another user, then set an installed onOpen trigger that creates Menu that fire Hello script. I want that when this editor click on the menu item hello in that spreadsheet he will see Hello! message. But that's not happen. Instead it just loading a script without ending. But when I run it under my account it works just fine. Could anybody point to me where is the mistake, or maybe i am just missleaded in something, and how i can fix it?

ziganotschka ziganotschka · Accepted Answer · 2020-11-20T09:11:10

Please have a look at the documentation:

getUi()

Returns an instance of the spreadsheet's user-interface environment that allows the script to add features like menus, dialogs, and sidebars. A script can only interact with the UI for the current instance of an open spreadsheet, and only if the script is bound to the spreadsheet. For more information, see the guides to menus and dialogs and sidebars.

In other words, you can only use getUi() if your script is bound to a spreadsheet
And whatever getUi() does (e.g. creating a custom menu) applies only to the current, bound spreadsheet - not to a new spreadsheet your code creates.

How editors in google sheets can run scripts in installed by app script menu

2 Answers

Please have a look at the documentation: