Google Apps Script PERMISSION_DENIED with sheets addon

Question

I tried to implement a simple "Multi selector sidebar" extension based on THIS SHEET which I found in this Google support thread

When I copy the sheet it works fine but when I try to put the exact same code in my real sheet, it doesn't work anymore. It throws an error when I try to access the GA function from within the template.

I have created a simplified test project which also fails to work for me.

To reproduce the error:

Create a new Spreadsheet at https://docs.google.com/spreadsheets/
Create a second sheet (tab bottom left) and name it CATEGORIES
Fill in a few fields in the first column. Content doesn't matter
Got to Tools -> Script editor

In the "code.gs" enter

function doGet() {
  var html = HtmlService.createHtmlOutputFromFile('Index')
      .setTitle('Multiple selector')
      .setWidth(300);
  SpreadsheetApp.getUi() // Or DocumentApp or SlidesApp or FormApp.
      .showSidebar(html);
}

function onOpen(e) {
    SpreadsheetApp.getUi().createMenu('Index')
        .addItem('Show Sidebar', 'doGet')
        .addToUi();
        doGet();
}

function getOptions() {
  var validation = {
    sheet: 'CATEGORIES',
    range: 'A2:A'
}

  Logger.log("running getOptions");
  Logger.log(SpreadsheetApp.getActive().getSheetByName(validation.sheet).getRange(validation.range).getDisplayValues());
    return SpreadsheetApp.getActive().getSheetByName(validation.sheet).getRange(validation.range).getDisplayValues()
        .filter(String)
        .reduce(function(a, b) {
            return a.concat(b)
        })
}

And create a second file (HTML file) called Index.html

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
    <script>
      function onFailure(error) {
        var div = document.getElementById('output');
        div.innerHTML = "ERROR: " + error.message;
      }

      google.script.run.withFailureHandler(onFailure)
          .getOptions();
    </script>
  </head>
  <body>
    <div id="output"></div>
  </body>
</html>

Save the project
Click Run-> Run function -> "onOpen" (on first run you'll probably need to authorize the application)

Now in the sheet there should be a sidebar which opens with an error for me PERMISSION_DENIED

Even when I select a project at Resources -> Cloud platform project it won't work.

Oddly enough if I use the original linked (working) spreadsheet and change something in the code, it won't work anymore for me.

Things I know by now: - It doesn't work with my gmail or google apps account - For other people using the same document it works - Still doesn't work if I disable Adblocker - Doesn't work if I access the sheet from incognito mode - It does work if I use Firefox instead of Chrome

What am I missing?

1. View> show manifest file>appscript.json> see if there are any scopes. 2. What are the scopes you authorized for this app? see myaccount.google.com/permissions 3.Translate the full error to English — TheMaster
I have tried to reproduce the error but it is working for me, is there anything else I can do to reproduce it? I have found a similar case on Google's Issue Tracker, maybe it gets answered and helps you: issuetracker.google.com/issues/150247026 — Kessy
If it were beacuse of the scopes it wouldn't work for me before changing the code, right? Also it works with the same account on Firefox but not Chrome Which is even more baffling to me — Christian

Flyingmars Flyingmars · Accepted Answer · 2020-03-04T09:17:49

I have the same issue with the "permission denied" error message, and I found this

https://github.com/burnnat/page-sizer/issues/3

I think the issue is that I'm logged into multiple google accounts when I am working on this. I logged out of all google accounts, then only logged into the one account that I was trying to use formMule and it worked.

so I tried the exactly same code with incognito mode in the chrome, with only one account logged in, and it works !

I hope this can help you with your issue.

Google Apps Script PERMISSION_DENIED with sheets addon

1 Answers