How to change the change the date format of a cell onChange?

Question

I need to change the format of an inputed Date/Time from

Mon Feb 03 2014 12:00:00 GMT+0000 (UTC)

to

Fri, 14 Jun 2013 14:38:13 GMT

I have been trying to figure it out with my limited JavaScript skills, but can not figure out how to select new rows.

I would like this to be done onChange to the cell range D:D & F:F

Here is what I have so far;

function onChange() {

var sheet = SpreadsheetApp.getActive();

ScriptApp.newTrigger("fixDate")

.forSpreadsheet(sheet)

.onChange()

.create();

};

And

function fixDate(oldDate) {

var oldDate = 'Mon Feb 03 2014 12:00:00 GMT+0000 (UTC)'
 var split = oldDate.split(/ /);

var newDate = split[0] + ", " + split[2] + " " + split[1] + " " + split[3] + " " + split[4] + " GMT";

 Logger.log(split[0]);
Logger.log(split[1]);

Logger.log(split[2]);

Logger.log(split[3]);

Logger.log(split[4]);

Logger.log(split[5]);

Logger.log(split[6]);

Logger.log(newDate);

};

Google Apps Script has a method to convert date format to string the way you want : developers.google.com/apps-script/reference/utilities/… — Serge insas
Note that if you change the cell content to strings it won't be a date object anymore of course... are you sure you are wanting to do that? — Serge insas
Thanks guys for your comments. @Sergeinsas, I don't care if its a string. More importantly is how to get this to execute on a specific column range upon new row being added, then writing it back to the same cell. — kkampen
with the new spreadsheet rollout you actually can't do that. It's a known defect and it need to be fixed. You can look at the issue tracker (and star it): code.google.com/p/google-apps-script-issues/issues/… — Harold

mhawksey mhawksey · Accepted Answer · 2014-02-09T22:27:22

I should start by saying I don't think an onChange/onEdit solution is what you are looking for as I'm assuming you are dynamically importing data which might not necessarily trigger an event. I will however answer your question and then show another way of doing this.

Looking at the Google Apps Script documentation for Events you will see that edit events can return 4 parameters: user (owner of Sheet), source (which Sheet), range (where in Sheet) and value. The range in particular is useful because it lets us know where the edit has taken place (here is more on Range Class). It is also worth noting that this information is not returned in Change Event which has broader scope.

It is also worth pointing out that triggers can be set from the Script editor Resources menu or programmatically (see Using Container-Specific Installable Triggers) as you've done in your question. Generally I set trigger events from the Resources menu as I'm often the only user. Sticking with the programmatic route your script could be expressed as (note there is a 2-3 sec delay updating after onEdit events):

function onChange() {
  var sheet = SpreadsheetApp.getActive();
  ScriptApp.newTrigger("fixDate")
           .forSpreadsheet(sheet)
           .onChange()
           .create();
}

function fixDate(e) {
  e.range.setValue(formatDate(e.value)); // set value based on formating existing value
}

function formatDate(aDate){
  var regexp = /([a-zA-Z]{3,4}) ([a-zA-Z]{3}) (\d{2}) (\d{2,4}) (\d{2}:\d{2}:\d{2}\s[A-Z]{3}).*$/; // RegEx to match date format
  return aDate.replace(regexp, "$1, $3 $2 $4 $5"); // return modified date as string
}

To use this you'd run the onChange function once to add the trigger. After this every time there is an edit in the spreadsheet the e parameter which contains the range, value information is used to get and set the edited value. This script will run regardless of the column being edited. You could extend this to check which column is being edited anf only affect column D and F by replacing fixDate with:

function fixDate(e) {
  var col = e.range.getColumn(); // returns int https://developers.google.com/apps-script/reference/spreadsheet/range#getColumn()
  if (col === 4 || col === 6){ // 4 = D, = F 
    e.range.setValue(formatDate(e.value)); // set value based on formating existing value
  }
}

For the formatDate function I'm using a Regular Expression to match parts of the string like dayofweek, month, day etc. returning in you desired format using String.replace(). This is more reliable than splitting on spaces as it looks for a pattern. This means that once a date has been edited it won't be reformatted again because the pattern won't match.

Using built-in formula instead

In the example above we have used a regular expression to return a string. Google Sheets has an existing functions/formula called REGEXREPLACE. Using the same pattern used in formatDate we can use the formula:

=REGEXREPLACE(D1,"([a-zA-Z]{3,4}) ([a-zA-Z]{3}) (\d{2}) (\d{2,4}) (\d{2}:\d{2}:\d{2}\s[A-Z]{3}).*$", "$1, $3 $2 $4 $5")

this will return a value in cell D1 in the desired format. Note this would have to be in a separate column as you can't have a circular reference. The issue might be that you don't want to copy this formula down an entire column manually, but Google Sheets has another formula which essentially allows applying this to a range of formula using ARRAYFORUMLA. To use this you could for example enter in cell G1:

=ARRAYFORMULA(REGEXREPLACE(D:D,"([a-zA-Z]{3,4}) ([a-zA-Z]{3}) (\d{2}) (\d{2,4}) (\d{2}:\d{2}:\d{2}\s[A-Z]{3}).*$", "$1, $3 $2 $4 $5"))

This will output formatted dates for the entire D:D column.

All the code used in this answer including the sheet formula can be found here (File > Make a copy to see code/formula)

How to change the change the date format of a cell onChange?

2 Answers

Using built-in formula instead