Why can't I call a server function from the sidebar in Google AppScript for Sheets?

6
votes

I'm having problems calling a server-side Apps Script function from a html sidebar in Google Sheets.

I've replicated my issue with the simple example code below.

What it should do

Clicking the button should call alert in my Code.gs script and display an alert to the user.

What actually happens

Clicking the button shows the error:

We're sorry, a server error occurred while reading from storage. Error code PERMISSION_DENIED.

There are no entries in the 'executions' section of the Apps Script dashboard for alert(), so it seems like the function never even gets called?

Code.gs:

function onOpen() {
  const ui = SpreadsheetApp.getUi();
  ui.createMenu('Matthew')
    .addItem('Show Sidebar', 'sidebar')
    .addToUi();
};

function sidebar() {
  const html = HtmlService.createHtmlOutputFromFile('test.html')
    .setTitle('Matthew Experiment')
    .setWidth(300);

  SpreadsheetApp.getUi().showSidebar(html);


}

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

test.html (well the body)

  <body>
    <div class="container">
       <button class="btn btn-success" onclick="doSomething()">Click Me!</button>
    </div>
    <script>
            function doSomething() {
          google.script.run.withFailureHandler(function(error) {
            console.log("error")
            console.log(error.message)
          }).withSuccessHandler(function(result) {
            console.log("done!")
          }).alert();
        }
    </script>
  </body>

I've confirmed that the client-code is getting properly inflated with server-side function names, it's just calling the function that doesn't work.

My code seems to be the same as all of the getting started guides, but I don't see anyone else posting about this issue. Hoping someone here has deeper knowledge of the problem than I do.

Update, even weirder

I opened a private window and it worked, so I thought, maybe this is an extension causing problems? But even disabling all extensions doesn't fix it in the main window. I've logged out/in to my google account but still can't get it to work without opening a private browser window.

This problem is consistent across chrome/edge/firefox.

2
javascriptgoogle-apps-scriptgoogle-sheetsweb-applications
Related: stackoverflow.com/q/60412319 - TheMaster
According to the issuetracker, this only happens in chrome, when the global logged in user is different from the owner of script and spreadsheet. - TheMaster
I am both the owner of the script, and the owner of the spreadsheet, and it happens in FF too. :-( - Matthew Rathbone
ohoh, yes my primary user is my gmail account, and this is on my secondary account - Matthew Rathbone
@TheMaster I'll dig into that issue, you may have figured it out - Matthew Rathbone

2 Answers

8
votes

This happens when the global logged in user is different from the owner of script.

Logout all your google account and then re-login with the account you used to created the script. This fixed the issue.

0
votes

Here's a simple example of a side bar dialog communicating with server

function showMySideBar() {
  var html='<input type="button" value="Click Me" onClick="doSomething()" />';
  html+='<script>function doSomething(){google.script.run.withSuccessHandler(function(msg){window.alert(msg);}).pleaseHelp();}</script>';
  SpreadsheetApp.getUi().showSidebar(HtmlService.createHtmlOutput(html));
 }

function pleaseHelp() {
  return 'What can I do to help?';
}