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 https://myaccount.google.com/permissions 3.Translate the full error to English — TheMaster, Feb 26 '20 at 12:21
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: https://issuetracker.google.com/issues/150247026 — Kessy, Feb 26 '20 at 14:25
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, Feb 28 '20 at 10:56

Flyingmars · Accepted Answer · 2020-03-04T09:40:12.257

12

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.

edited Mar 04 '20 at 09:40

answered Mar 04 '20 at 09:17

Flyingmars

166
1
7

3

Thanks! This really fixed it for me – Christian Mar 04 '20 at 13:27
1

it helps but it would help if we could submit this to google itself as it is in my opinion not logical that the sidebar would try to use the default account's permissions while having a sheet open with another account. – Sander Jun 16 '20 at 09:13
I agree with you. Though I have left the same comment to the same issue on the google issue tracker at the same time. (https://issuetracker.google.com/issues/150247026) , I am not sure if this will help. – Flyingmars Jun 17 '20 at 01:56

score 8 · Answer 2 · edited Aug 04 '23 at 14:03

I had the same issue. Problem is indeed caused by two or more Google accounts that are logged in into one session.

The problem is that the frontend is loaded and executed by logged in user X and the backend (Code.gs-file) is executed by logged in user Y.

So a workaround for this is to check if the user executing the backend code is the same user that is looking at the frondend code. (In this case: the frontend is your sidebar)

I found this workaround working for one of my add-ons:

Add this function to your Code.gs file. It will check if the "front end user" (initiator), that is viewing the html sidebar" is the same as the "backend user" (userEmailAddress). If it is not the same user, it will throw an error. If it is the same user, no error is thrown.

function checkMultipleAccountIssue(initiator) {

var userEmailAddress = Session.getEffectiveUser().getEmail();
if (initiator) {
// check if effective user matches the initiator (the account who triggered the display of the UI)
// Due to a Google bug, if user is connected with multiple accounts inside the same browser session
// google.script.run can be executed by another account than the initiator

 if (initiator != userEmailAddress) {
   console.error({
    message: "Client side calls initiated from wrong account",
    initiator:initiator, // user in frontend (sidebar)
    effectiveUser: userEmailAddress // user in backend (code.gs)
   });
 
 var errorMessage = "You are logged in with multiple accounts.<br>";
     errorMessage+= "This causes errors. Log out with account " + 
                   userEmailAddress;
     errorMessage+= " if you want to continue with the account: " +
     initiator;
 throw new Error(errorMessage);
 }
 else{
   return 'No conflicts found. Good to go.'
  }  
 }
}

In the frontend (html sidebar) add this bit of javascript that runs when the sidebar is loaded:

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
    <script>

var initiator = '<?!= Session.getEffectiveUser().getEmail() ?>'
        console.log('FRONTEND USER: ' + initiator)    
          google.script.run.
            withFailureHandler(function(error){
              alert(error); 
    // or prompt the user to logout! 
    //(HINT: let them click this link: https://google.com/accounts/Logout and login with the correct user.)
            })
            .withSuccessHandler(function(ret){
              console.log(ret)
            })
            .checkMultipleAccountIssue(initiator)



      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>

The part

var initiator = '<?!= Session.getEffectiveUser().getEmail() ?>'

is an apps script scriptlet that gets "injected" when the HTML is being prepared before it goes to the end user. If you want to use such scriptlets, you need to load your HTML-sidebar as a Template. To load your HTML-sidebar as templated HTML, change your doGet() function as follows:

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

More about scriptlets and templated HTML can be found here: https://developers.google.com/apps-script/guides/html/templates#code.gs

This is where I found the workaround: https://sites.google.com/site/scriptsexamples/home/announcements/multiple-accounts-issue-with-google-apps-script

You can star this issue here https://issuetracker.google.com/issues/69270374?pli=1 in order to get it resolved "faster". :)

Google Apps Script PERMISSION_DENIED with sheets addon

2 Answers2

Linked

Related