I cannot find the way to properly simplify the nested loops to build an array of values and data validations and then set them all in the sheet in a single call to the server. Is it even possible ??

function onEdit(){
  testValidation() 
}

function testValidation() {

  var sheet = SpreadsheetApp.getActiveSheet();

  var source = SpreadsheetApp.getActive().getRange('A3:J4').getValues()
  var destination = SpreadsheetApp.getActive().getRange('M3:V4');
  destination.clearDataValidations();  

  var validationRule = SpreadsheetApp.newDataValidation().requireCheckbox().build(); // checkbox
  
  for(var r = 0; r <= source.length - 1; r  ) {
    for(var c = 0; c <= source[0].length - 1; c  ) {
      if(source[r][c] ===""  ){
        sheet.getRange(r   3,c   14).clearDataValidations().setValue(null)
      }else{ 
        sheet.getRange(r   3,c   14).setDataValidation(validationRule).setValue("true")           
      }        
    } 
  }  
}

Link to shared spreadsheet : https://docs.google.com/spreadsheets/d/1fyFPIssp3zUjRmWxU9LqHvpowH8SHdMQYizNOZ3xKsA/edit?usp=sharing

CodePudding user response：

In your situation, how about the following modified script?

Modified script 1:

function testValidation() {
  var check = SpreadsheetApp.newDataValidation().requireCheckbox().build();
  var sheet = SpreadsheetApp.getActiveSheet();
  var source = sheet.getRange('A3:J4').getValues();
  var values = source.map(r => r.map(c => c != "" ? check : null));
  sheet.getRange('M3:V4').clearContent().setDataValidations(values).check();
}

• In this modification, the checkboxes and the clear are set by setDataValidations.
  • I thought that this method might be low process cost.

Modified script 2:

function testValidation() {
  // Ref: https://stackoverflow.com/a/21231012/7108653
  const columnToLetter = column => {
    let temp,
      letter = "";
    while (column > 0) {
      temp = (column - 1) % 26;
      letter = String.fromCharCode(temp   65)   letter;
      column = (column - temp - 1) / 26;
    }
    return letter;
  };

  var sheet = SpreadsheetApp.getActiveSheet();
  var source = sheet.getRange('A3:J4').getValues();
  var rangeList = source.flatMap((r, i) => r.flatMap((c, j) => c != "" ? `${columnToLetter(j   14)}${i   3}` : []));
  sheet.getRange('M3:V4').clearDataValidations().setValue(null);
  if (rangeList.length == 0) return;
  sheet.getRangeList(rangeList).insertCheckboxes().check();
}

• I thought that the cells of M3:V4 can be cleared using sheet.getRange('M3:V4').clearDataValidations().setValue(null).
• In this modification, the checkboxes are put using insertCheckboxes() method of Class RangeList.

References:

• insertCheckboxes()
• setDataValidations()