4

I have input on a Google spreadsheet specific dates that an e-mail should be sent. I have a script that gets the values from the spreadsheet to either (1) compare whether the "date email should be sent" equals today, if so, run script or (2) input into the spreadsheet today's date, which is the "date email was sent".

I am seeing the logger report 1 day behind the date that is actually in the spreadsheet. Example: Spreadsheet shows 3/11/2015 , and logger shows Tue Mar 10 22:00:00 PDT 2015, GMT-6:00, MM-dd-yyyy. Is this a result of daylight savings time? I have read of people having similar problems related to DST and scripts/triggers/spreadsheets.

The time zones for the Script Project and the Spreadsheet itself are both set to GMT-6 (central time), so there is no discrepancy there. I wasn't having this problem (to my knowledge) before the time change. Session.getTimeZone() although depreciated returns correct time zone (GMT-6).

If I use new Date() it returns today's date correctly. When I use Utilities.formatDate(sheet.getRange("A1:A1").getValue(), "GMT-6", "MM-dd-yyyy"); (where cell A1 contains the date) it reports in the log as 1 day behind what the actual date is showing on the spreadsheet.

What I have tried:

  1. Formatting time zone as "GMT-6:00" instead of just "GMT-6" makes no difference, although user Sergi suggested the change here.

  2. I tried referencing "today's date" from the spreadsheet (using =today()) instead of through the script with new Date (). This still causes the problem. When the script records "date email was sent (which is today)" back into the spreadsheet, it shows as 1 day behind.

Other options? Answer #6 on Google Script issues and Sergi's answer again suggested converting it to a string. Can someone explain (1) what the difference is in doing so and (2) how I would write it if referencing a cell value instead of new Date. Is it like this after defining the FUS1 variable?

 Utilities.formatDate(sheet.getRange("A1:A1").getValue(), FUS1 , "MMM-dd-yyyy h:mm a");

Thank you!!

Community
  • 1
  • 1
keyboardmouse
  • 125
  • 4
  • 11

3 Answers3

5

I think I might have found the answer to my own question. This reported Google Script Issue # 1035 indicates that the formatDate utility is incorrectly reading dates from the spreadsheet.

This answer and also this answer within that thread specifically provided a solution that currently seems to be working. Specifically, instead of using the time zone format "GMT-6:00" or any variation of "GMT-", one has to instead use the specific Time Zone name (as found here from Wikipedia). So, for Central Time I have input this and it seems to have fixed my issue. I will report back if this fix does not remain working.

var emailDate = Utilities.formatDate(sheet.getRange("A1:A1").getValue(), "US/Central", "MM-dd-yyyy");
keyboardmouse
  • 125
  • 4
  • 11
1

When you type a date into a cell of a Google sheet, it does NOT automatically record the current time. When you manually type a date into a sheet cell, the time is set to 0:00:00. It doesn't matter what time of the day it is, if you type in only the date, the time setting will always be 0:00:00. So any time zone settings that you use in code, trying to affect the hour, are affecting the time relative to the recorded time of zero. So, unless you explicitly type in the current time, along with the date, the time will be set to 0:00:00 when manually entering a date.

If you want the time to be recorded at the moment that you put the date into the cell, you could enter a function into the cell:

=now()

Then copy the cell, and Paste, Special Values Only.

Or you will need to manually type in the time along with the date. You can test this, by changing the Date/Time format of the cell to Date time.

So, this issue may have nothing to do with your code.

Alan Wells
  • 30,746
  • 15
  • 104
  • 152
  • Thanks for your response. I understand what you are saying about identifying time within Excel if I needed to specify corresponding time in script. I am actually only trying to extract the date; I don't need the time. So, I have follow up questions -- what would be the reason that a date input in Excel (example 3/15/2015) would show 1 day behind in the log (as 3/14/15)? And, is there a way to use `formatDate` to only get m/d/y and ignore time? – keyboardmouse Mar 16 '15 at 16:58
0

As of today, Google Sheets supports timezone settings for each sheet you create.

I fixed this exact issue by File > Spreadsheet Settings > Locale, and ensuring that it matched my local time. My date objects were all 2hrs off, and it was driving me nuts.

My hunch is that the Google Apps Script editor correctly calculated new Date() objects based on your browser's current local date/time, while the spreadsheet defaulted to a different timezone. For me, this was EST.

Chazbot
  • 1,890
  • 1
  • 13
  • 23