How to uppercase a cell range, sheet except one single word

Question

I found here,

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

the following code which solved my problem that, with onEdit trigger to uppercase words only in a specific range & sheet, not within the entire spreadsheet:

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)

    }

  }

}

I need now one single word in that range not to be uppercased, like "Hello" - this word I need to be ignored by the onEdit uppercase code, every time I write that specific word in that range.

I have really no idea how to solve my problem.

Welcome to [so]. You might find helpful spend some time learning the pretty basics of JavaScript, more specifically how to handle strings including the pretty basics of regular expressions. This will help you a lot to make more effective searches and to make your questions better. — Rubén, Oct 08 '22 at 15:39

Cooper · Accepted Answer · 2022-10-08T17:58:32.437

0

Try this:

function onEdit(e) {
  e.source.toast("Entry");
  if (e.range.getSheet().getName() == `Sheet0`) {
    const wd = "Hello";
    if (e.range.columnStart > 1 && e.range.columnEnd < 4 && e.range.rowStart > 1 && e.range.rowEnd < 1001) {
      e.source.toast("Gate1")
      const values = e.range.getDisplayValues().map(r => r.map(c => {
        let idx = c.toString().indexOf(wd);
        Logger.log(idx);
        if (~idx) {
          let t = c.toString().split(wd);
          Logger.log(JSON.stringify(t));
          return t[0].toString().toUpperCase() + wd + t[1].toString().toUpperCase();
        } else {
          return c.toString().toUpperCase();
        }
      }))
      e.range.setValues(values)
    }
  }
}

edited Oct 08 '22 at 17:58

answered Oct 08 '22 at 06:04

Cooper

59,616
6
23
54

Also, this string sayHellobrother is uppecased like this: SAYHelloBROTHER – TraianBrad Oct 08 '22 at 13:03
I don't understand all of you last comment could you be a little more precise in your description on the problems. – Cooper Oct 08 '22 at 13:40
I don t know how to explain better, I've recorded my screen for you to see how it works. I put it on youtube and you can see the video here: https://youtu.be/8J2zIdrRnZ4. In my video you can see there the Cooper code and range, and NEWAZA code and range. When I write something in Cooper range, the word simply dissapear... When I write in NEWAZA range, the word is uppercased and not dissapear. I need in Cooper range to uppercase all words I write, like in NEWAZA range, except one word: Hello. And I want the words not to dissapear after I write it. I hope now you understand what I need. – TraianBrad Oct 08 '22 at 17:30
... and in 1 cell I only write 1 word, I never use that range of cells to write more than 1 single word in every cell of that range. – TraianBrad Oct 08 '22 at 17:37
... The code should be related to cellcontent, like if { cell.Content == "Hello" then... not uppercase... else uppercase...} I think it should be something like that. – TraianBrad Oct 08 '22 at 17:47
I think there's a couple of things going on. I think you may have multiple onEdits which is causing problems and I made a change to return the entire string in uppercase if the word is not found – Cooper Oct 08 '22 at 18:00
Now the code is working as intended. – TraianBrad Oct 08 '22 at 19:04

How to uppercase a cell range, sheet except one single word

1 Answers1