1

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

Morteza Parkook
  • 11
  • 3

2 Answers2

1

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);
}
Cooper
  • 59,616
  • 6
  • 23
  • 54
  • Thanks you mean like this: function lfunko() { const ss = SpreadsheetApp.getActive(); const sh = ss.getSheetByName("Trade History"); const rgA1 ="B2:C1000" let vo = sh.getRange(rgA1).getDisplayValues().map(r => { let row = [] r.forEach(e => row.push(e.toUpperCase())); return row }) sh.getRange(rgA1).setValues(vo); } – Morteza Parkook May 14 '22 at 20:24
  • Thanks, it doesn't work. I also did it like this and not working: function lfunko() { const ss = SpreadsheetApp.getActive(); const sh = ss.getSheetByName("Trade History"); const rgA1 ="C4" let vo = sh.getRange(rgA1).getDisplayValues().map(r => { let row = [] r.forEach(e => row.push(e.toUpperCase())); return row }) sh.getRange(rgA1).setValues(vo); } – Morteza Parkook May 14 '22 at 20:57
1

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.

NEWAZA
  • 1,536
  • 2
  • 4
  • 19
  • Thanks, it didn't work when I used it like this, what am I doing wrong? function onEdit(e) { const targetSheet = `Trade History` const targetCell = `B2:C1000` if (e.source.getActiveSheet().getName() === targetSheet && e.range.getA1Notation() === targetCell) { e.range.setValue(String(e.value).toUpperCase()) } } – Morteza Parkook May 14 '22 at 20:28
  • Alternatively I did this and it didn't work, again what am I doing wrong? function onEdit(e) { if (e.source.getActiveSheet().getName() === `Trade History` && e.range.getA1Notation() === `B2:C1000`) { e.range.setValue(String(e.value).toUpperCase()) } } – Morteza Parkook May 14 '22 at 20:32
  • That's because your "cell" is a range of about 200 cells. I see you updated your post haha. – NEWAZA May 14 '22 at 20:34
  • Thanks, yes I did haha. This worked only on one CELL as soon as I add a range it doesn't work even A1:A2 – Morteza Parkook May 14 '22 at 20:55
  • Updated and tested. – NEWAZA May 14 '22 at 21:00
  • Many Thanks , it didn't work but when I looked closer I realised the line that starts with ".map(i =>" has more than one tab spacing, so I deleted the excessive tabs and all works. You're a genius Newaza!
    please update your work so that everyone can benefit from it ;)     – Morteza Parkook May 14 '22 at 21:24
  • Spacing shouldn't be an issue, that's odd. Would you mind slapping this answer as solved? Best wishes, let me know if you need any other help! – NEWAZA May 14 '22 at 21:28