Persistent Error in Authorisation Flow, for Spreadsheet bound script.
Thomas P.
Only one final problem, which eludes me: Authorisation fails!
The Spreadsheet has an "About"-sheet, where there's a "Setup"-button.
- "Setup"-button, meaning: An image, bound to a function named buttonInitialAuthorisationDance
When Clicked, then Google's ordinary authorisation flow is invoked, BUT it consistently fails at first attempt in different variations, ranked best to worst.
Variation.1 : Script throws a "Script buttonInitialAuthorisationDance experienced an error".
Variation.2 : Script is running for minutes!, before throwing the "Script buttonInitialAuthorisationDance experienced an error".
Variation.3 : Click the "Setup"-button again (after encountering the error) usually makes everything go as planned, but! I have, though only once, seen Google's first authorisation flowpage start w. a 404 ?!? (and further subsequent attempts to authorise will not work)
In case, you want to have the experience yourself, then the Speadsheet in question is public at: https://docs.google.com/spreadsheets/d/1bJj60-LQYr02hkTjyVevOpOQcmDMLdhDEGtwAsDRB3s
But, otherwise, then, here's the relevant code which is in the project's only file.
/** @OnlyCurrentDoc */
// ...
function buttonInitialAuthorisationDance(){
const oWorkbook = SpreadsheetApp.getActiveSpreadsheet();
const oSheetAbout = oWorkbook.getSheetById(0); //The "About"-Sheet
if(0 === PropertiesService.getUserProperties().getKeys().length){
//OK, Truely initial trip around the block.
let oSheetInstructions = oWorkbook.getSheetByName("Instructions");
oSheetInstructions.showSheet(); oWorkbook.setActiveSheet(oSheetInstructions);
oSheetAbout.hideSheet();
PropertiesService.getUserProperties().setProperty("sInstructionsSheetID", oSheetInstructions.getSheetId().toString());
} else
{
oWorkbook.toast("No need for doing the Initial Configuration & Authorisation-dance again.\nSo, just now: Nothing happened.","Has previously been done!?!",30);
};
};
// ...
Keith Andersen
My website: https://sites.google.com/view/klaweb/
Passions: God, Family, Scriptures, Learning, Data Management, Google Sheets + App Script and much more!
--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion visit https://groups.google.com/d/msgid/google-apps-script-community/71252ad4-2f01-4115-9a75-c970286d4b4fn%40googlegroups.com.
Thomas P.
Keith Andersen
My website: https://sites.google.com/view/klaweb/
Passions: God, Family, Scriptures, Learning, Data Management, Google Sheets + App Script and much more!
To view this discussion visit https://groups.google.com/d/msgid/google-apps-script-community/258c80a0-584a-42e3-a169-f52ed654c785n%40googlegroups.com.
Kildere S Irineu
This is a classic and very subtle Google Apps Script authorization issue that trips up even experienced developers. Your detailed report is excellent, and you've correctly identified the symptoms of a core platform behavior.
The short answer is: You cannot trigger an authorization flow for the first time by clicking an image or drawing that has a script assigned to it.
Let's break down exactly why this is happening and how to fix it correctly.
The Core Problem: Execution Contexts and Permissions
Google Apps Script runs functions in different "contexts," and each context has different permissions. This is a crucial security feature.
Simple Trigger Context: When a user clicks an image or drawing, the assigned function runs in a very limited context, similar to simple triggers like onOpen(e) or onEdit(e). In this mode, the script is not allowed to perform any action that requires user authorization. This is to prevent a malicious spreadsheet from, for example, reading a user's emails (GmailApp) or files (DriveApp) the moment they click a seemingly harmless button, without ever showing them a permission dialog.
Full Authorization Context: When a user runs a script from the Custom Menu, the Script Editor, or a Sidebar/Dialog, the script runs in a full-permission context. In this mode, if the script encounters a service that needs authorization (like PropertiesService, Sheet.hideSheet(), etc.), it knows how to correctly stop and present the pop-up authorization dialog to the user.
Your buttonInitialAuthorisationDance function attempts to call two restricted services from a "Simple Trigger Context":
PropertiesService.getUserProperties(): Requires permission to store data on a per-user basis.
oSheetAbout.hideSheet(): Requires permission to modify the structure of the spreadsheet.
When you click the image, the script starts, hits PropertiesService, realizes it's not authorized, and because it's in a context that cannot launch the auth dialog, it simply fails with a generic error.
Why Does it Work on the Second Click Sometimes?
This is the tricky part. The first failed attempt sometimes "primes" the system. The user might then try running the script from the editor or a menu, which does successfully complete the authorization flow. When they return and click the button again, the script already has the necessary permissions, so the restricted services can run without needing to ask, and everything works. The 404 error you saw is likely the result of an auth token being generated and then becoming invalid before the flow could complete, leading to a broken state.
The Solution: Use a Custom Menu for Initialization
The Google-approved and standard way to handle one-time setup and authorization is through a custom menu item. This provides the correct context to trigger the authorization flow reliably.
Here is how you should restructure your code for a robust and error-free user experience.
Step 1: Create an onOpen Function to Add a Menu
This function will create a menu in the spreadsheet's UI every time it's opened.
/**
* Runs when the spreadsheet is opened.
* Adds a custom menu to the UI for initialization and other actions.
*/
function onOpen() {
SpreadsheetApp.getUi()
.createMenu('🛠️ My Tool Setup') // Give your tool a name
.addItem('Initial Configuration & Authorisation', 'initialAuthorisationDance')
.addToUi();
}```
#### Step 2: Rename Your Button Function and Keep it Separate
Your core logic is perfect. We will just rename the function to make its purpose clear. It will now be called exclusively from the menu.
```javascript
/**
* Handles the initial setup and authorization for the tool.
* This function should be called from the custom menu.
*/
function initialAuthorisationDance() {
// Check if the setup has already been run
if (PropertiesService.getUserProperties().getProperty("setupComplete")) {
SpreadsheetApp.getUi().alert("Setup has already been completed. No action was taken.");
return; // Exit the function
}
// --- Start of Setup Logic ---
try {
const oWorkbook = SpreadsheetApp.getActiveSpreadsheet();
// Show a message to the user that the process is starting
oWorkbook.toast("Starting initial setup...", "Please Wait", 5);
// Get the sheets
const oSheetAbout = oWorkbook.getSheetById(0); // Assumes "About" is the very first sheet
let oSheetInstructions = oWorkbook.getSheetByName("Instructions");
// Perform the sheet operations
oSheetInstructions.showSheet();
oWorkbook.setActiveSheet(oSheetInstructions);
oSheetAbout.hideSheet();
// Set properties to remember the state
PropertiesService.getUserProperties().setProperty("sInstructionsSheetID", oSheetInstructions.getSheetId().toString());
PropertiesService.getUserProperties().setProperty("setupComplete", "true"); // Flag to prevent re-running
// Success message
SpreadsheetApp.getUi().alert("Setup and authorization complete! The tool is ready to use.");
} catch (e) {
// Catch any errors and show a user-friendly message
SpreadsheetApp.getUi().alert(`An error occurred during setup: ${e.message}`);
}
}Step 3 (Optional but Recommended): Change the Button's Function
You can keep your "Setup" button on the "About" sheet, but it should not try to run the authorization itself. Instead, it should guide the user to the correct place.
Assign this new, simple function to your button image:
/**
* This function is assigned to the "Setup" button/image.
* It guides the user to the custom menu to perform the actual setup.
*/
function guideUserToMenu() {
SpreadsheetApp.getUi().alert('To complete the initial setup, please use the "🛠️ My Tool Setup" menu at the top of the screen and select "Initial Configuration & Authorisation".');
}Summary of the Correct Workflow
User opens the Spreadsheet. The onOpen function runs and creates the "🛠️ My Tool Setup" menu.
User navigates to the "About" sheet and clicks your "Setup" button.
The guideUserToMenu function triggers, showing a pop-up that instructs them to use the custom menu.
User clicks the menu item: 🛠️ My Tool Setup > Initial Configuration & Authorisation.
This calls the initialAuthorisationDance function from the correct context.
Google's authorization dialog appears reliably. The user grants permission.
The rest of your script (hiding/showing sheets, setting properties) executes flawlessly.
If the user tries to run it again, the check at the top of the function prevents the setup from re-running.
By adopting this menu-driven approach for initialization, you align with Google's security model and will eliminate the authorization errors completely.
To view this discussion visit https://groups.google.com/d/msgid/google-apps-script-community/CAFKgK%2BENyCLNmSrV%2BUxdGD8XQtiZwD6ZPBGXX%3D3mt2MB9o5OMA%40mail.gmail.com.
Kildere Sobral Irineu
Analista e Desenvolvedor de Sistemas e Agentes de IA
Administração de Empresas – MBA Gestão
Thomas P.
Thank you very much!
Setting up a menu setup is what I've done in the past (for other spreadsheets), but for this particular simple spreadsheet, then I wanted a menu-free solution.
- I guess the old "One cannot always get, what one wants."-saying may have reared its ugly face.
Why Does it Work on the Second Click Sometimes?
This is the tricky part. The first failed attempt sometimes "primes" the system. The user might then try running the script from the editor or a menu, which ...
(always*: I'm regarding the single 404-experience, which I've had, as a temporary Google glitch.)
Thomas P.
// function provokeError();// Returns: Boolean ; False, if no error was provoked.
// Notes:
// - Only! Called from: function buttonInitialAuthorisationDance()
// - Attempting to provoke Error: "Script <NameOfFunction> experienced an error"
// - Background: "Persistent Error in Authorisation Flow, for Spreadsheet bound script."
// : https://groups.google.com/g/google-apps-script-community/c/qS05B1psKT0/m/hPli9nCSAQAJ
function provokeError(){
let fErrorCaught; //The return value.
const oSheetAbout = SpreadsheetApp.getActiveSpreadsheet().getSheetById(0); //The "About"-Sheet
try{
PropertiesService.getUserProperties().setProperty("DummyKey","DummyValue");
PropertiesService.getUserProperties().deleteProperty("DummyKey");
if(oSheetAbout.isSheetHidden()) oSheetAbout.hideSheet(); else oSheetAbout.showSheet(); //No Visible effect: Hide if already hidden, Show if already shown.
//Error Provokation must now have failed, i.e.: Authorisation Flow has been done, OR HAS JUST BEEN INVOKED (and then completed)
fErrorCaught = false;
} catch(oError) { fErrorCaught = true; };
return fErrorCaught;
};