根據今天日期 2個作業日在谷歌表格中鎖定單元格

我有一個谷歌表，其中我在“C”列中的名稱是星期幾，“D”列中是 DATE，在“E 到 K”的第二行中，我的名稱是 od 人。并且它應該計算 2 個作業日（跳過周末和節假日）并鎖定今天 2 個作業日的行并使其變為紅色所以人們高呼更改它并知道鎖定了什么。我更喜歡它是否可行并且沒有太多的作業來鎖定周末和假期但不要改變顏色。

即使鎖定是我和 1 位其他用戶，其他人也可以更改作業表，但可以更改未鎖定的單元格，但不能更改鎖定的單元格。 谷歌表的鏈接。我試圖在這里找到一些東西，所以我有一些東西，但我無法找到我需要的東西，而且它與 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(['[email protected]', '[email protected]']);

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

uj5u.com熱心網友回復：

您希望根據 D 列中的日期評估每一行并執行以下操作：

以下完成了所有這些。

// 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 天”值，然后將每個日期與它進行比較。

當您找到符合 <=今天 2 天的條件的行時，protectCells將從內部呼叫該函式lockCells。

colorCells當您評估的日期是作業日（周一至周五）時，將進一步呼叫該函式。

筆記

1. 我將日期格式設定為 en-US mm/dd/yyyy 但如果您通常使用不同的格式，您應該能夠相應地修改它和后續功能。
2. 我無法弄清楚如何在整行中設定保護 - 它僅延伸到范圍的末尾（col K）。

標籤：谷歌应用程序脚本 谷歌表格

上一篇：如何計算列中的值以使用它來定義變數，以及如何使用Google應用程式腳本將url顯示為超鏈接（文本）

下一篇：發送會話標識給SignalR核心的OnConnected方法