Can I lock a particular Google Apps worksheet using a script?

Question

Is there a way to lock a particular Google Apps worksheet using a script?

I have this code that renames the current spreadsheet to whatever you enter in the input box.

// The code below will rename the active sheet to what you enter in the input box
var myValue = Browser.inputBox("Enter: LastMonth - Year");
SpreadsheetApp.getActiveSpreadsheet().renameActiveSheet(myValue);
SpreadsheetApp.getActiveSpreadsheet().getRange("A1").setValue(myValue);

What can I add to the code above that will lock that same worksheet i just renamed only allowing me to edit that worksheet. Is that possible?

Erenow

Answer 1

// Enables sheet protection for  this sheet
var sheet = SpreadsheetApp.getActiveSheet();
var permissions = sheet.getSheetProtection();
permissions.setProtected(true);
sheet.setSheetProtection(permissions);

See Spreadsheet Services - Class PageProtection

Underwater

Can i lock that same worksheet i just renamed and specify the username that has access to edit the worksheet or does it only using the current logged-in user? – Erenow

Answer 2

Since Class PageProtection is currently deprecated you may use the Class Protection to achieve the same:

var sheet = SpreadsheetApp.getActiveSheet();
sheet.protect();

To unprotect when needed you may use the following code snippet:

var protection = sheet.getProtections(SpreadsheetApp.ProtectionType.SHEET)[0];
 if (protection && protection.canEdit()) {
   protection.remove();
 }

Lianne

Answer 3

Code below found in Spreadsheet Services, as @ScampMichael said.

Adds a user to the list of users who can edit the sheet, if it is protected.

// Add the "[email protected]" user to the list of users who can edit this sheet
 var sheet = SpreadsheetApp.getActiveSheet();
 var permissions = sheet.getSheetProtection();
 permissions.addUser('[email protected]');
 permissions.setProtected(true);
 sheet.setSheetProtection(permissions);

Mithras

Answer 4

There are two types of protections offered by the modern Protection class (as is the case in the UI):

• A protection which displays a warning when anybody attempt to edit a cell.
• A protection which denies write access to certain users altogether.

Although the second option sounds ideal, there is an important caveat:

Neither the owner of the spreadsheet nor the current user can be removed.

For my usecase of wanting to discourage anybody from editing the sheet while the script is running, I found the best solution to be to add a protection warning, and hide the sheet:

function lockSheet_(sheet) {
  sheet.hideSheet();
  const protection = sheet.protect();
  protection.setDescription('MyApplication Lock');
  protection.setWarningOnly(true);
  return protection;
}

function unlockSheet_(sheet, lock) {
  lock.remove();
  sheet.showSheet();
}

Usage:

  const lock = lockSheet_(sheet);

  // ...

  unlockSheet_(sheet, lock);

Rufe