First post!

Im a newbie to coding and need some help on google apps script.

I am looking to protect rows from editing once they have been approved (The range for approved aleard had protection so only authorised persons can approve). Once it becomes approved i want a script to run to protect that row from being edited.

Link to file I am using:https://docs.google.com/spreadsheets/d/1-LJfJ_qtq6_fC7zIrxi9Qs9vocuSCurlfMYMzwoe1iM/edit?usp=sharing

My code so far (but getting stuck):

function protectRows (){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Protect once approved");
  var col = 3
  var approvedColumn = sheet.getRange("D2:D").getValues()
 
 
   // what code needs to go here?


  var range = sheet.getRange(row, col);
  var protection = range.protect().setDescription('LOCKED NOW '   row);

var me = Session.getEffectiveUser();
 protection.addEditor(me);
 protection.removeEditors(protection.getEditors());
 if (protection.canDomainEdit()) {
   protection.setDomainEdit(false);
 }
}

Many Thanks in Advance!

CodePudding user response：

I believe your goal is as follows.

• You want to protect the columns "A" to "D" when the checkbox of the column "D" is checked.

In this case, how about the following modified script?

Modified script:

function protectRows() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Protect once approved");
  var approvedColumn = sheet.getRange("D2:D").getValues();
  var protectedRows = sheet.getProtections(SpreadsheetApp.ProtectionType.RANGE).map(p => p.getRange().getRow());
  approvedColumn.forEach(([d], i) => {
    var row = i   2;
    if (d === true && !protectedRows.includes(row)) {
      var range = sheet.getRange(`A${row}:D${row}`);
      var protection = range.protect().setDescription('LOCKED NOW '   row);
      var me = Session.getEffectiveUser();
      protection.addEditor(me);
      protection.removeEditors(protection.getEditors());
      if (protection.canDomainEdit()) {
        protection.setDomainEdit(false);
      }
    }
  });
}

Note:

• For example, when you want to execute the script when the cells are edited, please install OnEdit trigger to the function of protectRows as the installable trigger. By this, when the cells are edited, the checkbox of column "D" is checked and the rows are protected.

References:

• forEach()
• Installable Triggers