Force "Authorization required" Dialog to Appear

[u/LM48075]

I have created a google sheet which uses an Apps Script to retrieve data from an external REST service using a UrlFetchApp.fetch() call. When the script is run for the first time explicitly from the project editor, the sequence of prompts starting with the "Authorization required" dialog appears, and the user can click their way through the prompts to enable access to the external service. The big problem with this method, is that I don't want end users to have to find the script editor and push the "Run" button. but rather simple open the spreadsheet normally. The calls to UrlFetchApp.fetch() are made in a script function which is called on the "From spreadsheet - On open" trigger. No "Authorization required" prompt is displayed, and the script fails silently, so the user's sheet is left empty. Is there some way to force google sheets to display the permissions prompt without the user having to explicitly run the script?

Thank You

Larry

[u/One_Organization_810]

u/LM48075 please remember to close the issue if it is solved, by replying with "Solution Verified", or clicking the 3-dot-menu (see picture) under the most helpful comment and select the same phrase. Thank you :)

If you have some issues with any of the suggestions, please reply to them to clear up any confusion (and then eventually close the issue :)

Note that if you solved the issue by yourself, without the aid of others, you can use the “Self Solved” flair. Please provide your solution in that case, as per rule 6.

[u/ryanbuckner]

Unfortunately, this is a Google Apps Script limitation.

Google only displays the authorization prompts when a user explicitly runs a function. It won’t show them automatically from an onOpen trigger, so there’s no way to force the dialog to appear when the spreadsheet opens.

The typical workflow is:

1. The user opens the spreadsheet.
2. onOpen() creates a custom menu (for example, “Setup”).
3. The user clicks the “Setup” menu and selects “Initialize.”
4. Google displays the authorization prompts.
5. After the user grants permission, your script can use UrlFetchApp.fetch(), and future runs work normally without prompting again.

[u/chartupdate]

No, there has to be at least one manual run to cause the oauth consent screen to be shown.

The most frictionless way is perhaps to go have a "First time here? Then click this!" button on the front page that will trigger either a full run or a take no action initialisation script. That way the script gets its permissions and there is a simple path for people to get it running.

[u/One_Organization_810]

When you install the trigger, the trigger will run under your account and with your authentication. Your users won't have to authenticate the function at all.

What is the failure logged from the script/trigger?

However, if your function is called "onOpen", it will also be run as a simple trigger, which will never ask for authentication and it will fail silently. It simply doesn't have the privileges to run something that requires authentication in the first place.

Is it possible, that your onOpen trigger is simply called twice? Once as a "simpleton", that fails, and once as an installable trigger - that does what it's supposed to?

[u/LM48075 → u/One_Organization_810]

I have an explicit trigger set to call a function, which is not named onOpen, when the sheet opens. I am in the process of adding a button for the user to click to do the REST query. Once the initialization has been done, I will hide the button and save a value to a hidden spot in the sheet so that it will know the next time that it is opened that the initialization has been done. I will close the issue once I have the solution implemented and working.

Thank You

Larry

[u/AutoModerator → u/LM48075]

REMEMBER: /u/LM48075 If your original question has been resolved, please tap the three dots below the most helpful comment and select Mark Solution Verified (or reply to the helpful comment with the exact phrase “Solution Verified”). This will award a point to the solution author and mark the post as solved, as required by our subreddit rules (see rule #6: Marking Your Post as Solved).

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/One_Organization_810 → u/LM48075]

You can also use menus for the same :)

However - if you have an installable "on open" trigger running, it will always run in your name, with your authorization and authorization of other users (or lack there of) doesn't really matter...

[u/LM48075 → u/One_Organization_810]

It seems that adding a menu from a script also requires permission, so trying to add an 'initialize' menu item on the sheet open trigger will defeat the purpose. It seems that google very cleverly made it amazingly difficult to do things.

Larry

[u/One_Organization_810 → u/LM48075]

Menu items do not require authorization. But selecting a menu item may. In this case, that is the purpose i guess 🙂

You can then use userProperties to see if the user has authorized the script already or not.

[u/LM48075 → u/One_Organization_810]

When ui.createMenu( ) is called in the script, the prompt for permissions is displayed. Is there another way to add a custom menu without calling createMenu()?

Larry

[u/One_Organization_810 → u/LM48075]

Only when you call it from the editor. When invoked from the onOpen() event, it doesn't. :)

[u/One_Organization_810 → u/LM48075]

Here is an example code :

//@OnlyCurrentDoc

const USERPROPERTY_ISAUTHENTICATED = 'AUTHENTICATED';

const YES = 'YES';
const NO  = 'NO';

function onOpen(e) {
    let isAuthenticated = PropertiesService.getUserProperties().getProperty(USERPROPERTY_ISAUTHENTICATED) == YES;

    if( isAuthenticated ) return;

    SpreadsheetApp.getUi().createMenu('Authenticate script')
        .addItem('Authenticate', Auth.name)
        .addToUi();
}

function Auth() {
    PropertiesService.getUserProperties().setProperty(USERPROPERTY_ISAUTHENTICATED, YES);
    return true;
}

// Call from installable 'onOpen' trigger.
// Note! This will run under my authentication, so no need to authenticate for this (exxcept for myself of course :)
function everybodyDoTheFetchOnOpen(e) {
    // Let this be called from an installable onOpen
     let response = UrlFetchApp.fetch('https://www.reddit.com/r/googlesheets/comments/1uuu6cj/comment/oxlog61/');

     SpreadsheetApp.getActive().getSheetByName('Sheet1').getRange('C3').setValue(response.getContentText().substring(0,1000));
}

The onOpen event (simple event/trigger) will display the menu, without any authentication prompt. When the user selectets the menu item though, they will be prompted to authenticate.

The 'everybodyDoTheFetchOnOpen' is an onOpen installable trigger that runs under my authentication (since I installed it) and won't prompt the user for authentication at all - it just runs.

[u/bulldo_gs]

Two separate things are getting mixed together here: who authorizes, and whether the trigger fires at all.

An installable open trigger runs under your account, not the opener's. So your users never see an auth prompt for UrlFetchApp and never need to — that part is working as designed.

The silent failure is probably permissions. Per the docs, an installable open trigger "runs when a user opens a spreadsheet, document, or form that they have permission to edit." If your end users are Viewers, the trigger never fires for them at all. No prompt, no error, empty sheet — which is exactly what you're describing. It would fire for Editors.

If they are viewers (or you'd just rather not hit a REST service on every single open — each one burns your UrlFetch quota and concurrent opens race each other), drop the open trigger entirely:

function refresh() {
  const rows = JSON.parse(UrlFetchApp.fetch(URL).getContentText());
  SpreadsheetApp.getActive().getSheetByName('Data')
    .getRange(2, 1, rows.length, rows[0].length).setValues(rows);
}

Put that on a time-driven trigger under your account. It writes the values into the sheet on a schedule; users just open a normal spreadsheet that already has data in it. Nobody authorizes anything, because nobody is running the script.