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.

Hello! I was wondering if there is a way to do this, but when updating the data, not have the previous evends doubled (since I am using it for events from here to a year away, whe I try tu update, events in December for example are doubled or tripled) Thank you in advance!

I wrote (or copied) this? Omg.

As described in the last paragraph, the code does not support the update use case that you described. Either you write more code to check for updates, or you delete all the events and create all again.

Thank you!