append row with date in Google Sheet using Google Apps Scripts and retain date formatting specified in sheets

user_3383907 0
Viewed 2331

I have a column in my sheet with a custom date format. The format is set on the entire column. If put a date value in the column from within sheets it shows formatted the way I specified. But if I append a new row using Google Apps Scripts it will not obey the formatting.

Before adding a row through Google Apps Script:

| Timestamp                 | Message |
|---------------------------|---------|
| 2019-01-01 @ 13:12:32.013 | test    |

After adding a row:

| Timestamp                 | Message |
|---------------------------|---------|
| 2019-01-01 @ 13:12:32.013 | test    |
|         1/2/2019 14:51:31 | test2   |

Google Apps Script code to add the row:

SpreadsheetApp.getActiveSheet().appendRow([new Date(), "test2"])

Is there a way to get the date to be added in a way Sheets will format it per the formatting rule?

The only way I have found is converting the data to an integer representing the number of days since 1899-12-30 but this seems very hacky.

var dateSerial = ((new Date()) - (new Date(1899, 11, 30))) / 1000 / 60 / 60 / 24;
google-apps-script format date javascript
1 Answers

As @Cooper mentioned in his comment, you can achieve this with Utilities.formatDate().

I tested with the below script and the date format comes out exactly as you want it.

function addDate() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sh = ss.getActiveSheet();
  var tz = ss.getSpreadsheetTimeZone();
  var d = Utilities.formatDate(new Date(), tz, 'yyyy-MM-dd @ hh:mm:ss.SSS');
  sh.appendRow([d,'test']);
}

All you really need to be interested in is var d where the date is defined. I used the Spreadsheet timezone for this but you can use any time zone you'd like.

var d = Utilities.formatDate(new Date(), tz, 'yyyy-MM-dd @ hh:mm:ss.SSS');

Edit:

User does not want to set date format in the script itself, instead we can grab the number format from the column above using getNumberFormat() and then format the new entry based on this, using setNumberFormat().

See script below for details.

function setDate() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sh = ss.getActiveSheet();
  var lr = sh.getLastRow();

  //get format for last row in column A
  var format = sh.getRange(lr, 1).getNumberFormat();

  //add new data to sheet
  sh.appendRow([new Date(), 'test']);

  //format new data
  sh.getRange(lr+1, 1).setNumberFormat(format);
}
user_114273750
Related