通过脚本设置数据验证规则时相对引用异常变为绝对引用

核心原因 #

你遇到的问题根源在于requireValueInRange方法接收的是固定Range对象，它会自动将该Range的引用转为绝对地址，无论你写的是相对行号还是绝对行号。另外你的代码里存在一个小错误：spreadsheet.getRange中的spreadsheet变量未定义，应当替换为dataSheet或者SpreadsheetApp.getActiveSpreadsheet()。

当你给整个E2:E213范围批量设置数据验证时，Apps Script会把你传入的P2:U2这个固定Range的地址统一转为绝对引用，导致所有行都指向同一行的固定区域。

解决办法 #

要实现每行对应自身行的P-U列范围，需要逐行设置数据验证，给每个E列单元格单独指定对应行的P-U范围：

function FixDataValidation() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var dataSheet = ss.getSheetByName('SkillData');
  
  // 循环处理E2到E213的每一行
  for (var i = 2; i <= 213; i++) {
    // 获取当前行对应的P到U列范围
    var validationRange = dataSheet.getRange(`P${i}:U${i}`);
    // 创建数据验证规则
    var rule = SpreadsheetApp.newDataValidation()
      .setAllowInvalid(false)
      .requireValueInRange(validationRange, true)
      .build();
    // 给当前行的E列单元格应用规则
    dataSheet.getRange(`E${i}`).setDataValidation(rule);
  }
}

这样设置后，每个E列单元格的数据验证范围会是相对自身行的Pn:Un（n为当前行号），不会被强制转为绝对引用。

如果想提升效率（减少API调用次数），也可以用setDataValidations方法批量传入规则数组，本质还是为每个单元格生成对应行的规则：

function FixDataValidation() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var dataSheet = ss.getSheetByName('SkillData');
  var startRow = 2;
  var endRow = 213;
  var rowCount = endRow - startRow + 1;
  
  // 构建对应行数的规则二维数组
  var rules = [];
  for (var i = 0; i < rowCount; i++) {
    var currentRow = startRow + i;
    var validationRange = dataSheet.getRange(`P${currentRow}:U${currentRow}`);
    var rule = SpreadsheetApp.newDataValidation()
      .setAllowInvalid(false)
      .requireValueInRange(validationRange, true)
      .build();
    rules.push([rule]); // 二维数组结构匹配单元格范围
  }
  
  // 批量设置数据验证
  dataSheet.getRange(startRow, 5, rowCount, 1).setDataValidations(rules);
}

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