I'm using this amazing script to create an event from a google sheet. It's working well but the event is created with a 50min difference between the start date in my sheet. I can't figure this out. For example Date Title Start Time End Time Location Description Type Recurring
05.04.2022 Test 06:40:00 07:10:00 Sion test will create an event with a starting hour of 7:30!

Is it about a time zone difference?

//    Date | Title | Start Time | End Time | Location | Description | Type | Recurring | EventID
function exportEventsAvecferie() {
  var ss = SpreadsheetApp.getActive();
  var sheet = ss.getSheetByName("Evénements récurents - Avec fériés");
  var headerRows = 1;  // Number of rows of header info (to skip)
  var range = sheet.getDataRange();
  var data = range.getValues();
  var calId = "[email protected]"; //CalendarApp.getDefaultCalendar().getId();// use default claendar for tests
  var cal = CalendarApp.getCalendarById(calId);
  for (i in data) {
    if (i < headerRows) continue; // Skip header row(s)
    var row = data[i];
    var date = new Date(row[0]);  // First column
    var title = row[1];           // Second column
    var tstart = setTimeToDate(date,row[2]);
    var tstop = setTimeToDate(date,row[3]);
    Logger.log('date = ' date 'tstart = ' tstart '  tstop = ' tstop);
    var loc = row[4];
    var desc = row[5];
    var type = row[6];
    var times = row[7]
    var id = row[8]; 
    // Check if event already exists, update it if it does
    try {
      var event = cal.getEventSeriesById(id);
      event.setTitle('got you');
    }catch(e){
      var newEvent = cal.createEvent(title, tstart, tstop, {description:desc,location:loc});
      row[8] = newEvent.getId();  // Update the data array with event ID
      Logger.log('event created');
      var event = cal.getEventSeriesById(row[8]);
    }
    event.setTitle(title);
    event.setDescription(desc);
    event.setLocation(loc);
    if(type=='M'){
      var recurrence = CalendarApp.newRecurrence().addMonthlyRule().times(times);
      event.setRecurrence(recurrence, tstart, tstop);
    }else if(type=='S'){
      var recurrence = CalendarApp.newRecurrence().addWeeklyRule().times(times)
      event.setRecurrence(recurrence, tstart, tstop);
    }
    data[i] = row ;
  }
  range.setValues(data)
}

function setTimeToDate(date,time){
  var t = new Date(time);
  var hour = t.getHours();
  var min = t.getMinutes();
  var sec = t.getSeconds();
  var dateMod = new Date(date.setHours(hour,min,sec,0))
  return dateMod;
}

CodePudding user response：

I was able to reproduce and to fix the problem. To make this code work you need to synchronize the time zones in:

• Spreadsheet: File > Settings > Time Zone

• Script Editor: Project Settings > Time Zone

• Calendar: Settings > Time Zone

As soon as I set identical time zone (GMT 01:00 Paris, for example) in these three places the script starts work fine.

Probably there is a way to calculate the difference between these time zones and change the time of the event. Something like this: https://stackoverflow.com/a/35691298/14265469 (not for faint hearted, though)