Google Sheets脚本开发：按日期自动保护指定行权限

原脚本存在的问题 #

• 全局变量风险：ss定义在函数外部，工作表切换或脚本授权后易引发引用错误，应移至函数内部。
• 无效列查找循环：原循环只是把col赋值为最后列数，完全可以直接用ss.getLastColumn()替代，属于冗余代码。
• 日期比较逻辑错误：直接用字符串对比日期会因格式（如单双位数日/月）导致判断失误，必须转成Date对象再比较。
• 循环范围越界：dateRange的行数是ss.getLastRow()-2，但循环用x <= end（end为总行数）会超出数组长度，触发undefined报错。
• 缺失权限配置：原脚本没设置仅所有者可编辑的规则，协作者仍能修改锁定区域。
• 保护范围计算错误：数组索引对应工作表行号的逻辑错误，导致锁定范围偏差。

修正后的完整脚本 #

function lockRanges() {
  // 移至函数内部，避免全局变量引用问题
  const ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('MY SHEET');
  if (!ss) {
    SpreadsheetApp.getUi().alert('未找到名为"MY SHEET"的工作表');
    return;
  }

  const today = new Date();
  const dateFormat = "dd/M/yyyy";
  const timeZone = "GMT+1";
  const curDate = new Date(Utilities.formatDate(today, timeZone, dateFormat));

  const lastRow = ss.getLastRow();
  if (lastRow < 2) {
    SpreadsheetApp.getUi().alert('工作表中无足够数据行');
    return;
  }
  // 获取第2行到最后一行的日期列数据
  const dateRange = ss.getRange(2, 1, lastRow - 1, 1);
  const dateValues = dateRange.getDisplayValues();

  // 查找第一个晚于今日的日期行
  let lockUntilRow = lastRow;
  for (let x = 0; x < dateValues.length; x++) {
    const cellDateStr = dateValues[x][0];
    if (!cellDateStr) continue;
    const cellDate = Utilities.parseDate(cellDateStr, timeZone, dateFormat);
    if (cellDate > curDate) {
      lockUntilRow = x + 2; // 数组索引x对应工作表第x+2行
      break;
    }
  }

  // 确定要保护的范围：第2行到目标行的所有列
  const lastCol = ss.getLastColumn();
  const protectRange = ss.getRange(2, 1, lockUntilRow - 1, lastCol);

  // 处理已有保护或创建新保护
  let protection = ss.getProtections(SpreadsheetApp.ProtectionType.RANGE)[0];
  if (protection) {
    protection.setRange(protectRange);
  } else {
    protection = protectRange.protect();
  }

  // 设置仅所有者可编辑
  const owner = SpreadsheetApp.getActiveSpreadsheet().getOwner();
  protection.removeEditors(protection.getEditors());
  if (protection.canDomainEdit()) {
    protection.setDomainEdit(false);
  }
  protection.addEditor(owner);
}

关键修正说明 #

1. 变量作用域优化：将工作表引用移至函数内，添加缺失校验，避免无意义报错。
2. 日期逻辑修复：统一将单元格日期转为Date对象，确保比较逻辑准确。
3. 循环范围修正：只遍历实际存在的日期数据，避免数组越界。
4. 权限配置完善：移除所有协作者编辑权限，禁用域编辑，仅保留所有者权限。
5. 范围计算修正：根据数组索引正确映射工作表行号，确保锁定今日及之前的所有行。
6. 异常处理：针对工作表不存在、数据不足的情况添加提示，提升脚本健壮性。

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