I have multiple Google Apps Scripts deployed as libraries and added into Google Sheets.

The problem starts when I share these sheets with other users. To even let them see the user menu load, I needed to give them edit access to my library scripts. View only access wasn't enough. Then I got the following error message from them "Exception: Service Admin SDK API has not been enabled for your Apps Script-managed Cloud Platform project"

And I know I'm getting this for using function AdminDirectory.Members.insert(...); I don't want add API privileges for them in GSuite.

The following oauthScopes are in use:

"oauthScopes": [
"https://www.googleapis.com/auth/drive",
"https://www.googleapis.com/auth/spreadsheets",
"https://www.googleapis.com/auth/documents",
"https://www.googleapis.com/auth/script.send_mail",
"https://www.googleapis.com/auth/script.external_request",
"https://www.googleapis.com/auth/userinfo.email",
"https://www.googleapis.com/auth/admin.directory.group.member"
]

What I'm looking for is a solution to create these scripts in a way that I would able to add them to my Google Sheets, where user menu could be loaded from the scripts and called by users. All script code from the library/add-on (eg,: GSuite create new user / add user to group / open, edit docs / open, edit sheets) should be run through my credential, not with theirs. Users shouldn't be able to view/edit the libraries/add-on scripts' code.

The only code they should able to see and edit is the tiny onOpen script in the Sheet's script file. They have to have access to the script library / add-on but only to run it, call functions from it. They shouldn't be able to read and edit the library/add-on script code. Their code in the spreadsheet would be really small. Just an onOpen trigger which would load the menu from the script and give access to the main functions which could be called from the menu. Those would be public functions. The rest is private.

Script from Sheet

Library called "Script"


This is how my script is loading the menu in Sheets and calls the functions through it.

CodePudding user response：

Issue:

If I understand your situation correctly:

• You have an editor add-on through which you want to execute functions that access Admin SDK (via a custom menu).
• You want users in your domain, which do not have privileges to access Admin SDK, to be able to execute these functions.
• You are a domain admin and can access the desired Admin SDK services.

You won't accomplish this by placing your code in a library, since there's no way to "delegate" the execution of a library function: the library function will run under the authority of the user executing the menu function.

Solution:

In that case, I'd suggest the following alternative:

1. Create a service account.
2. Follow this guide to grant the service account domain-wide delegation, so that it can be used to impersonate any account in your domain: in this case, your account.
3. Import and use the library OAuth2 for Apps Script in order to use the service account in your Apps Script project.
4. Use UrlFetchApp to call your desired API, using the access token from the service account.

Code sample:

For example, if you wanted to call Directory API's users.get to retrieve data from the user currently executing this, you would do something like this:

function getService() {
  const service = OAuth2.createService("Service account")
                    .setTokenUrl('https://accounts.google.com/o/oauth2/token')
                    .setPrivateKey(SERVICE_ACCOUNT_PRIVATE_KEY)
                    .setIssuer(SERVICE_ACCOUNT_EMAIL)
                    .setSubject(IMPERSONATED_EMAIL)
                    .setPropertyStore(PropertiesService.getScriptProperties())
                    .setParam('access_type', 'offline')
                    .setScope('https://www.googleapis.com/auth/admin.directory.user')
  return service;
}

function getActiveUserData() {
  const service = getService();
  if (service.hasAccess()) {
    const userKey = Session.getActiveUser();
    const url = `https://admin.googleapis.com/admin/directory/v1/users/${userKey}`;
    const options = {
      headers: {
        'Authorization': "Bearer "   service.getAccessToken(),
        'Content-Type': 'application/json'
      },
      muteHttpExceptions: true
    }
    const resp = UrlFetchApp.fetch(url, options);
    const userData = JSON.parse(resp.getContentText());
    return userData;
  }
}