Optimize AppScript Data Validation Script

February 25, 2022

I have a two columns in a Google sheet, column A and B.

Column A contains an "X" if we want to add data validation in column B. Column A is blank if we do no want data validation in column B.

I’ve created the below script, which works but takes around 2 minutes for 500 rows to run. Is there any way to write the script more efficiently to run faster or is this the best solution?

function AddDataValidation(){
 var ss4 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet4');  
  for(row=2; row<=500; row++){
    if (ss4.getRange('A'+row).getValue() == 'X'){
    ss4.getRange('B'+row).setDataValidation(SpreadsheetApp.newDataValidation()
    .requireValueInList(['a', 'b', 'c'], true)
    .build())
    }
  }
}

>Solution :

Description:

To optimize spreadsheet scripts alway use Range.getValues()/setValues(). So I get all the values rather than individually. This applies to every other category so for your example use Range.getDataValidations() and Range.setDataValidations(). Notice I have wrapped rule in Array brackets. That’s because rules needs to be a 2D array to set in the spreadsheet. Please let me know the difference in performance.

Script

function AddDataValidation() {
  var ss4 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet4');
  var data = ss4.getDataRange().getValues();
  var row = 0;
  var rule = SpreadsheetApp.newDataValidation().requireValueInList(['a', 'b', 'c']).build();
  var rules = [];
  // data start in row 1 index 0 so skip header
  for( row=1; row<data.length; row++ ) {
    if( data[row][0] === 'X' ) {
      rules.push([rule]);
    }
    else {
      rules.push([null]);
    }
  }
  // assuming header in row 1 start in row 2
  ss4.getRange(2,2,rules.length,1).setDataValidations(rules);
}

References