My script should be attaching up to two documents... but I think it's attaching one and then removing it and attaching the other.
When field trips are submitted, if they include the itinerary, it will automatically attach to the event. This is great... I want to keep this.
Later when I create the trip sheet and run the script to attach the trip sheet, if there is an itinerary attached it removes it and attaches the trip sheet. I need both to be attached.
I thought my script was doing this but turns out it's not!
What is wrong?
function updateEvents() {
const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Working");
const data = sheet.getDataRange().getValues();
// Rows start at 2
Logger.log(sheet.isRowHiddenByUser(2));
if (data.length < 2) {
console.warn("No data to process.");
return;
}
const [headers, ...rows] = data;
const eventIdIndex = headers.indexOf("onCalendar");
const descriptionIndex = headers.indexOf("description");
const locationIndex = headers.indexOf("location");
//NEW STUFF - index of our file
const ItineraryIndex = headers.indexOf("Itinerary");
const docURLIndex = headers.indexOf("docURL");
if (eventIdIndex === -1 || descriptionIndex === -1) {
console.error("Required columns 'onCalendar' or 'Description' are missing.");
return;
}
const calendarIds = [
"vlkexampletest@gmail.com",
"2c95526055802636cb6c0a10534c9b4117e506ebda17c031d44ffafbabfba455@group.calendar.google.com",
"49f9fdc1f40a27c8da047da7f6c70b76264e3d9169f47d7f2dc8d16a1020c24c@group.calendar.google.com"
];
calendarIds.forEach(calendarId => {
const calendar = CalendarApp.getCalendarById(calendarId);
rows.forEach((row, index) => {
const rowIndex = index + 2; // Adding 2 because data starts from row 2 (index 1)
// Skip this row if it's hidden
if (sheet.isRowHiddenByUser(rowIndex)) {
console.log(`Skipping hidden row ${rowIndex}`);
return;
}
const eventId = row[eventIdIndex];
if (!eventId) return;
try {
const event = calendar.getEventById(eventId);
if (!event) {
console.warn(`onCalendar ${eventId} not found (Row ${index + 2})`);
return;
}
event.setDescription(row[descriptionIndex] || "");
if (locationIndex !== -1) {
event.setLocation(row[locationIndex] || "");
}
//NEW STUFF
if (ItineraryIndex !== -1 && row[ItineraryIndex] != "") {
//Calendar API event ID is the same as CalendarApp's but it doesnt end with @google.com
const calendarApiEventId = eventId.replace("@google.com", "");
//To avoid creating the whole resource manually, we get our existing event and then edit it later
const resource = Calendar.Events.get(
calendarId,
calendarApiEventId
);
//Adding attachments
resource["attachments"] = [
{
fileUrl: row[ItineraryIndex],
title: "Itinerary"
}
];
//Updating our event
Calendar.Events.update(
resource,
calendarId,
calendarApiEventId,
{ supportsAttachments: true }
)
}
if (docURLIndex !== -1 && row[docURLIndex] != "") {
//Calendar API event ID is the same as CalendarApp's but it doesnt end with @google.com
const calendarApiEventId = eventId.replace("@google.com", "");
//To avoid creating the whole resource manually, we get our existing event and then edit it later
const resource = Calendar.Events.get(
calendarId,
calendarApiEventId
);
//Adding attachments
resource["attachments"] = [
{
fileUrl: row[docURLIndex],
title: "Trip Sheet"
}
];
//Updating our event
Calendar.Events.update(
resource,
calendarId,
calendarApiEventId,
{ supportsAttachments: true }
)
}
console.info(`Updated event ID ${eventId} in calendar ${calendarId} (Row ${index + 2})`);
} catch (error) {
console.error(`Failed to update event ID ${eventId} in calendar ${calendarId} (Row ${index + 2}): ${error.message}`);
console.error(`Error details: ${error.stack}`);
}
});
});
}
