I created a Google Apps Script to extract events from users’ calendars but it’s not working for some users. I did some reading, and apparently, the issue is that getCalendarById is returning null.

This is the code:

function getCalendarAppointments() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = spreadsheet.getSheetByName("calendars");
  var employees = sheet.getRange("A2:A").getValues();
  var resultsSheet = spreadsheet.getSheetByName("Calendar-query-results") || spreadsheet.insertSheet("Calendar-query-results");
  resultsSheet.clear();
  var row = 2;

  employees.forEach(function(employee) {
    var email = employee[0];
    if (email) {
      var calendar = CalendarApp.getCalendarById(email);
      var events = calendar.getEvents(new Date('2024-06-09'), new Date('2024-06-15')); // Adjust date range as needed
        
        resultsSheet.getRange(1, 1).setValue("email");
        resultsSheet.getRange(1, 2).setValue("Title");
        resultsSheet.getRange(1, 3).setValue("Start Time");
        resultsSheet.getRange(1, 4).setValue("End Time");
        events.forEach(function(event) {
        
        resultsSheet.getRange(row, 1).setValue(email);
        resultsSheet.getRange(row, 2).setValue(event.getTitle());
        resultsSheet.getRange(row, 3).setValue(event.getStartTime());
        resultsSheet.getRange(row, 4).setValue(event.getEndTime());
        row++;
      });
    }
  });
}

The first workaround suggested was to make sure I was subscribed to those calendars, which I did but it didn’t seem to resolve the issue.