2

I have a spreadsheet with about 10-12 sheets that several users adding data daily. The dates are mixed with numbers, texts and dates. So, I want all the inserted data to convert automatically to UPPERCASE but not the numbers since all cells that containing dates into big long numbers and I have to format them again to have the proper format.

I don't want to use Google UPPERCASE() function. I need a script to do this automatically upon any data insertion in all sheets of the spreadsheet.

Currently I am using the following script which works fine but unfortunately, it breaks the dates cells...

function onEdit(e) {
if (typeof e.value != 'object') {
e.range.setValue(e.value.toUpperCase());
}
}

Any ideas on how can I make the above script to skip number and date formatted cells?

player0
  • 124,011
  • 12
  • 67
  • 124
Nessus B
  • 43
  • 1
  • 9
  • Do your non date cells contain numbers? Give us an example of a cell that is not converting correctly – utphx Oct 05 '19 at 16:54
  • @utphx... any formatted cell as date have that problem. For example If you apply the above script in a spreadsheet with any cell formatted as date and you type the date in this form: 18/10 ...it will convert to: 43756. – Nessus B Oct 05 '19 at 17:21
  • What I am thinking is to check if there are numbers in the cell but this only works if your non date cells do not contain numbers, is this true? Or check if the cell is of date type. – utphx Oct 05 '19 at 17:25
  • The cells that are converted not properly are date types. There might be numbers in some cells but i dont see any problems there. Only in date type formatted cells. Here is a sample file... https://docs.google.com/spreadsheets/d/1QplyEcNu-svYwFq9wvPVEKnsEP1AnrlAkbBxNwEFPXg/edit?usp=sharing If you apply the above script to that file and try to enter a date you will see the problem – Nessus B Oct 05 '19 at 17:29

1 Answers1

2

This should work:

function onEdit(e) {
if (Object.prototype.toString.call(e.range.getValue()) !== "[object Date]" ) {
e.range.setValue(e.value.toUpperCase());
}
}
utphx
  • 1,287
  • 1
  • 8
  • 19
  • Hey @utphx, can i ask one more question?... what if i want to exclude some unicode characters that they might be mixed with letters and numbers in the same cells ?... eg... Ⓚ Ⓣ ☎. Can i specify such characters in the above code? After all, we use a very few of them. About 8-10. – Nessus B Oct 08 '19 at 08:11