I have a spreadsheet ([example sheet and code here][1]) where I need permissions set on individual cells in column R only. These permissions are based off the current user and the value in the corresponding row in column A.
Example:
User with last name of ‘Montana’ is selected in A4. The only person that should be able to edit cell R4 is Montana. I have an array of email address I can call upon so I could easily add their email to protect R4. They should also only be able to select their initials in the dropdown in cell R4. I have an array of last names and an array of initials. So I assume I could compare those and then know what user has what initials?
function onEdit() {
var sheet = SpreadsheetApp.getActiveSpreadsheet();
//var userEmail = Session.getActiveUser().getEmail(); //Only not used for testing purposes.
//var re = /(?<=[.]).*(?=[@])/g; //Strips last name from email address of currently logged in user. For testing purposes assume logged in user has last name montana.
var re2 = /[A-Za-z]{7}[0-9]{1,2}/g; //regexp for name of protection description
//var lastName = userEmail.match(re);
var lastName = 'montana'; //using for testing purposes.
var name = sheet.getRange("A4:A52").getValues().toString().toLowerCase().split(","); //Changes last name to lowercase and splits array at commas.
var swap = sheet.getRange("R4:R52").getValues();
var setPermissions = [];
//In case any permissions were accidentally set, wanted to remove those that don't match the description set by this program.
//Doesn't pull the correction array of permissions though.
var protections = sheet.getProtections(SpreadsheetApp.ProtectionType.RANGE);
for (var i = 0; i < protections.length; i++) {
Logger.log(protections[i]);
if (protections[i].getDescription() != re2) {
protections[i].remove();
}
}
//The only person that should be able to edit the corresponding cell in Column R is those that have the matching last name of the
//currently logged in user. For testing purposes assume that logged in user is 'montana'. So if A4 is Montana, then permissions should be set for
//R4 so that the only person that can edit that is the user with that last name.
//An easier way might be to set permissions on column R as soon as a dropdown is selected from column A. I have an array of the employees email
//addresses that I could use. They should also only be able to select their own initials from column R. I can compare two arrays for that?
for (var i = 0; i < name.length; i++) {
var a = name[i];
var r = swap[i];
if (a != "" && a == lastName) {
setPermissions.push("R" + (i + 4));
Logger.log(setPermissions);
rangeString = setPermissions.toString();
var currentPermissions = sheet.getProtections(SpreadsheetApp.ProtectionType.RANGE);
var protection = currentPermissions;
var protection = sheet.getRange("R9").protect().setDescription('Testing' + i); //This needs to always be the corresponding cell in column R
var users = protection.getEditors();
protection.removeEditors(users);
var me = Session.getEffectiveUser();
protection.addEditor(me);}
}
}
}
[1]: https://docs.google.com/spreadsheets/d/19zF6fJjFtKpY_HIMLSpJh2bUJTQge4Okurti675oxIw/edit?usp=sharing