https://www.pbainbridge.co.uk/2018/12/create-calendar-event-from-spreadsheet.html

Create a calendar event from a spreadsheet.gs

function createEventFromSheet() {
  
  // get 'Plan' sheet
  var planSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Plan');
  // get 'Plan' sheet data range
  var planDataRange = planSheet.getDataRange();
  // get 'Plan' sheet data values
  var planData = planDataRange.getValues();
  // get number of last row of data in 'Plan' sheet
  var planLastRow = planSheet.getLastRow();
  
  // get 'Email Address' sheet
  var emailSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Email Address');
  // get 'Email Address' sheet data
  var emailData = emailSheet.getDataRange().getValues();
  // get number of last row of data
  var emailLastRow = emailSheet.getLastRow();
  
  
  // loop through each Assignee from 'Plan' sheet to get relevant details (skipping row headers) **********
  for (i=1; i<planLastRow; i++) {
    
    // check Assignee cell is not blank before proceeding
    // i = row, 6 = column - as is 2-D array
    if (planData[i][6]) {
      Logger.log('Assignee cell is not blank');
      
      // get current cell of 'Assignee' column row. Add 1 to row value 'i' otherwise is a row behind
      var getCell = planDataRange.getCell([i+1], [7]);
      
      // get cell background colour of 'Assignee' column row. 
      var getCellColour = getCell.getBackground();
      
      // check Assignee cell colour is white, otherwise assume event email aready sent and take no action
      if (getCellColour == '#ffffff') {
        Logger.log('Cell is white, will proceed');
        
        // get corresponding 'Room number' for Assignee
        var roomNumber = (planData[i][4]);
        Logger.log(roomNumber);
        
        // Date Start **************************************************************************************
        // get corresponding 'Date' for Assignee via 'new Date' so is a date object (correctly formatted)
        var date = new Date(planData[i][1]);
        // add 16 hours to the date: for 4pm start time of event
        date.setHours(16);
        // specifically format date so appropriate for feeding into event
        var dateStart = Utilities.formatDate(date, "GMT+1", "MMMM dd yyyy' 'HH:mm");
        Logger.log(dateStart);
        
        //create end date for event - add 16 hours 30 mins to the original date for 4:30pm finish
        date.setHours(16);
        date.setMinutes(30);
        // specifically format date so appropriate for feeding into event
        var dateEnd = Utilities.formatDate(date, "GMT+1", "MMMM dd yyyy' 'HH:mm");
        Logger.log(dateEnd);
        // Date End ****************************************************************************************
        
        // loop through each Assignee from 'Plan' with each Initials in 'Email Address' to find match ******
        for (j=0; j<emailLastRow; j++) {
          
          if (planData[i][6] == emailData[j][0]) {
            Logger.log(planData[i][6] + ' ' + emailData[j][0]);
            // if match found then get corresponding email address from column 2
            var assigneeEAddress = emailData[j][1];
            Logger.log(assigneeEAddress);
          } // end of 'if match' loop **********************************************************************
          
        } // end of 'j' loop *******************************************************************************
        
        
        // details for calendar event
        var cal = CalendarApp.getDefaultCalendar();
        var title = 'Classroom rebuild';
        
        /* creates calendar event with supplied data, uses 'new Date' as even though getting this from
        '(planData[i][1])' changing the format breaks it, so stipulate again here */
        var event = cal.createEvent(title, new Date(dateStart), new Date(dateEnd),
                                    {location:roomNumber,guests:assigneeEAddress,sendInvites:'true'});
        
        
        // set cell background colour of 'Assignee' column row to green to mark as complete 
        var setCellColour = getCell.setBackground('#00ff00');
        
        // create a popup on the spreadsheet after each successful iteration
        SpreadsheetApp.getActiveSpreadsheet().toast('Created event for ' + assigneeEAddress);
        
      } // end of 'if' for checking Assignee cell is white
      else {Logger.log('Cell is not white, will not proceed')};
      
    } // end of 'if' for checking Assignee cell not blank
    else {Logger.log('Assignee cell is blank')};
    
  } // end of 'i' loop through each Assignee **************************************************************
  
}


function onOpen() {
  SpreadsheetApp.getUi()
  .createMenu('Menu')
  .addItem('Email Assignees', 'createEventFromSheet') // label for menu item, name of function to run.
  .addToUi();
}