CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheetsgoogle-calendar-apiintegration
Dialdo
Dialdo

Reputation: 3

Google Sheet to Google Calendar Integration

I am a beginner, writing a code for google sheet to create event on google calendar based on data entered on a google sheet. My start Date and end date is written in this format: yyyy", "m", "d", "h", "m", "s for example : on cell Z2 it is written as: 2023, 2, 1, 11, 0, 0. Time Zone on my google calendar is set to GMT+07:00. The code has successfully create event on the right date but the time is always wrong for example on cell Z2 the script has successfully created an event on 2 February 2023 but the time of event is logged incorrectly (00:00 - 01:00) when it should be (11:00 - 12:00). Can anyone help me identify whether the problem is in my datetime format on the google sheet or is it on the code itself instead? Any help is appreciated.

here is the code i have tried:

function createEvent() {
  var spreadsheet = SpreadsheetApp.openById('SpreadsheetId');
  var sheet = spreadsheet.getSheetByName('SheetName');

  var data = sheet.getRange(2, 1, sheet.getLastRow() - 1, sheet.getLastColumn()).getValues();

  var calendar = CalendarApp.getCalendarById('Calendar ID');

  for (var i = 0; i < data.length; i++) {
    var row = data[i];
    var title = row[31]; // Column AF
    var description = row[3]; // Column D
    var startDate = new Date(row[25]); // Column Z
    var endDate = new Date(row[26]); // Column AA

    var event = calendar.createEvent(title, startDate, endDate, { description: description });

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

Upvotes: 0

Views: 454

Answers (1)

Linda Lawton - DaImTo
Linda Lawton - DaImTo

Reputation: 116978

Stop adding a timezone, by default timezone is configured in the calendar itself. Google knows what timezone the calendar is configured in.

So if you insert it at 10 in the morning it will insert at ten in the morning for that calendars timezone.

By adding timezone you tend to confuse things and the results are well unreelable.

Upvotes: 0

Related Questions