Import events from a Spreadsheet into Google Calendar. Written in Google Apps script, allows you to create a planning in Google Sheets, and then sync the dates written in the sheet to your calendar.

sheets-to-calendar.gs

// ID of the calendar, can be found in sharing settings of the calendar
var calendarID = "[email protected]"

// Input data from the sheet with name "Final Planning"
let planning = SpreadsheetApp.getActive().getSheetByName('Final Planning')
// Get cell values as strings in 2D array format
let eventsData = planning.getDataRange().getValues()

// Column name shortcuts, their index get determined in setRowIndices based on the column header
let dateRow, clubRow, eventRow, openHouderRow1, openHouderRow2, facebookEventRow, calendarEventIDRow, calendarLinkRow, lastUpdateRow, startDateTimeRow, endDateTimeRow, availableRow, shiftLinkRow, lastCalendarUpdate;
setRowIndices();

// calendar to output to
let eventCal = CalendarApp.getCalendarById(calendarID);

// When opening a spreasheet, this functions adds the menuitems to add and remove the events
function onOpenTrigger() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Hagarkalender')
      .addItem('Soft update all events to calendar', 'updateCalendarEvents')
      .addItem('Remove all events from calendar', 'removeCalendarEvents')
      .addItem('Remove and readd all events', 'addCalendarEvents')
      .addToUi();
}

function updateCalendarEvents() {
  addCalendarEvents(true);
}

// Returns the number of the row of which the header has given name
function getRowWithHeader(headerName){
  headers = eventsData[0];
  // Stops at 50 to prevent infinite loop, but still allow some empty columns
  for(var i = 0; i < 50; i++) {
    if (headers[i] == headerName) {
      return i;
    }
  }
  throw "Error: Header with name " + headerName + " not found"
}

// Sets the variables for their respective row number
function setRowIndices() {
  dateRow, clubRow, eventRow, openHouderRow1, openHouderRow2, calendarLinkRow, startDateTimeRow, endDateTimeRow
  dateRow = getRowWithHeader("Datum");
  clubRow = getRowWithHeader("Club");
  eventRow = getRowWithHeader("Event");
  openHouderRow1 = getRowWithHeader("Openhouder 1");
  openHouderRow2 = getRowWithHeader("Openhouder 2");
  facebookEventRow = getRowWithHeader("Facebook Event");
  calendarEventIDRow = getRowWithHeader("Event ID")
  calendarLinkRow = getRowWithHeader("Calendar link");
  lastUpdateRow = getRowWithHeader("Last row update");
  lastCalendarUpdate = getRowWithHeader("Last calendar update");
  startDateTimeRow  = getRowWithHeader("Start datetime");
  endDateTimeRow = getRowWithHeader("End datetime");
  availableRow = getRowWithHeader("Beschikbaar");
  shiftLinkRow = getRowWithHeader("Shift link");
}

function containsWINAK(string) {
  return string.includes("WINAK");
}

function getEventTitle(rowData) {
  let searchingPeople = rowData[openHouderRow1] || containsWINAK(rowData[clubRow]) ? "" : rowData[availableRow] ? "'" : "*"
  return searchingPeople + rowData[clubRow] + " - " + rowData[eventRow]
}

function getStartTime(rowData) {
  return rowData[startDateTimeRow]
}

function getEndTime(rowData) {
  return rowData[endDateTimeRow]
}

function getDescription(rowData) {
  var facebook = rowData[facebookEventRow] ? "Facebook event: " + rowData[facebookEventRow] + "\n\n" : "";
  var openhouders = "Openhouders: " + rowData[openHouderRow1] + " " + rowData[openHouderRow2] + "\n";

  var lastUpdate = "This event was last updated on " + new Date() + "\n"; 

  if (containsWINAK(rowData[clubRow])){
    openhouders = rowData[openHouderRow1] || rowData[openHouderRow2] ? openhouders + '\n' : "Dit is een WINAK activiteit, en heeft dus geen openhouders nodig. \n\n";
    return facebook + openhouders + lastUpdate;
  }

  var openHoudLink = rowData[openHouderRow1] && rowData[openHouderRow2] ? "" : "Registreer om open te houden: " + rowData[shiftLinkRow] + "\n\n"
  var beschikbaar = "Beschikbare openhouders: " + rowData[availableRow] + "\n\n";
  return facebook + openhouders + beschikbaar + openHoudLink + lastUpdate;
}

// Creates an event in eventCal based on the data found in rowData
function createEvent(rowData, eventCal) {
  var eventTitle = getEventTitle(rowData);
  var startTime = getStartTime(rowData);
  var endTime = getEndTime(rowData);

  var options = {
    location: 'Den Hagar, Fort VI, 2610 Antwerpen',
    description: getDescription(rowData)
  };

  var event = eventCal.createEvent(eventTitle, startTime, endTime, options)

  // Custom colors aren't allowed in Google Calendar :(
  // So using function to get the closest  color instead

  // let club = rowData[clubRow];
  // let clubColorHex = getClubColor(club);
  // let color = getClosestColor(clubColorHex);
  // event.setColor(color);

  // People didn't like different color, so making everything gray instead
  // Color codes can be found here: https://developers.google.com/apps-script/reference/calendar/event-color
  event.setColor("8");

  return event;
}

// Looks at the eventsData (from the spreadsheet) and creates Google Calendar events from it
// Also updates the event id, link and last updated date in the sheet
// softmode checks the last edited date and only updates if recently edited. Note: Only user edits are captured
function addCalendarEvents(softmode=false) {
  setRowIndices();

  var lock = LockService.getScriptLock();
  lock.waitLock(30000); // wait 30s max on the lock

  eventsData = planning.getDataRange().getValues();
  var rowNumber = 1;
  while(true) {
    var rowData = eventsData[rowNumber];

    if(rowData[0] == "") {
      break;
    }

    if(rowData[calendarEventIDRow] != "") {
      var lastRowUpdate = planning.getRange(rowNumber+1, lastUpdateRow+1).getValue();
      var lastEventUpdate = planning.getRange(rowNumber+1, lastCalendarUpdate+1).getValue();
      
      if (softmode && lastRowUpdate && lastEventUpdate && lastRowUpdate < lastEventUpdate){
        // Overwritten events must all be removed manually, so let's not do it
        // console.log("Skipping existing event...");
        rowNumber++;
        continue;
      }

      // There already exists a row, removing it first
      var eventID = planning.getRange(rowNumber+1, calendarEventIDRow+1).getValue();
      removeCalendarEvent(rowNumber, eventID);
    }

    var event = createEvent(rowData, eventCal)

    // Get a link to the event
    var splitEventId = event.getId().split('@');
    var eventURL = "https://calendar.google.com/calendar/u/2/r/event?eid=" + Utilities.base64Encode(splitEventId[0] + " " + calendarID);
    var eventHyperlink = "=HYPERLINK(\"" + eventURL + "\", \"Click here\")";

    // Update event info in sheet
    planning.getRange(rowNumber+1, calendarEventIDRow+1).setValue(event.getId());
    planning.getRange(rowNumber+1, calendarLinkRow+1).setValue(eventHyperlink);
    planning.getRange(rowNumber+1, lastCalendarUpdate+1).setValue(new Date());

    SpreadsheetApp.flush();

    console.log("Updated rowNumber ", rowNumber);

    rowNumber++;
  }
  lock.releaseLock();
}

// Remove all events from the Google Calendar
// Note: Their id must be in the sheet for this
function removeCalendarEvents() {
  setRowIndices();
  
  var rowNumber = 1;
  while(true) {
    var eventClub = eventsData[rowNumber][clubRow];
    var eventID = eventsData[rowNumber][calendarEventIDRow];

    if (eventClub == ""){
      break;
    }

    if (eventID == "") {
      rowNumber++;
      continue;
    }

    removeCalendarEvent(rowNumber, eventID);

    rowNumber++;
  }
}

function removeCalendarEvent(rowNumber, eventID) {
  try {
    var event = eventCal.getEventSeriesById(eventID);
    event.deleteEventSeries();

    planning.getRange(rowNumber+1, calendarEventIDRow+1).setValue("");
    planning.getRange(rowNumber+1, calendarLinkRow+1).setValue("");
    planning.getRange(rowNumber+1, lastCalendarUpdate+1).setValue("");

    SpreadsheetApp.flush();
  } catch(e) {
    // Expected error: Event does not exist
    console.log(e.message);
  }
}

function addAndRemove() {
  // Does only remove for some reason
  removeCalendarEvents();
  SpreadsheetApp.flush();
  addCalendarEvents();
}

// Update the last edited row every time a cell is editted
function onEditTrigger() {
 var s = SpreadsheetApp.getActiveSheet();
 if( s.getName() == "Final planning" ) { //checks that we're on the correct sheet
   var r = s.getActiveCell();
   var time = new Date();
   var rowNumber = r.getRow()
   if (planning.getRange(rowNumber, dateRow+1).getValue() != "" && rowNumber > 1) {
    planning.getRange(rowNumber, lastUpdateRow+1).setValue(time);
    SpreadsheetApp.flush();
    updateCalendarEvents();
   }
 };
}

// Update the last row change time when a form is submitted
function onSubmitTrigger(e){
//   Logger.log("%s", JSON.stringify(e));
//   {
//    "authMode":"FULL",
//    "namedValues":{
//       "Timestamp":[
//          "01/09/2021 15:21:35"
//       ],
//       "Datum Activiteit":[
//          "25/09/2021"
//       ],
//       "Naam Activitieit":[
//          "DIEFKA - Overdrachtscantus"
//       ],
//       "Email address":[
//          "[email protected]"
//       ]
//    },
//    "range":{
//       "columnEnd":4,
//       "columnStart":1,
//       "rowEnd":6,
//       "rowStart":6
//    },
//    "source":{
      
//    },
//    "triggerUid":"8120067",
//    "values":[
//       "01/09/2021 15:21:35",
//       "[email protected]",
//       "25/09/2021",
//       "DIEFKA - Overdrachtscantus"
//    ]
// }

  Logger.log("%s", JSON.stringify(e));
  var formDateStr = e["namedValues"]["Datum Activiteit"][0];
  var formEventName = e["namedValues"]["Naam Activiteit"][0];
  var splittedDate = formDateStr.split('/');
  // The month is 0-indexed
  var formDate = new Date(splittedDate[2], splittedDate[1]-1, splittedDate[0])
  var formDateFormattedStr = Utilities.formatDate(formDate, "GMT+2", "dd/MM/yyyy")

  var dateColumn = dateRow;
  var currentRow = 1;
  while (true) {
    let cell = planning.getRange(currentRow+1, dateColumn+1);
    let cellDate = cell.getValue()

    let clubName = planning.getRange(currentRow+1, clubRow+1).getValue();
    let eventTitle = planning.getRange(currentRow+1, eventRow+1).getValue();
    let eventName = clubName + " - " + eventTitle;
    console.log(eventName, formEventName)
    
    if (cellDate == ""){
      throw "Date and title combination not found"
      break;
    }
    
    var cellDateStr = Utilities.formatDate(cellDate, "GMT+2", "dd/MM/yyyy")
    if (cellDateStr == formDateFormattedStr && formEventName == eventName) {
      console.log("Row ", currentRow, " was changed")
      var now = new Date();
      planning.getRange(currentRow+1, lastUpdateRow+1).setValue(now);
      SpreadsheetApp.flush();
      break;
    }

    currentRow++;
  }
  
  updateCalendarEvents();
}

// Can only be triggered manually, removes all events in given time range, use with caution
// https://rishidhar.me/how-to-bulk-delete-google-calendar-events-via-google-apps-script/
function bulk_delete_events()
{
  //Please note: Months are represented from 0-11 (January=0, February=1). Ensure dates are correct below before running the script.
  var fromDate = new Date(2021,8,11,0,0,0); //This represents Sept 11th 2021
  var toDate = new Date(2021,11,31,0,0,0); //This represents Dec 31th 2020

  var calendar = CalendarApp.getCalendarById(calendarID);
  
  //Search for events between fromdate and todate with given search criteria
  var events = calendar.getEvents(fromDate, toDate);
  for(var i=0; i<events.length;i++) //loop through all events
  {
    var ev = events[i];
    Logger.log('Event: '+ev.getTitle()+' found on '+ev.getStartTime()); // Log event name and title
      ev.deleteEvent(); // delete event
  }
}


// Functions below this are only for coloring the Events based on a color table defined in Google Sheets
// Google Calendar only supports a few colors, so there are also help functions to find the closest matching color

// Convert a hex color string (e.g. "#A4BDFC") to it's RGB values (max. 255 each)
function hexToRgb(hex){
 	return {
    	red: parseInt(hex.slice(1,3),  16),
      green: parseInt(hex.slice(3,5),  16),
  		blue: parseInt(hex.slice(5,7),  16)
    }
}

// Convert a RGB color to a HSV color, so we can use the Hue to find the closest matching color
// Note: Euclidean distance for colors doesn't give a good result, so we have to use Hue instead
// https://stackoverflow.com/questions/8022885/rgb-to-hsv-color-in-javascript
// input: r,g,b in [0,1], out: h in [0,360) and s,v in [0,1]
function rgb2hsv(r,g,b) {
  let v=Math.max(r,g,b), c=v-Math.min(r,g,b);
  let h= c && ((v==r) ? (g-b)/c : ((v==g) ? 2+(b-r)/c : 4+(r-g)/c)); 
  return [60*(h<0?h+6:h), v&&c/v, v];
}

// Determines how similar two colors are based on the Hue value
function colorDistance(colorHue1, colorHue2) {
  // https://stackoverflow.com/questions/9018016/how-to-compare-two-colors-for-similarity-difference
  var avghue = (colorHue1 + colorHue2)/2
  var distance = Math.abs(colorHue1-avghue)
  return distance;
  // Euclidian -> Gives colors that doesn't seem familiar for humans, so don't use this
  // return (colorRgb1.red - colorRgb2.red)^2 + (colorRgb1.green - colorRgb2.green)^2 + (colorRgb1.blue - colorRgb2.blue)^2
}

// Returns the Hue
function getHue(rgbColor) {
  return rgb2hsv(rgbColor.red / 256, rgbColor.green / 256, rgbColor.blue / 256)[0];
}


// Given a color hex, this gives the ID of the closest matching color in Google Sheets
// All colors and their ID's can be found here https://developers.google.com/apps-script/reference/calendar/event-color
function getClosestColor(colorHex) {
  var clubColor = hexToRgb(colorHex);
  var clubHue = getHue(clubColor)
  // Colors from the Enum EventColor docs (obtained with inspect element on the colors)
  // https://developers.google.com/apps-script/reference/calendar/event-color
  var colorRGBs = ["#a4bdfc", "#7AE7BF", "#BDADFF", "#FF887C", "#FBD75B", "#FFB878", "#46D6DB", "#E1E1E1", "#5484ED", "#51B749", "#DC2127"]

  var minDistance = Infinity;
  var closestColor = 0;
  for (var index in colorRGBs) {
    var color = colorRGBs[index];
    var colorRGB = hexToRgb(color);
    var colorHue = getHue(colorRGB);

    var distance = colorDistance(clubHue, colorHue);

    if (distance <= minDistance) {
      minDistance = distance;
      closestColor = index;
    }
  }
  
  return parseInt(closestColor) + 1;
}

// In the calender view spreadsheet, there is a column with each club on it, this returns the background color of that cell
function getClubColor(club) {
  let calenderView = SpreadsheetApp.getActive().getSheetByName('Calendar view')
  var colorColumn = 15+1;
  var currentRow = 3+1;
  while (true) {
    let cell = calenderView.getRange(currentRow, colorColumn);
    let cellClub = cell.getValue()
    
    if (cellClub == ""){
      break;
    }
    
    if (cellClub == club) {
      let backgroundColor = cell.getBackground()
      return backgroundColor;
    }

    currentRow++;
  }
}

Here you go: https://docs.google.com/spreadsheets/d/1BRIsK14XGSHiB-AN8WX2NlzZ88mOj42fY3KR7M6LCms/edit#gid=1172790191

Here you go: https://docs.google.com/spreadsheets/d/1BRIsK14XGSHiB-AN8WX2NlzZ88mOj42fY3KR7M6LCms/edit#gid=1172790191
Thank you very much Arno!