In my mind, the script below takes a form submission gathered on a sheet and books it into a calendar (calId
). In reality, however, the script falls apart at var event = thisCalendar.createEvent(
:
Exception: Invalid argument:
booker
.
function calendarUpload() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName("Form Responses 1");
var Avals = ss.getRange("A1:A").getValues();
var lastRow = Avals.filter(String).length;
Logger.log(lastRow);
for (var i = 2; i <= lastRow ; i++) {
var approvalStatus = sheet.getRange(i,1).getValue();
var name = sheet.getRange(i,8).getValue(); //Event name.
var description = sheet.getRange(i,9).getValue(); //Event description, agenda.
var date = sheet.getRange(i,5).getValue(); //Event date.
var formattedStart = Utilities.formatDate(new Date(date), 'Europe/London', 'MMMM dd, yyyy');
var startTime = sheet.getRange(i,6).getValue(); //Event starting time.
var formattedSTime = Utilities.formatDate(new Date(startTime), 'Europe/London','hh:mm');
var endTime = sheet.getRange(i,7).getValue(); //Estimated end time of event.
var formattedETime = Utilities.formatDate(new Date(endTime), 'Europe/London','hh:mm');
var guests = sheet.getRange(i,15,1,10).getValues(); //Event guests by email address.
var staffMember = sheet.getRange(i,10).getValue(); //Meeting with... Determines Calendar ID (CalID).
var calId = sheet.getRange(i,11).getValue(); //Calendar.
var booker = sheet.getRange (i,3).getValue(); //The person booking the meeting.
var bookerEmail = sheet.getRange(i,4).getValue(); //Email booker, adds to guest list.
var eventStatus = sheet.getRange(1,1,i,12).getCell(i,12);
//Create eventName based on reason for meeting.
if (name == "one-on-one"){
var title = "ℳ " + booker + " and " + staffMember;
} else {
var title = name
}
Logger.log(eventStatus);
Logger.log(title);
Logger.log(formattedStart);
Logger.log(formattedSTime);
Logger.log(formattedETime);
Logger.log(guests);
var startDateandTime = (formattedStart+" "+formattedSTime);
var endDateandTime = (formattedStart+" "+formattedETime);
Logger.log(startDateandTime);
if (approvalStatus == "Approved" && eventStatus.isBlank()){
var thisCalendar = CalendarApp.getCalendarById(calId);
Logger.log('calId: '+calId);
Logger.log(thisCalendar );
var event = thisCalendar.createEvent(
title,
new Date(startDateandTime),
new Date(endDateandTime),
{guests: guests && bookerEmail, description: description});
Logger.log('Event Series ID: ' + eventSeries.getId());
var setEventStatus = sheet.getRange(i,12).setValue('Event Series ID: ' + event.getId());
} else {
Logger.log("No Events Found");
}
}
}
The logs seem to show that things go smoothly. Is there anything that you'd do differently? What can I do about the Exception error?