How can I make this code shorter?, Data Validation-CodePudding

Please help me to short this code. This code is for different ranges for dropdowns in each cell of column L. I want this to work upto L200

For dropdown in cell L3 , range is (''Helper 2'!B1:P1').
For dropdown in cell L4, range is (''Helper 2'!B2:P2')
For dropdown in cell L5, range is (''Helper 2'!B3:P3')

And so on.

function wa() {
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getRange('L3').activate();
  var currentCell = spreadsheet.getCurrentCell();
  spreadsheet.getSelection().getNextDataRange(SpreadsheetApp.Direction.DOWN).activate();
  currentCell.activateAsCurrentCell();
  spreadsheet.getRange('L3:L134').clearDataValidations();
  spreadsheet.getRange('L3').activate();
  spreadsheet.getRange('L3').setDataValidation(SpreadsheetApp.newDataValidation()
  .setAllowInvalid(true)
  .requireValueInRange(spreadsheet.getRange('\'Helper 2\'!B1:P1'), true)
  .build());
  spreadsheet.getRange('L4').activate();
  spreadsheet.getRange('L4').setDataValidation(SpreadsheetApp.newDataValidation()
  .setAllowInvalid(true)
  .requireValueInRange(spreadsheet.getRange('\'Helper 2\'!B2:P2'), true)
  .build());
  spreadsheet.getRange('L5').activate();
  spreadsheet.getRange('L5').setDataValidation(SpreadsheetApp.newDataValidation()
  .setAllowInvalid(true)
  .requireValueInRange(spreadsheet.getRange('\'Helper 2\'!B3:P3'), true)
  .build());
  spreadsheet.getRange('L6').activate();
  spreadsheet.getRange('L6').setDataValidation(SpreadsheetApp.newDataValidation()
  .setAllowInvalid(true)
  .requireValueInRange(spreadsheet.getRange('\'Helper 2\'!B4:P4'), true)
  .build());
  spreadsheet.getRange('L7').activate();
  spreadsheet.getRange('L7').setDataValidation(SpreadsheetApp.newDataValidation()
  .setAllowInvalid(true)
  .requireValueInRange(spreadsheet.getRange('\'Helper 2\'!B5:P5'), true)
  .build());
  spreadsheet.getRange('L8').activate();
  spreadsheet.getRange('L8').setDataValidation(SpreadsheetApp.newDataValidation()
  .setAllowInvalid(true)
  .requireValueInRange(spreadsheet.getRange('\'Helper 2\'!B6:P6'), true)
  .build());
  spreadsheet.getRange('L9').activate();
  spreadsheet.getRange('L9').setDataValidation(SpreadsheetApp.newDataValidation()
  .setAllowInvalid(true)
  .requireValueInRange(spreadsheet.getRange('\'Helper 2\'!B7:P7'), true)
  .build());
  spreadsheet.getRange('L10').activate();
  spreadsheet.getRange('L10').setDataValidation(SpreadsheetApp.newDataValidation()
  .setAllowInvalid(true)
  .requireValueInRange(spreadsheet.getRange('\'Helper 2\'!B8:P8'), true)
  .build());
  spreadsheet.getRange('L11').activate();
  spreadsheet.getRange('L11').setDataValidation(SpreadsheetApp.newDataValidation()
  .setAllowInvalid(true)
  .requireValueInRange(spreadsheet.getRange('\'Helper 2\'!B9:P9'), true)
  .build());
  spreadsheet.getRange('L12').activate();
  spreadsheet.getRange('L12').setDataValidation(SpreadsheetApp.newDataValidation()
  .setAllowInvalid(true)
  .requireValueInRange(spreadsheet.getRange('\'Helper 2\'!B10:P10'), true)
  .build());
  spreadsheet.getRange('L13').activate();
  spreadsheet.getRange('L13').setDataValidation(SpreadsheetApp.newDataValidation()
  .setAllowInvalid(true)
  .requireValueInRange(spreadsheet.getRange('\'Helper 2\'!B11:P11'), true)
  .build());
  spreadsheet.getRange('L14').activate();
  spreadsheet.getRange('L14').setDataValidation(SpreadsheetApp.newDataValidation()
  .setAllowInvalid(true)
  .requireValueInRange(spreadsheet.getRange('\'Helper 2\'!B12:P12'), true)
  .build());
  spreadsheet.getRange('L15').activate();
  spreadsheet.getRange('L15').setDataValidation(SpreadsheetApp.newDataValidation()
  .setAllowInvalid(true)
  .requireValueInRange(spreadsheet.getRange('\'Helper 2\'!B13:P13'), true)
  .build());
  spreadsheet.getRange('L16').activate();
  spreadsheet.getRange('L16').setDataValidation(SpreadsheetApp.newDataValidation()
  .setAllowInvalid(true)
  .requireValueInRange(spreadsheet.getRange('\'Helper 2\'!B14:P14'), true)
  .build());
  spreadsheet.getRange('L17').activate();
  spreadsheet.getRange('L17').setDataValidation(SpreadsheetApp.newDataValidation()
  .setAllowInvalid(true)
  .requireValueInRange(spreadsheet.getRange('\'Helper 2\'!B15:P15'), true)
  .build());
  spreadsheet.getRange('L18').activate();
  spreadsheet.getRange('L18').setDataValidation(SpreadsheetApp.newDataValidation()
  .setAllowInvalid(true)
  .requireValueInRange(spreadsheet.getRange('\'Helper 2\'!B16:P16'), true)
  .build());
  spreadsheet.getRange('L19').activate();
  spreadsheet.getRange('L19').setDataValidation(SpreadsheetApp.newDataValidation()
  .setAllowInvalid(true)
  .requireValueInRange(spreadsheet.getRange('\'Helper 2\'!B17:P17'), true)
  .build());
  spreadsheet.getRange('L20').activate();
  spreadsheet.getRange('L20').setDataValidation(SpreadsheetApp.newDataValidation()
  .setAllowInvalid(true)
  .requireValueInRange(spreadsheet.getRange('\'Helper 2\'!B18:P18'), true)
  .build());
  spreadsheet.getRange('L21').activate();
  spreadsheet.getRange('L21').setDataValidation(SpreadsheetApp.newDataValidation()
  .setAllowInvalid(true)
  .requireValueInRange(spreadsheet.getRange('\'Helper 2\'!B19:P19'), true)
  .build());
  spreadsheet.getRange('L22').activate();
  spreadsheet.getRange('L22').setDataValidation(SpreadsheetApp.newDataValidation()
  .setAllowInvalid(true)
  .requireValueInRange(spreadsheet.getRange('\'Helper 2\'!B20:P20'), true)
  .build());
 spreadsheet.getRange('L1').activate();
};

CodePudding user response：

It looks that you used the macro recorder to generate the code as it includes a lot of spreadsheet.getRange('A1').activate() like statements that, in this case, are completely unnecessary. To slight shorten your code, remove all of them. This not only will shorten the code but also reduce the execution time.

Also the following lines aren't needed

var currentCell = spreadsheet.getCurrentCell();
  spreadsheet.getSelection().getNextDataRange(SpreadsheetApp.Direction.DOWN).activate();
  currentCell.activateAsCurrentCell();

To further shorter your code, I suggest you to spend some time learning about the pretty basics of JavaScript, i.e. about variables, functions, data structures, arrays and loops.

Resources

https://developers.google.com/apps-script/guides/sheets

Service Spreadsheet Time Out - Optimizing Macro script?

CodePudding user response：

Try this:

function wa() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName("Helper 2");
  const rl0 = sh.getRangeList(['B1:P1', 'B2:P2', 'B3:P3', 'B4:P4', 'B5:P5', 'B6:P6', 'B7:P7', 'B8:P8', 'B9:P9', 'B10:P10', 'B11:P11', 'B12:P12', 'B13:P13', 'B14:P14', 'B15:P15', 'B16:P16', 'B17:P17', 'B18:P18', 'B19:P19', 'B20:P20']).getRanges();
  const rl1 = sh.getRangeList(['L3', 'L4', 'L5', 'L6', 'L7', 'L8', 'L9', 'L10', 'L11', 'L12', 'L13', 'L14', 'L15', 'L16', 'L17', 'L18', 'L19', 'L20', 'L21', 'L22']).getRanges();
  sh.getRange('L3').activate();
  var currentCell = sh.getCurrentCell();
  sh.getSelection().getNextDataRange(SpreadsheetApp.Direction.DOWN).activate();
  currentCell.activateAsCurrentCell();
  sh.getRange('L3:L134').clearDataValidations();
  rl1.forEach((rg, i) => {
    rg.activate();
    rg.setDataValidation(SpreadsheetApp.newDataValidation()
    .setAllowInvalid(true)
    .requireValueInRange(rl0[i],true)
    .build());
  });
  sh.getRange('L1').activate();
};