Syncing Data from Google Sheets to Events in Google Calendar

From Google Sheets you can add an “App Script” from the “Extensions” menu.

This will allow you to do a few things, such as code and test an App Script. Here we most importantly need a way to sync some rows from a sheet that we can consider as events in Google Calendar.

I was following this link, but it is like a bunch of clickbait to go load a bunch of other blog posts and videos (for ad revenue I’m sure). Not to mention it is incomplete and missing important details in my opinion.

TL;DR, here are three functions you can add that will allow you to trigger a sync from the Google Sheet document to Google Calendar. It will take care of looping and deleting existing events from that day to prevent duplications and is actually fully functioning.

// https://developers.google.com/apps-script/guides/menus
// https://workspace.google.com/blog/productivity-collaboration/g-suite-pro-tip-how-to-automatically-add-a-schedule-from-google-sheets-into-calendar
// https://medium.com/@dontmesswithjo/coding-basics-for-loops-90dca2873c70
// https://medium.com/@dontmesswithjo/custom-menus-94d8e4325b0d
function onOpen() {
  var ui = SpreadsheetApp.getUi();
  // Or DocumentApp, SlidesApp or FormApp.
  ui.createMenu('Sync to Calendar')
      .addItem('Schedule Reservations', 'scheduleReservations')
      .addToUi();
}

function scheduleReservations() {
  SpreadsheetApp.getUi() // Or DocumentApp, SlidesApp or FormApp.
     .alert('You clicked the first menu item!');
  syncReservations();
}

function syncReservations() {
  var spreadsheet = SpreadsheetApp.getActiveSheet();
  var calendarId = "foobar@group.calendar.google.com";

  var eventCal = CalendarApp.getCalendarById(calendarId);

  var reservations = spreadsheet.getRange("A18:D30").getValues();

  for (x=0; x<reservations.length; x++) {
    var reservation = reservations[x];
    
    var startTime = reservation[2];
    var endTime = reservation[3];
    var title = reservation[0] + " @ " + reservation[1];

    if(title.trim().length > 2) {
      // clean up any existing events from the day first
      eventCal.getEventsForDay(new Date(startTime)).forEach(function(e){
        Logger.log(e.getTitle());
        e.deleteEvent();
      })

      // create new events
      Logger.log("Creating event with title [" + title + "]");
      eventCal.createEvent(title, new Date(startTime), new Date(endTime));
    }
  }
}

Just in case you care,

• Column A – contains a name
• Column B – contains a title / location
• Column C – Start day
• Column D – End day