Call showSidebar() from a cell in google spreadsheets

Question

As user Serge insas answered here,

Ui Dialogs can not be called by time triggered functions, they have to be triggered by a user action, that's to say a click on a menu item or some sort of button that calls the function showing the UI.

What about calling showSidebar() or showModalDialog() from a cell? Something like the following formula, which should open the sidebar automatically when the value in A1 is greater than 0:

=if(A1>0;showSidebar();"nothing to show")

Is this possible?

This is my script:

function onOpen() {
  SpreadsheetApp.getUi().createAddonMenu().addItem('Open Beeper', 'openBig').addToUi();
}

function openBig() {
  var htmlContent = HtmlService.createHtmlOutputFromFile('audioHtml').setTitle('Beeper');
  SpreadsheetApp.getUi().showSidebar(htmlContent);
}

I can open the sidebar by clicking the item in the addons menu without problems but when I call openBig() from a cell I get the following error:

Cannot call SpreadsheetApp.getUi() from this context.

What am I doing wrong? What is missing?

Not possible. Custom functions have much less permissions/authority than time triggers — TheMaster, Jan 27 '20 at 15:34

score 1 · Accepted Answer · answered Jan 27 '20 at 16:35

1

TheMaster is right. You are doing nothing wrong. Simply, you cannot call SpreadsheetApp.getUi() from the sheet itself (any cell). It is only allowed to be called from the context of the menu.

answered Jan 27 '20 at 16:35

Aerials

4,231
1
16
20

It works fine with a onEdit trigger. However if the onChange (automate) trigger is used, it sends the context error. – jparty Jan 27 '20 at 17:10
That's because the onEdit() trigger only occurs on user edits. – Cooper Jan 27 '20 at 18:05
Some people have been using the onEdit trigger to run functions from the mobile versions of Google Sheets. – Cooper Jan 27 '20 at 18:06

Call showSidebar() from a cell in google spreadsheets

1 Answers1