LinkedIn
Twitter
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.
