Home > Software engineering >  Google Sheets App Script lock cells for specific days
Google Sheets App Script lock cells for specific days

Time:04-04

I am trying to unlock certain range of cells on every month for the first 10 days and after that lock the range until next month. Here is what I've been working on. But this doesn't work as i expected. Any help is appreciated.

function tenDaysAllowance() {
  var ss = SpreadsheetApp.getActive();
  var source = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Online Allowance");
  var todaysdate = Utilities.formatDate(new Date(), 'GMT 5', 'dd/MM/yyyy');
  var firstDate = new Date();
  var fd = firstDate;
    fd.setMonth(fd.getMonth());
    fd.setDate(1);
  var td = new Date();
    td.setMonth(td.getMonth());
    td.setDate(10);
  var firstDay = Utilities.formatDate(fd, 'GMT 5', 'dd/MM/yyyy');
  var afterTen = Utilities.formatDate(td, 'GMT 5', 'dd/MM/yyyy');
  Logger.log(firstDay);
  Logger.log(afterTen);

  if (todaysdate >= firstDay && todaysdate <= afterTen) {
    Logger.log("todays date is in range");
    var range = ss.getRange('B27:AF40');
    var protection = range.protect().setDescription('Sample protected range');
    var me = Session.getEffectiveUser();
    protection.addEditor(me);
    protection.removeEditors(protection.getEditors());
    if (protection.canDomainEdit()) {
      protection.setDomainEdit(false);
    }
  } else {
    Logger.log("todays date is not in range");
    var range = ss.getRange('B27:AF40');
    var allProtections = ss.getProtections(SpreadsheetApp.ProtectionType.RANGE);
    var matchingProtections = allProtections.filter(function(existingProtection) {
    return existingProtection.getRange().getA1Notation() == 'B27:AF40';
    });
    var protection = matchingProtections[0];
    protection.remove();
    };
};

CodePudding user response:

In your google sheet use a today() to determine today's date and day() to determine value of today's date.

enter image description here

function myProtection() {
var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Date Check");
var date=ss.getRange("B1");

//if it more the 10th of the month lock sheet, else allow others to edit
if(date > 10)
{
// Protect range A1:B10, then remove all other users from the list of editors.
var range = ss.getRange('B27:AF40');
var protection = range.protect().setDescription('Sample protected range');
var me = Session.getEffectiveUser();
protection.addEditor(me);
protection.removeEditors(protection.getEditors());
if (protection.canDomainEdit()) {
protection.setDomainEdit(false);
}
}


else{
    var allProtections = ss.getProtections(SpreadsheetApp.ProtectionType.RANGE);
    var matchingProtections = allProtections.filter(function(existingProtection) {
    return existingProtection.getRange().getA1Notation() == 'B27:AF40';
    });
    var protection = matchingProtections[0];
    protection.remove();
};
}
  • Related