I am using the below script to protect a Google Sheet (script runs on opening the sheet). The protection will be applied for all users except the sheet owner i.e. only the owner will have the access to the protected range. I want to change the protection criteria i.e. the owner and specific user based on email can get access to protected range.

function installedOnOpen(e) {
const sheetNames = ["Sheet1"]; // Please set the sheet names you want to protect.
const sheets = e.source.getSheets().filter(s => 
sheetNames.includes(s.getSheetName()));
if (sheets.length == 0) return;
sheets.forEach(s => {
const p = s.getProtections(SpreadsheetApp.ProtectionType.RANGE);
if (p.length > 0) {
  p.forEach(pp => pp.remove());
}
const lastRow = s.getLastRow();
if (lastRow != 0) {
  const newProtect = s.getRange(1, 1, lastRow, s.getMaxColumns()).protect();
  newProtect.removeEditors(newProtect.getEditors());
  if (newProtect.canDomainEdit()) newProtect.setDomainEdit(false);
 }
 });
 }

Any help on above will be appreciated.

CodePudding user response：

I believe your goal is as follows.

• You want to protect the cells. In this case, you want to edit the cells by the owner and other specific users.

In this case, how about the following modification?

Modified script:

function installedOnOpen(e) {
  const editors = ["###"]; // Added: Please set the email addresses you want to give the permission as the editor.

  const sheetNames = ["Sheet1"]; // Please set the sheet names you want to protect.
  const sheets = e.source.getSheets().filter(s => sheetNames.includes(s.getSheetName()));
  if (sheets.length == 0) return;
  sheets.forEach(s => {
    const p = s.getProtections(SpreadsheetApp.ProtectionType.RANGE);
    if (p.length > 0) {
      p.forEach(pp => pp.remove());
    }
    const lastRow = s.getLastRow();
    if (lastRow != 0) {
      const newProtect = s.getRange(1, 1, lastRow, s.getMaxColumns()).protect();
      newProtect.removeEditors(newProtect.getEditors());
      newProtect.addEditors(editors); // Added
      if (newProtect.canDomainEdit()) newProtect.setDomainEdit(false);
    }
  });
}

• In this modification, it supposes that the function installedOnOpen is run by the installable OnEdit trigger. Please be careful about this.

Reference:

• addEditors(emailAddresses)