Search code examples
javascriptgoogle-apps-scriptgoogle-sheetsgoogle-calendar-api

How to modify function below to get events from multiple calendars


I am using the below function to extract events from Google Calendar to Google Sheet, is there a way to modify this to extract events from multiple calendars.

function getEvents () {
  
  var ss = SpreadsheetApp.openById("spreadsheetID").getSheetByName("Cal");
  var cal = CalendarApp.getCalendarById("username@email.ca");
   var events = cal.getEvents(new Date("4/1/2022 12:00 AM"), new Date("4/30/2022 11:59 PM"));

  var lr = ss.getLastRow();
  ss.getRange(2, 1, lr-1, 5).clearContent();

  
  for(var i = 0;i<events.length;i++){
    var title = events[i].getTitle();
    var sd = events[i].getStartTime();
    var ed = events[i].getEndTime();
    var loc = events[i].getLocation();
    var des = events[i].getDescription();
    
    ss.getRange(i+2, 1).setValue(sd);
    ss.getRange(i+2, 1).setNumberFormat("mm/dd/yyyy h:mm AM/PM")
    ss.getRange(i+2, 2).setValue(ed);
    ss.getRange(i+2, 3).setNumberFormat("mm/dd/yyyy h:mm AM/PM")
    ss.getRange(i+2, 3).setValue(title);
    ss.getRange(i+2, 4).setValue(loc);
    ss.getRange(i+2, 5).setValue(des);
 
 } 
  
}
 

Solution

  • Try this:

    function getEvents() {
      const ss = SpreadsheetApp.getActiveSheet();
      var lr = ss.getLastRow();
      if (lr > 0) {
        ss.getRange(2, 1, lr - 1, 5).clearContent();
      }
      const ids = ['q289qp9augtnshkn70dkibcc54@group.calendar.google.com', 'jimesteban@jimesteban.com'];//cal ids
      ids.forEach(id => {
        let cal = CalendarApp.getCalendarById(id);
        let events = cal.getEvents(new Date("4/23/2022 8:00 AM"), new Date("4/23/2022 5:00 PM"));
        for (var i = 0; i < events.length; i++) {
          var title = events[i].getTitle();
          var sd = events[i].getStartTime();
          var ed = events[i].getEndTime();
          var loc = events[i].getLocation();
          var des = events[i].getDescription();
          ss.appendRow([sd,ed,title,loc,des])
        }
      })
    }