Create a calendar event from a spreadsheet

The following Google Apps Script code is designed to send an event invitation to a number of assignee's for the date their name is against, along with a room location. For a couple of years I project managed our annual summer rebuilds which involved the help of other technical assistants who would be assigned a room to attend on the day rebuilds were scheduled. During my JavaScript learning I decided to use this small project as a way of trying out some functions that could automate creating a calendar event automatically for each person.

A number of the functions have been described in my other blog posts (Check cell colour, Get date for email event, Lookup email address), here I now combine them all. By changing the cell background colour I can provide a way to safely repeat the function should an issue occur - or more likely, if there is a change in assignee.

In terms of the calendar event this is managed via 'CalendarApp' and it will create the event in your default calendar, subsequently inviting the assignee. 'createEvent' is used to set the title, start/end details, along with extra options - location, guests (to invite the assignee), etc.

	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();
	}

view raw Create a calendar event from a spreadsheet.gs hosted with ❤ by GitHub

Calendar Event from Sheet.xlsx