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

Question

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.

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, Feb 27 '20 at 18:06
I am both the owner of the script, and the owner of the spreadsheet, and it happens in FF too. :-( — Matthew Rathbone, Feb 27 '20 at 18:14
ohoh, yes my primary user is my gmail account, and this is on my secondary account — Matthew Rathbone, Feb 27 '20 at 18:15
@TheMaster I'll dig into that issue, you may have figured it out — Matthew Rathbone, Feb 27 '20 at 18:15
From the code editor, manually run the `alert()` function -- does it work? Then try the sidebar again. — IMTheNachoMan, Feb 27 '20 at 19:59
@MatthewRathbone was [this](https://stackoverflow.com/q/60412319) eventually what caused this issue? Try using your primary email account to see if that actually works. — Mateo Randwolf, Feb 28 '20 at 11:24
I have a strong suspicion this is a cookie permission issue. Especially cause browsers have been tightening security around cookies. I've also had issues with GAS projects when I'm logged into multiple Google accounts. I don't know how to test this theory though... — IMTheNachoMan, Feb 28 '20 at 15:04

score 8 · Accepted Answer · answered Mar 01 '20 at 07:37

8

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.

answered Mar 01 '20 at 07:37

Hujie Wang

96
1

1

this is such a terrible bug and means the sidebar is almost useless unless you're building a fully fledged add-in – Matthew Rathbone May 15 '20 at 17:28

score 1 · Answer 2 · answered Feb 27 '20 at 20:56

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?';
}

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

What it should do

What actually happens

Code.gs:

test.html (well the body)

Update, even weirder

2 Answers2

Linked

Related