Google Script Calendar Events Time Zone Is Off 7 hrs

Question

I am new to google scripts (any programming for that matter and have pieced together a few scripts I've found on the internet, then changed a few things I could figure out to make it work for my purposes. I am VERY new to this and would appreciate any teaching/explanations you can offer rather than just "the answer". :) Thanks in advance!

In short, I have created this script to calculate employee hours based off a google calendar. The script gathers all calendars, prompts me with a checkListBox that has me select the specific calendar name, and then beginning and ending payroll dates for the range of events. I hit submit and it returns the range along with the length (in hours) of the event. This is then totaled in a script generated header that calculate the total pay for the employees hours based off a vlookup pay rate elsewhere on the spreadsheet.

Everything works great .... except .... the time zone is off somewhere (7+ hrs). This is a problem because some of the events land on the date outside of the payroll date I've selected. For example, one event date starts on the 15th of january at 8pm MST (which is my real time zone - MST or GMT-7). When you add 7 hours to 8 pm, were at 3 am on the 16th... payroll ends on the 15th at midnight and this time isn't counted in the total.

I can't seem to figure out where to change the script for the time zone.... The searches I have done here on Stackoverflow urged me to checked my calendar tz, and it is set to MST (or GMT-7). I've also checked the script properties and it's timezone is also set to MST. There is code (that I didn't put there... like I said I copied it from somewhere else) that seems to be indicating something with time zones, but when changed it has no effect on the output of the script. One example is in the first line : var FUS1=new Date().toString().substr(25,6)+':00';

To help you help me, I've included the code below. I didn't know exactly what parts of the code to include (remember, I'm new at this), so I've added nearly everything.. I apologize if that is not kosher.

var FUS1=new Date().toString().substr(25,6)+':00';
//example : Fri Sep 24 2010 10:00:00 GMT+0200 (CEST) 

// 
function onOpen() {   
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  Logger.log(FUS1)
  var menuEntries = [{name: "Import from Calendar", functionName: "Cal_to_sheet"},
                 {name: "Delete all sheets", functionName: "delsheets"}
                ];  
  ss.addMenu("Calendar Uitilities", menuEntries);  
}
//
function importEvents(e) {
  var calendar_name = e.parameter.calendar;
  var startDate = new Date(e.parameter.start);
  var endDate = new Date(e.parameter.end);
  var Calendar = CalendarApp.getCalendarsByName(calendar_name);
  var sheetName = calendar_name 
// 
  var events = Calendar[0].getEvents(startDate , endDate);
  if (events[0]) {
    var eventarray = new Array();
    var line = new Array();
    line.push('Event','Date','Hours');
    eventarray.push(line);
    for (var i = 0; i < events.length; i++) {
      line = new Array();
      FUS1=new Date(events[i]).toString().substr(25,6)+':00';
      line.push(events[i].getTitle());
      line.push(Utilities.formatDate(events[i].getStartTime(), FUS1, "MMM-dd-yyyy"));
      line.push((events[i].getEndTime() - events[i].getStartTime()) / 3600000);
      eventarray.push(line);
    }
    Logger.log(eventarray);// 2D array ready to write to sheet
    var sheet = SpreadsheetApp.getActiveSpreadsheet().insertSheet(sheetName);
    sheet.getRange(3,1,eventarray.length,eventarray[0].length).setValues(eventarray);
    sheet.setColumnWidth(1, 150);sheet.setColumnWidth(2, 75);sheet.setColumnWidth(3, 50)
    sheet.setFrozenRows(1)
    sheet.getRange('A' +     (1)).setValue(sheetName).setBorder(true,true,true,true,true,true).setBackgroundColor('#6C86BA');
    sheet.getRange('B' + (1)).setValue('').setBorder(true,true,true,true,true,true).setBackgroundColor('#6C86BA');
    sheet.getRange('C' + (1)).setValue('Total \n Pay').setBorder(true,true,true,true,true,true).setBackgroundColor('#52CC52');
    sheet.getRange('A' + (2)).setValue('Employee Pay Rate --->').setBorder(true,true,true,true,true,true).setBackgroundColor('#85A6E6');
    sheet.getRange('B' + (2)).setFormula('=vlookup(A1,\'DO NOT MOVE!\'!H4:I,2,false)').setBorder(true,true,true,true,true,true).setBackgroundColor('#85A6E6'); 
    sheet.getRange('C' + (2)).setFormula('=C3*B2').setBorder(true,true,true,true,true,true).setBackgroundColor('#66FF66');
  sheet.getRange(3,1,1,3).setBorder(true,true,true,true,true,true).setBackgroundColor('#94B8FF');
    sheet.getRange('A' + (3)).setValue('Event Description').setBorder(true,true,true,true,true,true).setBackgroundColor('#94B8FF');
    sheet.getRange('B' + (3)).setValue('Total').setBorder(true,true,true,true,true,true).setBackgroundColor('#94B8FF');
    sheet.getRange('C' + (3)).setFormula('=SUM(C4:C' + (i+3)+ ')').setBorder(true,true,true,true,true,true).setBackgroundColor('#94B8FF');
    sheet.getRange('D' + (3)).setValue('Notes').setBorder(true,true,true,true,true,true).setBackgroundColor('#94B8FF');

  } else {
    var startstring = Utilities.formatDate(e.parameter.start, FUS1, "MMM-dd-yyyy");
    var endstring = Utilities.formatDate(e.parameter.end, FUS1, "MMM-dd-yyyy");
    Browser.msgBox('no event between ' + startstring + ' et le ' + endstring +' in calendar '+calendar_name);
  }

  var app = UiApp.getActiveApplication();
  app.close();
  return app;  
}

function Cal_to_sheet() {
  var doc = SpreadsheetApp.getActiveSpreadsheet();
  var app = UiApp.createApplication().setTitle('Calendar Import').setHeight('320').setWidth('440').setStyleAttribute('background','beige');

 // Create a grid with 3 text boxes and corresponding labels
 var grid = app.createGrid(3, 2);
 grid.setWidget(0, 0, app.createLabel('Calendar:').setWidth('100'));
 var list = app.createListBox();
 list.setName('calendar');
 grid.setWidget(0, 1, list);
 var calendars = CalendarApp.getAllCalendars();

 for (var i = 0; i < calendars .length; i++) {
   list.addItem(calendars[i].getName());
 }

 grid.setWidget(1, 0, app.createLabel('Payroll Start date:').setWidth('110'));
 grid.setWidget(1, 1, app.createDateBox().setId("start"));
 grid.setWidget(2, 0, app.createLabel('Payroll End date :').setWidth('110'));
 grid.setWidget(2, 1, app.createDateBox().setId("end"));
 var panel = app.createVerticalPanel();
 panel.add(grid);
 var button = app.createButton('Submit');
 var handler = app.createServerClickHandler('importEvents');
 handler.addCallbackElement(grid);
 button.addClickHandler(handler);
 panel.add(button);
 app.add(panel);
 doc.show(app);
}

Thanks again for any help,

K

thoughtcrime thoughtcrime · Accepted Answer · 2014-02-13T04:43:56

line.push(Utilities.formatDate(events[i].getStartTime(), "GMT-7", "MM-dd-yyyy HH:mm"));

It looks like FUS1 is a fancy way to set the timezone by stripping some characters out of a new Date() method. I'd just cut the references to it out, and replace them with the timezone "GMT-7" for MST. However, if you're using this for a time sheet, maybe you'd be better off keeping the event start time as a raw value (which is in milliseconds since the beginning of 1900, I think). You can format that final value into the number of hours and minutes worked later with anothe function.

Can you add a timezone argument to that line.push like I did above? I've used the following when setting a variable:

var date = Utilities.formatDate(new Date(), "GMT-6", "MM/dd/yyyy HH:mm");

In my case, new Date() takes the place of your events[i].getStartTime(), and it seems to take as many arguments as you can shove at it, as long as they make sense. Anywhere you use Utilities.formatDate(), add the timezone argument to the list, and let me know.

Unfortunately, I don't have links or research, since I'm new at this, but I'm just showing what has worked in code that I've done.

Google Script Calendar Events Time Zone Is Off 7 hrs

1 Answers