Formatted dates in Google Apps script

Question

I am trying to get a table with simple formatted dates when I submit the form, but all dates, including the timestamp, will be published as "December 31, 1969 2:00 pm" ... What am I doing wrong? ANY help would be greatly appreciated.

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

I think the “dates” in your table are not date objects, they are just rows, and JavaScript evaluates them to 0, so you get the date value “origin” (aka epoch) minus GMT offset ... depending on how these values ​​enter the cells, there may be different solutions. Can you show sample data and tell how it was created?

To check the correctness of my guess, you can just try changing the display format of the date cells using the spreadsheet interface. If you can change it there and get consistent results, then I'm wrong ... if not ... well, I think I'm right :-)

Answer 2

I suggest considering this:

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

And format templates:

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

And one example that will help you

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

Answer 3

This code takes the date of the first line that carries a “time stamp”, i.e. new Date ("TimeStamp") , so it returns an invalid date since getRowIndex returns 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