I have a functioning app script to add events to a Google Calendar. Calendar events are correctly created in the calendar, with guest if applicable. Logs show the guest email is being properly recorded. This is my script:
function addEventsToCalendar(e) {
if (e.range.columnStart != 11 || e.range.getValue() == "" || e.range.offset(0, -10).getValue() == "") return;
const rData = e.source.getActiveSheet().getRange(e.range.rowStart, 1, 1, 15).getValues();
const calendarId = 'insertcalenderid'; // Replace with your calendar ID
const calendar = CalendarApp.getCalendarById(calendarId);
let date2 = rData[0][1];
let request = rData[0][2];//psr or clin
let loc = rData[0][3];//event location
let starttime = rData[0][4]; //start time
let endtime = rData[0][5]; //end time
let floatName = rData[0][7];
let status = rData[0][9];
let guestEmail = rData[0][12];
var title = floatName; //event title
var title2 = "Not assigned"
if (date2 !== '') {
var startdate1 = Utilities.formatDate(new Date(date2), SpreadsheetApp.getActive().getSpreadsheetTimeZone(), "MM/dd/yyyy");
var enddate1 = Utilities.formatDate(new Date(date2), SpreadsheetApp.getActive().getSpreadsheetTimeZone(), "MM/dd/yyyy");
var starttime1 = Utilities.formatDate(new Date(starttime), SpreadsheetApp.getActive().getSpreadsheetTimeZone(), "hh:mm a");
var endtime1 = Utilities.formatDate(new Date(endtime), SpreadsheetApp.getActive().getSpreadsheetTimeZone(), "hh:mm a");
// Log values to debug
Logger.log(`Date: ${date2}, Start Time: ${starttime}, End Time: ${endtime}, Title: ${title}, Guest Email: ${guestEmail}, Status: ${status}`);
// Get start date, start time, end date and end time and reformat to correct date and time format for calendar
// Combine date and time for calendar event
var startDatetime = new Date(`${startdate1} ${starttime1}`);
var endDatetime = new Date(`${enddate1} ${endtime1}`);
Logger.log(`Start Datetime: ${startDatetime}, End Datetime: ${endDatetime}`);
if (rData[0][0] !== "" && status !== 'X') {
let event;
let eventID; // Declare eventID in the correct scope
if (guestEmail !== "") {
Logger.log(`Creating event with title: ${title}`);
event = calendar.createEvent(title, startDatetime, endDatetime, {
location: loc,
color: 5,
guests: guestEmail,
sendInvites: true
});
} else {
Logger.log(`Creating event with title: ${title2}`);
event = calendar.createEvent(title2, startDatetime, endDatetime, {
location: loc,
color: 5
});
}
Logger.log(guestEmail);
// Capture the event ID and remove the @google.com part
eventID = event.getId().split('@')[0];
Logger.log(`Event ID: ${eventID}`);
// Write the event ID to column N (14th column, since columns are 0-indexed)
const currentCell = e.source.getActiveSheet().getRange(e.range.rowStart, 15);
currentCell.setValue(eventID);
}
}
}
Everything is working properly, except for no invitation is sent to the email provided. The log shows the correct email. I have tried:
hard-coding the email,
using different emails,
using a different personal account not in my organization to run the script,
using different calendars,
setting calendar permissions to full management permissions,
ensuring that calendar is shown/permission is given to view all details,
running the script through ai,
googling the problem (including stackoverflow)
all to no avail.
I was expecting an email invitation to be sent.
Does anyone have any insight as to why this script would not be sending out the invite?
pianonet is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.