Prevent a Google Script from Modifying Date

Question

I have a Google Sheet that I am applying an onEdit script to. That script (borrowed from WebApps:Convert all text to UPPERCASE in a Google Spreadsheet) capitalizes all new entries in every field of all ranges of all sheets.

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

Unfortunately, it eliminates 2 kinds of formatting which I need.

1. Any formatting that I have applied for dates gets eliminated, replacing it with a 5-digit number (number of days since time started)
2. a field is pre-formatted as a percent. If I type a single-digit number, it automatically converts it to percent. When the script acts upon it, shows the value as a decimal. For example, I type in 7, the pre-formatting makes it 7%, and then the script makes it 0.07

I tried adding conditional statements at the beginning of the onEdit function, such as:

if(or(typeof e.value != 'object',not(isDate(e)))) {

But unfortunately that stopped the script from working at all.

How can I prevent the onEdit script from modifying dates and percentages?

Thanks!

Answer 1

Whenever any text is entered in a cell, you want to convert it to uppercase. But your script applies to all kinds of types (includes dates and numbers) and this has undesirable results.

Javascript recognises a variety of types. The reason that your script isn't working as expected is that it applies to all types, rather than only to the "string" type.

---

function onEdit(e) {
  Logger.log("the value type = "+typeof e.value)
  if (typeof e.value != 'object' && typeof e.value == 'string') {
    e.range.setValue(e.value.toUpperCase());
  }
}