Google Apps脚本中的格式化日期

Question

我试图在提交表单时获得一个带有简单格式化日期的电子表格，但包括时间戳在内的所有日期都被发布为“1969年12月31日下午2:00”……我做错了什么？任何帮助都将不胜感激。

function formSubmitReply(e) {
  var sheet = SpreadsheetApp.getActiveSheet();
  var row = sheet.getActiveRange().getRowIndex();
  var lastRow = sheet.getLastRow();
  // Set the status of a new ticket to 'New'.

  sheet.getRange(lastRow, getColIndexByName("Status")).setValue("New");

  var ticketTime = sheet.getRange(row, getColIndexByName("Timestamp")).getValue();

  var subdate = Utilities.formatDate(new Date(ticketTime), "GMT-10", "EEE MM/dd/yyyy 'at' h:mm a");
  sheet.getRange(lastRow, getColIndexByName("Timestamp")).setValue(subdate);

  var sDate = sheet.getRange(row, getColIndexByName("Start Date")).getValue();
  var strtdate = Utilities.formatDate(new Date(sDate), "GMT-10", "EEE, MMM dd, yyyy");
  sheet.getRange(lastRow, getColIndexByName("Start Date")).setValue(strtdate);

  var sTime = sheet.getRange(row, getColIndexByName("Start Time")).getValue();
  var strttime = Utilities.formatDate(new Date(sTime), "GMT-10", "h:mm");
  //sheet.getRange(lastRow, getColIndexByName("Start Time")).setValue(strttime);

  var eDate = sheet.getRange(row, getColIndexByName("End Date")).getValue();
  var enddate = Utilities.formatDate(new Date(eDate), "GMT-10", "EEE, MMM dd, yyyy");
  sheet.getRange(lastRow, getColIndexByName("End Date")).setValue(enddate);

  var eTime = sheet.getRange(row, getColIndexByName("End Time")).getValue();
  var endtime = Utilities.formatDate(new Date(eTime), "GMT-10", "h:mm");
  //sheet.getRange(lastRow, getColIndexByName("End Time")).setValue(endtime);
}

Answer 1

我认为你电子表格中的‘date’不是date对象，它们只是字符串，JavaScript将其计算为0，这就是为什么你得到一个'origin‘(又称纪元)减去GMT offset...depending的日期值，关于这些值是如何在单元格中出现的，可能会有不同的解决方案。你能展示示例数据并告诉他们是如何创建的吗？

要检查我的猜测是否正确，您可以简单地尝试使用电子表格interface.If更改“日期”单元格的显示格式。您可以在那里更改它并获得一致的结果，那么我就错了……如果不是..。好吧，我想我是对的:-)

Answer 2

我建议回顾一下：

https://developers.google.com/apps-script/reference/utilities/utilities?hl=es#formatDate(Date,String,String)

格式模式如下：

http://docs.oracle.com/javase/7/docs/api/java/text/SimpleDateFormat.html

下面是一个帮助您的示例

Utilities.formatDate(thisFile.getLastUpdated()，"GMT-5"，"yyyy/MM/dd，HH:mm:ss")

Answer 3

这段代码获取带有“时间戳”的第一行的日期，也就是new Date ("TimeStamp")，因此它返回错误的日期，因为getRowIndex返回1：

function formSubmitReply(e) {
  var sheet = SpreadsheetApp.getActiveSheet();
  var row = sheet.getActiveRange().getRowIndex();
  //try to increment row as below to get row 2
  row++;
  var lastRow = sheet.getLastRow();
  //increment last row to write to new row to observe new date
  lastRow++;
  // Set the status of a new ticket to 'New'.

  sheet.getRange(lastRow, getColIndexByName("Status")).setValue("New");

  var ticketTime = sheet.getRange(row, getColIndexByName("Timestamp")).getValue();

  var subdate = Utilities.formatDate(new Date(ticketTime), "GMT-10", "EEE MM/dd/yyyy 'at'   h:mm a");
  //use log window select views-> logs to see your output
  Logger.log("subdate : "+subdate);
  sheet.getRange(lastRow, getColIndexByName("Timestamp")).setValue(subdate);

  var sDate = sheet.getRange(row, getColIndexByName("Start Date")).getValue();
  var strtdate = Utilities.formatDate(new Date(sDate), "GMT-10", "EEE, MMM dd, yyyy");
  sheet.getRange(lastRow, getColIndexByName("Start Date")).setValue(strtdate);

  var sTime = sheet.getRange(row, getColIndexByName("Start Time")).getValue();
  var strttime = Utilities.formatDate(new Date(sTime), "GMT-10", "h:mm");
  //sheet.getRange(lastRow, getColIndexByName("Start Time")).setValue(strttime);

  var eDate = sheet.getRange(row, getColIndexByName("End Date")).getValue();
  var enddate = Utilities.formatDate(new Date(eDate), "GMT-10", "EEE, MMM dd, yyyy");
  sheet.getRange(lastRow, getColIndexByName("End Date")).setValue(enddate);

  var eTime = sheet.getRange(row, getColIndexByName("End Time")).getValue();
  var endtime = Utilities.formatDate(new Date(eTime), "GMT-10", "h:mm");
  //sheet.getRange(lastRow, getColIndexByName("End Time")).setValue(endtime);
}

//enter code here