Google Calendar Webhook Post Automation

[amldee]

Hi all! I have an automation for webhook process where I used appscript to make the row appear and update on Google Calendar.

If new/unedited event, it will just be it (trigger event is just add). If edited/updated event, that is when the event in Gcalendar updates (since there would be change in [Date] and [Time] and requires approval, it has to wait before the [Status] is "OFFICIAL"). So 2 different automation for Add and Update.

The thing is, the appscript is working fine (when I press run, that is only when the event/s appear on Gcalendar) but the automation is not working.

Here is the appscript formula I used: (based on here, Logger.log should be same with JSON body on process setting?)

function createOrUpdateCalendarEvent() {
  // Calendar ID of the shared community calendar
  let communityCalendar = CalendarApp.getCalendarById('c_b83105b1fd5dd0ea5adcdea784a9c0476159a5cd3c0fe6c7e78ba8825b95bf23@group.calendar.google.com');
  
  // Getting data from the 'Itinerary Calendar' sheet
  let sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Itinerary Calendar');
  let scheduleData = sheet.getDataRange().getValues();
  
  // Remove the first row (header)
  scheduleData.splice(0, 1);

  // Loop through each row in the schedule
  scheduleData.forEach(function(entry) {
    let itineraryKey = entry[0]; // Itinerary Key (if needed for identifying the event)
    let routeCode = entry[1]; // Route Code
    let date = entry[2]; // Date (Date column)
    let time = entry[3]; // Time (Time column)
    let schedule = entry[4]; // Schedule
    let newSchedule = entry[5]; // New Schedule (if any)
    let psrName = entry[6]; // PSR Name
    let psrEmail = entry[7]; // PSR Email
    let teamLeaderEmail = entry[8]; // Team Leader Email
    let supervisorEmail = entry[9]; // Supervisor Email
    let status = entry[10]; // Status (OFFICIAL SCHEDULE, CANCELLED, etc.)

    // Get the current user's email
    let userEmail = Session.getActiveUser().getEmail();

    // Log the event data for debugging
    Logger.log('Route Code: ' + routeCode);
    Logger.log('Schedule: ' + schedule);
    Logger.log('New Schedule (if any): ' + newSchedule);
    Logger.log('PSR Name: ' + psrName);
    Logger.log('PSR Email: ' + psrEmail);
    Logger.log('Status: ' + status);

    // Check if the current user's email matches the PSR, Team Leader, or Supervisor Email
    if (userEmail === psrEmail || userEmail === teamLeaderEmail || userEmail === supervisorEmail) {

      // Event title: "[Route Code] ([PSR Name])"
      let eventTitle = routeCode + " (" + psrName + ")";

      // Create the startDateTime from the date
      let startDateTime = new Date(date); // Use the date from the sheet

      // If time exists, append it to the startDateTime (no parsing or formatting)
      if (time && typeof time === "string" && time.includes(":")) {
        // Split time into hours and minutes (assuming the time is in hh:mm format)
        let [hours, minutes] = time.split(":");
        startDateTime.setHours(parseInt(hours, 10));
        startDateTime.setMinutes(parseInt(minutes, 10));
      }

      // If New Schedule exists, update the time
      if (newSchedule) {
        let newScheduleTime = new Date(newSchedule);
        startDateTime.setHours(newScheduleTime.getHours());
        startDateTime.setMinutes(newScheduleTime.getMinutes());
      } else if (!newSchedule && schedule) {
        // If New Schedule doesn't exist, use Schedule time (if available)
        let scheduleTime = new Date(schedule);
        startDateTime.setHours(scheduleTime.getHours());
        startDateTime.setMinutes(scheduleTime.getMinutes());
      }

      // If Status is OFFICIAL SCHEDULE, create or update the event
      if (status === "OFFICIAL SCHEDULE") {
        let events = communityCalendar.getEventsForDay(startDateTime); // Get events for the date of startDateTime
        let eventUpdated = false;

        // Loop through events to find the one to update based on itineraryKey in the title
        for (let i = 0; i < events.length; i++) {
          let event = events[i];
          if (event.getTitle().indexOf(itineraryKey) > -1) {
            // Event found, check if Date or Time has changed
            let eventDate = event.getStartTime();
            let eventTime = eventDate.getHours() + ':' + eventDate.getMinutes();

            // If the date or time has changed, update it
            if (eventDate.getTime() !== startDateTime.getTime()) {
              event.setTitle(eventTitle);
              event.setTime(startDateTime, startDateTime); // No end time, single time event
              Logger.log('Event updated: ' + eventTitle + ' at ' + startDateTime);
            }
            eventUpdated = true;
            break; // Exit loop once the event is found and updated
          }
        }

        // If event was not found, create a new event
        if (!eventUpdated) {
          Logger.log('Creating new event: ' + eventTitle + ' at ' + startDateTime);
          communityCalendar.createEvent(eventTitle, startDateTime, startDateTime); // No end time, single time event
        }
      } else if (status === "CANCELLED") {
        let events = communityCalendar.getEventsForDay(startDateTime); // Get events for the date of startDateTime
        
        // Loop through events to find the one to update based on itineraryKey
        for (let i = 0; i < events.length; i++) {
          let event = events[i];

          if (event.getTitle().indexOf(itineraryKey) > -1) {
            // Mark the event as cancelled by updating the title
            let eventTitleWithCancellation = event.getTitle();
            if (!eventTitleWithCancellation.includes("CANCELLED")) {
              eventTitleWithCancellation += " - CANCELLED";
            }
            event.setTitle(eventTitleWithCancellation);
            event.setTime(startDateTime, startDateTime); // No end time, single time event
          }
        }
      } else {
        // If the status is neither OFFICIAL SCHEDULE nor CANCELLED, create a new event
        Logger.log('Creating new event: ' + eventTitle + ' at ' + startDateTime);
        communityCalendar.createEvent(eventTitle, startDateTime, startDateTime); // No end time, single time event
      }

    } else {
      Logger.log('User is not authorized to create or update this event');
    }
  });
}

 Here are my columns:

and Here is my automation setting and error: (photo is for update and monitor error is from Add (new) event)

Thanks! Hopefully to be solved today.