Dates format changes to mm/dd/yyyy when put to spreadsheet from google apps script

0
votes

Here is my simple code:

function doPost(request) {
  var deviceId="JHjdkfjak123214";
  var emailId="[email protected]";

  var sheet=SpreadsheetApp.getActiveSpreadsheet().getSheets()[0];

  var rowvals=[];
  var formattedDate = Utilities.formatDate(new Date(), "GMT", "dd-MMM-yyyy");
  Logger.log(formattedDate);
  rowvals.push(deviceId);
  rowvals.push(emailId);
  rowvals.push(formattedDate);
  sheet.appendRow(rowvals);
}

On running this code, the Logger.log shows date like: 

29-Jul-2014

But in spreadsheet it gets entered as 7/29/2014. I have changed the format of the cells using the abc button so that all cells of the date column have format dd-MMM-yyyy.

Now when entered manually, the dates appear in the format dd-MMM-yyyy. But when entered by script, again they change to MM/dd/yyyy. Whats the solution for this?

1
datetimegoogle-apps-scriptgoogle-sheets

1 Answers

2
votes

You don't need to format date using Utilities.formatDate() that converts dates to strings.

The spreadsheet tries to be smart and reconverts these strings to dates again but sometimes its not smart enough !

keep date objects as dates and you should get consistent results that work as well for keyboard input ans script output.

EDIT : it shows indeed a complete date value, change code by adding this line after appendRow.

  ...
  sheet.getRange(sheet.getLastRow(),3).setNumberFormat('dd MMM yyyy')
}

full code below :

function doPost(request) {
  var deviceId="JHjdkfjak123214";
  var emailId="[email protected]";
  var sheet=SpreadsheetApp.getActiveSpreadsheet().getSheets()[0];
  var rowvals=[];
  var formattedDate = new Date();
  Logger.log(formattedDate);
  rowvals.push(deviceId);
  rowvals.push(emailId);
  rowvals.push(formattedDate);
  sheet.appendRow(rowvals);
  sheet.getRange(sheet.getLastRow(),3).setNumberFormat('dd MMM yyyy');// add separators if you want or change the format here
}