如何在Apps Script中为日期验证添加TODAY()公式实现动态更新

问题场景 #

我有一个工作用的站点故障记录表，上报日期填写在D列，处理日期填写在H列。要求处理日期必须满足：

• 不早于对应行的上报日期
• 不晚于当日（不能填写未来日期）

手动在Google Sheets中设置数据验证时，直接引用对应行的D列单元格（比如D10）和TODAY()公式就能正常生效，但用Apps Script制作表单时遇到了问题：

1. 录制宏生成的代码中，requireDateBetween()用了固定的1899年日期，仅当前单元格可用，复用无效：

spreadsheet.getRange('H10').setDataValidation(SpreadsheetApp.newDataValidation()
  .setAllowInvalid(false)
  .setHelpText('Introduce una fecha entre el =D10 y el =HOY()')
  .requireDateBetween(new Date(1899, 11, 30), new Date(1899, 11, 30))
  .build());

2. 自行修改的代码用new Date()获取当前日期，但这个值是代码执行时的固定日期，次日不会自动更新，无法实现动态验证：

var ss= SpreadsheetApp.getActiveSpreadsheet()
var form= ss.getSheetByName('REGISTRAR_FALLA')

form.getRange('H11').setDataValidation(SpreadsheetApp.newDataValidation().setAllowInvalid(false).setHelpText('Introduce una fecha entre el =D10 y el =HOY()').requireDateBetween(new Date(form.getRange('D11').getValue()), new Date()).build());

解决方案：使用公式规则实现动态验证 #

要实现和手动设置一致的动态验证效果，不能用固定日期对象，而是要通过requireFormulaSatisfied()方法，直接引用Google Sheets的公式规则，这样TODAY()会自动每日更新，同时关联对应行的上报日期。

1. 单个单元格的动态验证代码 #

以H11单元格为例，对应上报日期在D11：

var ss = SpreadsheetApp.getActiveSpreadsheet();
var form = ss.getSheetByName('REGISTRAR_FALLA');

// 创建验证规则：处理日期 >= 对应行上报日期，且 <= 当日
var validationRule = SpreadsheetApp.newDataValidation()
  .setAllowInvalid(false)
  .setHelpText('Introduce una fecha entre el =D11 y el =HOY()')
  .requireFormulaSatisfied('=AND(H11>=D11, H11<=TODAY())')
  .build();

// 应用到目标单元格
form.getRange('H11').setDataValidation(validationRule);

2. 批量设置整列的动态验证 #

如果要给H列（从H10开始）所有处理日期单元格批量设置验证，可以使用相对引用公式：

var ss = SpreadsheetApp.getActiveSpreadsheet();
var form = ss.getSheetByName('REGISTRAR_FALLA');
// 选择H列从第10行开始的所有单元格
var targetRange = form.getRange('H10:H');

// 创建带相对引用的验证规则
var validationRule = SpreadsheetApp.newDataValidation()
  .setAllowInvalid(false)
  .setHelpText('Introduce una fecha entre el =D[行号] y el =HOY()')
  // 公式用相对引用，应用到整列时会自动适配每行的单元格
  .requireFormulaSatisfied('=AND(H1>=D1, H1<=TODAY())')
  .build();

// 批量应用验证规则
targetRange.setDataValidation(validationRule);

原理说明 #

• requireDateBetween()方法只能接收固定的Date对象，无法引用Sheet的动态公式，因此无法实现自动更新。
• requireFormulaSatisfied()方法直接使用Google Sheets的内置公式，TODAY()会随系统日期自动更新，相对引用的单元格（如D1、H1）会在应用到不同行时自动适配，完全复刻手动设置数据验证的动态效果。

内容的提问来源于stack exchange，提问作者Luis Fernando Medina Iglesias