Google apps script - form input time changes when copied

Question

I have a Google form to collect info on people leaving the organisation. One of the questions is 'What date and what time do they leave' The response is in the format dd/mm/yyyy, hh:mm. so a typical response would be 24/04/2015 17:00:00, and that's what I see in the Form responses 1 worksheet when the form is submitted.

I need to add the day of the week and copy the information into another worksheet within the spreadsheet, so I use

var leaveDate = inputSheet.getRange("G" + lastRow).getValue();  
var leaveDateTime = Utilities.formatDate(leaveDate, "GMT", "EEE dd-MM-yyyy hh:mm:ss");

The issue I'm seeing is that when I paste the value the time is changing, and what gets pasted is

Fri 24-04-2015 04:00:00

Can anyone explain why this is happening and what I can do to resolve it?

Thanks

Alan Wells Alan Wells · Accepted Answer · 2015-04-17T15:41:17

Apps Script converts date types to the time zone set up in the script editor. In the script editor, you need to set the time zone, even if you've set it in the spreadsheet.

Project Properties

Set the time zone to your local time zone.

Time Zone

Make sure the time zone in your spreadsheet, and the time zone in your script editor match.

If you have multiple editors of the spreadsheet in multiple time zones, and the other users have set the time to their local time, then obviously, their time and your time won't be the same. If someone left the organization on the other side of the world, at 4pm their time, they didn't leave the organziation at 4pm your time. You could assume that if the spreadsheet states 4pm, that it was their local time, and convert the time. In that case, you'd need to know the time zone of every time entry that is made, and then adjust it as necessary.

Google apps script - form input time changes when copied

3 Answers