I want to capitalize all cells with text from a range list. Can this be done?

Currently I have

function onEdit(e) {
  var myRange = SpreadsheetApp.getActiveSheet().getRange('triggerRange'); //My range
  var row = e.range.getRow();
  var col = e.range.getColumn();
  var activeSheet = e.source.getActiveSheet();
  if (col >= myRange.getColumn() && col <= myRange.getLastColumn() && row >= myRange.getRow() && row <= myRange.getLastRow()) {
    capit(e);
  } else if (activeSheet.getName() == 'Roster' && e.range.getValue() != "TRIGGER TEXT") {
    unborder(e);
  } else if (activeSheet.getName() == 'Roster' && e.range.getValue() == "TRIGGER TEXT") {
    border(e);
  }
}

// CAP IT
function capit(e) {
    e.range.setValue(e.value.toUpperCase());
}

// UNBORDER
function unborder() {
  SpreadsheetApp.getActiveSheet().getRange("B12:F22").setBorder(false, false, false, false, false, false);
}

// BORDER
function border() {
    SpreadsheetApp.getActiveSheet().getRange("B12:F12").setBorder(true, true, true, true, false, false, "black", SpreadsheetApp.BorderStyle.SOLID_THICK); //HEADER
    SpreadsheetApp.getActiveSheet().getRange("B13:B18").setBorder(true, true, true, true, false, false, "black", SpreadsheetApp.BorderStyle.SOLID_THICK); //ROW HEADER
    SpreadsheetApp.getActiveSheet().getRange("B13:B18").setBorder(null, null, null, null, true, true, "black", SpreadsheetApp.BorderStyle.SOLID); //ROW HEADER
    SpreadsheetApp.getActiveSheet().getRange("C13:F18").setBorder(true, true, true, true, false, false, "black", SpreadsheetApp.BorderStyle.SOLID_THICK); //PLAYERS
    SpreadsheetApp.getActiveSheet().getRange("C13:F18").setBorder(null, null, null, null, true, true, "black", SpreadsheetApp.BorderStyle.SOLID); //PLAYERS
    SpreadsheetApp.getActiveSheet().getRange("B20:B22").setBorder(true, true, true, true, false, false, "black", SpreadsheetApp.BorderStyle.SOLID_THICK); //LOWER ROW HEADER
    SpreadsheetApp.getActiveSheet().getRange("B20:B22").setBorder(null, null, null, null, true, true, "black", SpreadsheetApp.BorderStyle.SOLID); //LOWER ROW HEADER
    SpreadsheetApp.getActiveSheet().getRange("C20:F22").setBorder(true, true, true, true, false, false, "black", SpreadsheetApp.BorderStyle.SOLID_THICK); //LOWER CONTENT
    SpreadsheetApp.getActiveSheet().getRange("C20:F22").setBorder(null, null, null, null, true, true, "black", SpreadsheetApp.BorderStyle.SOLID); //LOWER CONTENT
}

But instead of setting my 'triggerRange' I want it to be non-continuous columns and rows. Specifically ['B4:F10','C13:F18','C20:F22']

How can I achieve this? Do I have to use a loop?

P.S. sometimes my border and unborder functions are called as expected but other times they do not execute, any idea why?

CodePudding user response：

I believe your goal is as follows.

• You want to use the range list of ['B4:F10','C13:F18','C20:F22'] to myRange. And, when the edited range is in the range list, you want to run capit.

In this case, how about the following modified script?

Modified script:

In this modification, the functions of onEdit and capit were modified. In order to use the range list of ['B4:F10','C13:F18','C20:F22'], the method of rangeList of Class Sheet is used. Please modify your functions of onEdit and capit as follows and test it again.

function onEdit(e) {
  var activeSheet = e.source.getActiveSheet();
  var myRange = activeSheet.getRangeList(['B4:F10','C13:F18','C20:F22']).getRanges();
  var range = e.range;
  myRange.forEach(r => {
    var startRow = r.getRow();
    var startCol = r.getColumn();
    var endRow = startRow   r.getNumRows() - 1;
    var endCol = startCol   r.getNumColumns() - 1;
    if (range.columnStart >= startCol && range.columnEnd <= endCol && range.rowStart >= startRow && range.rowEnd <= endRow) {
      capit(e);
    } else if (activeSheet.getName() == 'Roster' && e.range.getValue() != "TRIGGER TEXT") {
      unborder(e);
    } else if (activeSheet.getName() == 'Roster' && e.range.getValue() == "TRIGGER TEXT") {
      border(e);
    }
  });
}

function capit(e) {
  e.range.setValues(e.range.getValues().map(r => r.map(c => c.toUpperCase())));
}

Reference:

• getRangeList()