I have created a Google Apps Script linked to a Google Sheet that includes two functions. The first function successfully pulls details of events from a specified calendar between two dates, where the calendar name, start date, and end date are sourced from cells A2, B2, and C2. The code for this function is as follows:
// Function to retrieve events from Google Calendar.
function fetchCalendarEvents() {
var sheet = SpreadsheetApp.getActiveSheet();
var calendarName = sheet.getRange('A2').getValue();
if (calendarName === '') {
SpreadsheetApp.getActiveSpreadsheet().toast('Please provide a valid calendar name!');
return;
}
var startDate = sheet.getRange('B2').getValue();
if (startDate === '' || isNaN(startDate)) {
SpreadsheetApp.getActiveSpreadsheet().toast('Please enter a valid start date!');
return;
}
var endDate = sheet.getRange('C2').getValue();
if (endDate === '' || isNaN(endDate)) {
SpreadsheetApp.getActiveSpreadsheet().toast('Please enter a valid end date!');
return;
}
var calendar = CalendarApp.getCalendarsByName(calendarName)[0];
var events = calendar.getEvents(startDate, endDate);
var eventData;
for (var i = 0; i < events.length; i++) {
var event = events[i];
eventData = [
event.getTitle(),
event.getDescription(),
event.getStartTime(),
event.getEndTime(),
event.getLocation(),
event.getId(),
'N'
];
sheet.appendRow(eventData);
}
sheet.autoResizeColumn(1);
sheet.autoResizeColumn(2);
sheet.autoResizeColumn(3);
sheet.autoResizeColumn(4);
sheet.autoResizeColumn(5);
sheet.autoResizeColumn(6);
sheet.autoResizeColumn(7);
}
The second function is intended to read the event IDs from column F so that I can update specific events in the sheet. However, I’m encountering an error at calendarEvent.getId()
, stating Cannot call method 'getId' of undefined. (line 118, file 'Code')
.
I understand that the getEventSeriesById()
method might be necessary, but my events are not part of any series. Can I modify single events using this script, or do I need to remove and recreate them to make changes? Below is my second function’s code:
function updateCalendarEvents() {
var sheet = SpreadsheetApp.getActiveSheet();
var startingRow = 6; // First data row to consider
var totalRows = sheet.getLastRow(); // Total rows to consider
var calendarName = sheet.getRange('A2').getValue(); // Retrieve calendar name
var rangeData = sheet.getRange(startingRow, 1, totalRows, 7); // Define data range
var entries = rangeData.getValues();
var calendar = CalendarApp.getCalendarsByName(calendarName)[0];
for (var i in entries) {
var record = entries[i];
var eventTitle = record[0];
var eventDescription = record[1];
var eventStart = record[2];
var eventEnd = record[3];
var eventLocation = record[4];
var eventID = record[5];
var deleteFlag = record[6];
var eventItem = calendar.getEventById(eventID);
if (deleteFlag === 'N' || deleteFlag === 'n') {
if (eventItem) {
eventItem.setTitle(eventTitle);
eventItem.setDescription(eventDescription);
eventItem.setTime(eventStart, eventEnd);
eventItem.setLocation(eventLocation);
}
} else {
if (eventItem) {
eventItem.deleteEvent();
}
}
}
}
I would appreciate any advice or guidance on this issue.