How to (auto-manually) update Google Calendar entries with Google Spreadsheets

calendar.md

How to (auto-manually) update Google Calendar entries with Google Spreadsheets

Key reference

https://towardsdatascience.com/creating-calendar-events-using-google-sheets-data-with-appscript-203b26446ce9

Result is here

https://calendar.google.com/calendar/embed?src=smu.edu.sg_j6t7pmeq7214nqb72lf2j3vs7s@group.calendar.google.com&ctz=Asia%2FSingapore&mode=week&wkst=1

The name of the script file does not matter.

This is the code to createEvent from his dropbox, copied here for easier reference.

function CreateEvent() {
  
  var spreadsheet = SpreadsheetApp.getActiveSheet();
  var calendarId = "smu.edu.sg_j6t7pmeq7214nqb72lf2j3vs7s@group.calendar.google.com";
  var eventCal = CalendarApp.getCalendarById(calendarId);
  var lr = spreadsheet.getLastRow();
  Logger.log(lr);
      
  var count = spreadsheet.getRange("A2:F"+lr+"").getValues(); 
  var count = spreadsheet.getRange("A2:F999").getValues(); 
  
    for (x=0; x<count.length; x++) {
      
      var shift = count[x];
      
      var summary = shift[0];
      var startTime = new Date(shift[1]);
      var endTime = new Date(shift[2]);
      var guests = shift[3];
      var description = shift[4];
      var location = shift[5];   
      var event = {
          'location': location,
          'description': description,
          'guests':guests +',',
          'sendInvites': 'True',
      }
      eventCal.createEvent(summary, startTime, endTime)
  }
}

This creates a tab on the sheets, as an UI to update your spreadsheets.

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Sync to Calendar')
      .addItem('Create Events Now', 'CreateEvent')
      .addToUi();
}

On the spreadsheet. Parsing time is a tricky problem. If you pass a number to Date() Google Apps Script, which is Javascript, takes it as mini-seconds starting from 1970.

On the other hand, the date is stored in terms of days, and New Year Day of 2000 is day 36526.

Therefore we parse it in ISO Datetime format which is YYYY-MM-DDTHH:MM:SS. When parsed into ISO Datetime format, the timezone is already factored in, we should leave it so. When Javascript parses the ISO Datetime, it assumes the GMT+0 time. When you view on your Google Calendar, the times are already timezone adjusted.

This is, of course, a hacky solution. When you press update, it creates all the events again. If you want to only update new events, then you need to write plenty of code. If you want the calendar to automatically update, you need to write code again. If you want it.

Thank you!