Google Apps Script – AuthMode

I mean, when I run it normally, it works just fine!

This is an interesting problem I came across recently when reviewing one of my Google add-ons.

The Problem

The issue was that when I ran my Google Apps Script add-on code normally – in my case, connected to a Google Sheet – the add-on would appear just fine in my add-on menu. However, when I tested the script as an add-on ( run >>> Test as add-on…), as installed for current user but not enabled, my app would not appear in the add-on menu.

If I were to publish this app in this condition (and I may have…eep!), the user would be able to see my add-on in the add-on menu but not be able to access the link to run the app’s function without going into Add-ons >>> Manage add-ons… then click on Manage >>> Use this add-on. The user would have to do this each time they want to apply my add-on to a new Google doc for the first time. Not a good user experience.

The reason is that the first time the app is run in a new document, the Authorization Mode is set to none:

AuthMode.NONE

When the user opens the document for the first time, the onOpen(e) trigger function is loaded along with any global variables that are in the script.

This is what Google Apps Script has to say about this:

So the problem was that one of my Global Variables did not abide by the scope of AuthMode.NONE.

How about we look at some example code to see how to identify the problem and work towards a solution.

The Example

In this example,  I have created a simple Google Sheets Add-on that displays (alert) the correct answer to a question I pose in Google Sheets.  This is what it is supposed to do. And it does if you are running it normally.

(Note: add-ons is now labelled Extensions)

When I first created the code and ran it for the first time, I went through all the obligatory authorization requests and the big scary warnings that you should be used to by now when you create and run a script for the first time.

So when I open the Google Sheet attached to my script normally its Authorization mode is AuthMode.LIMITED. This means that some of my cheeky global variables that request access to my data are available to me without invoking an error or script failure warning.

However, when I test the code and run it as an Add-on that is installed, but not enabled, then those cheeky global variables get a hard no from Google Apps Script.

Let’s take a look at the ‘bad’ code and run through identifying the offending Global Variable.

The Bad Code

Okay, time to test this code as an add-on how the user will see it for the first time, installed, but not enabled (AuthMode.NONE).

When my Google Sheet loads and I go to select the add-on, it isn’t there.

On the Sheet page, hitting F12 for Dev Tools and navigating to the console, I can see the error:

Google Apps Script: You do not have permission to perform that action

Cool. Thanks, Google. I will go back to my script and check our my Google Apps Script and click View >>> Execution transcript.

This is what comes up:

[19-02-21 22:01:47:208 PST] Execution failed: You do not have permission to perform that action. (line 11, file “Code”) [0 seconds total runtime]

Noice! At least I know where the error occurred. It looks like I can’t call SpreadsheetApp.getActiveSpreadsheet() as a Global when the add-on is installed but not enabled.  Fair Enough.

var ss = SpreadsheetApp.getActiveSpreadsheet(); -Line 11

Looking at the Google Apps Script Docs on Add-ons I can now see that, in AuthMode.NONE, I don’t have permission to access the document.

However, it does say I have access to create a menu item so my onOpen function should be fine.

The Solution

It looks like we need to do one of two things:

1. Move the offending global variable out of the global variables and into the runAlert_()function.
2. Turn the SpreadsheetApp.getActiveSpreadsheet() into its own function so that I can continue to use it as a global ‘variable’ should I add to this project with other functions that may call on it.

The Code – Local Variable Solution

This will handle the error and allow the add-on to run successfully creating the menu item in preparation to run the add-on.  However, it is not particularly efficient if I want to use that SpreadsheetApp.getActiveSpreadsheet() a lot more in other functions down the track.

The Code – Global-ish as a Function Solution

Here, we have changed our ss and ui (ui is probably not necessary) to functions. We will use them the same way, but as a function, when the document is loaded, it won’t initialise the SpreadsheetApp in both these cases. This will only occur when they are called in the functions.

Conclusion

Well..um…read the docs. Do as I say, not as I do and all that. Jeez!

Although I had gone through the docs at the time, creating an add-on for the first, or even the second time, can be a pretty convoluted task to ensure that you meet the standards necessary for scopes, authorization and publishing standards.

I hope that if you have come across this problem in your own Google Add-on development and stumbled upon this post, that this has been of some benefit to you.

Here is a link to the Google Sheet. Make a copy and try out the code!

Why Add-on Menu No Loady Loady?

Need help with Google Workspace development?

Go something to solve bigger than Chat GPT?

I can help you with all of your Google Workspace development needs, from custom app development to integrations and security. I have a proven track record of success in helping businesses of all sizes get the most out of Google Workspace.

Schedule a free consultation today to discuss your needs and get started or learn more about our services here.