i have a google sheet where i have in column "C" names od day in week, in column "D" is DATE and in 2nd row from "E to K" i have names od people. And it should count 2 workdays ( skip weekends and holidays ) and lock lines which are today 2 workdays and make it red So people chant change it and know what is locked. I prefer if its posible and not too much work to lock weekends and holidays but dont change colour.

Only who can change sheets even if lock is me and 1 another user rest of ppl can change not locked cells but cant change the locked ones. link for google sheet. I was trying to find something here so i have something but i wasnt able to find what i need and its too much diferent from VBA :(

So if someone have skill and time i will be happy :)

  function lockcells() {
  var me = Session.getEffectiveUser();
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('reservat');
  var values = sheet.getRange("D3:D"   sheet.getLastRow()).getValues();
  


// Here i need to check date and lock the cells

//i found examples how to remove editors so they cant edit locked cells

      var editors = protection.getEditors();
      protection.removeEditors(editors);
      protection.addEditor(me)
      protection.addEditors(['[email protected]', '[email protected]']);

      if (protection.canDomainEdit()) {
        protection.setDomainEdit(false);
      }
    }
  });
}
          

CodePudding user response：

You are looking to evaluate each row based on the date in column D and take the following actions:

The following accomplishes all of that.

// set global variables for the spreadsheet, sheet and list of holidays. The list of holidays is an object where the key is the date of the holiday, formatted as mm/dd/yyyy, and the value is the name of the holiday. We don't acutally use the holiday names, but they are there to make it easier to keep this list up to date.
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName('Reservat');
var holidayList = { 
    "10/27/2021": "Svátek",
    "11/25/2021": "Thanksgiving",
    "12/21/2021": "Christmas",
    "1/1/2022": "New Year's Day"
};

function lockCells(){
  var values = sheet.getRange("D3:D"   sheet.getLastRow()).getValues();

  //Find the date which is 2 days from today, not counting weekends and holidays. This will be the criterion we use to determine how to adjust any of the rows in the sheet. We'll call that variable todayPlusTwo. We also have a mm/dd/yyyy formatted version (todayPlusTwoStr) for comparing to the holiday list.
  var today = new Date()
  var todayPlusTwo = new Date(today.setDate(today.getDate() 2))  
  var todayPlusTwoStr = todayPlusTwo.toLocaleDateString("en-US")
  
  if (todayPlusTwo.getDay() === 0 || todayPlusTwo.getDay() === 6){   //if the date lands on the weekend, we add 2 days so that we are only counting weekdays in the  2 evaluation. 
    todayPlusTwo = new Date(todayPlusTwo.setDate(todayPlusTwo.getDate() 2))
    todayPlusTwoStr = todayPlusTwo.toLocaleDateString("en-US")
  }
  if (holidayList[todayPlusTwoStr] ){   //since the date is the key in the holidayList object, we just evaluate if the todayPlusTwoStr date exists in the HolidayList and if so, we add another day to the todayPlusTwo criterion.
    todayPlusTwo = new Date(todayPlusTwo.setDate(todayPlusTwo.getDate() 1))
    todayPlusTwoStr = todayPlusTwo.toLocaleDateString("en-US")
  }  

  //Loop through the range in Column D, evaluating each date and doing the following:
  //If Date is more than two days (todayPlusTwo) away, then leave it be.
  //If Date IS within two days (or earlier) then lock the row -- also, if it's a weekday color the row red.
  for (i=0; i<values.length; i  ){
    // get each date in the range, find the day of week (Sunday=0) and then convert the date format to mm/dd/yyyy for comparing with the holiday list
    var date = new Date(values[i]) 
    var day = date.getDay() //gets the day value for the date, where Sunday = 0 and Saturday = 6.
    var dateStr = date.toLocaleDateString("en-US") 

    //set a variable to keep track of what kind of day the current date is: workday, weekend or holiday. We set its default to workday.
    var dayType = "workday" 
    if(day === 0 || day === 6){
      dayType = "weekend" 
    } else {
      if(holidayList[dateStr]){
        dayType = "holiday"
      }
    }

    //now check each date: if it is more than two days away then do nothing, otherwise evaluate whether it's a workday or weekend/holiday and lock/color the row as appropriate
    if(date > todayPlusTwo){ 
    } else {
      if(dayType === "workday"){ 
        colorCells(i 3)
        protectCells(i 3)
      } else {
          protectCells(i 3) 
      }
    }
  }
}

function protectCells(row){
  //sets the protected range based on the row passed in from the lockCells function. The range extends from column A to the last column where there's data.
  var range = sheet.getRange(row,1,1,sheet.getLastColumn()) 
  var protection = range.protect().setDescription('Past Date: no changes allowed') 

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

function colorCells(row){
  //colors the background of the cells in Columns E - K based on the row passed in from the lockCells function. 
  var range = sheet.getRange(row,5,1,7)
  range.setBackground("#ea9999")
}

Breakdown

Creating a holidayList object (inspired by this) lets you easily keep track of any holidays in one place.

The lockCells function is the primary means by which you will retrieve all the dates in column D and evaluate each against the criteria established above. We find the "today 2 days" value that skips over weekends and holidays, and then compare each date against that.

The protectCells function is called from within lockCells when you find a row that meets the criteria of being <=today 2 days.

The colorCells function is further called when the date you are evaluating is a weekday (Mon-Fri).

Notes

1. I have the dates formatted as en-US mm/dd/yyyy but if you typically work with a different format you should be able to modify this and the subsequent functions accordingly.
2. I was unable to figure out how to set the protection across the entire row - it extends only to the end of the range (col K).