javascript Google Apps 脚本中的格式化日期
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11875989/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Formatted dates in a Google Apps script
提问by user1586374
I'm trying to get a spreadsheet with simple formatted dates when a form is submitted, but all dates, including the timestamp keep getting posted as "Dec 31 1969 2:00PM"... What am I doing wrong? ANY help would be greatly appreciated.
我正在尝试在提交表单时获取带有简单格式日期的电子表格，但是所有日期，包括时间戳记一直被发布为“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);
}
回答by Serge insas
I think the 'dates' in your spreadsheet are not date objects, they are just strings and JavaScript evaluate it as 0, that's why you get a date value of the 'origin' (aka epoch) minus GMT offset...depending on how these values comes in the cells there might be different solutions to this. Can you show example data an tell how they were created?
我认为电子表格中的“日期”不是日期对象，它们只是字符串，JavaScript 将其评估为 0，这就是为什么您会得到“原点”（又名纪元）减去 GMT 偏移量的日期值......取决于如何这些值来自单元格，对此可能有不同的解决方案。你能展示示例数据并说明它们是如何创建的吗？
To check if my guess is right you could simply try to change the display format of the 'date' cells using the spreadsheet interface.If you can change it there and get coherent results then I'm wrong... if not ... well I guess I'm right :-)
要检查我的猜测是否正确，您可以简单地尝试使用电子表格界面更改“日期”单元格的显示格式。如果您可以在那里更改它并获得一致的结果，那么我错了......如果不是......好吧，我想我是对的:-)
回答by Doberon
I suggest review this:
我建议回顾一下：
And the format patterns are these:
格式模式是这些：
http://docs.oracle.com/javase/7/docs/api/java/text/SimpleDateFormat.html
http://docs.oracle.com/javase/7/docs/api/java/text/SimpleDateFormat.html
And one example to help you
一个例子来帮助你
Utilities.formatDate(thisFile.getLastUpdated(),"GMT-5", "yyyy/MM/dd, HH:mm:ss")
Utilities.formatDate(thisFile.getLastUpdated(),"GMT-5", "yyyy/MM/dd, HH:mm:ss")
回答by Amany
This code takes date of first row which carries "Timestamp", i.e. the new Date ("TimeStamp")so it's getting back the wrong date as getRowIndex return 1:
此代码采用带有“时间戳”的第一行的日期，即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
