Add Additional All-Day Events to Google Calendar

Based off of Fernando Lara's answer here https://stackoverflow.com/questions/73322743/google-app-script-add-or-update-calendar-event/73325010#comment135147780_73325010 I am trying to create new events on Google Calendar. Here is my script:

function updateCal(){
  var cal = CalendarApp.getCalendarById("CalendarID");
  var data = SpreadsheetApp.getActive().getSheetByName("Sheet1");
  const rows = data.getDataRange().getValues();
  rows.forEach(function(row, index){
    if (index === 0) return;
    var eventdate = data.getRange(index, 4, 1, 1).getValue();
    var eventtitle = data.getRange(index,1,1,1).getValue();
    var eventdescription = data.getRange(index,2,1,1).getValue();
    console.log(eventdate + eventtitle + eventdescription)
    const eventdate1 = new Date(eventdate)
    const events = cal.getEventsForDay(eventdate1);
    if (events.length == 0){          //The error was in this line
      cal.createAllDayEvent(eventtitle,eventdate1,{description: eventdescription});
    } else {
      ev = events[0];
      ev.setDescription(eventdescription)
    }

  })

}

The above script is successful at adding a new event or modifying an existing event if the description changes. There is one major flaw, however. The script does not allow for the creation of another all-day event on a day that already has an existing event. I tried deleting the original event from the spreadsheet manually and adding a new event for the same day with a different title and different description. The title remained the same on Google Calendar, but the description changed.

I believe this line below is incorrect, but I am not sure how to fix it:

if (events.length == 0)

Can anyone point me in the right direction?

Order of Process:

• This script is run with a time-based trigger.
• Managers submit form if employee is absent, late, or leaving early. The employee name is the Calendar event title. The details (absent, late, or leaving early) are the description.
• Spreadsheet filters out responses to only find the last record of a given employee on a given date. That way, if an employee's attendance status changes, only the last recorded status is evaluated. (Therefore, the importance of being able to update status based on description which my script does currently.)
• Need to be able to schedule/record the attendance status of multiple employees for the same day. (The script does not currently perform this task.)
• While this could be done via manual entry on Google Calendar, I am using a Gravity Form linked to a Google spreadsheet. The data gets entered and recorded. A particular employee's attendance record can be better monitored with a spreadsheet.

Modification points:

• From your script, how about checking the event title? I guessed that your goal might be able to be achieved by checking both the event date and the event title.
• In your script, getValue() is used in a loop. In this case, the process cost will become high.

When these points are reflected in your script, how about the following modification?

Modified script:

function updateCal() {
  var cal = CalendarApp.getCalendarById("CalendarID");
  var data = SpreadsheetApp.getActive().getSheetByName("Sheet1");
  const [, ...rows] = data.getDataRange().getValues();
  rows.forEach(([eventtitle, eventdescription, , eventdate]) => {
    console.log(eventdate + eventtitle + eventdescription)
    const eventdate1 = new Date(eventdate)
    const events = cal.getEventsForDay(eventdate1);
    if (events.length == 0 || (events.length > 0 && !events.some(e => e.getTitle() == eventtitle))) {
      cal.createAllDayEvent(eventtitle, eventdate1, { description: eventdescription });
    } else {
      ev = events[0];
      ev.setDescription(eventdescription)
    }
  });
}

• By this modification, cal.createAllDayEvent is run by checking both the event date and the event title.

• In your script, I guessed that you might have wanted to skip the header row. But, if you don't want to skip it, please modify const [, ...rows] = data.getDataRange().getValues(); to const rows = data.getDataRange().getValues();.

• If you want to check the description, please modify if (events.length == 0 || (events.length > 0 && !events.some(e => e.getTitle() == eventtitle))) { to if (events.length == 0 || (events.length > 0 && !events.some(e => e.getTitle() == eventtitle) && !events.some(e => e.getDescription() == eventdescription))) {.

Added:

From your following reply,

> One big problem, however. When I change the description of an event in the spreadsheet, the description does not change in the calendar as it did before.

How about the following modified script?

function updateCal() {
  var cal = CalendarApp.getCalendarById("CalendarID");
  var data = SpreadsheetApp.getActive().getSheetByName("Sheet1");
  const [, ...rows] = data.getDataRange().getValues();
  rows.forEach(([eventtitle, eventdescription, , eventdate]) => { // Now [eventtitle, eventdescription, eventdate] ?
    console.log(eventdate + eventtitle + eventdescription)
    const eventdate1 = new Date(eventdate)
    const events = cal.getEventsForDay(eventdate1);
    if (events.length == 0 || (events.length > 0 && !events.some(e => e.getTitle() == eventtitle) && !events.some(e => e.getDescription() == eventdescription))) {
      cal.createAllDayEvent(eventtitle, eventdate1, { description: eventdescription });
    } else {
      events.forEach(e => {
        if (e.getTitle() == eventtitle && e.getDescription() != eventdescription) {
          e.setDescription(eventdescription);
        }
      });
    }
  });
}

• From your reply, if your current script is rows.forEach(([eventtitle, eventdescription, eventdate]) => {, please modify rows.forEach(([eventtitle, eventdescription, , eventdate]) => { to it.

Suggestion

> NOTE: If you think your question has been misinterpreted, kindly clarify it again and include a dummy sheet of your data and your actual desired results.

In my understanding of your question, here's the flow of your process:

1. If an employee does not exist based on the date, add a new event.
2. If an existing employee has a new description, update the event.
3. Repeat step 1 and/or step 2 for the rest of the other employees with the same date.

Perhaps you could try the sample below as a guide for your actual project. This script will fetch all data from your Sheet1 and iterate through each row. For each row of data, it will retrieve the date and fetch the current events based on the date. It will then check if the row's employee already exists or not.

If the employee does not exist yet, it will be added as a new event. On the other hand, if the employee already exists, the script will update the existing event. Since you are using a sheet, assuming the most updated data is at the bottom in incremental order, if there are multiple updates for the same employee but with different descriptions, the very last data in the sheet for that employee will be used as the updated event data.

Sample Tweaked Script

> I have broken down your script into manageable pieces for maintainability.

//Set this as a global variable for easy access
const cal = CalendarApp.getCalendarById("■■■■■■■■■■■■■■■■■"); 
function updateCal() {
var sheetData = SpreadsheetApp.getActive().getSheetByName('Sheet1').getDataRange().getValues();
//remove sheet headers.
sheetData.shift();
sheetData.forEach(row => {
var events = getEvents(new Date(row[3]));
events.length == 0 && addEvent(row[0], row[1], row[3]); //Add the first event if the base 'date' has no events yet.
var eventRecords = events.map(x => [x.getTitle(),x.getDescription(),x.getStartTime(),x.getId()]);//get current events from the base 'date' as an array.
var currentEvent = eventRecords.filter(x => x[0] == row[0]);//Checks if the current row 'employee' already has an event or not yet.
console.log(`Add "${row[0]}"?\n[${currentEvent.length == 0 ? //If current event exists.
addEvent(row[0],row[1],row[3]) : //[true] add the event.
updateEvent(currentEvent[0][3],row[0],row[1],row[3]) //otherwise [false] update the current event
}]\nDescription: "${row[1]}"\nDate: "${row[3]}" `)});
}
function getEvents(date) {
var events = cal.getEventsForDay(date);
return events;
}
function updateEvent(eventID,title,eventDescription,date){
var event = cal.getEventById(eventID);
event.setDescription(eventDescription)
event.setAllDayDate(new Date(date));
return `"${title}" (id: ${eventID}) has been updated.`
}
function addEvent(title, eventDescription, date) {
cal.createAllDayEvent(title, date, { description: eventDescription });
return `"${title}" has been added as a New Event.`;
}

Demo

• Dummy Sheet1:<br> (E.g. "John Appleseed" has not been added yet & another "Jon Doe" exists but has an updated description which needs to be updated.)

>

• Current calendar event based on date: <br> (It currently contains "Jon Doe" & its description is set to Late)

>

• After running theupdateCal(): <br> (Shows logs for review & readability.)

>

• The new updated calendar events: <br> (Jon Doe's Description has been updated to Leaving Early & John Appleseed has been added)

>

Reference

• How to Organize Your Code with Functions

• JavaScript Array shift()

• JavaScript Array filter()

• JavaScript Array map()

• Javascript Short Circuiting Operators