WINAK Praesidium Calendar from sheets

// ID of the calendar, can be found in sharing settings of the calendar
// TODO: Dit is momenteel nog het ID van de Hagarkalender, updaten naar de praesidiumkalender aub!
var calendarID = "[email protected]"

// Input data from the sheet with name "Blad3"
let planning = SpreadsheetApp.getActive().getSheetByName('Blad3')
// 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
// TODO: Sorry, I switched the word Row and Column, this are of course all columns
let dateRow, eventRow, calendarEventIDRow, calendarLinkRow;
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
// Note that to get this to work, you must add this function in the triggers
function onOpenTrigger() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Hagarkalender')
      .addItem('Soft update all events to calendar', 'updateCalendarEvents')
      .addItem('Remove all events from calendar', 'removeCalendarEvents')
      .addToUi();
}

// 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 = getRowWithHeader("Datum");
  eventRow = getRowWithHeader("Activiteit");
  calendarEventIDRow = getRowWithHeader("Event ID")
  calendarLinkRow = getRowWithHeader("Calendar link");
}

function getEventTitle(rowData) {
  return rowData[eventRow]
}

// Creates an event in eventCal based on the data found in rowData
function createEvent(rowData, eventCal) {
  var eventTitle = getEventTitle(rowData);
  var date = rowData[dateRow];

  var event = eventCal.createAllDayEvent(eventTitle, date)

  // Color codes can be found here: https://developers.google.com/apps-script/reference/calendar/event-color
  event.setColor("6");

  return event;
}


// Looks at the eventsData (from the spreadsheet) and creates Google Calendar events from it
// Also updates the event id and link
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(typeof rowData === "undefined" || rowData[0] == "") {
      // Stop on the row where there is no date, aka the first empty row
      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);

    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) {
    if (typeof eventsData[rowNumber] === "undefined"){
      break;
    }

    var eventName = eventsData[rowNumber][eventRow];
    var eventID = eventsData[rowNumber][calendarEventIDRow];

    if (eventName == ""){
      // In case row is not undefinded, but still contains no event
      break;
    }

    if (eventID == "") {
      // When there hasn't been an event created for this
      rowNumber++;
      continue;
    }

    removeCalendarEvent(rowNumber, eventID);

    console.log("Removed event ", rowNumber)

    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("");
    
    SpreadsheetApp.flush();
  } catch(e) {
    // Expected error: Event does not exist
    console.log(e.message);
  }
}