Problems with permissions in google sheets add-on

Question

I have an add-on, which manipulates with data in Google Sheets. It works fine, but I am still seeing errors in Stackdriver logs for other users.

I can't replicate those errors during my tests, but the problems still exist for some users. Using try {} catch {} is not solving the problem, but just avoids getting error messages in Stackdriver logs.

Here is my code:

appsscript.json

{
  "timeZone": "Europe/Moscow",
  "dependencies": {
    "enabledAdvancedServices": [{
      "userSymbol": "Sheets",
      "serviceId": "sheets",
      "version": "v4"
    }]
  },
  "oauthScopes": [
"https://www.googleapis.com/auth/script.container.ui",
"https://www.googleapis.com/auth/script.external_request",
"https://www.googleapis.com/auth/script.scriptapp",
"https://www.googleapis.com/auth/spreadsheets",
"https://www.googleapis.com/auth/userinfo.email"
  ],
  "exceptionLogging": "STACKDRIVER"
}

Code.gs

function onOpen(e) {
    var ui = SpreadsheetApp.getUi();
    SpreadsheetApp.getUi()
        .createAddonMenu()
        .addItem('Make some stuff', 'showMakesomeStuff')
        .addToUi();
    if (e && e.authMode !== ScriptApp.AuthMode.NONE) { // It looks that the problem is here. 
        myLinkCells();
        google_analytics('file opened');
    }
}

function myLinkCells() {
    var allTriggers =[];
    var allTriggers = ScriptApp.getProjectTriggers(); \\ This line triggers error; 
    var editTriggerSet = false;
        for (var i = 0; i < allTriggers.length; i++) {
            if (allTriggers[i].getEventType() == ScriptApp.EventType.ON_EDIT) 
            {
                editTriggerSet = true;
                break;
            }
        }
    if (editTriggerSet == false) ScriptApp.newTrigger("callOnEdit").forSpreadsheet(SpreadsheetApp.getActive()).onEdit().create();
    }

function google_analytics(url) {
    UrlFetchApp.fetch('https://ssl.google-analytics.com/collect', OPTIONS); 
// This line also cause error. FYI OPTIONS is defined, but not in this example. 
}

Error message examples: "You do not have permission to call UrlFetchApp.fetch. Required permissions: https://www.googleapis.com/auth/script.external_request" or "You do not have permission to call ScriptApp.getProjectTriggers. Required permissions: https://www.googleapis.com/auth/script.scriptapp".

I looked through existing questions, but could not find anything similar.

CodingBrunette · Accepted Answer · 2019-06-13 14:23:14Z

1

It seems I nailed the bug. It had nothing to do with permission, but with check AuthMode I replaced:

if (e && e.authMode !== ScriptApp.AuthMode.NONE) { // It looks that the problem is here. 
        myLinkCells();
        google_analytics('file opened');
    }

with

if (e && e.authMode == ScriptApp.AuthMode.NONE) {
            \\Do nothing; 
        }
        else
        {
            myLinkCells();
            google_analytics('file opened');

    }

Now waiting for logs to review.

UPDATE. According to https://developers.google.com/gsuite/add-ons/concepts/editor-auth-lifecycle UrlFetchApp.fetch will not work even in AuthMode.LIMITED.

It also seems impossible to install a trigger from onOpen() function.

edited Jun 13, 2019 at 14:23

answered Jun 13, 2019 at 12:14

CodingBrunette

311 silver badge6 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Alan Wells Over a year ago

The onOpen function name is a reserved function name. The reason for using reserved function names for simple triggers is so that the user does not need to install (set up) anything in order to have a simple operation performed, like building a custom menu. But it would be a security risk to allow the code to access user data or perform actions without the user first authorizing it. Use the "post-install" tip when publishing the add-on to instruct the user what to do immediately after installing. Creating the triggers could be done when the user turns a button ON for example.

CodingBrunette Over a year ago

Thanks, @Alan, do I understand correctly that when a user installed the onEdit trigger for spreadsheet it will continue running when a user opens spreadsheet next time. Or user need to re-install trigger?

Alan Wells Over a year ago

If a function is "installed" as a trigger, the installed trigger has greater authority, and can execute services that the simple trigger can not. The installed trigger remains in effect indefinitely. Although Google has an ongoing issue with triggers failing silently. But, the user would not need to re-install the trigger if it's working as it should. If you decide to install a trigger, then you should name the function something other than onOpen() because it will be recognized as a simple trigger and also as the installed trigger.

Collectives™ on Stack Overflow

Problems with permissions in google sheets add-on

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related