I have the following Google Sheets Apps Script to extract calendar invites to a Google Sheet, however, I am trying to make some adjustments that I am struggling to find a solution for:

function getEvents() {

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Sheet1");

  var start_time = sheet.getRange("A2").getValue();
  var end_time = sheet.getRange("B2").getValue();
  var id_cal = sheet.getRange("P5").getValue();

  var cal = CalendarApp.getCalendarById(id_cal);
    var events = cal.getEvents(new Date(start_time), new Date(end_time));

  for (var i = 0;i<events.length;i  ){
    
    var title =  events[i].getTitle();
    var start_time =  events[i].getStartTime();
    var end_time =  events[i].getEndTime();
    var des =  events[i].getDescription();
    var vis = events[i].getVisibility();
    var guestlist = events[i].getGuestList();

    sheet.getRange(i 5,1).setValue(title);
    sheet.getRange(i 5,2).setValue(start_time);
    sheet.getRange(i 5,3).setValue(end_time);
    sheet.getRange(i 5,4).setValue(des);
    sheet.getRange(i 5,5).setValue(vis);
    sheet.getRange(i 5,6).setValue(guestlist);
  }

  Logger.log("Events have been added to the Spreadsheet");
}

How do I amend this script to:

• Extract from several calendars that I have the email addresses in a range of cells (Z1:Z25) instead of just 1 calendar. I have attempted changing the range, but it only pulls the calendar from the top cell.
• Include all accepted/pending attendees (including the organiser). I have attempted to add .getGuestList, but this returns 'EventGuest'
• Include the calendar that event was taken from

CodePudding user response：

Try something like this:

function getEvents() {
  var ss = SpreadsheetApp.getActive();
  var sh = ss.getSheetByName("Sheet1");
  var start = sh.getRange("A2").getValue();
  var end = sh.getRange("B2").getValue();
  const ids = [];
  CalendarApp.getAllCalendars().forEach(c => ids.push(c.getId()));
  ids.forEach(id => {
    let cal = CalendarApp.getCalendarById(id);
    let events = cal.getEvents(new Date(start), new Date(end));
    for (var i = 0; i < events.length; i  ) {
      var title = events[i].getTitle();
      var start = events[i].getStartTime();
      var end = events[i].getEndTime();
      var des = events[i].getDescription();
      var vis = events[i].getVisibility();
      var guestlist = events[i].getGuestList();
      let row = sh.getLastRow()   1
      sh.getRange(sh.getLastRow()   1, 1,1,5).setValues([[title,start,end,des,vis,guestlist]]);      
    }
  })  
  Logger.log("Events have been added to the Spreadsheet");
}

I'll leave it up to you to add the additional items as this is not a venue for getting free scripts.

CodePudding user response：

I believe your goal is as follows.

• You want to retrieve the events from multiple Calendars by the calendar IDs which are retrieved from the cells "Z1:Z25" of the Spreadsheet.
• As the data, from Include all accepted/pending attendees (including the organiser)., you want to add the user's email addresses.
• You want to add the calendar IDs as the additional value.

Modification points:

• In your script, only one Calendar ID is used. In order to achieve your goal, it is required to retrieve the calendar IDs from the cells "Z1:Z25".
• About I have attempted to add .getGuestList, but this returns 'EventGuest', the method of getGuestList() returns EventGuest object. I think that this is the reason for your issue.
• In your script, setValue is used in a loop. In this case, the process cost becomes high. Ref

When these points are reflected in your script, it becomes as follows.

Modified script:

function getEvents() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Sheet1");
  var [start_time, end_time] = sheet.getRange("A2:B2").getValues()[0];
  var dates = [new Date(start_time), new Date(end_time)]; // If the cell value is date object, you can also use [start_time, end_time]
  var ids = sheet.getRange("Z1:Z25").getValues().reduce((ar, [z]) => {
    if (z) ar.push(z);
    return ar;
  }, []);
  var values = ids.flatMap(id_cal => {
    var cal = CalendarApp.getCalendarById(id_cal);
    var events = cal.getEvents(...dates);
    return events.map(e => {
      var title = e.getTitle();
      var start = e.getStartTime();
      var end = e.getEndTime();
      var des = e.getDescription();
      var vis = e.getVisibility().toString(); // or e.getVisibility()
      var guestlist = e.getGuestList().map(f => f.getEmail()).join(",");
      return [id_cal, title, start, end, des, vis, guestlist];
    });
  });
  sheet.getRange(5, 1, values.length, values[0].length).setValues(values);

  Logger.log("Events have been added to the Spreadsheet");
}

• When this script is run, the calendar IDs are retrieved from "Z1:Z25" of "Sheet1". And, the events are retrieved, and the values are retrieved from each event. In this case, the calendar ID, event title, start time, end time, description, visibility, and guest email addresses are put on the sheet in order.

  • If you want to change the order of values, please modify return [id_cal, title, start, end, des, vis, guestlist];.

• If you want to change the user's email to the user's name, please modify f.getEmail() to f.getName().

Note:

• This modified script is a simple modification. So, please modify this for your actual situation.

References:

• map()
• Class EventGuest