0

I have a google spreadsheet in which I need to check if the value entered in the cell by the user is a date, if it is a date, it must be formatted as dd/MM/yy, if it is not a date, than the value must be converted to uppercase.

The date conversion is what is not working by now, and i would like some help on how can i do that.

This is how the script is right now. Thanks in advance.

function onEdit(e) {
  var tab = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getName();
  if (tab != "backup")  { //this is NOT working
    if(isDate(e.range.getValue())) {
      var date = new Date(e.range.getValue());
      var formattedDate = Utilities.formatDate(date, "GMT-3", 'dd/MM/yy');
      e.range.setValue(formattedDate);
    } else { //this is working
        e.range.setValue(e.value.toUpperCase()); 
      }
  }
}

function isDate(date) {
  return(Object.prototype.toString.call(date) === '[object Date]');
}

EDIT: the simplest solution i found for this is:

function onEdit(e) {
  var tab = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getName();
  if (tab != "backup")  { 
    if(isDate(e.range.getValue())) {
      e.range.setNumberFormat("dd/MM/yy");
    } else { 
        e.range.setValue(e.value.toUpperCase()); 
      }
  }
}

function isDate(date) {
  return(Object.prototype.toString.call(date) === '[object Date]');
}
Lucas Matos
  • 1,112
  • 5
  • 25
  • 42
  • Can I ask you about the detail of `The date conversion is what is not working by now`? Because, in your script, I thought that when a date object is put into a cell except for "backup" sheet, the script works. First, I would like to correctly understand your question. – Tanaike Jul 11 '23 at 23:03
  • Can you specify any error or elaborate how your date conversion isn't working? – Twilight Jul 12 '23 at 03:07
  • i want to force all dates entered by user to be formatted as dd/MM/yy, regardless of the format in which the user typed. the way it is, it doesn't cause any errors, but it also doesn't make any changes to the date format. – Lucas Matos Jul 12 '23 at 16:09

1 Answers1

0

I am not fully familiar with your data and/or error you are encountering but how about this alternative solution:

function onEdit(e) {
  var tab = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getName();

  if (tab != "backup")  {
    if(isDate(e.range.getValue())) {
      var date = new Date(e.range.getValue());
      var formattedDate = Utilities.formatDate(date, "GMT-3", "''dd/MM/yy"); // using '' to force string literal
      e.range.setValue(formattedDate);
    } else { //this is working
        e.range.setValue(e.value.toUpperCase()); 
      }
  }
}

function isDate(date) {
  return(Object.prototype.toString.call(date) === '[object Date]');
}

You can use this symbol '' to force the format of date to your desired output.

Result:

enter image description here

Ref: Google Apps Script date format issue (Utilities.formatDate)

Twilight
  • 1,399
  • 2
  • 11