javascriptdategoogle-apps-scriptgoogle-calendar-api

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 = 'your_calendar_id_here@gmail.com'; // 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:

Any help is greatly appreciated!

Edit 1:-

Thanks a lot everyone for your support but I was so busy and confused with the errors that I forgot to check the time zone of my google sheet and have no clue how it got altered. Damn you Google..! LOL. below is the full code which is working now.

function createCalendarEvents() {
  // Select the Calendar by ID
  const calendarId = 'your-calendar-id@group.calendar.google.com'; // Replace with your actual Calendar ID
  const calendar = CalendarApp.getCalendarById(calendarId);

  // Get the active spreadsheet and the specific data range (P2:U)
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('YourSheetName'); // Replace with your sheet name
  const dataRange = sheet.getRange("P2:U" + sheet.getLastRow());
  const rows = dataRange.getValues(); // Get all data in the range

  // Loop through the rows and create events
  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: Event date
    var startTime = new Date(rows[i][2]); // Column R: Start time
    var endTime = new Date(rows[i][3]);   // Column S: End time
    var description = rows[i][4];      // Column T: Event description
    var location = rows[i][5];         // Column U: Event location

    // Skip if any of the key values are missing
    if (!eventName || !eventDate || !startTime || !endTime) {
      Logger.log(`Missing data on row ${i + 2}`);
      continue;
    }

    // Set the correct hours and minutes for start and end times
    const startDateTime = new Date(eventDate);
    startDateTime.setHours(startTime.getHours(), startTime.getMinutes(), 0, 0); // Set start time
    
    const endDateTime = new Date(eventDate);
    endDateTime.setHours(endTime.getHours(), endTime.getMinutes(), 0, 0); // Set end time

    // Log the calculated datetime for debugging
    Logger.log(`Start DateTime: ${startDateTime}`);
    Logger.log(`End DateTime: ${endDateTime}`);

    // Create the calendar event
    try {
      calendar.createEvent(eventName, startDateTime, endDateTime, {
        description: description,
        location: location
      });
      Logger.log(`Event created: ${eventName}, Start: ${startDateTime}, End: ${endDateTime}`);
    } catch (e) {
      Logger.log(`Failed to create event for row ${i + 2}: ${e.message}`);
    }
  }
}
Solution

  • Perhaps the easiest way to do this is to combine date and time columns in the spreadsheet rather than dealing with the spreadsheet-appsscript(JavaScript) interface and it's idiosyncrasies. In a new column, just add date and start time and in an another, date and end time.

    StartDateTime:

    =Q2+R2

    EndDateTime:

    =Q2+S2

    Once you have that fixed, getValues() will return the the correct JavaScript equivalent start and end date time as date objects, which you can then use in your event creation API call. Make sure the script, spreadsheet and calendar timezones are the same.