Search code examples
google-apps-scriptgoogle-sheetsweb-applicationspermissionsmultiple-accounts

Google Apps Script PERMISSION_DENIED with sheets addon

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:

  1. Create a new Spreadsheet at https://docs.google.com/spreadsheets/
  2. Create a second sheet (tab bottom left) and name it CATEGORIES
  3. Fill in a few fields in the first column. Content doesn't matter categories
  4. 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>

Testproject with the code

  1. Save the project
  2. 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

enter image description here

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

Console log from the browser

What am I missing?

Solution

  • 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.