CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheets
edelans
edelans

Reputation: 9088

Protect a range with apps script takes too much time

Some context

What I want to do

As I already had twice a problem with translators editing the wrong column by mistake, I want to set protected columns to restrict edition for each translator only to the column of his language (1 translator = 1 email address granted access to the spreadsheet).

How I did it

Setting it manually is a pain (repetitive task), and must be done again if a translator change. So I wrote a script for it.

How i stored the permissions : 

var ProtectionsDefinitions = [{
  langHeader: "en",
  emails: ["[email protected]"]
},{
  langHeader: "fr",
  emails: ["[email protected]"]
}
...]

Pseudo code : 

For every sheet:
    For every language:
        Protect the column whose header match the langHeader

Real code for the function that do the real work : 

function setProtection(range, rangeDesc, emails) {
  // range = class range
  // rangeDesc = string (description for protected range)
  // emails = [[email protected], [email protected]]

  var protection = range.protect(); // Creates an object that can protect the range from being edited except by users who have permission. 
                                    // Until the script actually changes the list of editors for the range
                                    // the permissions mirror those of the spreadsheet itself, which effectively means that the range remains unprotected.
  protection.removeEditors(protection.getEditors()); // this takes more than 1s ! 
  protection.setDomainEdit(true);  // all users in the domain that owns the spreadsheet have permission to edit the protected range
  protection.setDescription(rangeDesc);
  if(emails.length > 0){
    // this takes more than 1s !!
    range.getSheet().getParent().addEditors(emails); //  give the users permission to edit the spreadsheet itself, required for protection.addEditors()
    protection.addEditors(emails); // give the users permission to edit the protected range
  }
}

Why it's not satisfying

I traced the lines that takes a lot of times thanks to the logging tool, see comments in the function.

I wonder if I can do something to make it work.

Example spreadsheet

Upvotes: 2

Views: 370

Answers (1)

Wicket
Wicket

Reputation: 38469

You could apply to the Early Access Program or rather than process all the sheets/columns with a single script call, split the job.

Ideas for solutions:

Related Q&A

Upvotes: 1

Related Questions