Cannot call method "createEvent" of null.Details -- Manage employee vacation time requests

Open Smshaul opened this issue 5 years ago • 0 comments

I am getting the message "Cannot call method "createEvent" of null. with this code". any ideas?

// Copyright 2019 Google LLC // // Licensed under the Apache License, Version 2.0 (the "License"); // you may not use this file except in compliance with the License. // You may obtain a copy of the License at // // https://www.apache.org/licenses/LICENSE-2.0 // // Unless required by applicable law or agreed to in writing, software // distributed under the License is distributed on an "AS IS" BASIS, // WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. // See the License for the specific language governing permissions and // limitations under the License.

var COLUMN_NUMBER = { EMAIL: 2, NAME: 3, START_DATE: 4, END_DATE: 5, APPROVAL: 7, NOTIFIED: 8, };

var APPROVED_DROPDOWN = { APPROVED: 'APPROVED', NOT_APPROVED: 'NOT APPROVED', IN_PROGRESS: 'IN PROGRESS', };

var NOTIFIED_DROPDOWN = { NOTIFIED: 'NOTIFIED', NOT_NOTIFIED: 'NOT NOTIFIED', };

var VACATION_REASONS = [ 'Vacation', 'Sick leave', 'Maternity/Paternity', 'Bereavement', 'Leave of absence', 'Personal time', ];

var REJECTION_EMAIL_SUBJECT = 'ERR: Vacation Time Request NOT Approved'; var EVENT_TITLE = "VACATION FOR ";

/** TODO: Hard code your manager's email */ var MANAGER_EMAIL = '[email protected]';

/**

Add custom menu items when opening the sheet. */ function onOpen() { var sheetUi = SpreadsheetApp.getUi(); sheetUi.createMenu('TimeOff') .addItem('Form Setup', 'setUpForm') .addItem('Column Setup', 'createNewColumns') .addItem('Notify Employees', 'notifyEmployees') .addToUi(); }

/**

Creates a new column for a manager to input her approval
of each employee's vacation request. Uses a helper function
to create an additional notification column. */ function createNewColumns() { var sheet = SpreadsheetApp.getActiveSheet(); var lastCol = sheet.getLastColumn(); var lastRow = sheet.getLastRow(); var frozenRows = sheet.getFrozenRows(); var startRow = frozenRows + 1; var numRows = lastRow - frozenRows;

// Creates approval column. sheet.insertColumnAfter(lastCol); sheet.getRange(frozenRows, COLUMN_NUMBER.APPROVAL) .setValue('APPROVAL');

// Sets drop-down menu cells in approval column. var approvalColumnRange = sheet.getRange(startRow, COLUMN_NUMBER.APPROVAL, numRows, 1); var dropdownValues = [APPROVED_DROPDOWN.APPROVED, APPROVED_DROPDOWN.NOT_APPROVED, APPROVED_DROPDOWN.IN_PROGRESS]; var rule = SpreadsheetApp.newDataValidation().requireValueInList(dropdownValues) .build(); approvalColumnRange.setDataValidation(rule); approvalColumnRange.setValue(APPROVED_DROPDOWN.IN_PROGRESS);

// Calls helper function to repeat the above code but for the NOTIFIED column. createNotifiedColumn(); }

/**

Adds a column to allow managers to view which employees
have or have not yet been notified. The value of the cells
is set to 'NOT NOTIFIED' on default & changed accordingly. */ function createNotifiedColumn() { var sheet = SpreadsheetApp.getActiveSheet(); var lastCol = sheet.getLastColumn(); var lastRow = sheet.getLastRow(); var frozenRows = sheet.getFrozenRows(); var startRow = frozenRows + 1; var numRows = lastRow - frozenRows;

// Sets up column properties. sheet.insertColumnAfter(lastCol); sheet.getRange(frozenRows, COLUMN_NUMBER.NOTIFIED) .setValue('NOTIFIED STATUS');

// Sets column's cells to be drop-down menus. var notifiedColumnRange = sheet.getRange(startRow, COLUMN_NUMBER.NOTIFIED, numRows, 1); var dropdownValues = [NOTIFIED_DROPDOWN.NOTIFIED, NOTIFIED_DROPDOWN.NOT_NOTIFIED]; var rule = SpreadsheetApp.newDataValidation().requireValueInList(dropdownValues) .build(); notifiedColumnRange.setDataValidation(rule); notifiedColumnRange.setValue(NOTIFIED_DROPDOWN.NOT_NOTIFIED); }

/**

Creates a calendar event for an employee whose vacation
request has been approved.
@param {String} employeeName Name of employee.
@param {String} employeeEmail Email of employee.
@param {date} startDate Vacation request start date.
@param {date} endDate Vacation request end date. **/ function createCalEvent(employeeName, employeeEmail, startDate, endDate) { var managerCal = CalendarApp.getCalendarById(MANAGER_EMAIL);

// Creates a calendar event. var descriptionText = Utilities.formatString('Your vacation time from %s to %s has been approved. Enjoy!', startDate, endDate); var event = managerCal.createEvent(EVENT_TITLE + employeeName, startDate, endDate, { description: descriptionText, guests: employeeEmail, sendInvites: true, }); }

/**

Sends emails to employees whose vacation time request
was NOT approved.
@param {String} employeeEmail Email of employee. */ function sendRejectionEmail(employeeEmail, startDate, endDate) { // Craft specific e mail body. var emailBody = Utilities.formatString('Your vacation time request from %s to %s has NOT been approved.', startDate, endDate);

// Send email. MailApp.sendEmail(employeeEmail, REJECTION_EMAIL_SUBJECT, emailBody); }

/**

Checks the approval status of each employee and notifies
them of their status accordingly, either through creating
a shared calendar event or sending a notification email.
@param {String} employeeEmail Email of employee.
@param {String} employeeName Name of employee.
@param {String} approvalStatus Manager-set status.
@param {date} startDate Vacation request start date.
@param {date} endDate Vacation request end date.
@return {String} Value of whether or not employee needs to be notified. */ function approvalCase(employeeEmail, employeeName, approvalStatus, startDate, endDate) { var sheet = SpreadsheetApp.getActiveSheet(); var managerCal = CalendarApp.getCalendarById(MANAGER_EMAIL);

// Checks approval status. if (approvalStatus == APPROVED_DROPDOWN.NOT_APPROVED) { // Sends email of disapproval. sendRejectionEmail(employeeEmail, startDate, endDate); return 'NOTIFY'; } else if (approvalStatus == APPROVED_DROPDOWN.APPROVED) { // Creates calendar event. createCalEvent(employeeName, employeeEmail, startDate, endDate); return 'NOTIFY'; } else if (approvalStatus == APPROVED_DROPDOWN.IN_PROGRESS) { return 'DO NOT NOTIFY'; } }

/**

Checks the notification status of each employee and, if not notified,
notifies them of their status accordingly, through use of helper
functions. */ function notifyEmployees() { var sheet = SpreadsheetApp.getActiveSheet(); var lastRow = sheet.getLastRow(); var lastCol = sheet.getLastColumn(); var frozenRows = sheet.getFrozenRows(); var startRow = frozenRows + 1; var numRows = lastRow - startRow; var numCols = COLUMN_NUMBER.NOTIFY - COLUMN_NUMBER.EMAIL;

// Go through every employee's information. for (var i = 0; i < numRows; i++) { var currentStartRow = i + startRow;

// Obtains current employee's values.
var range = sheet.getRange(currentStartRow, COLUMN_NUMBER.EMAIL,
    1, COLUMN_NUMBER.NOTIFIED - COLUMN_NUMBER.EMAIL + 1);
var rangeValues = range.getValues();

// Ensures does not notify twice.
var notifiedStatus = rangeValues[0][COLUMN_NUMBER.NOTIFIED - COLUMN_NUMBER.EMAIL];
if (notifiedStatus == NOTIFIED_DROPDOWN.NOTIFIED) {
  continue;
}

// Obtains necessary variables for notification.
var employeeEmail = rangeValues[0][COLUMN_NUMBER.EMAIL - COLUMN_NUMBER.EMAIL];
var employeeName = rangeValues[0][COLUMN_NUMBER.NAME - COLUMN_NUMBER.EMAIL];    
var startDate = rangeValues[0][COLUMN_NUMBER.START_DATE - COLUMN_NUMBER.EMAIL];
var endDate = rangeValues[0][COLUMN_NUMBER.END_DATE - COLUMN_NUMBER.EMAIL];
var approvalStatus = rangeValues[0][COLUMN_NUMBER.APPROVAL - COLUMN_NUMBER.EMAIL];

// Calls helper function to check approval & notify accordingly.
var notifyKey = approvalCase(employeeEmail, employeeName, approvalStatus,
              startDate, endDate);

// Set values to 'NOTIFIED'.
if (notifyKey == 'NOTIFY'){
  sheet.getRange(currentStartRow, COLUMN_NUMBER.NOTIFIED)
  .setValue(NOTIFIED_DROPDOWN.NOTIFIED);
}

} }

/**

Set up the Vacation Time Requests form, & link the form's trigger to
send manager an email when a new request is submitted. */ function setUpForm() { var sheet = SpreadsheetApp.getActiveSpreadsheet(); if (sheet.getFormUrl()) { var msg = 'Form already exists. Unlink the form and try again.'; SpreadsheetApp.getUi().alert(msg); return; }

// Create the form. var form = FormApp.create('Vacation Time Requests') .setCollectEmail(true) .setDestination(FormApp.DestinationType.SPREADSHEET, sheet.getId()) .setLimitOneResponsePerUser(false); form.addTextItem().setTitle('Employee Name:').setRequired(true); form.addTextItem().setTitle('Start Date:').setRequired(true); form.addDateItem().setTitle('End Date:').setRequired(true); form.addListItem().setTitle('Reason:').setChoiceValues(VACATION_REASONS);

// Set up on form submit trigger. ScriptApp.newTrigger('onFormSubmit') .forForm(form) .onFormSubmit() .create(); }

/**

Handle new form submissions to trigger the workflow.
@param {Object} event Form submit event */ function onFormSubmit(event) { var response = getResponsesByName(event.response); sendFormSubmitEmail(response);

// Load form responses into a new row. var row = ['New', '', response['Emoloyee Email:'], response['Employee Name:'], response['Start Date:'], response['End Date:'], response['Reason:']]; var sheet = SpreadsheetApp.getActiveSpreadsheet(); sheet.appendRow(row); }

/**

Converts a form response to an object keyed by the item titles. Allows easier
access to response values.
@param {FormResponse} response
@return {Object} Form values keyed by question title */ function getResponsesByName(response) { var initialValue = { email: response.getRespondentEmail(), timestamp: response.getTimestamp(), }; return response.getItemResponses().reduce(function(obj, itemResponse) { var key = itemResponse.getItem().getTitle(); obj[key] = itemResponse.getResponse(); return obj; }, initialValue); }

/**

Sends email notifying the manager a new vacation time request
has been submitted.
@param {Object} request Form request details. */ function sendFormSubmitEmail(request) { var template = HtmlService.createTemplateFromFile('new-vacationtime-request.html'); template.request = request; template.sheetUrl = SpreadsheetApp.getActiveSpreadsheet().getUrl(); var msg = template.evaluate();

// Send email to manager. MailApp.sendEmail({ to: MANAGER_EMAIL, subject: 'New Vacation Time Request', htmlBody: msg.getContent(), }); }

Feb 27 '20 21:02 Smshaul