Calendar Events Automation: Streamline Your Life with App Script Automation

In the bustling offices of TechNova Inc., a rapidly growing tech company known for its innovative software solutions, a unique predicament has emerged. As the company expanded, so did its array of events and meetings. From product launches and client presentations to internal team-building activities, TechNova had a calendar bursting with events.

The responsibility of managing and scheduling these events had traditionally fallen on a dedicated events coordinator. However, due to budget constraints, the company made a tough decision to cut costs by eliminating the events coordinator position. To keep things running smoothly, the daunting task of scheduling all these events now fell squarely onto the shoulders of Mark Thompson, a diligent and capable project manager.

Mark was already juggling a heavy workload, overseeing multiple projects simultaneously. The sudden addition of event scheduling to his responsibilities left him feeling overwhelmed and stressed. He found himself buried under a mountain of calendar invites, trying to ensure that all events were well-coordinated, and everyone received the necessary notifications. It was an exhausting task that consumed a significant portion of his workdays.

Understanding Calendar Automation:

Calendar automation refers to the process of automating tasks and actions related to calendar management, scheduling, and events using software tools and scripts. It aims to streamline and optimize calendar-related workflows, reducing manual efforts and minimizing the risk of errors. Calendar automation can be applied in various contexts, including personal scheduling, business meetings, event management, and more.

Google Apps Script, a JavaScript-based scripting language developed by Google, provides a powerful platform for automating calendar-related tasks and interactions with Google Calendar. Using Google Apps Script, you can create custom scripts and applications that interact with Google Calendar to automate tasks such as:

1. Event Creation: Automatically create events on Google Calendar based on predefined criteria, data from external sources, or user input.

2. Event Updates: Automatically update event details, including date, time, location, and descriptions, when changes occur in connected systems or data sources.

3. Event Deletion: Remove outdated or canceled events from the calendar to keep it up to date.

4. Notification and Reminders: Trigger notifications and reminders for upcoming events, sending emails, SMS messages, or app notifications to attendees.

5. Availability and Scheduling: Check the availability of attendees and schedule events at suitable times, taking into account conflicting schedules.

6. Data Integration: Integrate data from external sources, such as spreadsheets, databases, or APIs, with calendar events for dynamic event creation and management.

7. Recurring Events: Set up recurring events and automate the generation of recurring event instances.

8. Resource Booking: Manage the booking of conference rooms, equipment, or other resources associated with events.

So let's start with the calendar automation process.

Getting Started with Calendar Automation:

Step 1: Prepare a Google sheet that contains the list of events that we have to schedule.

The sheet should contain the following attributes:

1. Name of the Event

2. Start datetime of the Event

3. End datetime of the Event

4. Description of the Event

5. Attendees of the Event

6. Optional Attendees of the Event

Step 2: Add the Calendar API

We are inside the script editor.

Now on the left side, there is a tab called Services. Click on the plus icon of that.

Click the plus icon (+) to add the Calendar API service. With the Calendar service added you are now prepared to execute the script.

The Google Calendar API has successfully been added to the Project.

Step 3: Write the Automation Script

Let's begin scripting by opening the Google Apps Script editor.

function main() {
 
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName("Calendar_Events");
  const eventsData = sheet.getDataRange().getValues();
  const eventArray = [];
 
  for (var i = 1;i<eventsData.length;i++){
    const eventMap = {};
    eventMap['eventName'] = eventsData[i][0];
    eventMap['start'] = eventsData[i][1];
    eventMap['end'] = eventsData[i][2];
    eventMap['description'] = eventsData[i][3];
    eventMap['attendees'] = eventsData[i][4];
    eventMap['optionalAttendees'] = eventsData[i][5];
 
    eventArray.push(eventMap);
  }
 
  Logger.log(eventArray);
 
}

It will generate the object as shown below:

[
  {
    "eventName": "Event Number 1",
    "start": "2021-06-26T9:30:00",
    "end": "2021-06-26T10:30:00",
    "description": "This is the event number 1",
    "attendees": "burhanuddinnahargarwala@gmail.com, dashingbb786@gmail.com",
    "optionalAttendees": "dashingbb786@gmail.com"
  },
........
]

Now let's create another function that takes this object and creates new events in the calendar.

function createNewEvent(eventArray) {
 
  for (i=0; i < eventArray.length; i++) {
    const event = eventArray[i];
    const calendarEvent = CalendarApp.getDefaultCalendar().createEvent(
      event['eventName'],
      new Date(event['start']),
      new Date(event['end']),
      { sendInvites: true, sendUpdates: "all", guests: event['attendees']}
    );
  }
}

The 'createNewEvent' function operates on an array of event objects (eventArray) and executes the following steps for each event:

• It iterates through the eventArray, extracting essential event details such as the event name, start date, end date, and attendees from each event object.

• Using the CalendarApp.getDefaultCalendar().createEvent() method, the function generates a new event in the default Google Calendar. It's important to note that this method automatically selects the calendar associated with the email account used for accessing Google Sheets and running the Apps Script. This ensures that the event is added to the calendar linked to the same email account.

  Pro Tip: If you need to schedule the event on a different calendar, you can utilize a provided code snippet to specify the calendar ID, allowing you to precisely control the destination calendar.

 const calendarEvent = CalendarApp.getCalendarById(calendarId).createEvent(
      event['eventName'],
      new Date(event['start']),
      new Date(event['end']),
      { sendInvites: true, sendUpdates: "all", guests: event['attendees']}
    );

• Sets the event name, start date, and end date for the newly created event.

• Specifies additional options, such as sending invitations to guests (sendInvites: true), sending updates to all attendees (sendUpdates: "all"), and specifying the event's attendees based on the input event object.

How to Obtain the Calendar ID?

The calendarId is the ID of your calendar that you can fetch from your calendar itself

Click the three dots of any of the shared calendars.

Click on Settings and sharing

Go to the Integrate calendar section to get the calendar ID.

Running Your Calendar Automation Script.

Now let's run the app script to get the desired outcome.

At the top of the Apps Script editor, select the main function from the dropdown menu. Click the "Run" button to execute the script. Follow these steps to observe the script's functionality and automate calendar events based on the provided sheets.

Review the permissions and allow access to the data.

Once the code is successfully executed, the events will be scheduled in your calendar.

The event has been successfully scheduled in the calendar, and both attendees have been added to the event. Additionally, email notifications regarding the meeting have been sent to the attendees.

Marking Attendees as Optional

A notable observation is that while the Google Sheet designates 'dashingbb786@gmail.com' as an optional attendee, this designation is not reflected in the event. To address this, we will implement logic to mark the attendees as optional within the event details.

function markAttendeeAsOptional(eventId, optionalGuestsList, calendarId = 'primary') {
  // Get the event to update
  const event = Calendar.Events.get(calendarId, eventId);
 
  const attendees = event['attendees'];
  const optionalAttendees = []
 
  for (let i = 0; i < attendees.length; i++) {
    const attendee = attendees[i];
    const guest = attendee['email'];
 
    // if guest is there in optional guestLists, then mark that as optional
    if (optionalGuestsList.includes(guest)) {
      attendee['optional'] = true;
    }
 
    optionalAttendees.push(attendee);
  }
 
  // Save the changes to the event
  var resource = { attendees: optionalAttendees };
  Calendar.Events.patch(resource, calendarId, event['id']);
}

This function, 'markAttendeeAsOptional()', is designed to mark specific attendees as optional within a Google Calendar event.

So let's delete the events and again run the script to check whether the attendee is marked as optional or not.

The attendee is successfully set as optional for the given event.

Refer to the provided GitHub repository link to access the complete script for automating your calendar events. Make sure to maintain the same sheet format as outlined in the blog for seamless integration:

For more in-depth information about various methods offered by the Calendar API, it's advisable to refer to the official Calendar API documentation, where you can explore a comprehensive range of functionalities and capabilities.

Summary:

In this comprehensive guide, we've explored the power of automation using Google Apps Script and the Google Calendar API to streamline event management. Whether you're scheduling meetings, workshops, or any type of event, this automation process can save you time, reduce errors, and ensure consistency in your calendar.

Let's see the condition of the mark after going through this blog.

In the upcoming section of our blog, we will delve into the dynamic world of event management. We'll explore how to make modifications to your scheduled events and efficiently remove any events that need to be canceled. Stay tuned for these valuable insights!