0

Is it just me or has something in Google changed recently? I have a function that adds a timestamp to edits that was working just fine, and just the other day it broke.

It broke in the sense that it used to stamp the time in my timezone - as is specified by the function formatting I use, Now it appears to be timestamping in GMT instead of GMT-8. Nothing has changed in my script, so what happened?

function happyFunTime() {
  var s = SpreadsheetApp.getActiveSheet();
  var r = s.getActiveCell();
  var columnNum = r.getColumn();
  var timeLastCalledColumnOffset = getTimeLastCalledColumnOffset();
  var rowNum = r.getRow();

if (rowNum <=1) return;

  timeLastCalledColumnOffset++;
  // set dateCell = the current row at column J
  var dateCell = s.getRange(rowNum, timeLastCalledColumnOffset);


  var tZone= "GMT-8";
  // Format the current date into datetime format 
  var dateTime = Utilities.formatDate(new Date(), tZone, "MMM-dd-yyyy h:mm a");

  // Set the cell value.  Add apostrophe to force text treatment & keep the spreadsheet from reformatting
    dateCell.setValue("'" + dateTime);

}

getTimeLastCalledColumnOffset() is a custom function to return a number of the column that contains the value I'm interested in (J, so 9 in this case).

Mogsdad
  • 44,709
  • 21
  • 151
  • 275
Prisoner 13
  • 83
  • 3
  • 14

3 Answers3

3

Yes, there are a issue from yesterday because this string "GMT-2" or "GMT-0200" has been working fine and now is faulty.

You can change this line :

   var tZone= "GMT-8"

to this one 

   var tZone= "GMT-08:00"

or you can get the time zone with this lines of code :

  var FUS1 = new Date().toString().substr(25,8);  // I use FUS1 instead of tZone

  // GMT-0800  ->  GMT-08:00 , ...
  var FUS1 = FUS1.substr(0,6) + ':' + FUS1.substr(6)

  // Format the current date into datetime format 
  var dateTime = Utilities.formatDate(new Date(), FUS1 , "MMM-dd-yyyy h:mm a");

or you can writte your time zone from here :

http://en.wikipedia.org/wiki/List_of_tz_database_time_zones

"America/Dawson" -> -08:00 (I don't know where you live :-( )

and this code :

  var dateTime = Utilities.formatDate(new Date(), "America/Dawson" , "MMM-dd-yyyy h:mm a");

Note: if you want to format a date captured from a DateBox (label, textbox, ...) then, the last line, can be something like:

   var dateTime = Utilities.formatDate(new Date(e.paramameter.datebox), "America/Dawson" , "MMM-dd-yyyy h:mm

Sergi

sergicurtu
  • 223
  • 2
  • 6
  • Or more simply for FUS1 : `var FUS1=new Date().toString().substr(25,6)+":00";` – Serge insas Dec 16 '12 at 02:03
  • Very nice! Thank you! Will this handle daylight savings time properly or do I need to look into that? – Prisoner 13 Dec 16 '12 at 02:06
  • It will but if you work with events that are in summer while you are not, then you should use `new Date(the time of the event).toString...` instead of a simple `new Date()` (and of course also for the inverse case) – Serge insas Dec 16 '12 at 02:10
  • Hello Serge, I added some of your comments to the code. About your first comment (+ ': 00'): please note that there are some time zones with ': 30' at the end, eg 'Asia / Calcutta'. – sergicurtu Dec 17 '12 at 10:12
1

Another easy solution to get the right string to include in Utilities.formatDate() is to use Session.getTimeZone() which returns for example 'Europe/Paris' in my case and is a valid argument.

Serge insas
  • 45,904
  • 7
  • 105
  • 131
  • Thank you for that suggestion, that is good information. I suspect this will not be useful in my case, as I have collaborators in other timezones. Presumably this will return the timezone of the user that is running the script? I need to be able to specify a specific timezone - for now that timezone happens to coincide with my own, I'm not sure if that will ever change but that's the situation as of today. – Prisoner 13 Dec 18 '12 at 06:23
0

We can directly change the time zone of spreadsheet using below line

SpreadsheetApp.getActive().setSpreadsheetTimeZone(Session.getScriptTimeZone())

Jaffer
  • 41
  • 2