0

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);
  }
}
  • The second line return; makes that the function do nothing. How is the function being called? – Rubén Oct 18 at 13:07
  • Removed. It was left for debugging purposes! (edited) – Riccardo Oct 18 at 13:22
  • 1
    I supposed that. What about the second part of my previous comment? ->How is the function being called? – Rubén Oct 18 at 13:25
  • For instance: protectRange (gridRange,subject+" main grid markings"); – Riccardo Oct 18 at 13:34
  • Is it not included on a loop (for, do, while...)? Please edit the question to add all the relevant details. Also include one execution transcript sample. – Rubén Oct 18 at 13:42
0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.