How to uppercase a cell range even if user type or paste lowercase with no warning in Google Sheet-CodePudding

I've made a simple table in a one of many sheets in a google sheet file and I would like a cell range of a sheet to alway appear upper case no mater what user input without any warning.

Currently I found and used the script below in Apps Script and it works on all sheets and only on input texts, not pasted texts, but I would like to upper case a cell range on a sheet only.

function onEdit(e) {
  if (Object.prototype.toString.call(e.range.getValue()) !== "[object Date]" ) {    
    if (!e.range.getFormula()) {
      e.range.setValue(e.value.toUpperCase());
    }
  }
}

Can someone help please? Thanks

CodePudding user response：

Something like this

function lfunko() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName("SheetName");
  const rgA1 ="Whatever"
  let vo = sh.getRange(rgA1).getDisplayValues().map(r => {
    let row = []
    r.forEach(e => row.push(e.toUpperCase()));
    return row
  })
  sh.getRange(rgA1).setValues(vo);
}

CodePudding user response：

Try:

function onEdit(e) {

  if (e.source.getActiveSheet().getName() === `Trade History`) {

    if ((e.range.columnStart >= 2 && e.range.columnEnd <= 3) && (e.range.rowStart >= 2 && e.range.rowEnd <= 1000)) {

      const values = e.range.getDisplayValues().map(i => i.map(item => String(item).toUpperCase()))

      e.range.setValues(values)

    }

  }

}

This works by first checking the event happened on the correct sheet by name, then checks the changed cells occurred within a specified range (from your comment, the current range is B2:C1000).

If the changed cells meet these conditions, the values from the range are then converted to UpperCase and set.