iam trying to lock all cells in col "A" than contains number lower than 3 but it is locking every cell it goes by no matter the condition. Maybe i put wrong bracket somewhere? Or is it easier way to write this? This is my first try. Iam use to VBA in excel but this is much harder.

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName('Rezervace');

function lockRanges() {
  //First cell to lock
  var row = 1;
  var col = 1;

  // Get last row with data in sheet
  var lastRow = sheet.getLastRow();


  for (var i = row; i < lastrow; i  ) {
    //Lock current cell

 if(sheet.getRange(row,col).getValue() < 3) 
 {      
  lockRange(row, col);
      row = row   1;
  };

  };
}




function lockRange(row, col){
  var range = sheet.getRange(row, col);

  // Create protection object. Set description, anything you like.
  var protection = range.protect().setDescription('Protected, row '   row);

 // Ensure the current user is an editor before removing others. Otherwise, if the user's edit
 // permission comes from a group, the script will throw an exception upon removing the group.
 var me = Session.getEffectiveUser();
 protection.addEditor(me);
 protection.removeEditors(protection.getEditors());
 if (protection.canDomainEdit()) {
   protection.setDomainEdit(false);
 }
}

CodePudding user response：

Modification points:

• In your loop, row is added by 1 when sheet.getRange(row, col).getValue() < 3 is true. In this case, row is grown as 1, 2, 3,,,, and the cell is locked every row. I thought that your issue might be due to this.
• When getValue is used in a loop, the process cost will be high.
  • In this modification, at first, the values are retrieved from the column "A" and the cells are protected using the retrieved values.

When these points are reflected to your script, it becomes as follows.

Modified script:

function lockRanges() {
  var me = Session.getEffectiveUser();
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('Rezervace');
  var values = sheet.getRange("A1:A"   sheet.getLastRow()).getValues();
  values.forEach(([a], i) => {
    if (a < 3) {
      var protection = sheet.getRange("A"   (i   1)).protect().setDescription('Protected, row '   (i   1));
      protection.addEditor(me);
      protection.removeEditors(protection.getEditors());
      if (protection.canDomainEdit()) {
        protection.setDomainEdit(false);
      }
    }
  });
}

References:

• getValues()
• forEach()