The Prince Academy株式会社 | Advanced Scenarios: Customizing Form-to-Calendar Workflows | Working with Google Forms and Calendar | Introduction to Google Apps Script: Automate Your Workflows and Boost Productivity

In this section, we'll delve into more advanced scenarios for integrating Google Forms and Google Calendar using Apps Script. While the basic setup of creating calendar events from form submissions is powerful, real-world workflows often require more sophisticated logic and customization. We'll explore how to handle conditional event creation, manage recurring events, and even dynamically set event properties based on form responses.

Conditional Event Creation

Sometimes, you might only want to create a calendar event if certain conditions are met within the form submission. For example, if a user selects 'Urgent' as a priority, you might want to create a dedicated calendar event, but not for 'Low' priority requests. This can be achieved by adding if statements to your Apps Script code.

function onFormSubmit(e) {
  var formResponse = e.response;
  var itemResponses = formResponse.getItemResponses();

  var eventDetails = {};
  for (var i = 0; i < itemResponses.length; i++) {
    var itemResponse = itemResponses[i];
    var question = itemResponse.getItem().getTitle();
    var answer = itemResponse.getResponse();
    eventDetails[question] = answer;
  }

  // Example: Only create event if priority is 'Urgent'
  if (eventDetails['Priority'] === 'Urgent') {
    var calendar = CalendarApp.getDefaultCalendar();
    var startTime = new Date(eventDetails['Start Time']);
    var endTime = new Date(eventDetails['End Time']);
    var title = 'Urgent Request: ' + eventDetails['Name'];
    var description = 'Details: ' + eventDetails['Description'];

    calendar.createEvent(title, startTime, endTime, {
      description: description,
      // Add more properties as needed
    });
    Logger.log('Urgent event created.');
  } else {
    Logger.log('Event not created due to priority.');
  }
}

Handling Recurring Events

Google Calendar allows for recurring events, which can be a significant time-saver. While Apps Script doesn't directly have a 'recurring' flag for createEvent, we can simulate recurrence by creating multiple events for a specified period or by using the rrules property. For simpler recurring patterns, such as daily or weekly, you might opt to create a series of events programmatically. For more complex recurrence rules, consider using the rrules option when creating the event.

function createWeeklyEvent(e) {
  var formResponse = e.response;
  var itemResponses = formResponse.getItemResponses();

  var eventDetails = {};
  for (var i = 0; i < itemResponses.length; i++) {
    var itemResponse = itemResponses[i];
    var question = itemResponse.getItem().getTitle();
    var answer = itemResponse.getResponse();
    eventDetails[question] = answer;
  }

  var calendar = CalendarApp.getDefaultCalendar();
  var startDate = new Date(eventDetails['Start Date']);
  var startTime = new Date(eventDetails['Start Time']);
  var endTime = new Date(eventDetails['End Time']);

  // Combine date and time for accurate event start/end
  var eventStartTime = new Date(startDate);
  eventStartTime.setHours(startTime.getHours(), startTime.getMinutes(), startTime.getSeconds());

  var eventEndTime = new Date(startDate);
  eventEndTime.setHours(endTime.getHours(), endTime.getMinutes(), endTime.getSeconds());

  // Define a weekly recurrence rule
  // For example, this creates an event every Monday, Wednesday, Friday
  var rrule = 'RRULE:FREQ=WEEKLY;BYDAY=MO,WE,FR'; 

  calendar.createEvent(eventDetails['Event Title'], eventStartTime, eventEndTime, {
    description: eventDetails['Description'],
    // Use rrule for recurring events
    rrules: [rrule]
  });
  Logger.log('Recurring event created.');
}

The rrules property expects a string in iCalendar format. Common frequency options include DAILY, WEEKLY, MONTHLY, and YEARLY. You can also specify BYDAY for days of the week, BYMONTHDAY for days of the month, and COUNT to limit the number of occurrences.