Google Sheets按今日+2工作日自动锁定对应行功能实现咨询

前置准备 #

• 你需要先在表格内新建一个sheet，命名为节假日配置，将你所在地区的法定节假日日期全部录入到该sheet的A列，方便脚本判断排除
• 后续代码中['email1@etc.com', 'email2@example.com']部分需要替换为你指定的可编辑用户真实邮箱

完整可运行代码 #

function lockcells() {
  var me = Session.getEffectiveUser();
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('reservat');
  var holidaySheet = ss.getSheetByName('节假日配置');
  // 读取节假日列表
  var holidays = holidaySheet.getRange("A:A").getValues().flat().filter(date => date instanceof Date);
  // 计算今日+2个工作日的目标日期（自动排除周末+配置的法定节假日）
  var today = new Date();
  var targetWorkDay = getWorkDayAfter(today, 2, holidays);
  // 先清除该sheet所有旧的保护规则，避免冲突
  var oldProtections = sheet.getProtections(SpreadsheetApp.ProtectionType.RANGE);
  for (var i = 0; i < oldProtections.length; i++) {
    oldProtections[i].remove();
  }
  // 读取所有行的日期（D列从第3行开始）
  var lastRow = sheet.getLastRow();
  var dateRange = sheet.getRange("D3:D" + lastRow);
  var dateValues = dateRange.getValues();
  // 先重置所有行的背景色为默认白色
  sheet.getRange("3:" + lastRow).setBackground("#ffffff");
  // 遍历每一行判断
  for (var row = 0; row < dateValues.length; row++) {
    var currentDate = dateValues[row][0];
    if (!(currentDate instanceof Date)) continue;
    // 统一时间戳精度，避免时分秒影响判断
    currentDate.setHours(0,0,0,0);
    var isWeekend = currentDate.getDay() === 0 || currentDate.getDay() === 6;
    var isHoliday = holidays.some(holiday => {
      holiday.setHours(0,0,0,0);
      return holiday.getTime() === currentDate.getTime();
    });
    var isTargetWorkDay = currentDate.getTime() === targetWorkDay.getTime();
    // 满足锁定条件的行：目标工作日/周末/法定节假日
    if (isTargetWorkDay || isWeekend || isHoliday) {
      var lockRange = sheet.getRange(row + 3, 1, 1, sheet.getLastColumn());
      // 目标工作日标红
      if (isTargetWorkDay) {
        lockRange.setBackground("#ff0000");
      }
      // 加保护
      var protection = lockRange.protect().setDescription('自动锁定行：' + currentDate.toLocaleDateString());
      // 配置权限
      var editors = protection.getEditors();
      protection.removeEditors(editors);
      protection.addEditor(me);
      protection.addEditors(['email1@etc.com', 'email2@example.com']); // 替换为指定用户邮箱
      if (protection.canDomainEdit()) {
        protection.setDomainEdit(false);
      }
    }
  }
}
// 工具函数：计算指定日期后N个工作日的日期，排除周末和法定节假日
function getWorkDayAfter(startDate, days, holidays) {
  var currentDate = new Date(startDate);
  var count = 0;
  while (count < days) {
    currentDate.setDate(currentDate.getDate() + 1);
    var dayOfWeek = currentDate.getDay();
    var isWeekend = dayOfWeek === 0 || dayOfWeek === 6;
    var isHoliday = holidays.some(holiday => {
      holiday.setHours(0,0,0,0);
      currentDate.setHours(0,0,0,0);
      return holiday.getTime() === currentDate.getTime();
    });
    if (!isWeekend && !isHoliday) {
      count++;
    }
  }
  return currentDate;
}

使用说明 #

• 首次运行代码时需要授权权限，按照弹窗提示完成授权即可
• 如果需要每日自动执行，可以在Apps Script编辑器左侧点击「触发」，新增一个时间驱动触发器，设置每天固定时间（比如凌晨1点）运行lockcells函数，即可自动更新锁定规则和标红状态
• 当需要更新法定节假日列表时，直接修改节假日配置sheet的A列内容即可，下次运行脚本自动生效

内容的提问来源于stack exchange，提问作者Mra Jaromir Yerome