im trying to make a google script to protect a range in google sheets based on dates

I want to protect all the rows that its date is 14 days before today

this is the code I have so far,

    function ProtectEntradas() {

  const ss = SpreadsheetApp.getActiveSpreadsheet()
  const sheetEntradas = ss.getSheetByName('Entradas')
  var dateRange = sheetEntradas.getRange(3, 1, sheetEntradas.getLastRow() - 2, 1);
  var val = dateRange.getDisplayValues();
  var date = new Date();
  var protectDateRaw = new Date(date.getFullYear(), date.getMonth(), date.getDate() - 14);
  var protectDate = Utilities.formatDate(protectDateRaw, Session.getScriptTimeZone(), "dd-MMM-YY");
  var protectRow;
  //check if date is less than the current date
  for (var i = 0; i < val.length; i  ) {
    if (val[i][0] >= 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();
  }

it is doing some protection, but not the range im expecting

what im I doing wrong ?

then I want to make a trigger to run every day so it will be protecting the range dinamically

Thanks in advance

CodePudding user response：

Try this to compare correctly dates (3 lignes of code has been modified)

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();
  }
}