Odd Issue of Date Changing in Script Output

Question

A colleague of mine has written a Google Apps script to automate a workflow (approving room comp requests at a hotel). The script, by and large, seems to be functioning as expected. There is a function in the script that runs on an automated schedule (time based, not event driven). There have been several instances where the check-in date has been off by one, leading to a booking error. Unfortunately, it does not occur for all requests. All relevant users making the requests are in the same timezone (EST).

For example, I've seen incidents where a request is submitted with a check-in date of 10/22 and a check-out date of 10/23 but when the script runs and the submitter receives notice of the approval the check-in date shifted to 10/21 and check-out to 10/22.

The relevant portion of the HTML email is simply pulling values from a spreadsheet row: + "

" + "Est Check In Date: " + Utilities.formatDate(row.arrivalDate, "EST", "MM-dd-yyyy") + "

" + "Est Check Out Date: " + Utilities.formatDate(row.departureDate, "EST", "MM-dd-yyyy")

What could cause this behavior? Could this be because of the time on a given Google server where the code is executed?

Answer 1

I had a similar problem but mine was from entering the date in a form in the format MM/dd/yyy. When getting the date from the spreadsheet to post to an email, it was always one day less.

I'm in Eastern time (GMT-5:00). I verified that it was correct in my Project properties and my Spreadsheet setting. After digging deeper, I realized that although the date entered in the form and displayed in the spreadsheet was 7/27/2012 the actual date in the spreadsheet was:

Thu Jul 26 21:00:00 PDT 2012

This made "some" sense, as 7/27/2012 00:00:00 Eastern Time (GMT-5:00) would be the day before in PDT by three hours. I simply formatted the date GMT. I could have formatted it to EDT, and it should have always worked, but hard coding it GMT more or less took the guessing out of someone else reading the code if the code would need to be changed as the seasons changed. I only needed the date.

I did copy and paste the FUS1 example in this post (for now as comments) as a more "eloquent" way of handling the problem. Thanks!

Answer 2

Please ensure that the timezone of the spreadsheet (located in File > Spreadsheet settings and the timezone of the script project (located in File > Project properties) are both set correctly. The timezone of the spreadsheet controls how date/time values are read from cells, while the timezone of the script controls the default for new Date objects, triggers, etc.

Answer 3

From my experience it could be a result of a confusion between EST and EDT, especially if data where submitted in the changeover period. Let me take an example : suppose we are in winter time (EDT) and that one submit a request for a date in EST : if your script forces the date to be EST the EDT date will be misunderstood. Depending how you set hours it could result in a day shift. What I did that solved the problem radically is to take the current summer/winter time into account while using Utilities.formatDate(). Here the code snippet I use, maybe you could try it : (I'm in europe so values are different but the principle is the same)

  var FUS1=new Date().toString().substr(25,8);// FUS1 gets the GMT+0200 or GMT+0100 string
// Fri Sep 24 2010 10:00:00 GMT+0200 (CEST) for example
  if (FUS1!="GMT+0200"){FUS1="GMT+0100"};// and takes care of summer time !
  var today=Utilities.formatDate(new Date(),FUS1,"dd-MM-yyyy")+" à "+Utilities.formatDate(new Date(),FUS1,"HH:mm")

for a full description you can have a look at this issue I raised long ago which was not really an issue ;-)