Protecting Cells of Spreadsheet by Clicking Checkbox using Google Apps Script

submit.md

Protecting Cells of Spreadsheet by Clicking Checkbox using Google Apps Script

This is a sample script for protecting the cells of a Spreadsheet by clicking a checkbox using Google Apps Script.

You might have a situation where you want to protect the cells when a user checks a checkbox. This sample script is for achieving this situation. The demonstration of this sample script is as follows.

• This demonstration is for a user. You can see that when the checkbox is checked, the checkbox and the right side of the checkbox are protected.

  

• This demonstration is for the owner of Spreadsheet. The owner can manage the checkboxes. You can see that when the checkbox is unchecked, the checkbox and the right side of the checkbox are unprotected.

  

Usage

1. Prepare a sample Spreadsheet.

Please create a sample Spreadsheet. And, please put the checkboxes in the column "B".

2. Sample script.

Please copy and paste the following script to the script editor of Google Spreadsheet. And, please install the OnEdit trigger to the function of installedOnEdit. Ref

function installedOnEdit(e) {
  const sheetName = "Sheet1"; // Please set sheet name.
  const checkbox = 2; // In this sample, the checkboxes are put in the column "B".

  const range = e.range;
  const sheet = range.getSheet();
  if (sheet.getSheetName() != sheetName || range.columnStart != checkbox)
    return;
  const p = sheet
    .getProtections(SpreadsheetApp.ProtectionType.RANGE)
    .find((r) => {
      const temp = r.getRange();
      return (
        temp.getRow() == range.rowStart &&
        temp.getColumn() == range.columnStart &&
        temp.getNumColumns() == 2
      );
    });
  if (!p && range.isChecked()) {
    const p = range.offset(0, 0, 1, 2).protect();
    p.removeEditors(p.getEditors());
    if (p.canDomainEdit()) p.setDomainEdit(false);
  } else if (p && !range.isChecked()) {
    p.remove();
  }
}

3. Testing.

When you use this script, please check the checkbox of the column "B". By this, the script works. And, the checkbox and the right side of the checkbox are protected.

When a user who is not the owner of the Spreadsheet is checked, the user cannot edit the protected cells. On the other hand, the owner can edit the protected cells. By this, when the owner unchecks the checkbox, the protected cells are unprotected. By this, the user can edit the cells, again.

Reference

• I proposed a similar script to this thread of Stackoverflow.

Many thank your help when i use your solution.

I hope you can help me more, i need add 1 email to controle checkbox, cause your solution only unckeck by onwner. So, how i fix your code? Thank!

I write your code again and let my email at there, so, you may fix help me:

function installedOnEdit(e) {
const sheetName = "Data_"; // Please set sheet name.
const checkbox = 35; // In this sample, the checkboxes are put in the column "AI".

const range = e.range;
const sheet = range.getSheet();
if (sheet.getSheetName() != sheetName || range.columnStart != checkbox)
return;
const p = sheet
.getProtections(SpreadsheetApp.ProtectionType.RANGE)
.find((r) => {
const temp = r.getRange();
return (
temp.getRow() == range.rowStart &&
temp.getColumn() == range.columnStart &&
temp.getNumColumns() == 2
);
});
if (!p && range.isChecked()) {
const p = range.offset(0, 0, 1, 2).protect();
p.removeEditors(p.getEditors());
p.addEditors("abc@gmail.com");
if (p.canDomainEdit()) p.setDomainEdit(false);
} else if (p && !range.isChecked()) {
p.remove();
}
}