Fixing a script to update Google Calendar from a Google Sheet

Other Questions Google Sheets
1

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.

2

It seems the problem might be occurring due to the eventItem object being null when retrieving it with calendar.getEventById(eventID). This can happen if the eventID is incorrect or doesn’t exist in the specified calendar. Double-check that the event IDs in your Sheet are correct and match those in the calendar. Also, ensure that the desired calendar is correctly retrieved. If the data in the cells can be updated externally, you might want to handle possible null values to avoid runtime errors using a conditional check before accessing methods on eventItem. For instance, wrapping getEventById(eventID) in a try-catch block can help to gracefully handle such situations.

3

Make sure the calendar actually exists. Also, getEventById only works with primary calendar. For other calendars, use getEvents and match manually. If IDs seem correct, you might have fetch limits, sometimes API gives cutoffs. Check if the APIs are returning all the events you’re expecting.

4

In my experience, double-checking timezones can be crucial when working with Google Calendar events. If the dates and times in your Google Sheet are in a different timezone than your calendar, this can lead to issues with fetching and updating events. Make sure your sheet and Google Calendar are aligned in terms of timezone settings. Additionally, any input errors or typos in event details can cause retrieval problems, so confirm all your event information is accurate and consistent.