Issues on modifiying Datetime to Date

Question

While on Google Apps Script, I am trying to fix a timestamp of my data into a regular date (M/dd/yyyy).

This is a sample of dates that I get on a specific column of my dataset:

[[2017-06-19T09:53:12+0200], [2017-06-19T10:08:14+0200], [2017-06-19T10:08:55+0200], [2017-06-19T10:14:48+0200], [2017-06-19T10:19:35+0200], [2017-06-19T10:30:44+0200], [2017-06-19T10:32:51+0200], [2017-06-19T10:44:28+0200]]

I have been trying to substring year, month and day in order to concatenate them in the way I want with no success. (Still noobie on JS)

function formatDates () {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("ImportData");
  var data = sheet.getRange("A2:A").getValues(); //Remove header from range


  var year = sheet.getRange(2,1,data.length, 1).getValue().substr(0,4);
  var month = sheet.getRange(2,1,data.length, 1).getValue().substr(5,2);
  var day = sheet.getRange(2,1,data.length, 1).getValue().substr(8,2);

  var fulldate = month +'/'+day+'/'+ year;

  sheet.getRange(2,1,data.length, 1).setValues(fulldate);
}

Can someone help me on that?

Answer 1

You can use split to get parts of date string:

function formatDates() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('ImportData');
  var dataRange = sheet.getRange(2, 1, sheet.getLastRow()-1, 1);
  var data = dataRange.getValues();

  for (var i = 0; i < data.length; i++)
  {
    // get date
    var date = data[i][0].split('T')[0];
    // get date parts
    var dateParts = date.split('-');

    // get date in format MM/dd/yyyy
    var newDate = dateParts[1]+'/'+dateParts[2]+'/'+dateParts[0];

    // rewrite date in data array
    data[i] = [newDate];
  }

  // write new values to sheet
  dataRange.setValues(data);
}