基于日期的Google Sheets行保护脚本问题排查及动态触发需求

一、代码中的错误点 #

• 日期比较逻辑错误：使用getDisplayValues()获取格式化后的日期字符串，字符串的字典序比较和实际日期的时间顺序不一致（例如"01-Jan-24"字符串比"15-Dec-23"小，但实际日期更晚），导致判断结果完全错误。
• 循环边界处理缺失：若所有行的日期都早于保护日期，循环结束后protectRow未赋值，会直接导致后续创建范围时抛出错误；若所有行都晚于保护日期，protectRow为0，此时创建的范围行数为0，同样会报错。
• 保护范围行数计算偏差：原代码中protectRow是循环的索引值（从0开始），但getRange的第三个参数是行数，直接使用索引值会导致保护范围少一行或多一行。
• 多保护实例未清理：若工作表中存在多个范围保护，原代码仅更新第一个保护，旧的保护会残留，导致最终保护范围不符合预期。

二、修正后的代码 #

function ProtectEntradas() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheetEntradas = ss.getSheetByName('Entradas');
  if (!sheetEntradas) return; // 防止工作表不存在报错

  const startRow = 3; // 数据起始行
  const dateCol = 1; // 日期所在列
  const totalCols = 10; // 要保护的列数
  const daysBefore = 14; // 提前保护的天数

  // 获取日期列的原始日期对象
  const lastRow = sheetEntradas.getLastRow();
  if (lastRow < startRow) return; // 无数据时直接返回
  const dateRange = sheetEntradas.getRange(startRow, dateCol, lastRow - startRow + 1, 1);
  const dateValues = dateRange.getValues().flat(); // 转为一维数组

  // 计算保护阈值日期（当前日期往前推14天，重置时间为00:00:00避免时分秒干扰）
  const protectDateRaw = new Date();
  protectDateRaw.setDate(protectDateRaw.getDate() - daysBefore);
  protectDateRaw.setHours(0, 0, 0, 0);

  // 找到第一个晚于保护阈值的行索引，确定需要保护的行数
  let protectRowCount = 0;
  for (let i = 0; i < dateValues.length; i++) {
    const rowDate = new Date(dateValues[i]);
    if (isNaN(rowDate.getTime())) continue; // 跳过无效日期

    // 若当前行日期晚于阈值，停止循环，前面的行都需要保护
    if (rowDate > protectDateRaw) {
      protectRowCount = i;
      break;
    }
    // 若所有行都早于阈值，保护所有数据行
    if (i === dateValues.length - 1) {
      protectRowCount = dateValues.length;
    }
  }

  // 移除所有旧的范围保护，避免残留
  const existingProtections = sheetEntradas.getProtections(SpreadsheetApp.ProtectionType.RANGE);
  existingProtections.forEach(protection => protection.remove());

  // 若有需要保护的行，创建新的保护
  if (protectRowCount > 0) {
    const protectRange = sheetEntradas.getRange(startRow, 1, protectRowCount, totalCols);
    const protection = protectRange.protect();
    
    // 可选：设置仅允许特定用户编辑（比如脚本所有者）
    // const me = Session.getEffectiveUser();
    // protection.addEditor(me);
    // protection.removeEditors(protection.getEditors());
    // if (protection.canDomainEdit()) protection.setDomainEdit(false);
  }
}

三、关键修正说明 #

1. 日期对象直接比较：改用getValues()获取原始日期对象，通过时间戳对比，确保日期判断完全准确。
2. 完善边界判断：增加工作表不存在、无数据的判断；处理所有行都早于/晚于阈值的情况，避免变量未赋值导致的错误。
3. 清理旧保护：先移除所有旧的范围保护，确保新保护是唯一生效的。
4. 精准计算保护范围：protectRowCount直接代表需要保护的行数，传入getRange后，保护范围从起始行开始，向下覆盖对应行数，完全符合需求。

四、设置每日自动触发器 #

1. 打开Google Apps Script编辑器，点击左侧菜单栏的触发器图标（时钟形状）。
2. 点击右下角的添加触发器按钮。
3. 在配置面板中：
   • 选择要运行的函数：ProtectEntradas
   • 选择事件源：时间驱动
   • 选择类型：日计时器
   • 选择每天运行的时间段（推荐凌晨时段，避免影响日常操作）
4. 点击保存，完成触发器设置。

内容的提问来源于stack exchange，提问作者Union Movil