Automatically Block Time Slots in Google Calendar With Google Apps Script

• Thu, Mar 16, 2023
• 2-minute read

In this post, I’ll walk you through creating a Google Apps Script that automatically blocks time slots in your Google Calendar based on the free/busy status of other shared calendars. This can be useful when you need to keep your calendar in sync with colleagues or team members.

Getting Started

First, create a new Google Sheet and rename it as you like. Then, open the Script Editor by going to Extensions > Apps Script. In the Script Editor, create a new script file and copy the following code into it:

function onOpen() {
  const spreadsheet = SpreadsheetApp.getActive();
  const menuItems = [
    {name: 'Block Appointments', functionName: 'run_blockAppointments'}
  ];
  spreadsheet.addMenu('Calendar Blocker', menuItems);

function run_blockAppointments() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Config");
  const localCalendarID = sheet.getRange("B1").getValue();
  const lastRow = sheet.getLastRow();
  const remoteCalendarIDs = sheet.getRange("B2:B" + lastRow).getValues().flat().filter(String);

  blockAppointments(remoteCalendarIDs, localCalendarID);
}

function blockAppointments(remoteCalendarEmails, localCalendarEmail) {
  const startDate = new Date();
  const endDate = new Date(startDate);
  endDate.setDate(startDate.getDate() + 7);

  const localCalendar = CalendarApp.getCalendarById(localCalendarEmail);
  const freebusy = Calendar.Freebusy.query({
    timeMin: startDate.toISOString(),
    timeMax: endDate.toISOString(),
    items: remoteCalendarEmails.map((email) => ({ id: email })),
  });

  const combinedBusySlots = remoteCalendarEmails.flatMap((email) => freebusy.calendars[email].busy);

  combinedBusySlots.forEach((slot) => {
    const startTime = new Date(slot.start);
    const endTime = new Date(slot.end);
    const existingEvents = localCalendar.getEvents(startTime, endTime);

    const blockingEmails = remoteCalendarEmails.filter((email) => {
      const remoteBusySlots = freebusy.calendars[email].busy;
      return remoteBusySlots.some((remoteSlot) => {
        return startTime.getTime() === new Date(remoteSlot.start).getTime() &&
          endTime.getTime() === new Date(remoteSlot.end).getTime();
      });
    });

    if (!existingEvents.length) {
      if (blockingEmails.length) {
        const blockingDomains = blockingEmails.map((email) => email.split('@')[1]).join(', ');
        const eventTitle = `Block - ${blockingDomains}`;
        localCalendar.createEvent(eventTitle, startTime, endTime);
      }
    } else {
      existingEvents.forEach((event) => {
        const isScriptCreatedEvent = remoteCalendarEmails.some((email) => {
          const domain = email.split('@')[1];
          return event.getTitle().includes(`Block - ${domain}`);
        });

        if (isScriptCreatedEvent) {
          if (blockingEmails.length) {
            const blockingDomains = blockingEmails.map((email) => email.split('@')[1]).join(', ');
            const eventTitle = `Block - ${blockingDomains}`;
            event.setTitle(eventTitle);
          } else {
            event.deleteEvent();
          }
        }
      });
    }
  });
}