Google Forms and Repeating Event Requests

Posted on by

Google Calendar is an incredibly robust and useful planning tool, but it has limitations related to scheduling approved event requests. Many people use Google Forms to collect information about an event to put on a calendar after it’s been approved. There are also a number of add-ons that facilitate the creation of Calendar Events from a Google Sheet. Most of these are constructed to handle one Calendar Event per row of data. This is fine for the one and done events, but seems unnecessarily redundant for events that repeat multiple times at regular intervals for a given date range. Nobody wants to fill out the same form for the same event just because it’s for a different day of the week. Of course there is always the option to manually create the recurring event based on the data in the spreadsheet, but that’s prone to errors and who really enjoys doing that anyway.

Thanks to Apps Script, we can automate event recurrence intervals based on information we collect in a form.

Sample Google Form

In the form above, every question is generating data in a Form Response sheet for variables that are used to create an event series.

The Start Date and End Date columns determine the date range within which to repeat the event. The Repeat Every column determines which days of the week the event should be repeated on. These should be in all caps. The Location column determines which calendar to upload the events to using a =VLOOKUP for retrieving the calendar id from another tab.

When working with dates and the CalendarApp in Apps Script it’s important to get the date format right or your script simply won’t work. The Utilities Class is super helpful in getting the format right. You’ll see in the code where I used this class to get the result I needed.

The code below is bound to the Form Response Sheet and should be set to trigger on Form Submit because in this instance the script is always working with the last row of data. A more robust version of this would have a status column, and perhaps a column that indicated whether or not the event was approved, then loop through all of the rows adding events as conditions are met, but that’s beyond the scope of this blog post.

Another idea is to simply upload every recurring event request to a placeholder calendar, then when approved copy the event series to the appropriate calendar.

function calendarUpload() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName("Form Responses 1");
var Avals = ss.getRange("A1:A").getValues();
var lastRow = Avals.filter(String).length;
Logger.log(lastRow);
var title = sheet.getRange(lastRow,2).getValue();
var description = sheet.getRange(lastRow,3).getValue();
var startDate = sheet.getRange(lastRow,4).getValue();
var formattedStart = Utilities.formatDate(new Date(startDate), 'America/New_York', 'MMMM dd, yyyy');
var endDate = sheet.getRange(lastRow,5).getValue();
var formattedEnd = Utilities.formatDate(new Date(endDate), 'America/New_York', 'MMMM dd, yyyy');
var startTime = sheet.getRange(lastRow,6).getValue();
var formattedSTime = Utilities.formatDate(new Date(startTime), "EST","HH:mm:ss");
var endTime = sheet.getRange(lastRow,7).getValue();
var formattedETime = Utilities.formatDate(new Date(endTime), "EST","HH:mm:ss");
var location = sheet.getRange(lastRow,9).getValue();
var weekDays = sheet.getRange(lastRow,8).getValue();
var calId = sheet.getRange(lastRow,10).getValue();
Logger.log(title);
Logger.log(formattedStart);
Logger.log(formattedEnd);
Logger.log(formattedSTime);
Logger.log(formattedETime);
Logger.log(location);

var startDateandTime = (formattedStart+" "+formattedSTime);
var endDateandTime = (formattedStart+" "+formattedETime);
Logger.log(startDateandTime);
var days = weekDays.split(', ').map(function(i) { return CalendarApp.Weekday[i]; });
var eventSeries = CalendarApp.getCalendarById(calId).createEventSeries(title,
new Date(startDateandTime),
new Date(endDateandTime),
CalendarApp.newRecurrence().addWeeklyRule()
.onlyOnWeekdays(days)
.until(new Date(formattedEnd)),
{location: location, description: description});
Logger.log('Event Series ID: ' + eventSeries.getId());
}

Here is a companion video to this blog post demonstrating this workflow.


If you need to include an approval workflow, it’s a good idea to add a custom menu to your Google Sheet for easy triggering of the calendarUpload() function. Also, your script will need to include status columns for the approval status and the calendar event status.  These columns will create the conditions for adding approved events to the calendar and avoid duplication.

I’ve also included DataValidation for the Approval Status column,  in this case Column A.  Here you can generate a list of your own status types.  Events will only be added if “Approved” is found in Column A and the Calendar Event Status column, Column L, is blank.

Here is the Apps Script for adding the approval workflow to your Google Sheet and you can watch another video at the end of the post seeing it in action.

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Process Events')
      .addItem('Add Approved Events to Calendar', 'calendarUpload')
      .addToUi();
}


function addDataValidation(){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Form Responses 1");
  var Bvals = ss.getRange("B1:B").getValues();
  var lastRow = Bvals.filter(String).length;
  Logger.log(lastRow);
  var rule = SpreadsheetApp.newDataValidation()
     .requireValueInList(["Pending","Approved"], true)
    .setAllowInvalid(false)
    .build();
  var setApprovalStatus =  sheet.getRange(lastRow,1).setDataValidation(rule).setValue("Pending");
}
  


function calendarUpload() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Form Responses 1");
  var Avals = ss.getRange("A1:A").getValues();
  var lastRow = Avals.filter(String).length;
  Logger.log(lastRow);
  
  
  
  
  for (var i = 2; i <= lastRow ; i++) {
  var approvalStatus = sheet.getRange(i,1).getValue();
  var title = sheet.getRange(i,3).getValue();
  var description = sheet.getRange(i,4).getValue();
  var startDate = sheet.getRange(i,5).getValue();
  var formattedStart = Utilities.formatDate(new Date(startDate), 'America/New_York', 'MMMM dd, yyyy');
  var endDate = sheet.getRange(i,6).getValue();
  var formattedEnd = Utilities.formatDate(new Date(endDate), 'America/New_York', 'MMMM dd, yyyy');
  var startTime = sheet.getRange(i,7).getValue();
  var formattedSTime = Utilities.formatDate(new Date(startTime), "EST","HH:mm:ss");
  var endTime = sheet.getRange(i,8).getValue();
  var formattedETime = Utilities.formatDate(new Date(endTime), "EST","HH:mm:ss");
  var location = sheet.getRange(i,10).getValue();
  var weekDays = sheet.getRange(i,9).getValue();
  var calId = sheet.getRange(i,11).getValue();
  var eventStatus = sheet.getRange(1,1,i,12).getCell(i,12);
  Logger.log(eventStatus);  
  Logger.log(title);
  Logger.log(formattedStart);
  Logger.log(formattedEnd);
  Logger.log(formattedSTime);
  Logger.log(formattedETime);
  Logger.log(location);
  
  var startDateandTime = (formattedStart+" "+formattedSTime);
  var endDateandTime = (formattedStart+" "+formattedETime);
  Logger.log(startDateandTime);
  
  
  if (approvalStatus == "Approved" && eventStatus.isBlank()){
   
  
  var days = weekDays.split(', ').map(function(i) { return CalendarApp.Weekday[i]; });
  var eventSeries = CalendarApp.getCalendarById(calId).createEventSeries(title,
    new Date(startDateandTime),
    new Date(endDateandTime),
    CalendarApp.newRecurrence().addWeeklyRule()
        .onlyOnWeekdays(days)
        .until(new Date(formattedEnd)),
        {location: location, description: description});
Logger.log('Event Series ID: ' + eventSeries.getId());
  var setEventStatus = sheet.getRange(i,12).setValue('Event Series ID: ' + eventSeries.getId());
  }
    else{
     Logger.log("No Events Found"); 
    }
  }
}