google-apps-script - 根据谷歌表格中的今天日期+2个工作日锁定单元格

Question

我有一个谷歌表，我在“C”列中有一周中的一天，在“D”列中是日期，在“E 到 K”的第二行中我有 od 人的名字。它应该计算 2 个工作日（跳过周末和节假日）并锁定今天 + 2 个工作日的行并使其变为红色因此人们高呼更改它并知道什么被锁定。我更喜欢如果它可以锁定周末和节假日而不需要太多工作但不改变颜色。

即使锁定是我，也只有谁可以更改工作表，而 ppl 的另一个用户可以更改未锁定的单元格，但不能更改锁定的单元格。 谷歌表的链接。我试图在这里找到一些东西，所以我有一些东西，但我无法找到我需要的东西，而且它与 VBA 有太多不同:(

所以如果有人有技巧和时间我会很高兴:)

  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(['[e-mail]', '[e-mail]']);

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

标签： google-apps-scriptgoogle-sheets

Answer 1

您希望根据 D 列中的日期评估每一行并采取以下措施：

日期	行动
<=今天 + 2 天（不包括周末和节假日），当天是周一至周五	锁定行并将其涂成红色
<= 今天 + 2 天，这一天是周六至周日或节假日	仅锁定行
>今天 + 2 天（不包括周末和节假日）	没做什么

以下实现了所有这些。

// 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")
}

分解

创建一个对象（受此holidayList启发）可以让您轻松地在一个地方跟踪任何假期。

该lockCells函数是您检索 D 列中所有日期并根据上述标准评估每个日期的主要方法。我们找到跳过周末和节假日的“今天 + 2 天”值，然后将每个日期与之进行比较。

当您找到满足 <=today + 2 天条件的行时，protectCells从内部调用该函数。lockCells

colorCells当您评估的日期是工作日（周一至周五）时，将进一步调用该函数。

笔记

1. 我将日期格式设置为 en-US mm/dd/yyyy，但如果您通常使用不同的格式，您应该能够相应地修改它和后续功能。
2. 我无法弄清楚如何在整个行中设置保护 - 它仅延伸到范围的末尾（col K）。