Javascript – Calendar Event Time Incorrect in Script despite Correct Formatting in Google Sheets

I’m currently working on a Google Apps Script that automatically creates events in Google Calendar based on data from a Google Sheets document. The script executes successfully, and events are created, but the event times are incorrect, despite the correct time values being input in Google Sheets.

Issue
For instance, in the data, I request an event to be created at 8:00 AM, but when the event is created, it shows up as 8:52 AM in the calendar. The event time seems shifted by 52 minutes.

Here is the script I’ve used so far:

function createCalendarEvent() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1');
  var calendarId = '[email protected]'; // Replace with your actual calendar ID
  var calendar = CalendarApp.getCalendarById(calendarId);

  // Check if the calendar was retrieved successfully
  if (!calendar) {
    Logger.log("Calendar not found. Please check the calendar ID.");
    return;
  }

  // Get the data from the specified range
  var dataRange = sheet.getRange("P2:U" + sheet.getLastRow());
  var rows = dataRange.getValues();

  for (var i = 0; i < rows.length; i++) {
    var eventName = rows[i][0]; // Column P: Event Name
    var eventDate = new Date(rows[i][1]); // Column Q: Date
    var startTime = rows[i][2]; // Column R: Start Time
    var endTime = rows[i][3]; // Column S: End Time
    var description = rows[i][4]; // Column T: Description
    var location = rows[i][5]; // Column U: Location (optional)

    if (isNaN(eventDate.getTime())) {
      Logger.log('Invalid date on row ' + (i + 2));
      continue;
    }

    if (startTime && endTime) {
      var startDateTime = new Date(eventDate);
      startDateTime.setHours(Math.floor(startTime * 24), (startTime * 24 * 60) % 60);

      var endDateTime = new Date(eventDate);
      endDateTime.setHours(Math.floor(endTime * 24), (endTime * 24 * 60) % 60);

      // Create the event in Google Calendar
      calendar.createEvent(eventName, startDateTime, endDateTime, {
        description: description,
        location: location
      });

      Logger.log('Event created: ' + eventName + ', Start: ' + startDateTime + ', End: ' + endDateTime);
    } else {
      Logger.log('Invalid time on row ' + (i + 2));
    }
  }
}

What I’ve Tried:

1. Checked the Calendar Object: Verified that the calendar is correctly fetched using getCalendarById(). The event is successfully created, so the calendar is being accessed.

2. Date Formatting: Verified that the date format in Google Sheets is correct. The =ISDATE() formula confirms that the date column is valid.

3. Time Formatting: Used the =ISNUMBER() formula to check the start and end times. It returns TRUE, confirming the time is valid.

4. Combining Time with Date: For both start and end times, I used setHours() to combine the time values with the event date. Despite this, the event appears with an incorrect time shift (usually around 52 minutes later than expected).

Question:

• How can I ensure that the time read from the sheet and set in the calendar matches the exact time in Google Sheets?
• Is there a better way to handle time values from Google Sheets that might prevent this 52-minute shift?

Any help is greatly appreciated!