apps script : how can I simplify to avoid multiple calls to server?

Question

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

Why don't you try to do this yourself? Checkout https://stackoverflow.com/questions/35289183/long-processing-time-likely-due-to-getvalue-and-cell-inserts — TheMaster, Feb 10 '22 at 11:42
@TheMaster : as an amateur programmer, I learn by trying to implement my small personal projects looking at videos and articles. However when I hit a wall after dozens of hours trying to find a way and failing miserably, I'd rather ask than remain stuck or keep ugly code. I didn't stumble on your post and Martin's : brilliant ! Thanks for the link ! — Michel, Feb 10 '22 at 14:02

Tanaike · Accepted Answer · 2022-02-10T13:07:03.410

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:

Yes, that was it again ! I tested modified script 1 (concise and fast) and it worked beautifully :-) You saved me again after many hours of unsuccessful trial and errors. I will check script #2 too, as I can learn a lot by this approach ! Thank you again. — Michel, Feb 10 '22 at 14:25
@Michel Thank you for replying and testing it. I'm glad your issue was resolved. Thank you, too. — Tanaike, Feb 10 '22 at 23:50

apps script : how can I simplify to avoid multiple calls to server?

1 Answers1

Modified script 1:

Modified script 2:

References: