Automatically make all inserted text uppercase in Google spreadsheet based on some conditions

Question

This is my third time asking about this (1st, 2nd), since the previous answers turned out they had some issues. So this is my (final?) third question:

I have Google spreadsheet calendar type planner that 8-10 users are inserting data every day. Those data can be numbers, letters and some unicode characters, sometimes mixed all in one cell. I want all these text data to change to uppercase automatically when typed by the user.

So, the task is, change all text data to uppercase except...

if the cells values are dates
if the cells contains formulas
and exclude also some of the Unicode/Enclosed Alphanumerics characters and these Text Symbols which they might be mixed with letters and numbers in the same cell.

Now, after my prior attempts, i have the following script that does the job and keeps the conditions above, except one. It does not exclude the the Unicode/Enclosed Alphanumerics characters and after the uppercase change they become in very small size...

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

So... how can i, in the above code, to exclude also the Unicode/Enclosed Alphanumerics characters?

EDIT: This is a visual result of how the script it's working now. I pointed the correct results with "Yes" and the wrong ones with "No"... Script Results

Please enclose a screenshot to show what exactly goes wrong. And you allready get the answer in your previous post. You just have to extend the letternumber variable. — PySeeker, Oct 18 '19 at 21:59
@PySeeker, thanks for your answer. I don't know how to do that in the above script. The letternumber variable in the previous answer was for changing the font size but that was causing issues to some formulas and some non-English texts. Is there no another way to exculde that group of characters? — Nessus B, Oct 19 '19 at 10:01
Following your screenshot, the special characters are getting smaller in an absolute sense, not just relative to the text, as a result of applying `toUpperCase()` as in the script you provided? That is a strange behaviour, I'm not having it when trying to reproduce your issue. Can you provide a copy of the spreadsheet you are working on? — Iamblichus, Oct 22 '19 at 10:52
That's because i have some templates cells with those special characters mixed with text values wich they are in bigger font size than the texts. When users want to use those characters they copy the template cell and they just edit the text to corresponding data. That's were the script resets them to their original size and that's why i want to exclude them. — Nessus B, Oct 22 '19 at 12:19

score 0 · Accepted Answer · answered Oct 24 '19 at 09:18

So, following the way from a previous answer, i've played a little bit with the REGEX in this and finally i managed to change the font size only to Enclosed Alphanumerics characters...

function font_size(cell){
  var font_size = 18
  var range = SpreadsheetApp.getActiveSheet().getRange(cell);
  var value = range.getValue().toString();
  var letterNumber = /[\w*\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 (!e.range.getFormula())
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)
}
}

This is working for all case scenarios for me. The Uppercase procedure works except:

If the cells values are dates
If the cells contains formulas
If the cells contains some specific Text Symbols mixed with text.

For the Enclosed Alphanumerics characters the code function font_size(cell) it changes the font size to little bigger... var font_size = 18.

Ideally, the best way will be to exclude the Enclosed Alphanumerics characters the same way like it's done with dates & formulas but i have no idea if that is possible and how it's done. If someone know please do share but for now this is the best solution.

Automatically make all inserted text uppercase in Google spreadsheet based on some conditions

1 Answers1