Google-sheets – Prevent a Google Script from Modifying Date

google sheetsgoogle-apps-scriptgoogle-apps-script-triggersgoogle-sheets-dates

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.

Any formatting that I have applied for dates gets eliminated, replacing it with a 5-digit number (number of days since time started)
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!

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

Getting and setting datetime values

Whenever your script calls .getValue() on a cell formatted as a date, date-time, or duration, it gets a JavaScript Date object. You can then apply various methods listed on that MDN page to this object, including getMonth() for the month. Example:

  var sheet = SpreadsheetApp.getActiveSheet();
  var value = sheet.getRange('A1').getValue();  // is a Date object
  Logger.log([value.getMonth(), value.getHours(), value.getUTCHours()]); 
  // examples of available methods

Conversely, you can assign a Date object to a cell using setValue, and it will be automatically formatted as such.

  sheet.getRange('B2').setValue(new Date());  // current timestamp

Timezones

The timestamp in Google Sheets is in local timezone, which is set via File > Spreadsheet settings. The script also operates within a timezone, which may be different and is found under File > Project properties in the script editor. If these timezones don't agree, you'll have a problem with timestamps.

To check whether the timezones agree, you can either compare both manually, or do something like this:

var sheet = SpreadsheetApp.getActiveSheet();
sheet.getRange('A1').setFormula('=date(2015,1,1)');
Logger.log(sheet.getRange('A1').getValue().getHours());

This inputs the formula =date(2015,1,1) in the cell A1. The result will be 2015-01-01 00:00:00 in the spreadsheet's time zone. The script then gets the date and extracts hours in the local time of the script. If the timezones agree, you should see 0.0 in the log.

Google Apps Script – Date Manipulation in GAS Script

Short answer

Use setDate JavaScript method.

Explanation

Google Apps Script use JavaScript to handle date objects, so you should use JavaScript methods instead of typical Google Sheets methods.

By the other hand, Utilities.formatDate() returns a string, not a date object.

The following script set the name of the second sheet as the date of seven days from now:

function myFunction() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var tz = ss.getSpreadsheetTimeZone();
  var date = new Date();
  var nextDate = new Date();
  var days = 7;
  nextDate.setDate(date.getDate() + days);
  var stringDate = Utilities.formatDate(nextDate, tz, 'MM-dd-yy');
  ss.getSheets()[1].setName(stringDate)
}

References

Add days to JavaScript Date