I have a nested for each loop that sets the background color for cells if the cell has no text in it. I want to update this loop to set a data validation instead. However, I am stuck on the code line indicated below (I am not sure how to update the conditional statement to apply to data validation instead).

const vs = ss.getRange(11, 7, 17, 5).getValues();
const cs = ss.getRange(11, 7, 17, 5).getBackgrounds();
// const nameRange = ss.getRange(1, 1, 10, 1);
// const nameValidation = SpreadsheetApp.newDataValidation().requireValueInRange(nameRange).build();

vs.forEach((r,i)=>{
    r.forEach((c,j)=>{
      if(c=="") {
        cs[i][j] = "#cc4125"; //cs[i][j] = nameRange does not work
      }
   });
  });

ss.getRange(11, 7, 17, 5).setBackgrounds(cs);

CodePudding user response：

I believe your goal is as follows.

• You want to set the data validations instead of the background colors by modifying your showing script.
• The data validation rule is nameValidation.

In this case, how about the following modification?

Modified script:

var ss = // Please set this.

const range = ss.getRange(11, 7, 17, 5);
const vs = range.getValues();
const nameRange = ss.getRange(1, 1, 10, 1);
const nameValidation = SpreadsheetApp.newDataValidation().requireValueInRange(nameRange).build();
const dataValidations = vs.map(r => r.map(c => c = c == "" ? nameValidation : null));
range.setDataValidations(dataValidations);

• When this script is run, when the cell is empty in the range of ss.getRange(11, 7, 17, 5), the data validation rule of nameValidation is set to the cell.

Reference:

• setDataValidations(rules)