Syncing subscribed calendar events to Google Sheets: any tips?

Other Questions Google Sheets
1

Hey everyone! I’m working on a project for our medical residency program. We need to keep track of shifts in Google Sheets but I’m stuck. I’ve got a script that pulls events from regular Google calendars into Sheets. The problem is our residents use Amion for scheduling which creates separate calendar feeds.

I’ve added these Amion feeds to Google Calendar as subscriptions. Now I want to grab those events and put them in our Sheets database. But I can’t figure out how to do it.

Here’s what I’ve tried:

function getEvents() {
  var calendarId = 'primary';
  var startDate = new Date('2023-01-01');
  var endDate = new Date('2023-01-31');
  
  var calendar = CalendarApp.getCalendarById(calendarId);
  var events = calendar.getEvents(startDate, endDate);
  
  var sheet = SpreadsheetApp.getActiveSheet();
  sheet.getRange(1, 1, 1, 3).setValues([['Event', 'Start', 'End']]);
  
  for (var i = 0; i < events.length; i++) {
    sheet.getRange(i + 2, 1, 1, 3).setValues([
      [events[i].getTitle(), events[i].getStartTime(), events[i].getEndTime()]
    ]);
  }
}

This works for the main calendar but not the subscribed ones. Any ideas on how to access those subscribed calendar events? Thanks!

2

I’ve encountered a similar issue when working with subscribed calendars. One approach that worked for me was using the Advanced Calendar Service instead of the standard CalendarApp. To enable it, go to Resources > Advanced Google Services in the Apps Script editor and turn on the Calendar API.

Then, you can modify your code to use Calendar.Events.list() method. Here’s a basic example:

function getSubscribedEvents() {
  var calendarId = '[email protected]';
  var events = Calendar.Events.list(calendarId, {
    timeMin: new Date().toISOString(),
    maxResults: 10,
    singleEvents: true,
    orderBy: 'startTime'
  });
  
  // Process events here
}

This should allow you to access events from subscribed calendars. Remember to replace ‘your_subscribed_calendar_id’ with the actual ID of the Amion calendar feed you’ve subscribed to in Google Calendar.

3

I’ve dealt with a similar challenge in my work coordinating surgical schedules. One workaround I found was to use the Google Calendar API’s ‘calendarList’ endpoint to retrieve all calendars, including subscribed ones. Then you can iterate through them to fetch events.

Here’s a rough outline of the approach:

  1. Use Calendar.CalendarList.list() to get all calendars
  2. Filter for the Amion calendars (maybe by name or ID)
  3. For each Amion calendar, use Calendar.Events.list() to fetch events
  4. Process and add those events to your spreadsheet

This method requires some setup with OAuth and enabling the Calendar API, but it’s quite flexible once implemented. It allowed me to pull in events from various external scheduling systems we use.

Let me know if you need more specifics on the implementation. Good luck with your residency program project!

4

hey grace, have you tried using the calendar API instead of CalendarApp? i think it might let u access those subscribed calendars. you’d need to set up OAuth2 and use the gapi library, but it could work. maybe worth a shot if you’re still stuck!