I have the following function:

function FixDataValidation() {
  var dataSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('SkillData')
  dataSheet.getRange('E2:E213').setDataValidation(SpreadsheetApp.newDataValidation()
  .setAllowInvalid(false)
  .requireValueInRange(spreadsheet.getRange('SkillData!$P2:$U2'), true)
  .build());
};

However, after it runs, it sets the validation range to 'SkillData!$P$2:$U$2'. Obviously, this breaks, since it makes every row reference the same range, instead of relative ranges. But I've triple-checked (including a direct paste of the function), and the function is not putting dollar signs in front of the row numbers. What's going on?

CodePudding user response：

You need different rules for each row.

function FixDataValidation() {
  const dataSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('SkillData');
  const rules = [];
  for (let i = 2; i <= 213; i  ) {
    const range = dataSheet.getRange(`P${i}:U${i}`);
    const rule = SpreadsheetApp.newDataValidation()
        .setAllowInvalid(false)
        .requireValueInRange(range)
        .build();
    rules.push([rule]);
  }
  dataSheet.getRange('E2:E213').setDataValidations(rules);
};