Protect a Range or a Sheet with Apps Script in Google Sheets

Google Sheets makes sharing your spreadsheets with other users easy. It only takes two or three clicks to give one of your contacts editor, commenter or view-only access to your sheet. But what if you want to go a little further? What if you want to protect parts of a sheet from being edited, but allow the rest? Maybe you have an automatic timestamp column that should be read-only, but the rest of the sheet still needs to work.

This is where protected sheets and ranges come into play. You normally add these using the menus ‘Data > Protect sheets and ranges‘.

But, what if you want to protect a row only after a box gets checked, or after all the entries are complete? It’s tiresome to use the menus to do this, but you can protect sheets and ranges using Apps Script, so you can trigger protection to activate (or de-activate) on any range, based on button clicks or whenever cells change.

Below are 3 examples of how to protect a sheet, a range of cells and how to protect all of a sheet excluding a certain range using Apps Script.

How to protect an entire sheet using Apps Script

The following example code contains a function that gets the sheet named Sheet1 and uses the protection class to protect it, and a function that removes sheet level protection for that sheet.

function protectSheet() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
  if (sheet != null) {
    var protection = sheet.protect();
    protection.setDescription('Protected sheet');
  }
}

function unprotectSheet() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
  if (sheet != null) {
    var protection = sheet.getProtections(SpreadsheetApp.ProtectionType.SHEET);
    protection[0].remove();
  }
}Code language: JavaScript (javascript)

How to protect a range of cells in Apps Script

To protect a range of cells, you first need to get a Range object relating to those cells. The sheet object makes that easy using R1C1 notation. In the following example, there are 2 functions, one to protect the range A2:D10. The second will remove all range protections from a sheet.

function protectRange() {
  // Protect range A1:B10, then remove all other users from the list of editors.
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
  var range = sheet.getRange('A2:D10');
  var protection = range.protect();
  protection.setDescription('Protected range');
}

function removeRangeProtections() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
  var protections = sheet.getProtections(SpreadsheetApp.ProtectionType.RANGE);
  for (var i = 0; i < protections.length; i++) {
    var protection = protections[i];
    if (protection.canEdit()) {
      protection.remove();
    }
  }
}Code language: JavaScript (javascript)

How to protect all of a sheet excluding a certain range

Our third example will protect an entire sheet, but exclude a certain range from that protection. This can be much quicker and safer, rather than trying to remember every part of a sheet that needs to be protected. You just remember the parts that you need to allow access to.

function protectSheetExcludingRanges() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
  if (sheet != null) {
    var protection = sheet.protect();
    protection.setDescription('Protected sheet with exclusions');
    protection.setUnprotectedRanges([sheet.getRange('A2:A10'),sheet.getRange('C2:C10')]);
  }
}Code language: JavaScript (javascript)

To remove this protection, you would use the example above for removing ‘Sheet‘ level protection.

Here is the result of running the above function:

Hopefully, one of these examples helps with your sheet project!