212th Training Calendar

function createCalendarEvent() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var rows = sheet.getDataRange().getValues();
  var timeZone = 'America/New_York'; // EST time zone
  var calendarId = '212thabtraining@gmail.com'; // Calendar email address
  var calendar = CalendarApp.getCalendarById(calendarId);

  if (!calendar) {
    Logger.log("Calendar not found. Check the Calendar ID.");
    return;
  }

  // Assuming first row is headers and data starts from the second row
  for (var i = 1; i < rows.length; i++) {
    try {
      var row = rows[i];
      var eventInfo = row[1]; // Event information
      var startTimeStr = row[2]; // Start time of the event as string
      var endTimeStr = row[3]; // End time of the event as string

      // Check if the row is already processed
      if (row[4] !== 'Processed') {
        var startDate = new Date(startTimeStr);
        var endDate = new Date(endTimeStr);

        // Adjust times for EST
        var startTime = Utilities.formatDate(startDate, timeZone, "yyyy-MM-dd'T'HH:mm:ss'Z'");
        var endTime = Utilities.formatDate(endDate, timeZone, "yyyy-MM-dd'T'HH:mm:ss'Z'");

        var event = calendar.createEvent(eventInfo, new Date(startTime), new Date(endTime));

        // Mark the row as processed
        sheet.getRange(i + 1, 5).setValue('Processed');
      }
    } catch (e) {
      // Log the error
      Logger.log('Error processing row ' + i + ': ' + e.toString());
      sheet.getRange(i + 1, 5).setValue('Error: ' + e.toString().substring(0, 50)); // Truncate error message if it's too long
    }
  }
}