How can I fix require Value In Range

Question

I have this script that works perfectly in some of my spreadsheets but fails on others.

I have code that is too long, or crashes. Please help me shorten the code Can someone help and explain me this?

Here is the code:

function setDataValid_(range, sourceRange) {
  var rule = SpreadsheetApp.newDataValidation().requireValueInRange(sourceRange, true).build();
  range.setDataValidation(rule);
}
 
function onEdit() {
  var aSheet = SpreadsheetApp.getActiveSheet();
  var aCell = aSheet.getActiveCell();
  var aColumn = aCell.getColumn();
 
    if (aColumn == 4 && aSheet.getName() == 'Tháng 6') {
    var range = aSheet.getRange(aCell.getRow(), aColumn + 1);
    var sourceRange = SpreadsheetApp.getActiveSpreadsheet().getRangeByName(aCell.getValue());
    setDataValid_(range, sourceRange)
  }
  else if (aColumn == 5 && aSheet.getName() == 'Tháng 6') {
    var range = aSheet.getRange(aCell.getRow(), aColumn + 1);
    var sourceRange = SpreadsheetApp.getActiveSpreadsheet().getRangeByName(aCell.getValue());
    setDataValid_(range, sourceRange)
  }
  
  if (aColumn == 4 && aSheet.getName() == 'Tháng 7') {
    var range = aSheet.getRange(aCell.getRow(), aColumn + 1);
    var sourceRange = SpreadsheetApp.getActiveSpreadsheet().getRangeByName(aCell.getValue());
    setDataValid_(range, sourceRange)
  }
  else if (aColumn == 5 && aSheet.getName() == 'Tháng 7') {
    var range = aSheet.getRange(aCell.getRow(), aColumn + 1);
    var sourceRange = SpreadsheetApp.getActiveSpreadsheet().getRangeByName(aCell.getValue());
    setDataValid_(range, sourceRange)
  }
  if (aColumn == 4 && aSheet.getName() == 'Tháng 8') {
    var range = aSheet.getRange(aCell.getRow(), aColumn + 1);
    var sourceRange = SpreadsheetApp.getActiveSpreadsheet().getRangeByName(aCell.getValue());
    setDataValid_(range, sourceRange)
  }
  else if (aColumn == 5 && aSheet.getName() == 'Tháng 8') {
    var range = aSheet.getRange(aCell.getRow(), aColumn + 1);
    var sourceRange = SpreadsheetApp.getActiveSpreadsheet().getRangeByName(aCell.getValue());
    setDataValid_(range, sourceRange)
  }
  if (aColumn == 4 && aSheet.getName() == 'Tháng 9') {
    var range = aSheet.getRange(aCell.getRow(), aColumn + 1);
    var sourceRange = SpreadsheetApp.getActiveSpreadsheet().getRangeByName(aCell.getValue());
    setDataValid_(range, sourceRange)
  }
  else if (aColumn == 5 && aSheet.getName() == 'Tháng 9') {
    var range = aSheet.getRange(aCell.getRow(), aColumn + 1);
    var sourceRange = SpreadsheetApp.getActiveSpreadsheet().getRangeByName(aCell.getValue());
    setDataValid_(range, sourceRange)
  }
}

TheWizEd · Answer

Here is an prototype of how I would do it. I can't guarantee it because your date set if pretty complex.

I use the event object e to identify the active sheet, range and value.

Notice I use Range.offset() because you want to set the value in the column next to the one edited.

function onEdit(e) {
  let sheets = ['Tháng 6','Tháng 7','Tháng 8','Tháng 9'];
  let sheet = e.range.getSheet();
  if( sheets.indexOf(sheet.getName()) < 0 ) return;

  let column = e.range.getColumn();
  if( ( column === 4 ) || ( column === 5 ) ) {
    let range = e.range.offset(0,1);
    let sourceRange = e.source.getRangeByName(e.value);
    setDataValid_(range, sourceRange);
  }
}

How can I fix require Value In Range

1 Answers