How Can I Add/Update Events Spanning Multiple Days to Google Calendar?

Question

My current script below, which Tainake has helped me with and which I have modified to change the color of the event based on Attendance Status, adds an All Day Event or updates an All Day Event if the description of an already existing event changes.

A form entry populates the data. Currently, the form only adds or updates an event for a single day (usually the current day). Now I would like to extend the functionality a bit further. I am looking to create or update events that span multiple days in Google Calendar. I would like a manager to be able to schedule vacations for his workers or modify previous multiple-day events. To do so now, he would have to submit a form multiple times, changing the date each time.

At this time, I am not looking to schedule recurring events. Additonally, I use the spreadsheet for gathering statistics as well, so I don't want to allow managers to edit the Calendar manually.

I can think of two options for achieving the desired results. Here is what I am trying to figure out:

Option 1: What needs to be changed in the script to allow multiple-day events to be added or updated on Google Calendar? It is essential that if the description changes for a particular individual on a particular day (Note: The name of the individual serves as the Event Title.), the calendar updates the event with the new description as is happening currently with this script.

Column D in the script below is empty to allow for insertion of an end date.

Option 2: What do I need to change to have the script "look at" only those rows that have "Yes" in Column F. This allows me to to the following: Have my form insert a start date and end date and with formulas, evaluate all the days between. Then output this result on a separate sheet and run the script on the sheet populated by formulas.

I have already created a separate sheet with the formulas in question. The only problem I am having is that the script evaluates even the "empty" rows and takes forever to run, usually timing out before it is completed. I would need it to "look at" only the rows with "Yes" in Column E. This is a messier approach from a spreadsheet perspective, but perhaps easier approach from a scripting perspective.

My current script is below.

function updateCal() {
  var cal = CalendarApp.getCalendarById("CalendarID");
  var data = SpreadsheetApp.getActive().getSheetByName("Entries");
  const [, ...rows] = data.getDataRange().getValues();
  rows.forEach(([eventtitle, eventdescription, eventdate, , color]) => { // 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 }).setColor(CalendarApp.EventColor[color]);
    } else {
      events.forEach(e => {
        if (e.getTitle() == eventtitle && e.getDescription() != eventdescription) {
          e.setDescription(eventdescription).setColor(CalendarApp.EventColor[color]);
        }
      });
    }
  });
}

Here is a screenshot of my Spreadsheet:

And here is my Calendar:

Thanks to @GiselleValladares, I have now reached this point:

function updateCal() {
  var cal = CalendarApp.getCalendarById("CalendarID");
  var data = SpreadsheetApp.getActive().getSheetByName("Entries");
  const [, ...rows] = data.getDataRange().getValues();
  rows.forEach(([eventtitle, eventdescription, eventdate, , color]) => { // Now [eventtitle, eventdescription, eventdate] ?
    console.log(eventdate + eventtitle + eventdescription)
    const eventdate1 = new Date(eventdate);

    // new variable for the end Date
    // To make it so that the users add the exact date the event ends in the form
    // And add the following to variables to make sure the event is added correctly.
    const millisPerDay = 1000 * 60 * 60 * 24 * 2; 
    const eventEndDate = new Date(eventEnddate.getTime() + millisPerDay);

    

    const events = cal.getEventsForDay(eventdate1);
    if (events.length == 0 || (events.length > 0 && !events.some(e => e.getTitle() == eventtitle) && !events.some(e => e.getDescription() == eventdescription))) {

      // add the end date to the code
      cal.createAllDayEvent(eventtitle, eventdate1, eventEndDate,{ description: eventdescription }).setColor(CalendarApp.EventColor.color);
    } else {
      events.forEach(e => {
        if (e.getTitle() == eventtitle && e.getDescription() != eventdescription) {
          e.setDescription(eventdescription).setColor(CalendarApp.EventColor.color);
        }
      });
    }
  });
}

I have modified my dates and have now set them for one day later. See Spreadsheet screenshot. The result is that single-day events are now showing up in the calendar correctly as single-day events. However, multiple-day events are ending one day too early.

I would have expected Don Drysdale to show up on May 9th thru May 12th.

Answer 1

You will need to modify the form to have a start date and an end date and pass this information to the creation of the event. Or at least the end date since you are already using const eventdate1 = new Date(eventdate).

You will need to add an extra date for the end date since if you added the event from July 19, 2023 to July 20, 2023 the event will be created only for July 19, 2023. Since base on the Google Documentation here:

The date when the event ends (only the day is used; the time is ignored). The end date is exclusive.

After that, you can modify the script like this:

function updateCal() {
  var cal = CalendarApp.getCalendarById("CalendarID");
  var data = SpreadsheetApp.getActive().getSheetByName("Entries");
  const [, ...rows] = data.getDataRange().getValues();
  rows.forEach(([eventtitle, eventdescription, eventdate, , color]) => { // Now [eventtitle, eventdescription, eventdate] ?
    console.log(eventdate + eventtitle + eventdescription)
    const eventdate1 = new Date(eventdate);

    // new variable for the end Date
    // To make it so that the users add the exact date the event ends in the form
    // And add the following to variables to make sure the event is added correctly.
    const millisPerDay = 1000 * 60 * 60 * 24; 
    const eventEndDate = new Date(eventEnddate.getTime() + millisPerDay);

    

    const events = cal.getEventsForDay(eventdate1);
    if (events.length == 0 || (events.length > 0 && !events.some(e => e.getTitle() == eventtitle) && !events.some(e => e.getDescription() == eventdescription))) {

      // add the end date to the code
      cal.createAllDayEvent(eventtitle, eventdate1, eventEndDate,{ description: eventdescription }).setColor(CalendarApp.EventColor.color);
    } else {
      events.forEach(e => {
        if (e.getTitle() == eventtitle && e.getDescription() != eventdescription) {
          e.setDescription(eventdescription).setColor(CalendarApp.EventColor.color);
        }
      });
    }
  });
}

Update:

Test with:

const millisPerDay = 1000 * 60 * 60 * 24;

or

const millisPerDay = 1000 * 60 * 60 * 24 * 2;

New Update

I create a table on Google Sheets, just like the one you posted.

Here is the complete code, I rename some variables and made some changes. And it's working for me:

function updateCal() {
  var cal = CalendarApp.getCalendarById("CalendarID");
  var data = SpreadsheetApp.getActive().getSheetByName("Entries");
  const [, ...rows] = data.getDataRange().getValues();
  rows.forEach(([eventtitle, eventdescription, eventStartdate, eventEnddate, color]) => { // Now [eventtitle, eventdescription, eventdate, eventEnddate] ?

    const eventStartDate1 = new Date(eventStartdate);

    // new variable for the end Date
    // To make it so that the users add the exact date the event ends in the form
    // And add the following to variables to make sure the event is added correctly.
    const millisPerDay = 1000 * 60 * 60 * 24;
    const eventEndDate = new Date(eventEnddate.getTime() + millisPerDay);

    const events = cal.getEventsForDay(eventStartDate1);

    if (events.length == 0 || (events.length > 0 && !events.some(e => e.getTitle() == eventtitle) && !events.some(e => e.getDescription() == eventdescription))) {
      

      // add the end date to the c
      cal.createAllDayEvent(eventtitle, eventStartDate1, eventEndDate, { description: eventdescription }).setColor(CalendarApp.EventColor[color]);

    } else {
      events.forEach(e => {
        if (e.getTitle() == eventtitle && e.getDescription() != eventdescription) {
          e.setDescription(eventdescription).setColor(CalendarApp.EventColor[color]);
        }
      });
    }
  });
}

This is my data:

And this is how it looks in my calendar: