Create Google Calendar Events from Document or Spreadsheet

First, I want to point out that there is a difference in the built-in Google Workspace services and the Advanced Google services. I'm not going to try to reinvent the wheel here in terms of explaining this so for more information, use the Google references. You'll see me use both services throughout this project and others. Most of the built-in Google Workspace service classes end in App (e.g., CalendarApp, DocumentApp, SpreadsheetApp, etc.) while the Advanced services do not. They also have different reference documents and I'll link to those as much as possible.

As the name suggests, the advances services give you a little more functionality. Personally, I prefer to use the Google Workspace services when possible. I feel like the code ends up being a little shorter (though that might not always be true). I think it really just boils down to that is what I learned first so that is my go to. If you do use the advanced services, be sure to enable it before you do so.

Calendar Service

apps script screenshot

In this project, I created a service file where I put most of the functions related to retrieving or handling Calendar data. I did this just to help me organize my code a little better.

In this example, I use the getAllOwnedCalendars() method instead of getAllCalendars(). If you wanted to get all the calendars that the user has access to, you would use the latter. This will return an array of the user's calendars. In this case, I wanted to get the name of each calendar so I use the map method to get the name of each calendar in that array. I then display each calendar name as an option in a select input in the sidebar:

(function getCals () {
  google.script.run.withSuccessHandler(
    function (selectList) {
      var select = document.getElementById("calendar_name");
      for (var i=0; i<selectList.length; i++) {
        var option = document.createElement("option");
        option.text = selectList[i];
        select.add(option);
      }
    }
  ).getUserCalendars();
}());

In this example, I use the As you can see, I use the parameter form_data. This is the client-side data captured in a form that is displayed in an HTML sidebar. (See this link for more information on building side-bars and other HTML content.)

The calendar ID can be found in the settings page of any Google Calendar. This function will return the ID as a string. This is useful since the getCalendarById(id) method requires a string as the ID parameter.

Creating a Calendar Event

There are many things you can do with the Calendar service. See the reference doc for more information. In this project, I only show how to create an event. You should be able to use these same principles to do anything else with this service.

First, notice that there are multiple event types: all day events, all day event series, events, and event series. (There is technically another type - createEventFromDescription(description). We will not talk about it in this project though.) I'm going to assume that you are already familiar with these event types from an end-user perspective. From the developer perspective, the difference in these event types are the required parameters. For all event types, a title is always required as well as some sort of start date or time. In addition, all day events require a start date (time is ignored) and regular events require and start date/time and an end date/time.

Each event type can also include an options class as a parameter. This is where we see one of the main differences between using the Workspace services and the advanced services. The options for the Workspace services are limited to description, location, guests, and sendInvites. The advanced services has many more options. See the reference doc for the full list.

Working with dates and times can get a little confusing. When creating a calendar event, if the parameter is a date, it will only use the date portion of the date that you pass in - so time typically doesn't matter. If it is a time, it will use the date and time.

When doing date and time math, call the getTime() method and then perform calculations in milliseconds. See the reference doc example for more information. So if you are creating an event that requires you to perform math to get the endDate, for example, the time will matter. See below:

function test() {
  const testDate = '6/28/2022';
  const testTime = '10:53 AM';

  //Combines a date string with a time string to create correctly formatted startTime parameter
  //Returns Tue Jun 28 2022 10:53:00 GMT-0500 (Central Daylight Time)
  const startTime = new Date(testDate + ' ' + testTime);

  //Adds 60 hours in milliseconds to startTime in order to get correctly formatted endTime parameter
  //Returns Thu Jun 30 2022 22:53:00 GMT-0500 (Central Daylight Time)
  const endTime = new Date(startTime.getTime() + (1000 * 60 * 60 * 24 * 2.5));
}

Time zones are another tricky element when working with dates and times in GAS. The GAS time zone may differ from the spreadsheet time zone which may differ from the calendar's time zone which may also differ from the user's physical time zone. Since a spreadsheet typically defaults to the owner's time zone, changing the sheet time zone to the user's time zone may not be the best approach if the spreadsheet is collaborated on across time zones. Likewise, changing the script or calendar time zone might cause similar issues. I find it easiest to get the user time zone and compare all dates and times with that time zone. Sometimes I even create a global time zone variable that adjusts for these time zone differences.

The script below creates a calendar event with a Google Meet link if the user selects the Google Meet option in a sidebar form. Many of these are nested properties.

const payload = {
  start: {
    dateTime: Utilities.formatDate(new Date(eventStartDateTime.getTime() + (1000 * 60 * 60 * 5)), 'GMT-0500', 'yyyy-MM 	dd'T'HH:mm:ss.SSS'Z''),
    timeZone: 'GMT-0500'
  },
  end: {
    dateTime: Utilities.formatDate(new Date(eventEndDateTime.getTime() + (1000 * 60 * 60 * 5)), 'GMT-0500', 'yyyy-MM-	dd'T'HH:mm:ss.SSS'Z''),
    timeZone: 'GMT-0500'
  },
  // Google Meet info
  conferenceData: {
    createRequest: {
      conferenceSolutionKey: {
        // This is the Google Meet type. There is an option here for third party conference providers using 'addOn' for the type.
        type: "hangoutsMeet"
      },
      // Gets a UUID. Per reference doc, "not guaranteed to be to be unique across all time and space".
      requestId: Utilities.getUuid(),
    },
  },
  // Event title and description
  summary: eventData.title,
  description: eventDescription
}

if (form_data.location == 'google_meet') {
  Calendar.Events.insert(
    payload, 
    calendarID, 
    {conferenceDataVersion: 1}
  )
}

I had trouble getting the dates to work unless I formatted them this way: yyyy-MM dd'T'HH:mm:ss.SSS'Z'.

I also had a hard time getting the time zones to work just right. When I used the advanced services, my events were always scheduled in the GMT time zone but it worked fine when I used the CalendarApp class. I double checked the script and calendar time zone and could not figure this out. If you know what's going on here, please let me know. My solution was to just add 5 hours to the time.

The script below uses the CalendarApp class. As you can see, it is not as advanced as the advanced service.

Also, I did not show the variables I created for the title, start date/time, end date/time, event description, or event location. This example does demonstrate the options parameter you can use in each event type.

CalendarApp.getCalendarById('calendarID').createEvent(
  eventTitle,
  eventStartDateTime,
  eventEndDateTime,
    {
      description: eventDescription,
      location: eventLocation,
    }
);