Optimize AppScript Data Validation Script-CodePudding

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())
    }
  }
}

CodePudding user response：

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

CodePudding user response：

Adding data validations

You might want to clear data validations first

function AddDataValidation(){
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName('Sheet0');  
  const vs = sh.getRange(2,1,sh.getLastRow() - 1,2).getDisplayValues();
  const rule = SpreadsheetApp.newDataValidation().requireValueInList(['a', 'b', 'c'], true).build();
  let vo = vs.map((r,i) => {
    if(r[0] == "X") {
      return [rule];
    } else {
      return [null];
    }
  })
  sh.getRange(2,2,vo.length,1).clearDataValidations().clearContent();
  sh.getRange(2,2,vo.length,1).setDataValidations(vo);
}

I cleared both dataValidations and content because the wrong content can cause issue with the validation