time zone difference from google sheet to calendar

I have this piece of code that reads from spreadsheet cells task name and a date and schedule a full day calendar event with task name on that date.

/** @OnlyCurrentDoc */

function AddToCalendar1(){

  var sheet = SpreadsheetApp.getActiveSheet();  

  var startRow = 2;  // First row of data to process - 2 exempts my header row
  var numRows = sheet.getLastRow();   // Number of rows to process
  var numColumns = sheet.getLastColumn();

  var dataRange = sheet.getRange(startRow, 1, numRows-1, numColumns); 
  var data = dataRange.getValues();

  var complete = "Yes";

  for (var i = 0; i < data.length; ++i) {
    var row = data[i];
    var eventDate = row[0]; //date to schedule
    var task = row[1];  //job description 
    var status = row[2] // Scheduled?

    if (status != complete) {
      var currentCell = sheet.getRange(startRow + i, 3);

      var c =CalendarApp.getDefaultCalendar();     

      var event = c.createAllDayEvent(task, new Date(eventDate));

      Logger.log('Event ID: ' + event.getId());

      currentCell.setValue(complete);
    }

  }

}

the issue is at this moment it is 11:10 PM GMT-3, now(not sure if it matters) The cell has the value '2019-09-09' but 'eventDate' is 'Sun Sep 08 2019 23:00:00 GMT-0400 (EDT)'

So the task is scheduled the day before.

How can I change this?

Calendar and spreadsheet has the same timezone configuration which is GMT-3

should/can I change the timezone the script is executing?

EDIT I just found adding a few hours I can get this done, but it does not look like the right solution

   var event = c.createAllDayEvent(task, new Date(eventDate.getTime()+3600000*10));

Edit2

this is the result of running the test function

[19-09-10 13:44:06:319 ART] Sheet time zone: America/Los_Angeles
[19-09-10 13:44:06:320 ART] Script time zone: America/New_York
[19-09-10 13:44:06:414 ART] Date from sheet: Thu Oct 10 2019 03:00:00 GMT-0400 (EDT)

Also, not sure what change now I have an error on this line

var event = CalendarApp.getDefaultCalendar().createAllDayEvent(task, new Date(eventDate.getTime()+3600000*10));
Cannot find function getTime in object .

1 answer

  • answered 2019-09-10 05:43 chuckx

    I'd recommend double checking the time zone set for both the sheet and the script.

    I see the behavior you're describing when there's a timezone mismatch between the two.

    I setup a sheet which has the value 2019-09-09 in cell A1. Then I setup a container-bound script with the following function:

    function testTimeZones() {
      Logger.log('Sheet time zone: ' + SpreadsheetApp.getActiveSpreadsheet().getSpreadsheetTimeZone());
      Logger.log('Script time zone: ' + Session.getScriptTimeZone());
      Logger.log('Date from sheet: ' + SpreadsheetApp.getActiveSheet().getRange(1, 1).getValue());
    }
    

    When both the sheet and the script have their time zones set to (GMT-03:00) Buenos Aires, this is the logging output after executing that function:

    [19-09-09 22:28:26:039 PDT] Sheet time zone: America/Buenos_Aires
    [19-09-09 22:28:26:041 PDT] Script time zone: America/Argentina/Buenos_Aires
    [19-09-09 22:28:26:127 PDT] Date from sheet: Mon Sep 09 2019 00:00:00 GMT-0300 (ART)
    

    However, when I switch to script time zone to (GMT-05:00) Eastern Time, this is the logging output:

    [19-09-09 22:37:34:116 PDT] Sheet time zone: America/Buenos_Aires
    [19-09-09 22:37:34:118 PDT] Script time zone: America/New_York
    [19-09-09 22:37:34:207 PDT] Date from sheet: Sun Sep 08 2019 23:00:00 GMT-0400 (EDT)
    

    Which matches the behavior you're seeing.

    Two things to try:

    1. Verify the script time zone by checking File -> Project Properties in the Script Editor.
    2. If 1 doesn't work, as a sanity check, try executing the test function above in your project. Either alter it to point to a cell with a date value, or use it as is by creating a temporary sheet with a date in cell A1. Consider updating the question with the results to provide a bit more context.