CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Nessus B
Nessus B

Reputation: 43

Change automatically all texts in all sheets to UPPERCASE in a Google spreadsheet but exclude some unicode characters

Following a question be me here i have this script provided by @utphx (thank you very much @utphx)...

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

This works just fine, but i want to extend it a little to exclude some unicode characters like... eg... Ⓚ ☎, becuase they are in bigger font size than the letters & numbers in the same cell, and after the uppercase fuction they change in the same font size like letters & numbers which they look very small comparing to them. I've try several ways that i found here and in the rest of the net but due my lack of knowledge in Java coding, and since these characters they can be in the same cell with numbers and letters, either all characters are changed to capitals or none of them.

Is there a way to specify this type of characters in the above code so to exclude them from changing to uppercase ?.

EDIT: I found the following code in this answer by @Ruben and with the addition of @ocordova's suggestion about using getFormula() method, the uppercase function works properly without touching the cells that contains any formulas and these type of characters: ☎ ✈

function onEdit(e) {
  var range = e.range;
  var value = range.getValue();
  var sheet = range.getSheet();
  if (range.getRow() > 1 && 
      range.getColumn() > 1 && 
      typeof value === 'string') {
    if(!range.getFormula()) {
      range.setValue(value.toUpperCase());
    } else {
    if (ind === 0 && e.range.rowStart > 1 && e.range.columnStart >= 1  && e.range.getFormula() == '') {
      e.range.setValue(e.value.toUpperCase());
      return;
      } 
    }
  }
}

The only thing that i need to add now is a check if the cells contain any of these of characters: Ⓚ Ⓘ Ⓣ Ⓕ (and perhaps some more letters or numbers of this type) so to exclude them from the uppercase function. Anyone know how i can do that?. I guess a REGEX will be a solution but i have no idea how and where to put it in the above code.

EDIT2: Scratch the above EDIT. That code behaves very strangely. If you select some cells to change the border style then, it copies the values from on cell to all the other selected cells!!!. I'll post a new question about this.

Thank you in advance

Nessus

Upvotes: 0

Views: 411

Answers (1)

utphx
utphx

Reputation: 1295

Try this (I set the font size to 15 but you can change as you like)

function font_size(cell){
  var font_size = 15
  var range = SpreadsheetApp.getActiveSheet().getRange(cell);
  var value = range.getValue().toString();
  var letterNumber = /^[\x00-\x7F]*$/;
  var rich = SpreadsheetApp.newRichTextValue();
  rich.setText(value); 
  for (var i=0;i<value.length;i++){ 
    if (letterNumber.test(value.charAt(i)) == false){
       var style = SpreadsheetApp.newTextStyle(); 
    style.setFontSize(font_size); 
    var buildStyle = style.build(); 
    rich.setTextStyle(i,i+1,buildStyle); 
    }   
  }
  var format = rich.build()
  range.setRichTextValue(format); 
}


function onEdit(e) {
if (Object.prototype.toString.call(e.range.getValue()) !== "[object Date]" ) {
 e.range.setValue(e.value.toUpperCase());
 var cell = e.source.getActiveRange().getA1Notation();
 font_size(cell)
}
}

Basically what this does is find non-ascii characters and change their font size.

Reference: https://developers.google.com/apps-script/reference/spreadsheet/rich-text-value-builder

Upvotes: 1

Related Questions