onOpen not executing?

1
votes

Hi guys im trying to display a sidebar on my google sheets every time it is opened. I am using the onOpen function and calling the html page, but it is not executing. When i run the function manually it works, however it doesnt execute on its own when the google sheet is opened. Here is my code: 

.gs code: 

function onOpen(){
       var html = HtmlService.createHtmlOutputFromFile('tipA')
      .setWidth(300);
       SpreadsheetApp.getUi() // Or DocumentApp or FormApp.
      .showSidebar(html); 
}

.html code: 

<!DOCTYPE html>
<html>
  <head>
  </head>
  <body>
   <div>
   <p> Some text <p> 
   <p><img src="some link" alt="xyz" width=240 height=210></p>
   </div>
  </body>
</html>
4
javascripthtmlgoogle-apps-scriptgoogle-sheets
When you say "running the function manually" do you mean using a browser debugger to run it (like chrome debugger?)? Can you give more context into what you would like to achieve? - Tran Triet
@TranTriet By "running it manually" i mean, when i go to Tools> script editor , i am able to select a specific function and run it there to test it. What i want to achieve is, when an end user opens my spreadsheet, i want a sidebar to show with some text on it. - NT-Hero

4 Answers

1
votes

A simple trigger open function can't open a sidebar because it runs on AuthMode.LIMITED. You should use a function that runs on Auth.Mode.FULL to open a sidebar, like an installable trigger.

I know that this isn't about add-ons but the following quote applies

From https://developers.google.com/apps-script/add-ons/lifecycle#authorization_modes

Note: Add-ons can't open sidebars or dialogs while executing in AuthMode.LIMITED. You can use menu items to open sidebars and dialogs since these run in AuthMode.FULL.

The cannonical references are

1
votes

Google has (quite) recently restricted the functionality of onOpen triggers. One of the restrains is the impossibility to show a sidebar. There is no real workaround since installable triggers will only work for the user that created the trigger, not for the other users. Instead you will receive a error notification each time someone opens the document. The best solution I found is to create a button that one can use to open the sidebar, this is easier than a menu and obviously more visible. There is a referenced issue on that subject : https://issuetracker.google.com/issues/69238694

0
votes

Maybe the user doesn't have permission to edit the SS? Google Developers says onOpen() only fires if the user who is visiting your sheet has permission to edit the SS.

0
votes

See if this works

function showSideBar() {
   SpreadsheetApp.getUi()
    .showSidebar(HtmlService.createTemplateFromFile('tipA')
        .evaluate()
        .setTitle('Some title'));
}

and use an installable onOpen trigger on this function. As the previous poster already mentioned, I also don't believe you can show a sidebar (which requires authorization) on a simple onOpen trigger (which doesn't require authorization).