Some context
- I manage the translations of my web app though a google spreadsheet to which I invite translators
- There are 30 sheets, each represents a part of the app (big app).
- There are 14 columns on every sheets, 1 column / language.
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: ["toto@gmail.com"]
},{
langHeader: "fr",
emails: ["toto@gmail.com"]
}
...]
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 = [toto@yopmail.com, tata@yopmail.com]
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
- The function
setProtectiontakes 2s for each range to protect - I have 30 sheets * 14 columns = 420 ranges to protect.
- The whole execution exceeds maximum time allowed by google apps script (~6min)
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
- translation data has been scrubbed
- structure of the sheets are preserved, script is available in script editor.
- https://docs.google.com/spreadsheets/d/1RRGSWsCz-Wyk3kxdldb6X0meYRV5xNEZKNQtLgfVv48/edit?usp=sharing
