Prevent a Google Script from Modifying Date

Posted on

Prevent a Google Script from Modifying Date – Whether you’re just starting out with Google Sheets or are a seasoned pro, sooner or later one of your formulas will give you a formula or script parse error message rather than the result you want. However, every Formula user, irrespective of whether it’s a beginner or expert, has invariably come across a formula parse error in Google Sheets at least once in their life (and if you haven’t. you soon will). It can be frustrating, especially if it’s a longer formula where the formula parse error may not be obvious. In this article, we will talk about some common script or formula parse errors in Google Sheets, like Prevent a Google Script from Modifying Date, and how to fix it.

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 :

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

Leave a Reply

Your email address will not be published. Required fields are marked *