Search code examples
google-apps-scriptgoogle-sheets

Add Additional All-Day Events to Google Calendar


Based off of Fernando Lara's answer here Google App Script - Add or Update Calendar Event 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.

Solution

  • 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.