How do I automatically create a Google Calendar event from Google Sheets?

Viewed 41

Total noob here.

Right now, I am trying to automatically create Google Calendar events based on data in Google Sheets with Apps Scripts. The original issue I had when trying to do this was creating duplicates each time I synced the Spreadsheet data to Google Calendar. I solved this with the code below (this is code that I put together from snippets, I don't have a good understanding of why it works the way it works):

function createCalendarEvent() {
  let sheet = SpreadsheetApp.getActiveSheet();
  var calendarId = sheet.getRange("V8:X8").getValue();
  let mintCalendar = CalendarApp.getCalendarById(calendarId);

  let schedule = sheet.getDataRange().getValues();
  schedule.splice(0, 2);

  schedule.forEach(function(entry) {
    let event = mintCalendar.getEvents(entry[27],entry[28]);
    if(event.length === 0){
      mintCalendar.createEvent(entry[0], entry[27], entry[28]);
    }
    
  });
}
function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Sync to Calendar')
  .addItem('Sync Mints Now','createCalendarEvent')
  .addToUi();
}

The issue now is that when I sync events from Google Sheets it will not post events during the same timeframe even if they are on separate rows. It also will not update previous entries. So how would I go about doing the following:

  1. Create Google Calendar event based on info from Google Sheets automatically when required fields are filled.
  2. Update any existing calendar events if I updated its data on Google Sheets.
  3. Only post updates and new entries of events from Google Sheets and not duplicate any previous entries.

Thank you!

Image 1: https://i.stack.imgur.com/KOAD0.png Example here of multiple entries on the same day. The date and time on rows 2 and 3 will not show up on Google Calendar because it falls within the timeframe of the item on row 1. Only row 1 will show up.

0 Answers
Related