Google Sheet Apps Script: change data validation ranges

code.gs

/** @OnlyCurrentDoc */

/**
 * Fixes the dropdown pickers on the Elections sheet if they need it.
 */
function fixPickers() {
  const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = (spreadsheet.getSheets().filter((sheet) => sheet.getSheetId() === 123123) || []).shift();
  const pickListsSheet = (spreadsheet.getSheets().filter((sheet) => sheet.getSheetId() === 234234) || []).shift();
  const pickers = spreadsheet.getRangeByName("ProductPickers");
  const dataValidations = pickers.getDataValidations();
  let row=0;
  let column=0;
  dataValidations.forEach((dvRow) => {
    row++;
    column=0;
    dvRow.forEach((dv) => {
      column++;
      if (dv?.getCriteriaType() !== SpreadsheetApp.DataValidationCriteria.VALUE_IN_RANGE) return;
      /** @type {SpreadsheetApp.Range} */
      const range = dv.getCriteriaValues()[0];
      const expectedRange = "B"+row+":"+row
      if (range.getA1Notation() !== expectedRange || range.getSheet().getSheetId() !== pickListsSheet.getSheetId()) {
        Logger.log("[%s]x[%s]\t[%s]", row, column, range.getA1Notation());
        pickers.getCell(row, column).setDataValidation(
          dv
          .copy()
          .requireValueInRange(sheet.getRange(pickListsSheet.getSheetName()+"!"+expectedRange))
          .setAllowInvalid(false)
          .build()
        );
      }
    });
  });
}