CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheets
Xander Lon
Xander Lon

Reputation: 1

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

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?

Upvotes: 0

Views: 61

Answers (2)

ziganotschka
ziganotschka

Reputation: 26836

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.

Upvotes: 1

kirkg13
kirkg13

Reputation: 3010

I was thinking that it was some form of authorisation error, but I've recreated the problem.

I think the problem is simply that the "hello" script is not included in the new sheet, Test Table, which your "testing" script creates. So even though the User Interface has Scripts Menu added to it, and you can select "Hello!" from it, the "hello" script does not exist in that sheet, so that script from the menu just hangs.

I would have expected some other error, like "script not found", or maybe it times out eventually.

Anyway, does this make sense and does it explain your problem?

Upvotes: 0

Related Questions