Reputation: 9088
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
setProtection
takes 2s for each range to protect 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
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