Search code examples
google-apps-scriptgoogle-sheetsevents

.getGuestList returning "EventGuest" instead of list of attendees

I have tried to fix the following script using codes provided on these forums but nothing has worked so far, I am using the following script to create a list of all the meetings on the calendar but it will not display guests:

function importGoogleCalendar() { 
  var sheet = SpreadsheetApp.getActiveSheet();
  var calendarId = sheet.getRange('B1').getValue().toString(); 
  var calendar = CalendarApp.getCalendarById(calendarId);
 
  // Set filters
  var startDate = sheet.getRange('B2').getValue();
  var endDate = sheet.getRange('B3').getValue();
  var searchText = sheet.getRange('B4').getValue();
 
  // Print header
  var header = [["Title", "Description", "Start", "End", "Ubicación", "Participantes"]];
  var range = sheet.getRange("A6:F6");
  range.setValues(header);
  range.setFontWeight("bold")
 
  // Get events based on filters
  var events = (searchText == '') ? calendar.getEvents(startDate, endDate) : calendar.getEvents(startDate, endDate, {search: searchText});
 
  // Display events 
  for (var i=0; i<events.length; i++) {
    var row = i+7;
    
    var details = [[events[i].getTitle(), events[i].getDescription(), events[i].getStartTime(), events[i].getEndTime(), events[i].getLocation(), events[i].getGuestList(), ]];
      
    range = sheet.getRange(row,1,1,6);
    range.setValues(details);
 
    // Format the Start and End columns
    var cell = sheet.getRange(row, 3);
    cell.setNumberFormat('mm/dd/yyyy hh:mm');
    cell = sheet.getRange(row, 4);
    cell.setNumberFormat('mm/dd/yyyy hh:mm');

   
  }
}
Solution

  • Modification points:

    • I think that the reason for your current issue of .getGuestList returning "EventGuest" instead of list of attendees is due to that the method of getGuestList of Class CalendarEvent returns CalendarApp.EventGuest[]. In this case, the name of Class like EventGuest is used. If you want to retrieve the email address and the status, it is required to use the methods of getEmail and getGuestStatus.

    • In your script, it seems that setValues is used in a loop. In this case, the process cost becomes high. Ref (Author: me)

    • var startDate = sheet.getRange('B2').getValue(); var endDate = sheet.getRange('B3').getValue(); var searchText = sheet.getRange('B4').getValue() can be modified to var [[startDate], [endDate], [searchText]] = sheet.getRange('B2:B4').getValues();.

    When these points are reflected in your script, how about the following modification?

    Modified script:

    function importGoogleCalendar() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var calendarId = sheet.getRange('B1').getValue().toString();
  var calendar = CalendarApp.getCalendarById(calendarId);

  // Set filters
  var [[startDate], [endDate], [searchText]] = sheet.getRange('B2:B4').getValues();

  // Print header
  var header = ["Title", "Description", "Start", "End", "Ubicación", "Participantes"];

  // Get events based on filters
  var events = (searchText == '') ? calendar.getEvents(startDate, endDate) : calendar.getEvents(startDate, endDate, { search: searchText });

  // Display events
  var values = [header, ...events.map(e => [e.getTitle(), e.getDescription(), e.getStartTime(), e.getEndTime(), e.getLocation(), e.getGuestList().map(f => `${f.getEmail()}(status ${f.getGuestStatus().toString()})`).join(",")])];
  sheet.getRange(6, 1, values.length, values[0].length).setValues(values);
  sheet.getRange("A6:F6").setFontWeight("bold");
  if (values.length == 1) return;
  sheet.getRange(7, 3, values.length - 1, 2).setNumberFormat('mm/dd/yyyy hh:mm');
}
    • When this script is run, the guest list is put into cells "F7:F" like emailAddreess(status ###),emailAddreess(status ###),,,.

    References: