0

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

Nessus B
  • 43
  • 1
  • 9
  • 1
    How does the current code act with the special characters? You might wanna take a look at [this answer](https://stackoverflow.com/a/58260356/11581830), it shows how to make a regex for special characters, although I believe that one in particular is for emojis, it should be easy to add the characters you use. – AMolina Oct 11 '19 at 08:59
  • What @AMolina said. For someone with limited coding knowledge, regex may seem daunting but it is a very versatile tool to know so worth spending a little time on understanding. – a-burge Oct 11 '19 at 09:10
  • Thansk both for your answers. @AMolina... your question has a point and i am sorry that i didn't clarify that in my question (updated my question now). The characters are not modified by the script. But, becuase they are in bigger font size than the letters & numbers in the same cell, after the uppercase fuction the change in the same font size like letters & numbers which they look very small comparing to them. I dotn think a regex is needed since we use only 5 or 6 of them but if that is the only way can you point me how can i insert them in my code? – Nessus B Oct 11 '19 at 09:31

1 Answers1

1

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

utphx
  • 1,287
  • 1
  • 8
  • 19
  • Huge thanks @utphx... again, everything works like a charm!. If you have the time to have a look also to this (https://stackoverflow.com/questions/58250884/script-to-format-a-range-of-cells-and-insert-a-specific-text-based-on-a-date-in)... it will be great. I am struggling with that (basically with trial-and-error ways) for over 10 days now with no luck !!!. Again... thank you very much for your help. – Nessus B Oct 12 '19 at 14:10
  • UPDATE: @utphx... unfortunately this breaks the dates cells. Todays date typed like this: 13/10 it converts to : Sun Oct 13 2019 00:00:00 GMT+0300 (EEST). Is there a way to specify excactly which characters to remain untouch by the script?... plus the formated as dates cells of course. – Nessus B Oct 13 '19 at 09:50
  • Thanks @utphx. Now works fine. Nevertheless, i would like to know (for educational purposes mostly) how can i specify some specific characters to exculde them from script's capitalazation function?. I guess it has to do something with this line...`var letterNumber = /^[\x00-\x7F]*$/;` ? – Nessus B Oct 13 '19 at 13:39
  • correct var letterNumber = /^[\x00-\x7F]*$/; looks for ASCII characters that's why i have if letterNumber.test(value.charAt(i)) == false means the character is not ASCII – utphx Oct 13 '19 at 23:37
  • UPDATE 2: @utphx... I've bump on another issue with this. If i try to write any kind formula in any cell, first converts all letters to uppercase (which breaks some query formulas) and deletes the whole formula from the cell. Any idea how to solve this? – Nessus B Oct 16 '19 at 14:16