Updated Sheet to Calendar

I was tasked recently with figuring out a way to automatically schedule calendar events using a Google Sheet as a starting point.

My previous guide went through setting up a Google Calendar, a Google Sheet, Apps Script and even importing a Google Calendar into an Outlook Calendar.

Schedule Google & Outlook Calendar Events Using Google Sheets

This guide will show a simple updated version of my Apps Script used to generate Google Calendar events from a Google Spreadsheet.

The new version allows the events to be sent to multiple calendars automatically with a simple copy paste block of code.

Spreadsheet Setup

The code specifically targets two sheets named “Events” and “Recipients” (either change the code or your sheet names to match).

Events

The “Events” sheet is laid out in this simple manner:

Note: When making an event that lasts all day start it at 00:00:00 of that day and end it at 00:00:00 of the next day.

Recipients

The “Recipients” sheet is also rather simple:

Apps Script

This improved Apps Script code not only allows for multiple recipients but also logs everything it does in a clear manner for easy troubleshooting.

function scheduleEvents() {
  var ss = SpreadsheetApp.getActive();
  var spreadsheet1 = ss.getSheetByName("Events");
  var spreadsheet2 = ss.getSheetByName("Recipients");

  var cals = spreadsheet2.getRange("!B2:B").getValues();

      var cals2 = cals.filter(function(r){
        return r.join("").length>0;
      });

  for (z=0; z<cals2.length;z++) {
    Logger.log("==============================================================");
    Logger.log(cals2[z]);
    Logger.log("==============================================================");
    var eventCal = CalendarApp.getCalendarById(cals2[z]);

      var list = spreadsheet1.getRange("!A2:C").getValues();

      var filtered = list.filter(function(r){
        return r.join("").length>0;
      });

      for (x=0; x<filtered.length;x++)
      {
        var event = filtered[x];
        Logger.log("-------------------------------------------");
        Logger.log(event[0]);
        Logger.log(event[1]);
        Logger.log(event[2]);
        Logger.log("-------------------------------------------");

        var startTime = event[0];
        var endTime = event[1];
        var details= event[2];
        eventCal.createEvent(details, new Date(startTime), new Date(endTime));
      }
  }
}