How to format the date in Google Sheets using Apps Script?

user_11641273 0
Viewed 2007

I am using Google Apps Script to create custom reports in Google Sheets. The data that I am putting in Google Sheets is coming from JSON data that I am parsing in. One of my columns contains a date and time string from the JSON and I want to format it so Google Sheets recognizes it as a date and time rather than a string.

Currently, the string displays as the following in Google Sheets: 

2019-06-10T22:00:00.000Z

I am not sure how to change the format so it looks like a proper date and time.

Edit: I would like it to look something like:

10-Jun HH-MM
google-apps-script javascript
1 Answers

Requirement:

Format date string in Google Apps Script.

Solution:

Pass date string to date constructor new Date() then use Utilities.formatDate to format it correctly.

Example:

function convertDate() {
  var sh = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var date = sh.getRange(1,1).getValue(); //this is your value '2019-06-10T22:00:00.000Z'
  var d = Utilities.formatDate(new Date(date),'GMT+0','dd-MMM HH-mm');
  Logger.log(d);
  //logger returns: 10-Jun 22-00
}

References:

  1. new Date() for date constructor.
  2. Utilities.formatDate() for formatting dates in Google Apps Script.
user_114273750
Related