Google Apps Script slow applying sheet/range protection

Question

I am building a set of spreadsheets using Google Apps Script. Each spreadsheet is being created with a set of 10 sheets. Now that the all seems working, I have added some code to protect ranges, however this is dramatically increasing script running time. The function is being invoked multiple times: on every sheet, the range protection function is being invoked say 10/15 times, with a total of some 150 times. Execution will whop from 76s without invoking range protections, up to 189s when protections is invoked. Is there anything that can be done to improve this?

Here's the code:

function protectRange(range, description){
  // Protect range, then remove all other users from the list of editors.
  var protection = range.protect().setDescription(description);

  // Ensure the current user is an editor before removing others. Otherwise, if the user's edit
  // permission comes from a group, the script throws an exception upon removing the group.
  var me = Session.getEffectiveUser();
  protection.addEditor(me);
  protection.removeEditors(protection.getEditors());
  if (protection.canDomainEdit()) {
    protection.setDomainEdit(false);
  }
}

Answer 1

The SpreadsheetApp calls are very expensive in terms of execution time, so considering the number of calls that applying the protection implies I don't think that it could be possible to reduce the execution time by doing slight changes.

One alternative is the use the Sheets Advance Service and batch update operations.

Note

Class RangeList doesn't include a method to apply protections to the ranges included in the range list.

Related

• Setting multiple protected ranges with one range value inputted