0

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!

2

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());
  }
}

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.