This function works only if you want to protect by dates

    function ProtectEntradas() {
  const ss = SpreadsheetApp.getActiveSpreadsheet()
  const sheetEntradas = ss.getSheetByName('Entradas')
  var dateRange = sheetEntradas.getRange(3, 1, sheetEntradas.getLastRow() - 2, 1);
  // #### modification on how to fetch dates
  var val = dateRange.getValues().map(d => Utilities.formatDate(d[0], Session.getScriptTimeZone(), "yyyy-MM-dd"))
  var date = new Date();
  var protectDateRaw = new Date(date.getFullYear(), date.getMonth(), date.getDate() - 14);
  // #### modification of the format
  var protectDate = Utilities.formatDate(protectDateRaw, Session.getScriptTimeZone(), "yyyy-MM-dd"); 
  var protectRow;
  //check if date is less than the current date
  for (var i = 0; i < val.length; i  ) {
    // #### modification
    if (val[i] >= protectDate) { 
      protectRow = i;
      break;
    }
  }
  var protection = sheetEntradas.getProtections(SpreadsheetApp.ProtectionType.RANGE);
  //If protection exists, update else add new one.
  if (protection.length > 0) {
    var range = sheetEntradas.getRange(3, 1, protectRow, 10);
    protection[0].setRange(range);
  }
  else {
    sheetEntradas.getRange(3, 1, protectRow, 10).protect();
  }
}

in this thread Mike Steelson helped me

After that I realized I also need to unprotect some rows if column Q was empty, so he provided this code

    function ProtectEntradas() {
  const ss = SpreadsheetApp.getActiveSpreadsheet()
  const sheetEntradas = ss.getSheetByName('Entradas')

  // dates
  var dateRange = sheetEntradas.getRange(3, 1, sheetEntradas.getLastRow() - 2, 1); // column A
  var val = dateRange.getValues().map(d => Utilities.formatDate(d[0], Session.getScriptTimeZone(), "yyyy-MM-dd"))
  var date = new Date();
  var protectDateRaw = new Date(date.getFullYear(), date.getMonth(), date.getDate() - 14);
  var protectDate = Utilities.formatDate(protectDateRaw, Session.getScriptTimeZone(), "yyyy-MM-dd");

  // other criteria
  var crit = sheetEntradas.getRange(3, 17, sheetEntradas.getLastRow() - 2, 1).getValues().flat(); // column Q

  // remove protections
  sheetEntradas.getProtections(SpreadsheetApp.ProtectionType.RANGE)
    .forEach(protection => {
      if (protection && protection.canEdit()) {
        protection.remove();
      }
    });

  for (var i = 0; i < val.length; i  ) {
    if (val[i] <= protectDate && crit[i] != '') {
      sheetEntradas.getRange(3   i, 1, 1, 10).protect();
    }
  }

}

but he commented has a better option, @Mike, can you please help on this ? I was testing the code, but it protects row by row, and not as group of protected ranges, which is very slow as my data has lots of rows, but also its not supposed to have that many protections

Thank you in advance

CodePudding user response：

This solution has 2 improvments :

• the protection is made by group of homogeneous rows which meet the criteria
• the script uses batchupdate process : please enable Sheets API Serive

Try

function ProtectEntradasWithBatchupdate() {
  const ss = SpreadsheetApp.getActiveSpreadsheet()
  const ssid = ss.getId()
  const sheetEntradas = ss.getSheetByName('Entradas')
  const shid = sheetEntradas.getSheetId()
  const firstRow = 3

  // dates
  var dateRange = sheetEntradas.getRange(firstRow, 1, sheetEntradas.getLastRow() - firstRow   1, 1); // column A
  var val = dateRange.getValues().map(d => Utilities.formatDate(d[0], Session.getScriptTimeZone(), "yyyy-MM-dd"))
  var date = new Date();
  var protectDateRaw = new Date(date.getFullYear(), date.getMonth(), date.getDate() - 14);
  var protectDate = Utilities.formatDate(protectDateRaw, Session.getScriptTimeZone(), "yyyy-MM-dd");

  // other criterion
  var crit = sheetEntradas.getRange(firstRow, 17, sheetEntradas.getLastRow() - firstRow   1, 1).getValues().flat(); // column Q

  // remove protections
  sheetEntradas.getProtections(SpreadsheetApp.ProtectionType.RANGE)
    .forEach(protection => {
      if (protection && protection.canEdit()) {
        protection.remove();
      }
    });

  var requests = []
  var old = true
  var row = 1
  for (var i = 0; i < val.length; i  ) {
    var test = (val[i] <= protectDate && crit[i] != '')
    if (i == 0) { old = test }
    if (test != old && i > 0) {
      if (!test) {
        requests.push({
          "addProtectedRange": {
            'protectedRange': {
              "range": {
                "sheetId": shid, "startRowIndex": row   firstRow - 2, "endRowIndex": i   firstRow - 1, "startColumnIndex": 0, "endColumnIndex": 17
              }, "description": "", "warningOnly": false, "editors": { "users": ["email1","email2"] }
            }
          }
        })
      }
      old = test
      row = i   1
    }
  }
  Sheets.Spreadsheets.batchUpdate({ requests }, ssid);
}