Create Google Calendar Recurring Events from Spreadsheet

Question

At the bottom is the code from a previous blog, which works great! This code is set up with the following Google sheet header:

Date | Title | Start Time | End Time | Location | Description | EventID

However, I need to have the ability to create recurring events. The new Google sheet header is as follow:

Date | Title | Start Time | End Time | Location | Description | Type | Recurring | EventID

I need to create recurring events if Type = "PM" (new column) on a monthly basis for "Recurring" (also a new column) amount of months. How is this possible while still not having duplicates every time the script is ran?

/**
 * Adds a custom menu to the active spreadsheet, containing a single menu item
 * for invoking the exportEvents() function.
 * The onOpen() function, when defined, is automatically invoked whenever the
 * spreadsheet is opened.
 * For more information on using the Spreadsheet API, see
 * https://developers.google.com/apps-script/service_spreadsheet
 */
function onOpen() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var entries = [{
    name : "Export Events",
    functionName : "exportEvents"
  }];
  sheet.addMenu("Calendar Actions", entries);
};

/**
 * Export events from spreadsheet to calendar
 */
function exportEvents() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var headerRows = 1;  // Number of rows of header info (to skip)
  var range = sheet.getDataRange();
  var data = range.getValues();
  var calId = "YOUR_CALENDAR_ID";
  var cal = CalendarApp.getCalendarById(calId);
  for (i in data) {
    if (i < headerRows) continue; // Skip header row(s)
    var row = data[i];
    var date = new Date(row[0]);  // First column
    var title = row[1];           // Second column
    var tstart = new Date(row[2]);
    tstart.setDate(date.getDate());
    tstart.setMonth(date.getMonth());
    tstart.setYear(date.getYear());
    var tstop = new Date(row[3]);
    tstop.setDate(date.getDate());
    tstop.setMonth(date.getMonth());
    tstop.setYear(date.getYear());
    var loc = row[4];
    var desc = row[5];
    var id = row[6];              // Sixth column == eventId
    // Check if event already exists, update it if it does
    try {
      var event = cal.getEventSeriesById(id);
    }
    catch (e) {
      // do nothing - we just want to avoid the exception when event doesn't exist
    }
    if (!event) {
      //cal.createEvent(title, new Date("March 3, 2010 08:00:00"), new Date("March 3, 2010 09:00:00"), {description:desc,location:loc});
      var newEvent = cal.createEvent(title, tstart, tstop, {description:desc,location:loc}).getId();
      row[6] = newEvent;  // Update the data array with event ID
    }
    else {
      event.setTitle(title);
      event.setDescription(desc);
      event.setLocation(loc);
      // event.setTime(tstart, tstop); // cannot setTime on eventSeries.
      // ... but we CAN set recurrence!
      var recurrence = CalendarApp.newRecurrence().addDailyRule().times(1);
      event.setRecurrence(recurrence, tstart, tstop);
    }
    debugger;
  }
  // Record all event IDs to spreadsheet
  range.setValues(data);

Answer 1

Ok, this was again something interesting... The code above needed a few modification to do what you wanted :

Since newly created events are not series (or else they must be created as eventSeries but this would make the conditions more complicated...) when we create a new event we dont use that object but get it using getEventSeriesById() which implicitly changes its nature without needing to define a recurrence.

This trick works just fine and makes the code simpler.

The other issue was about setting time and dates : your code took the hour/minutes value from a date object without year (that's normal when reading a SS) but it means that the Javascript Date has a date value in January (month 0) and January is in winter (as you know XD) so we had a problem with daylight savings and all time values were 1 hour later because setting month and date afterwards didn't change hour value (this is unclear I'm afraid...but you could check it using your code these days)

I had to invert the process and set time value to the date object instead, this gives the right result.

Since it's a bit more code to write I created a small function to do the job : it helps to keep the main code "cleaner".

Below it the full code, I added also a 'PER WEEK' recurrence to test the idea... keep it or leave it if you don't need it .

//    Date | Title | Start Time | End Time | Location | Description | Type | Recurring | EventID

function onOpen() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var entries = [{
    name : "Export Events",
    functionName : "exportEvents"
  }];
  sheet.addMenu("Calendar Actions", entries);
};

/**
 * Export events from spreadsheet to calendar
 */
function exportEvents() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var headerRows = 1;  // Number of rows of header info (to skip)
  var range = sheet.getDataRange();
  var data = range.getValues();
  var calId = CalendarApp.getDefaultCalendar().getId();// use default claendar for tests
  var cal = CalendarApp.getCalendarById(calId);
  for (i in data) {
    if (i < headerRows) continue; // Skip header row(s)
    var row = data[i];
    var date = new Date(row[0]);  // First column
    var title = row[1];           // Second column
    var tstart = setTimeToDate(date,row[2]);
    var tstop = setTimeToDate(date,row[3]);
    Logger.log('date = '+date+'tstart = '+tstart+'  tstop = '+tstop);
    var loc = row[4];
    var desc = row[5];
    var type = row[6];
    var times = row[7]
    var id = row[8]; 
    // Check if event already exists, update it if it does
    try {
      var event = cal.getEventSeriesById(id);
      event.setTitle('got you');// this is to "force error" if the event does not exist, il will never show for real ;-)
    }catch(e){
      var newEvent = cal.createEvent(title, tstart, tstop, {description:desc,location:loc}); // create a "normal" event
      row[8] = newEvent.getId();  // Update the data array with event ID
      Logger.log('event created');// while debugging
      var event = cal.getEventSeriesById(row[8]);// make it an event Serie
    }
    event.setTitle(title);
    event.setDescription(desc);
    event.setLocation(loc);
    if(type=='PM'){
      var recurrence = CalendarApp.newRecurrence().addMonthlyRule().times(times)
      event.setRecurrence(recurrence, tstart, tstop);// we need to keep start and stop otherwise it becomes an AllDayEvent if only start is used
    }else if(type=='PW'){
      var recurrence = CalendarApp.newRecurrence().addWeeklyRule().times(times)
      event.setRecurrence(recurrence, tstart, tstop);
    }
  data[i] = row ;
  }
range.setValues(data);
}

function setTimeToDate(date,time){
  var t = new Date(time);
  var hour = t.getHours();
  var min = t.getMinutes();
  var sec = t.getSeconds();
  var dateMod = new Date(date.setHours(hour,min,sec,0))
  return dateMod;
}

test sheet here in view only

Answer 2

If you want to display a recurring event on specific intervals, the .interval function allows you to do that under the recurrence method. In the above example, changing the appropriate code to

var recurrence = CalendarApp.newRecurrence().addMonthlyRule().interval(times)

does that. This means if for one event, times = 3, this event will appear on your calendar every 3 months.

Answer 3

I've been battling appscript tooth and nail for 2 days trying to do something similar. So, thanks, you saved me! And yes, my coding abilities are very lacking, and there probably is a better way to do this, but I wanna put this here so you guys can consult it.

My code has to take data from a sheet and connect it to calendar, but all of those are recurring events with different days of the week.

So, tweaking a bit your code what I have is this: my columns titles are

Subject | Start Date | End Date | Days of the week | Days of the week | Days of the week | Days of the week | Start Time | Duration | Guests

function onEdit(e) {
  var sheet = e.source.getSheetByName("Calendar");

  if (sheet.getName() == "Calendar") {
    var row = e.range.getRow();

    // Verifica se a linha editada não é um cabeçalho
    if (row > 1) {
      exportarEventos();
    }
  }
}

function exportarEventos() {
  var sheetId = "spreadsheetid";
  var sheetName = "Calendar";
  var calendarId = "mycalendarID";

  var spreadsheet = SpreadsheetApp.openById(sheetId);
  var sheet = spreadsheet.getSheetByName(sheetName);
  var data = sheet.getDataRange().getValues();

  var calendar = CalendarApp.getCalendarById(calendarId);

  var headerRows = 1; // Número de linhas de informações do cabeçalho (a ignorar)

  for (var i = headerRows; i < data.length; i++) {
    var row = data[i];
    var subject = row[0];
    var startDate = new Date(row[1]);
    var endDate = new Date(row[2]);
    var daysOfWeek = getDaysOfWeek(row.slice(3, 7).map(Number)); // Converter os números em strings
    var startTime = row[7];
    var guest = row[9];

    if (subject && startDate && startTime && guest) {
      var eventSeries = calendar.getEvents(startDate, endDate);
      var eventExists = checkEventExists(eventSeries, subject, startDate, startTime, guest);

      if (!eventExists) {
        Logger.log("Criando evento: " + subject);
        createEventSeries(calendar, subject, startDate, endDate, daysOfWeek, startTime, guest);
      } else {
        Logger.log("Evento já existe: " + subject);
      }
    }

    Utilities.sleep(2000); // Pausa de 2 segundos
  }
}

function getDaysOfWeek(daysArray) {
  var daysOfWeek = [];
  var days = ["Domingo", "Segunda-feira", "Terça-feira", "Quarta-feira", "Quinta-feira", "Sexta-feira", "Sábado"];

  for (var i = 0; i < daysArray.length; i++) {
    if (daysArray[i]) {
      daysOfWeek.push(days[i]);
    }
  }

  return daysOfWeek;
}

function checkEventExists(events, subject, startDate, startTime, guest) {
  for (var i = 0; i < events.length; i++) {
    var event = events[i];
    if (
      event.getTitle() === subject &&
      event.getStartTime().getTime() === startDate.getTime() &&
      event.getStartTime().getHours() === startTime.getHours() &&
      event.getStartTime().getMinutes() === startTime.getMinutes() &&
      event.getGuestList().indexOf(guest) !== -1
    ) {
      return true;
    }
  }
  return false;
}

function createEventSeries(calendar, subject, startDate, endDate, daysOfWeek, startTime, guest) {
  var recurrence = CalendarApp.newRecurrence().addWeeklyRule().interval(1)

  daysOfWeek.forEach(function(day) {
    switch (day) {
      case "Domingo":
        recurrence.addWeeklyRule().onlyOn(CalendarApp.Weekday.SUNDAY);
        break;
      case "Segunda-feira":
        recurrence.addWeeklyRule().onlyOn(CalendarApp.Weekday.MONDAY);
        break;
      case "Terça-feira":
        recurrence.addWeeklyRule().onlyOn(CalendarApp.Weekday.TUESDAY);
        break;
      case "Quarta-feira":
        recurrence.addWeeklyRule().onlyOn(CalendarApp.Weekday.WEDNESDAY);
        break;
      case "Quinta-feira":
        recurrence.addWeeklyRule().onlyOn(CalendarApp.Weekday.THURSDAY);
        break;
      case "Sexta-feira":
        recurrence.addWeeklyRule().onlyOn(CalendarApp.Weekday.FRIDAY);
        break;
      case "Sábado":
        recurrence.addWeeklyRule().onlyOn(CalendarApp.Weekday.SATURDAY);
        break;
    }
  });

  recurrence.until(new Date(endDate.getTime() + 24 * 60 * 60 * 1000)); // Adiciona 1 dia à data de término

  var startTime = setTimeToDate(startDate, startTime);
  var endTime = new Date(startTime.getTime() + 60 * 60 * 1000); // Adiciona 1 hora à hora de início

  Logger.log("Criando série de eventos: " + subject);
  calendar.createEventSeries(subject, startTime, endTime, recurrence, {
    guests: guest
  });
}

function setTimeToDate(date, time) {
  var timeString = Utilities.formatDate(date, "GMT", "yyyy-MM-dd") + " " + Utilities.formatDate(time, "GMT", "HH:mm:ss");
  return new Date(timeString);
}