'Apps Script google.script.run does not work within a Library-bound html file when triggered by other users

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!

javascripthtmlgoogle-apps-scriptgoogle-sheets


Solution 1:[1]

Library permissions:

Script users (spreadsheet owners/editors) should have edit or view access to the library in order to be able to execute the library code.

Effective user:

Make your script able to detect if the google.script.run effective user is the same as the active user and when they were different, suggest your users to

  • create and use an specific web browser profile for the account to be used
  • access the spreadsheet using the incognito / private mode and only sign-in in a single Google account

You also could share the library and the related files with anyone with the link instead of only with some users.

NOTES:

The effective user that interacts with the client-side code of a dialog/sidebar will be the "active user" not the user who created the installable trigger. The "active user" could even be different than the active user in the Google Sheets UI as it will be the default user according to the Google multi-account sign-in feature. This is an issue that have being reported to Google several years ago.

If you don't want to give permissions to the spreadsheet users to run the library code, then instead of using google.script.run you should use another way to call the server side code. One way is to deploy a web app and call it by doing a post http request by using the URL Fetch service.

Related

Libraries permissions

Multiple signed-in accounts

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1

Related Questions