Skip to content

Instantly share code, notes, and snippets.

Show Gist options
  • Save phillypb/9ffe1ec4dd9df0653340fc4abb11d651 to your computer and use it in GitHub Desktop.
Save phillypb/9ffe1ec4dd9df0653340fc4abb11d651 to your computer and use it in GitHub Desktop.
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();
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment