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