I have an authorization problem, but I can't figure it out.
- I have 50+ spreadsheets each for a separate user in our company.
- Each user spreadsheet has a container-bound script that accesses a standalone library called "Library" where all my code is hosted.
- The user bound scripts all have installable triggers (onEdit and onOpen) on them so that the library code is run as the admin user, not the individual users.
- Therefore each user is given google drive access to their own spreadsheet, but not the library or other spreadsheets that the library accesses.
- This makes it easy to update the code for all users, and so that the individual users do not need direct access to the central resources.
This setup has worked great for us for years, but now I'm trying to implement dynamic forms using html dialogues.
The problem I'm having is that google.script.run is working great for the admin user that is the owner of all the resources, but not for the individual users who only have access to their own spreadsheets.
Here's the file structure with simplified code:
User's container-bound script "Codes.gs" contains:
function userOnEdit(event) {
var range = event.range;
if (range.getValue() === true && range.getSheet().getName() == "Mileage Form" && range.getA1Notation() == "F1"){
Library.testHTML();
}
}
function getData(){
return Library["getData"].apply(this, arguments);
}
The Library's "Codes.gs" contains:
function testHTML(){
var html = HtmlService.createTemplateFromFile('testDialogue').evaluate()
SpreadsheetApp.getUi().showModalDialog(html, 'Test Submittal');
}
function getData(){
return "myData";
}
The Library's "testDialogue.html" contains:
<!DOCTYPE html>
<html>
<head>
<base target="_top">
</head>
<body>
<div class="container">
<label for="response" class="form-label" id="response_Label">Loading...</label>
</div>
</body>
<script>
function dataLoaded(data){
document.getElementById("response_Label").innerHTML = data;
}
function onFailure(error){
document.getElementById("response_Label").innerHTML = "ERROR: " + error.message;
}
function afterDialogueLoads(){
google.script.run.withSuccessHandler(function(data){dataLoaded(data)}).withFailureHandler(onFailure).getData();
}
document.addEventListener('DOMContentLoaded', afterDialogueLoads);
</script>
</html>
Within the user spreadsheet, I am using a checkbox in the cell 'Mileage Form'!F1. When that checkbox is marked TRUE, the userOnEdit is triggered and run using the admin's credentials since it's an installable trigger created by the admin. If the admin has the user's spreadsheet open and triggers the checkbox, then the html modal dialogue pops up with "Loading..." and after about 5 seconds it changes to "myData", which is exactly the expected behavior based on this code.
However, when the user (not the admin) has the spreadsheet open and checks that checkbox, the html modal dialogue pops up with "Loading..." but then the withFailureHandler triggers and results in this error: "ERROR: Authorization is required to perform that action."
And I can't figure out how to grant authorization for google.script.run as the admin via the installable trigger. Usually this is requested when the trigger is installed, but when I remove the trigger and add it back, it doesn't request an additional authorizations. Here's my oathScopes: "oauthScopes": ["https://www.googleapis.com/auth/script.projects", "https://www.googleapis.com/auth/script.container.ui", "https://www.googleapis.com/auth/script.external_request", "https://www.googleapis.com/auth/spreadsheets", "https://www.googleapis.com/auth/drive", "https://www.googleapis.com/auth/gmail.send", "https://www.googleapis.com/auth/gmail.compose", "https://www.googleapis.com/auth/gmail.modify", "https://www.googleapis.com/auth/script.scriptapp"]
Please help!
