google apps script - Bulk update authorization for multiple spreadsheets

Question

I have about 500 spreadsheets that use the same single library.

When I authorized them (one by one, upon creation, over the corss of about a year) - the https://www.googleapis.com/auth/script.container.ui authorization was missing, as it was not needed.

Now it is needed, for a new feature.

Is there a way to bulk-update the authorizarion for all the spreadsheet without having to remove access and re-allow it one by one?

Spreadsheet bounded script:

function mainFunction(e) { // with installable onEdit trigger
  myFunctionA(e);
  myFunctionB(e);
}

function myFunctionA(e) {
  myLibrary.myFunctionA(e);
}

...

Library script:

function myFunctionA(e) {
  var htmlOutput = HtmlService
    .createHtmlOutput('<p>some text</p>')
            .setWidth(250)
            .setHeight(200);
  SpreadsheetApp.getUi().showModelessDialog(htmlOutput,'some title');
}
...

Answer 1

TL;DR

You may use the Drive API’s permission resource to get, update, revoke, and/or add permissions.

Answer

It is possible to update a file permissions programmatically by using Drive v3 API’s Permissions resource (see its documentation). This allows you to control all the permissions. I’d try to call update with no change and, if it doesn’t work, revoke and re-assign the permission with the exact same parameters using delete and create. To do so you may use any client library you like (official libraries, there are also a lot of unofficial ones).

You can also use Google Apps Script to do it. It doesn’t support it out of the box, but you can use the advanced services (see Drive service documentation). Be aware that you need to add them in the project. Also it uses Drive v2 API (see v2 permissions resource documentation) so a few things may change.

It’s also worth noting that you probably have hundreds of requests to do, especially if doing all the files in one go. To speed things up I recommend using batch request (see Drive v3 performance guide here). This is a pain to do in Apps Script as you have to manually make the HTTP requests. I’d recommend using a local application where you can use libraries easily and you don’t have a maximum time until it’s killed.

Possible algorithm outline

If a simple update without changing any parameter doesn’t work, the algorithm I’d use would look something like:

1. Get all the files that need to be updated and save their ID.
2. For every file, get the list of permissions. Make sure to get all the pages if there are more than one. Make sure that you have a backup of this list. If anything goes wrong is the only way you may have to restore the correct values programmatically.
3. Prepare the list of permissions to be revoked and re-granted.
   • Get a copy of the permission list.
   • Remove all permissions that don’t have type writer, commenter, or reader.
   • Save the ids in a list (to be revoked).
   • Save the writable fields in a list to be granted.
4. Revoke the permissions and re-grant the permissions.

The idea of making this in steps is to be able to do 5 small scripts instead of 1 big one, making it less possible to make errors. It also allows you to make backups, which is very important.

I’d do steps 1-3 for all files at the same time, so before starting to make changes you have all the data necessary prepared. On step 4, I’d revoke and re-grant them permission by permission.

References

• Drive v3 API’s Permissions resource (Google Developers)
• Google API Client Libraries (Google Developers)
• Drive Service | Google Apps Script (Google Developers)

PD: You are asking this as a workaround but I wanted to leave it answered for future reference.